How to add the dedicated Configuration Server in the Centralized Management Solution

I’ve been working lately on so many different presentations and issues, that I rarely have time to take a note to what I’ve wanted to write you about. Anyway, I managed to find some free time and a very interesting case to share Smile – how to add an instance, that is dedicated as Centralized Management Instance (Configuration Server) to the registered instances so this instance can actually be part of the entire CMS environment. The default state of SQL Server does not let you register an instance in the CMS if this very instance is the same as the Configuration one i.e. if I have an instance SQLINSTANCE3 on my server, that is dedicated as CMS – I cannot register it in the CMS itself:


The error itslef is: “You cannot add a shared registered server with the same name as the Configuration Server. (.Net SqlClient Data Provider)”

As I really do not have the opportunity to use another instance that is just dedicated for Configuration Server and the solution I am implementing is working only with CMS Groups for evaluating policies, I needed some option that I can include the CMS Instance in the CMS groups.

Well this turned out to be very easy Smile – just create an Alias in the SQL Server Configuration Manager that is pointing to the dedicated CMS instance and then register the Alias in the CMS. Yep…that easy!

Now I am able to evaluate policies on all of my instances without loosing the CMS Configuration Server, which also should be evaluated as it is a full-blown instance after all!

By Ivan Donev Posted in DBA Tagged

How to get restore history in Microsoft SQL Server

Have you ever wondered how to extract restore history from your SQL Server instance? I believe the answer is yes. Well, so have I! And so far I have been using two different methods for getting what I want.

Number 1: the standard report in SSMS – right click on the database –> Reports –> Standard Reports –> Backup and Restore Events


Number 2: using the below query:

	rh.destination_database_name as 'Database Name',
	rh.[user_name] as 'Username',
	CASE rh.restore_type
		WHEN 'D' THEN 'Database'
		WHEN 'F' THEN 'File'
		WHEN 'G' THEN 'Filegroup'
		WHEN 'I' THEN 'Differential'
		WHEN 'L' THEN 'Log File'
		WHEN 'V' THEN 'Verifyonly'
		WHEN 'R' THEN 'Revert'
	END as 'Restore Type',
	CASE rh.[replace]
	END AS 'Database Replaced',
	rh.restore_date as 'Date Restored',
FROM msdb..restorehistory rh
inner join msdb..restorefilegroup rfg 
	on rh.restore_history_id = rfg.restore_history_id
inner join msdb..restorefile rf
	on rh.restore_history_id = rf.restore_history_id
order by rh.restore_date desc


This script is based on the queries in the report from SSMS and utilizes three system tables in msdb – restorehistory, restorefile and restorefilegroup. These three tables are quite convenient in getting the info you want and easily transfer it to Excel or something else and make it “detailed report” ready Smile

By Ivan Donev Posted in DBA

Webcast by Brent Ozar: “How to prove it’s a SAN Problem”

I just found a very cool webcast by Brent Ozar (blog|twitter) in which he shows how we can really dig-out the ugly truth around SAN storage and its performance. Probably you all know that the issue is not in the SAN, it is in your SQL Server…at least before this brilliant video (and script banks provided in the post).

The post and the video can be checked out here

So much said in just 45 minutes – this webcast is just amazing. I will definitely use it as a starting point whenever I have similar issues Smile 

Enjoy the video and make sure to bookmark all resources provided in the post!

What’s new in SQL Server “Denali”–database restores using SSMS–part 2

In my previous post I shortly introduced you some of the new cool possibilities of SQL Server Management Studio in “Denali”. Now I would like to continue with this and show you a bit more about the database restore part (last time I just showed you how to restore a page). First lets open the Database restore GUI


The windows that opens is quite similar to the one we’re so used to but with several differences:


In this dialog box I chose to restore database TestBR to the last backup taken. This database is set to Full recovery with Full DB backup on every hour, Diff on every 15 minutes and TLog backup on every 5 minutes (this is for tests sake, do not think that this is my standard approach for implementing a backup strategy Open-mouthed smile)

So, I want to restore my database to the last point possible. Till now that usually means that we have to take manually tail-log backup and then proceed with manual (again) restoring either using the GUI or script writing. Well…in Denali this is done automatically for us. We just have to choose the point to which we would like to recover. Based on that you have a Restore plan created where you can see in very user friendly table what are you going to restore, from what source, etc. In addition to that, on the top of that same window you have a notification message which says that a tail-log backup will be taken of the source database. Well there it is! – you have it all automated, you just need to worry about the availability of the restore media and its integrity Smile

There is one more cool feature on this screen – the Timeline thingy Smile It is used of you would like to restore not to the last backup taken but to a specific point in time in the past.


In my case I ‘d like to restore the database to couple of minutes back. After I hit the Timeline button I automatically get this colorful timeline pointing out where I have Full backup taken, where is my Diff and what exactly history I have covered by TLog backups. So my restore point is the red line and it is after my last TLog backup taken. SSMS automatically adds a tail-log backup (check the message on the top of the screen).


This backup is then immediately added to the restore plan. Amazing! Besides that almost everything is done for you (and you do not have to remember to take that tail-log backup), you get and a pretty good visualization of what you want exactly to do and see what your backup strategy is covering.

Once we setup the restore plan, there are couple of other options we have to take care about. On the files tab we can choose where our database files will physically be restored to. You can change the physical path for each file individually or you can do it as “mass” operation by checking the “Relocate all files to folder”.


This is extremely useful…especially when you are doing database refreshes.

And finally – the Options tab:


Here we also have quite a lot enhancements. The Recovery state is switched to a combo box leaving space for the rest of the stuff( you remember how it is in pre-Denali SSMS – all three options with their simple explanation placed on the dialogue box taking up almost the whole space).  After choosing the recovery state, you have one more new cool option introduced – the “Close existing connections” Options. This is THE THING! Smile I have quite a lot experience with trying to get rid of all users using this database. And besides scripting the restore with adding some ALTER DATABASE <> SET SINGLE_USER it is quite difficult to perform the restore through the GUI. Well now with this checkbox everything seems easier. Besides – this checkbox is doing exactly as we are used to do it manually – before the restore statements it adds a ALTER DATABASE <> SET SINGLE_USER WITH ROLLBACK IMMEDIATE and after the whole restore is completed – ALTER DATABASE <> SET MULTI_USER. Cool, huh? Smile

Well that is pretty much it! A lot of new stuff easing the DBAs life especially the ones that are just loving the GUI and hate scripting Devil

What’s new in SQL Server “Denali”–database restores using SSMS–part 1

After the release of SQL Server Code name “Denali” CTP3 I’ve been playing a lot with it and one of the things that stroke me was the new amazing features concerning database restores using SQL Server Management Studio. You are aware that the old SSMS was capable of doing a ton of stuff but there was also another ton of stuff that it was just not able to do (or suggest you to do). So let me begin with my short introduction to what I have found so far:

Point one is the ability to restore PAGE from the GUI. So far PAGE restores were only for those who can script Smile. Well now we have pretty neat screen where we can do it just with some lame clicking Open-mouthed smile


I used one of the corrupted database samples of Paul Randal (blog | twitter) which has a corrupted page (marked as suspect after a DBCC CHECKDB run). the PAGE restore window is a bit rough but for emergencies it will do the job:


After choosing the Database, SQL Server automatically extracts the info from table msdb.dbo.suspect_pages and populates the Pages window. You can enter additional pages if you want to using Add button. The tail-log backup is something new that is introduced in the GUI (you will see it and in database restore part) and this section is where you put the filename of the tail-log backup which is going to be performed during the restore. The bottom part is the Backup sets where you can see the last backups available.

In my next post I will try to show you the new stuff around Database restores so stay tuned! Smile

What is new in SQL Server 2008 R2 Service Pack 1

I just patched my dev instance with Service Pack 1 for SQL Server 2008 R2 and in this post I will share some first impressions and facts about it.

After you apply the patch the first thing that comes to your attention is the version of the SQL Server. The new version of R2 with SP1 is 10.50.2500. I have to say I like that number Smile

Next thing taken out from BOL and the SP1 release notes is the modified DMV sys.dm_exec_query_stats. In SP1 we have four new columns introduced: total_rows, last_rows, min_rows, max_rows. Those columns provide information on returned rows statistics of a plan since its been last compiled.

There are couple of new interesting DMVs which might come handy from time to time:

sys.dm_os_windows_info –> returns information on Windwos release, service pack level and language

sys.dm_os_volume_stats –> this is a new DMF which returns information on the partitions SQL databases are hosted on. The valuable info here is the new possibility to check the free space on that partition.

   vs.volume_mount_point, -- e.g. C:\
   vs.file_system_type,  -- e.g. NTFS
   db = DB_NAME(f.dbid), 
   [file] = f.[name],
   file_size_MB = f.[size] / 128,
   drive_size_MB = vs.total_bytes/1024/1024, 
   drive_free_space_MB = vs.available_bytes/1024/1024, 
   drive_percent_free = CONVERT(DECIMAL(5,2), vs.available_bytes * 100.0 / vs.total_bytes),
   sys.sysaltfiles AS f
   sys.dm_os_volume_stats(f.dbid, f.fileid) AS vs
   f.dbid < 32767
   drive_percent_free DESC,

*The above script is taken from the original post of Aaron Bertrand without any change whatsoever

sys.dm_server_registry –> this one is AMAZING. It has been a nightmare if you want to check any SQL Server related registry setting. Now you are able to check many of them just using this simple DMV

sys.dm_server_memory_dumps –> this one is keeping info on SQL Server dumps created during a crash so you can find easily the relevant info on the dump such as filename, when it was created and so on. At least now you do not have to browse to the error log folder and browse through all the files there looking for the one created around the crash you’ve been investigating.

sys.dm_server_services –> another just marvelous new DMV! With this one you can check all running services on the given server, their startup type, startup account, startup time and so on. Just keep in mind that this DMV just lists SQL Server and SQL Server Agent services.

sys.dm_os_sys_info –> this one is not new but has two new very important columns concerning virtualization – virtual_machine_type and virtual_machine_type_desc.

The rest of the stuff is for Extended Events and so far this is not my strong side so I will not just copy/paste the new stuff here trying to impress you Smile

Having said that I already have some great improvements in scripts I am regularly using in reports or ad-hoc information collection. The bad thing is that I still cannot lay hands on many (or more than one actually) SQL Server 2008 R2 SP1. I guess that is soon to be changed!

SQL Server 2008 R2 Service Pack 1 Released for download

Following the release news around SQL Server it seems that this week is quite busy with the CTP3 of SQL Server Code Name “Denali” and now the brand new Service Pack for SQL Server 2008 R2.

You can download it here.

So what is new in SP1? Well I did not have time to install it (still downloading Smile ) but according to some articles there is going to be a change in the DMVs we are so used to and keen on. One of them would be “sys.dm_exec_query_stats DMV which is extended with additional columns to improve supportabilities over troubleshooting long-running queries”. Of course there is and quite a long list with fixed bugs but I will leave to you to check them out here.

SQL Server performance and troubleshooting tools, tips and tricks

I’ve planned to write a series of post regarding different tools which you can use for performance analysis and troubleshooting but tonight I found that there is already such series posted. I have to say it is pretty neat and has a lot of amazing things to read and get to know. This includes RML Utilities, XPerf, Process monitor, Debug diagnostics and Crash rules. So instead of covering pretty much the same, I’d like to share the round-up post of the series so you can enjoy it.

Troubleshooting SQL: Tools Tips and Tricks round-up

I can only add to this that you can also consider PAL 2.0 (PAL@Codeplex) which  can help you directly analyze performance counters logs and prepare a template for counter collection. Also there is one more thing that I completely love about this tool – almost all counters have thresholds defined according to best practices and added explanation with reference links for each counter. The tool is pretty straight forward and after analysis it provides you with a report with marked problematic areas (IO, CPU, Memory or network).

So get introduced to the post I shared with you and all the tools, tips and tricks it provides and have a look at PAL 2.0 Tool which I think you will find very useful!


Today I am going to share a completely new tool that has been released last couple of weeks. The tool is called “ PSSDIAG/SQLDIAG Manager”. The tools is aiming at providing graphical interface for configuring custom collection sets for SQLDIAG and PSSDIAG utilities. Those two utilities are used for collecting performance and statistic data by Microsoft. I will talk further more on those tools, their aim and how to use them, but it is going to be in another post. For now I will stick to the new tool I am completely thrilled by.

I have experienced a lot of difficulties configuring any of the predefined .XML files used for SQLDIAG collection and now when I have a graphical interface the life just became easier. So let’s start with the quick introduction. The tool has a single screen where you can configure everything:


On the top left we have the menu for choosing platform type – x86, x64 or Itanium. On the top of the screen you can choose the version of the SQL Server that you are preparing the configuration. Of course so far we have SQL Server 7.0, SQL Server 2000, 2005 and 2008. Hopefully we will have soon and SQL 11Winking smile

I was kind of amazed seeing on top left corner a small edit box for the MS Case that the configuration is prepared for. This will not be used by the general DBA however it surely shows that the tool is the same as the one MS Engineers are using (used or modified).

Next we have the connection part:


The important thing here is the Machine name and Instance name configuration. It is important to enter specific instances if you are troubleshooting a server with several instances. Machine name can come useful when you are collecting information for SQL clustered instance.

The other three sections of the screen are for configuring Performance counters collection, predefined diagnostics and traces for DB Engine and Analysis Services engine. I still have not played enough with those in order to share with you how and what but I believe its going to be easy enough for you to check that out by yourself Smile

Once you have the configuration in place you can proceed with saving it. The screen is as follows:


You have the possibility to just have the XML configuration file and use it directly or generate a CAB file which will include everything needed for immediate execution and starting of the collection on the specified instance. Personally I prefer using the CAB file as it comes with several other scripts/configurations.

Download SQLDIAG Manager tool.

Well from now on you can just enjoy configuring SQLDIAG and generate your own investigation templates and scripts. Even if you are not that keen on altering defaults you can still use the tool for entering Machine and Instance name if you need to.

I have to say that with this tool Microsoft have a complete set of tools for performance analysis that are easy to use configure and analyze.

– Configuration of analysis tools – SQLDIAG Manager, PAL Tool

– Analysis – PAL and SQL Nexus

I am planning to do another post for those tools so you can have the big picture in place (if you already don’t have it). So stay tuned!