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

Download Microsoft SQL Server 2012 RC0

OK, this is definitely not what I have been expecting around SQL Server 2012. But as usual, Microsoft managed to surprise their users (along with the news around certification, editions, etc. lately) with the release for download of the RC0.

Download Microsoft SQL Server 2012 RC0

The download process requires registration but I believe that should not be a show stopper Smile

The news around SQL Server 2012 are so much and all so big and “life changing” that I would not like to spam you with them. I will just let you read about them yourself Smile

What is new in Microsoft SQL Server 2012 – information on what is new in this version

SQL Server 2012 Developer Training Kit – training kit preview where you can explore new features, technologies and innovations in All Mighty SQL 2012.

SQL Server 2012 Licensing FAQ – interesting questions and answers on the subject of licensing and licensing changes in SQL Server 2012!


How to set PreAllocate parameter in SQL Server Analysis Services and something more

I suppose you think that changing a parameter in SQL Server Analysis Services engine is quite easy stuff. And probably you are right, because I also thought so before I had to face some reconfiguration request. So straight to the point – changing this parameter is possible ONLY by manually editing the msmdsrv.ini file (the SSAS configuration file in the Config folder). After opening the file for editing just browse to the <Memory> section and you will find it on the bottom of it:


If you set a value between 0 and 100 then this will be converted to percentage of the Total Physical Memory that SSAS is configured to use. If the number is higher than 100 then it will be treated as absolute value in bytes. This setting is actually serving two purposes – warming up the SSAS memory and tacking an issue with memory allocation for SSAS on Windows Server 2003 (several reference articles can be found here and here).

So far so good. But this setting was quite new to me so before reaching the point of changing it I had to go through several different articles and whitepapers. Such are:

- SQL Server 2008 White Paper: Analysis Services Performance Guide

- SQL Server 2005 Analysis Services (SSAS) Server Properties

Based on the entire task for setting this and some other parameters I learned quite a few things for SSAS configuration. Some of them are:

1. There are two types of settings on SSAS instance level – basic and advanced. Advanced, just as the ones in SQL Server Engine, are hidden by default. In order to display and edit them you should check the box “Show Advanced (All) Properties” on the Analysis Services Properties window.


2. You can set absolute values for memory parameters – values between 0 and 100 are treated as percentage of the total physical memory available to SSAS (not the server itself). If the value is greater than 100 – then it is treated as an absolute value in bytes.

SQL Server and some random notes on Error Logs

Recently I was trying to build up my own virtual playground with active directory, couple of SQL Server machines including one cluster so I can try some of that new stuff that I have only been reading about in SQL Server Denali (or the officially announced SQL Server 2012). It’s been quite a journey but I am on the final stretch. Only one SQL Server instance left to be build and I can start play freely. In the meanwhile I brought online my first Denali CTP 3 instance last week and despite I had some troubles with the installation itself, I finally got it running. Not without further issues but for that I will write probably later. In this post I will share some insights that I noted when we talk about SQL Server Error logs.

So we all heard for the SQL Server error log. We all (hopefully) use it and refer to it as a first stop when resolving issues with our SQL Server. Recently couple of things came to my attention – how we configure the number of SQL Server error logs that are kept before recycling, what happens behind the scenes when we are configuring those numbers, where the values are stored, etc. and is there anything special going in the background when we try to browse through SQL Server Management Studio the error log, etc.

We can configure the number of error log files that are kept before recycling using basically two options: through SSMS ( and through manual edit of OS Registry( The interesting part here is that if you use SQL Server 2008 R2 SSMS you cannot enter more than 99 files:


Keeping in mind that second option of manually editing the registry you can easily change the number of error logs kept before recycling to be more than 99:


So that is strange, right?!? Through the GUI I cannot enter more than 99 files but manually I can set such number.

While I was digging around that thing I sniffed couple of interesting queries that SQL Server is performing:

- when I go to SQL Server Error Logs –> Configure behind the scenes SQL Server executes the below query to capture the values:

        declare @HkeyLocal nvarchar(18)
        declare @ServicesRegPath nvarchar(34)
        declare @SqlServiceRegPath sysname
        declare @BrowserServiceRegPath sysname
        declare @MSSqlServerRegPath nvarchar(31)
        declare @InstanceNamesRegPath nvarchar(59)
        declare @InstanceRegPath sysname
        declare @SetupRegPath sysname
        declare @NpRegPath sysname
        declare @TcpRegPath sysname
        declare @RegPathParams sysname
        declare @FilestreamRegPath sysname

        select @HkeyLocal=N'HKEY_LOCAL_MACHINE'

        -- Instance-based paths
        select @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer'
        select @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'
        select @FilestreamRegPath=@InstanceRegPath + N'\Filestream'
        select @SetupRegPath=@MSSqlServerRegPath + N'\Setup'
        select @RegPathParams=@InstanceRegPath+'\Parameters'

        -- Services
        select @ServicesRegPath=N'SYSTEM\CurrentControlSet\Services'
        select @SqlServiceRegPath=@ServicesRegPath + N'\MSSQLSERVER'
        select @BrowserServiceRegPath=@ServicesRegPath + N'\SQLBrowser'

        -- InstanceId setting
        select @InstanceNamesRegPath=N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

        -- Network settings
        select @NpRegPath=@InstanceRegPath + N'\SuperSocketNetLib\Np'
        select @TcpRegPath=@InstanceRegPath + N'\SuperSocketNetLib\Tcp'

        declare @SmoAuditLevel int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'AuditLevel', @SmoAuditLevel OUTPUT

        declare @NumErrorLogs int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'NumErrorLogs', @NumErrorLogs OUTPUT

        declare @SmoLoginMode int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'LoginMode', @SmoLoginMode OUTPUT

        declare @SmoMailProfile nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'MailAccountName', @SmoMailProfile OUTPUT

        declare @BackupDirectory nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'BackupDirectory', @BackupDirectory OUTPUT

        declare @SmoPerfMonMode int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'Performance', @SmoPerfMonMode OUTPUT

			  if @SmoPerfMonMode is null
			  set @SmoPerfMonMode = 1000

			  declare @InstallSqlDataDir nvarchar(512)
			  exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLDataRoot', @InstallSqlDataDir OUTPUT

			  declare @Arg sysname
			  declare @Param sysname
			  declare @MasterPath nvarchar(512)
			  declare @LogPath nvarchar(512)
			  declare @ErrorLogPath nvarchar(512)
			  declare @n int

			  select @n=0
			  select @Param='dummy'
			  while(not @Param is null)
			  select @Param=null
			  select @Arg='SqlArg'+convert(nvarchar,@n)

			  exec master.dbo.xp_instance_regread @HkeyLocal, @RegPathParams, @Arg, @Param OUTPUT
			  if(@Param like '-d%')
			  select @Param=substring(@Param, 3, 255)
			  select @MasterPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
			  else if(@Param like '-l%')
			  select @Param=substring(@Param, 3, 255)
			  select @LogPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
			  else if(@Param like '-e%')
			  select @Param=substring(@Param, 3, 255)
			  select @ErrorLogPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))

			  select @n=@n+1

			  declare @SmoRoot nvarchar(512)
			  exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLPath', @SmoRoot OUTPUT

        declare @SmoDefaultFile nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultData', @SmoDefaultFile OUTPUT

        declare @SmoDefaultLog nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultLog', @SmoDefaultLog OUTPUT

        declare @ServiceStartMode int
        EXEC master.sys.xp_instance_regread @HkeyLocal, @SqlServiceRegPath, N'Start', @ServiceStartMode OUTPUT

        declare @ServiceAccount nvarchar(512)
        EXEC master.sys.xp_instance_regread @HkeyLocal, @SqlServiceRegPath, N'ObjectName', @ServiceAccount OUTPUT

        declare @NamedPipesEnabled int
        exec master.dbo.xp_instance_regread @HkeyLocal, @NpRegPath, N'Enabled', @NamedPipesEnabled OUTPUT

        declare @TcpEnabled int
        EXEC master.sys.xp_instance_regread @HkeyLocal, @TcpRegPath, N'Enabled', @TcpEnabled OUTPUT

        declare @InstallSharedDirectory nvarchar(512)
        EXEC master.sys.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLPath', @InstallSharedDirectory OUTPUT

        declare @SqlGroup nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLGroup', @SqlGroup OUTPUT

        declare @FilestreamLevel int
        exec master.dbo.xp_instance_regread @HkeyLocal, @FilestreamRegPath, N'EnableLevel', @FilestreamLevel OUTPUT

        declare @FilestreamShareName nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @FilestreamRegPath, N'ShareName', @FilestreamShareName OUTPUT

        declare @cluster_name nvarchar(128)
        declare @quorum_type tinyint
        declare @quorum_state tinyint
        BEGIN TRY
            SELECT @cluster_name = cluster_name, 
                @quorum_type = quorum_type,
                @quorum_state = quorum_state
            FROM sys.dm_hadr_cluster
        END TRY
            IF(ERROR_NUMBER() != 297)
        END CATCH

@SmoAuditLevel AS [AuditLevel],
ISNULL(@NumErrorLogs, -1) AS [NumberOfLogFiles],
(case when @SmoLoginMode < 3 then @SmoLoginMode else 9 end) AS [LoginMode],
ISNULL(@SmoMailProfile,N'') AS [MailProfile],
@BackupDirectory AS [BackupDirectory],
@SmoPerfMonMode AS [PerfMonMode],
ISNULL(@InstallSqlDataDir,N'') AS [InstallDataDirectory],
CAST(@@SERVICENAME AS sysname) AS [ServiceName],
@ErrorLogPath AS [ErrorLogPath],
@SmoRoot AS [RootDirectory],
CAST(case when 'a' <> 'A' then 1 else 0 end AS bit) AS [IsCaseSensitive],
@@MAX_PRECISION AS [MaxPrecision],
CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
CAST(SERVERPROPERTY(N'ProductLevel') AS sysname) AS [ProductLevel],
CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser],
CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition],
convert(sysname, serverproperty(N'collation')) AS [Collation],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
CAST(SERVERPROPERTY(N'MachineName') AS sysname) AS [NetName],
ISNULL(@SmoDefaultFile,N'') AS [DefaultFile],
ISNULL(@SmoDefaultLog,N'') AS [DefaultLog],
@LogPath AS [MasterDBLogPath],
@MasterPath AS [MasterDBPath],
SERVERPROPERTY(N'ResourceVersion') AS [ResourceVersionString],
SERVERPROPERTY(N'ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime],
SERVERPROPERTY(N'CollationID') AS [CollationID],
SERVERPROPERTY(N'ComparisonStyle') AS [ComparisonStyle],
SERVERPROPERTY(N'SqlCharSet') AS [SqlCharSet],
SERVERPROPERTY(N'SqlCharSetName') AS [SqlCharSetName],
SERVERPROPERTY(N'SqlSortOrder') AS [SqlSortOrder],
SERVERPROPERTY(N'SqlSortOrderName') AS [SqlSortOrderName],
SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY(N'BuildClrVersion') AS [BuildClrVersionString],
@ServiceStartMode AS [ServiceStartMode],
ISNULL(@ServiceAccount,N'') AS [ServiceAccount],
CAST(@NamedPipesEnabled AS bit) AS [NamedPipesEnabled],
CAST(@TcpEnabled AS bit) AS [TcpEnabled],
ISNULL(@InstallSharedDirectory,N'') AS [InstallSharedDirectory],
ISNULL(suser_sname(sid_binary(ISNULL(@SqlGroup,N''))),N'') AS [SqlDomainGroup],
case when 1=msdb.dbo.fn_syspolicy_is_automation_enabled() and exists (select * from msdb.dbo.syspolicy_system_health_state  where target_query_expression_with_id like 'Server%' ) then 1 else 0 end AS [PolicyHealthState],
@FilestreamLevel AS [FilestreamLevel],
ISNULL(@FilestreamShareName,N'') AS [FilestreamShareName],
-1 AS [TapeLoadWaitTime],
CAST(SERVERPROPERTY(N'IsHadrEnabled') AS bit) AS [IsHadrEnabled],
SERVERPROPERTY(N'HADRManagerStatus') AS [HadrManagerStatus],
ISNULL(@cluster_name, '') AS [ClusterName],
ISNULL(@quorum_type, 4) AS [ClusterQuorumType],
ISNULL(@quorum_state, 3) AS [ClusterQuorumState],
       AS sysname) AS [Name],
       AS sysname) AS [InstanceName],
CAST(0x0001 AS int) AS [Status],
0 AS [IsContainedAuthentication],
CAST(null AS int) AS [ServerType]

The output from that query is quite interesting to me. It includes the number of error log files kept, root directory, instance directory, backup directory, default collation, product level, edition and so on. Cool, huh? Smile

- When I browse the SQL Server Error Logs folder from SSMS, the following query is executed:

create table #err_log_tmp(ArchiveNo int, CreateDate nvarchar(24), Size int)

insert #err_log_tmp exec master.dbo.sp_enumerrorlogs

CAST(er.ArchiveNo AS sysname) AS [Name],
'Server[@Name=' + quotename(CAST(
       AS sysname),'''') + ']' + '/ErrorLog[@ArchiveNo=''' + CAST(er.ArchiveNo AS sysname) + ''']' AS [Urn],
er.ArchiveNo AS [ArchiveNo],
CONVERT(datetime, er.CreateDate, 101) AS [CreateDate]
#err_log_tmp er
[ArchiveNo] ASC

drop table #err_log_tmp

The output form the query looks like this:


The important part here is the stored procedure that is in the bottom of everything and that is master.dbo.sp_enumerrorlogs. This stored procedure is very similar to the extended SP xp_enumerrorlogs. If you execute both of those queries in a normal query window you get exact output from both:


So yet another mystery to me Smile I dug deep because I was curious which stored procedure comes first – the extended one or the regular one in master. I ran sp_helptext against each of them and the output made it clear:


The extended stored procedure is used to implement the sp_ one with one and only one intention – security – if you are not part of the security admin role of the server then you are not able to run the stored procedure! And here is the error you get if you try to browse SQL Server Error Logs folder in SSMS:


I hope you enjoyed the reading and some of my geeky findings Smile

SQL Server 2008 SP3 available for download

It is been a long time since my last post and my list for blog article candidates constantly keep growing Smile I hope soon I will have enough time to return back to normal blogging speed.

Now I would like to share the new Service pack release for Microsoft SQL Server 2008 – list of all fixes can be found here. Actually as far as I could see those changes actually include all fixes till CU4 of SP2. So if you already have CU5 in place then applying SP3 is not really of a use. The SQL Server build version of SQL Server 2008 SP3 is 10.0.5500 (neat one, right? Smile)

Download SQL Server 2008 SP3 RTM

And happy patching Open-mouthed smile

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