Backups and Restores, Disaster Recovery, Tools

Ola Hallengren’s DDBoost Backups

The already awesome Ola’s Maintenance solution for SQL Server became more awesome with his latest release. If you are not aware of Ola’s maintenance solution, you should definitely check it out. In the July 2018 release, quite a few enhancements were introduced. My personal favorites are ability to add percentage of modified rows to update stats, resumable index rebuilds support and support for EMC DDboost(Data Domain Boost). In fact I was badly waiting for DDboost support last year(we were using DDboost to backup our VLDBs). Unfortunately, DDboost was not something Ola’s solution suported at that time and what I ended up was writing up my own stored procs calling “dbo.emc_run_backup” and “ddbmexptool.exe” based on our Lockbox settings and scheduled the jobs as needed. Well, I got it worked without any issues but had to spend quite a bit of time to understand all the bells and whistles of DDboost and come up with my own procs and error logging.

If you happen to have any plans evaluating DDBoost for SQL backups now or in near future, well you have an awesome solution already available. All you need to do is pass ‘DATA_DOMAIN_BOOST’ as a parameter for third-part backup software in Ola’s backup job and specify below parameters.

Image source: https://ola.hallengren.com/sql-server-backup.html

References:
https://ola.hallengren.com/

A big shout-out to Ola! Cheers, Happy weekend.

Advertisements
SSMS, Tools

Dedicated Admin Connection(DAC) from SSMS

DAC is probably the most useful tool for troubleshooting SQL Server in few scenarios. Basically, It can be used as a back door entry in case the server goes unresponsive and doesn’t accept any new connections. So, how to make a DAC to SQL Server Instance? Preferred way is to use SQLCMD by issuing -A switch. But few folks might prefer SSMS over SQLCMD. In this blog post let’s see how to make a dedicated admin connection from SSMS.

Well, all you need to do is prefexing “admin:”to your instance name. I’ve a default SQL Instance(SQLTREK2), so in order to make a dedicated admin connection, all I’ve to do is use “admin:SQLTREK2” as my connection string. Okay, Let’s see what my SSMS does when I do that.

Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

If you wonder what multiple connections its talking about, It is referring to Object Explorer(Yes, OE establishes its own connection). But there can be only one DAC session at any given time. So, how to get around this issue in SSMS?

Method 1:
Creating DAC session by initiating connection from query pane. In the below screenshot, you can see I have a query pane which is currently disconnected.

Right click in the query pane and go to connection and click connect(providing admin:Instance name)

Well, SSMS created a pop-up which says it failed to connect, But I would like you folks to focus on the status bar where it did established a connection using admin:SQLTREK2. So basically this window is your DAC session.

Method 2:
By using ‘Database Engine Query’ connection. By using this method, you can avoid the error message which SSMS returned(shown in the above screenshot). All you have to do is click on Database Engine Query button right beside ‘New Query’ and make a connection. See below screen shots.

Voila! That was easy right….

Bonus points to you if you have noticed difference between ‘connect to server’ and ‘connect to database engine’. See below if you haven’t.

From Object Explorer: (DAC fails)

DB Engine query: (DAC Succeeds)

Hope this helps! Happy holidays…!

miscellaneous, Tools

Idera’s SQL Extended Events Profiler – concise and Elegant tool for SQL DBAs!

In this blog post, I would like to introduce all of you to a very cool and concise Free tool from Idera.

Note:  I Do not work for Idera. Idera has nothing to do with this blog post. Am expressing just my opinion with my experiences with this free tool.

Okay, so….what is this tool al about?

If you are a SQL Server DBA, you might have already been in a situation where you had to run SQL Server trace on your SQL Server(s) per a developer request or for investigation purposes or some thing else.  Profiler, which comes with SQL Server installtion is a very heavy tool, especially if you are  not filtering out your events when running your trace. To avoid any overhead this graphical tool(profiler) causes, we could use “Server Side tracing” which basically runs the trace server side and stores the results either in a file or a table without the need for using profiler(Which constantly displays the results, which is very resource intensive). Impressive…But not as impressive as “Extended Events”. MSFT introduced “Extended Events” in SQL Server 2008 which is the low level/High performance eventing solution. Slowly and steadily…I am seeing people moving away from traces and Profiler which is nice.  If you are a SQL Server Profiler person, I’ve got a sad news for you, Profiler is depricated in SQL Server 2014(Instead, microsoft is asking you to use Extended events session(s) which is embedded in your SSMS under management Node). You might not like this initially, especially the way how you’ve to interact with XEvents from SSMS. This is where this free tool from Idera comes handy. This is very similar to how you would interact with Profiler(Selecting templates, events, filtering columns etc), but instead of starting a trace, it pulls info from Extended events which are very light weight and has significantly less over head on your SQL Server.

See below Screenshots on what am talking about…If you are already familiar with Profiler, this doesn’t need any further explanation on how to get started with this free tool.

1

Once connected, choose your template

2

Filter out columns as needed…

3

As you can see this could be very helpful tool to track down few things on your SQL Server without hindering it’s performance. Once you’ve got what you are lookin for, you can export the results as well.  This is a free tool, you can download it from idera and play with it and see if it meets your requirements.

PS Discussing the true power of Extended events which microsoft introduced for us is way beyond scope of this blog post.

Cheers!

SQL Server 2012, SQL Server 2014, Tools

SQL Server Discovery Report – Know What SQL components you’ve got Installed at a glance

Let’s assume you’ve got a new SQL Server under your support as a primary DBA and you’ve decided to investigate and document all the SQL Server components/features installed on this Server. What are your options? Will you go to Config manager and look at Services for Installed Services. Well, what about Client components? You can pull build levels/Version(SP/CU) easily for your database engine using T-SQL, what about other services? What about your client tool(s) version…Chances are they will be same as your DB Engine(but they could be different based on how and who patched the SQL Servers)? What if you’ve got multiple Instances of SQL Server with different versions…?

Well, we’ve something called “Installed SQL Server features Discovery Report” under Tools in your Installation Center which provides all this information in a single place 🙂 .  Go to the Installation Center from your start->All Programs->Microsoft SQL serverXXXX->Configuration tools->SQL Server Installation Center. Go to Tools and click on discovery report as shown below.

4

After few seconds, I got below report which gives me basic information on what all I’ve got Installed related to SQL Server on this Server with the build numbers, Edition, clustered/StandAlone etc all at single shot (You can see I’ve got 2 Named Instances, 2008 and 2012) which is pretty useful IMO.

5

Hope you guys find this nifty tool useful! Cheers…

Generic Errors, Tools, Windows

The Network folder specified is currently mapped using a different user name and password

Today I was trying to map a network drive on my laptop and received a message saying “The Network folder specified is currently mapped using a different user name and password“. Well, I thought there is one and went into “My Computer”. interestingly….I don’t see it! Well, I rebooted the laptop hoping it would drop any hanging hidden cables. Unfortunately it didn’t do the trick.

BTW, My laptop already has 2 mapped drives(Y$ and Z$) and I’m trying to create  a 3rd one pointing to another share on completely different file server.(See below screenshot) 

1_a

Now, I tried issuing “NET USE” command to see if it shows anything useful. Surprisingly It said, I’ve 3 mapped drives(3rd one being the one which I’m trying to map without any Drive letter being assigned!!!). See below Screenshot…

1_b

As you can see there’s one more thing(well, without drive letter assignment) and that’s the exact share which I’m trying to create as a mapped drive on my laptop. Now I know the issue. All we need to do is to Delete that “Hanging from Nowhere” Mapped Drive using “NET USE” and retry to map it.

Syntax for deleting mapped drive using NET USE:

NET USE /DELETE \\My_ServerName\My_Sharename

Boom….It dissapeared as you can see below.

2

Notice in the above screenshot, that “magical and annoying” share got deleted succesfully and only Y$ and Z$ are present as expected.

Now, I was able to map that share to a new drive without any issues…Hope this helps if you get into same issue. Cheers 🙂

Tools

SQL Live Monitor – Great Free Monitoring tool from Codeplex!

How many times do you use DMV’s daily supporting your SQL Server environments? How many of you rely on 3rd party tools for looking at the current snapshot of your SQL Server? Let me say this, I use DMV’s a lot even though I’m provided with fancy 3rd party monitoring tools. Each approach has it’s own pros and cons! Let’s assume our shop has no 3rd party tools for monitoring and we are just relying on native SQL Server mechanisms for all our monitoring. In that case, I can say 90% of the monitoring/Gathering Info will be done just using DMV’s. What if we’ve a free tool which displays the DMV results(of course, not everything..but all the useful info at a single glance)  in a pictorial representation? That would be awesome right?? Well, most of the cases, I feel seeing something visually is much more self explanatory and efficient rather than just looking at figures and numbers. YMMV!

SQL Live Monitor can do the same for you. It’s a free tool available from CodePlex community. This tool don’t even require any Installation. It’s just an executable(222 KB) which runs on fly without installing anything on your Servers. Isn’t it cool??

Let me show you how it looks like when I run against one of my Lab Servers…I copied SQL Monitor exe to my Server as you can see below and I just double clicked on it….No other Fancy Stuff needed!

Immediately I was welcomed with the following Screen.

As you can see initially it’s not connected and It’s asking for SQL Instance Name, whether it’s a cluster or Standalone, If it’s clustered…what’s the current active node for your Instance….etc. In my case, It’s a standalone box. Once I selected my Driver(In my case it’s 2008 Native Client) and provided with Instance name and clicked Start…It is providing me all the cool info as you can see below…

Awesome….! Undoubtedly this is one of my favorite tools from codeplex 🙂 Yes, this is not as fancy as other 3rd party tools out there, but still it does the justice and serve the purpose with much much less pain!

I hope this tool will be helpful to atleast few of you while investigating your Servers….Btw, you can download this for free from http://sqlmonitor.codeplex.com/

Tools, Windows

MAP(Microsoft Assessment and Planning Tool kit) 6.5 for SQL Server Discovery

Hello Folks! In this Blog Post let’s see how to make use of MAP tool for discovering all of your SQL Servers in your Environment. MAP is one of the wonderful tools(free) provided by Microsoft, which basically scans all over your Network and present you with a wonderful report on all of your Servers. They could be Windows Servers, Linux Servers, SQL Servers, Oracle, MySQL. Many companies use this fabulous tool for planning future road maps(Could Be Server Upgrades, Desktop Upgrades, MS Office Suite upgrade and what not), consolidations, upgrades, Licensing etc. In My Organization, we are planning to move on to SQL 2012 very soon and we need a report which basically gives us a clear and simple picture of our current SQL Server Environment and I was assigned to achieve this goal 🙂

Sooo….., let’s get started. First thing you’ve to do is download MAP ToolKit from below location

http://www.microsoft.com/en-us/download/details.aspx?&id=7826

Once downloaded, choose a Machine in your Domain where you want to Install MAP tool and start Discovery.

Note: MAP tool Installs SQL Server Express by Default for storing it’s Results.

Demo:

I’ve chosen “Mirror_Stand” machine in My Lab Domain to Install MAP. I copied over executable  to this box as the 1st step. Right click on the “Microsoft_Assessment_and_Planning_Toolkit_Setup.exe” and choose Run as Admin and you should be seeing the following Screen. (Make sure to Install all the Prereq’s. If not, you’ll be welcomed with an ugly X message as shown below!)

Once I finished meeting all the Prereq’s, I’ve opened the exe and now I’m welcomed with the below Screen 🙂

Click Next-> Accept License Terms and Next->Select your Installation Folder(I left it to default) and click Next. Now comes the interesting Screen where it asks for how do you want to Install SQL Express(see below Screenshot).

If your Box is connected to Internet, Just select the 1st Radio Button. If not, select 2nd option and provide it with the SQL express Installation media path.  I’ve chosen 2nd option and provided path for my SQL Express 2008R2 as shown below.

Once you read and accept the license terms, you should see a similar screen as below.(In My case, I already have a SQL Server Installed on this Machine, hence you can see “New Instance” being stated by the Installation wizard).

Click on Install and go and grab a cup of coffee :). It’ll take a while!  As you can see, my Installation is successful now.

It Installed SQL Express Edition Instance on my machine with Name “MAPS”. See below Screencap

We are done with the Installation. Now, let’s see how can we discover SQL Servers across our Domain using this Tool.

Step1: You will be welcomed with the below screen where you’ve to create an Inventory DB(I named it as “SQL_Inven”) for the First time. (You can create separate Databases for separate Discoveries, eg: One DB for SQL Servers, another for Exchange, another for VMWare Servers etc).

Step2: Select on Databases and Choose to perform an Inventory as shown below.

Step 3: Select SQL Servers to discover your SQL Servers as shown below.

Step 4: Choose a Discovery Method. I’ve left to default to AD as you can see below.

Step 5: Provide domain Credentials and select which parts of your AD Forest to scan for as shown below.

Once you proceed upto Finish, it automatically starts gathering info as per the options you’ve chosen. It will take a while for gathering all the required info…

Once it finished, you can see a neat and very useful summary of MAP assessment of your SQL Servers as shown below.

Don’t say it’s useless…It’s actually wonderful Summary of your Environment. For detailed Reports, gather the performance metrics as well and click on “Generate Report/Proposal” on your Tasks Pane to your Right. Once finished it’ll create couple of excel Reports which are very very detailed and useful…You’ll find those reports in your documents folder. In My case, it is “C:\Users\sreekanth\Documents\MAP\SQL_Inven”

Nice thing is, It’s agent less. All it needs is an Account name which has access to the Servers and the Servers should be Up and Running while It scans.

Hope this is informational and useful. Cheers!…