Dear readers,
I am glad to announce that SQLBG.WORDPRESS.COM is moved to a new and improved site, where you can find all my new articles and where I am aiming to invite feature bloggers in order to maximize the content and your experience. Please continue reading me on www.mssqlinsider.com. All content from this blog post is already transferred there :)
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 
– 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 – 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!
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:
SELECT rh.destination_database_name as 'Database Name', rh.[user_name] as 'Username', CASE rh.restore_type WHEN NULL THEN 'NULL' 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] WHEN NULL THEN 'NULL' WHEN 1 THEN 'YES' WHEN 0 THEN 'NO' END AS 'Database Replaced', rh.restore_date as 'Date Restored', rfg.[filegroup_name], rf.file_number, rf.destination_phys_drive, rf.destination_phys_name 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 
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 
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
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!
Enjoy!
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:
<PreAllocate>0</PreAllocate>
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.
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 (http://msdn.microsoft.com/en-us/library/ms177285.aspx) and through manual edit of OS Registry(http://support.microsoft.com/kb/196909). 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
begin
set @SmoPerfMonMode = 1000
end
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)
begin
select @Param=null
select @Arg='SqlArg'+convert(nvarchar,@n)
exec master.dbo.xp_instance_regread @HkeyLocal, @RegPathParams, @Arg, @Param OUTPUT
if(@Param like '-d%')
begin
select @Param=substring(@Param, 3, 255)
select @MasterPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
end
else if(@Param like '-l%')
begin
select @Param=substring(@Param, 3, 255)
select @LogPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
end
else if(@Param like '-e%')
begin
select @Param=substring(@Param, 3, 255)
select @ErrorLogPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
end
select @n=@n+1
end
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
BEGIN CATCH
IF(ERROR_NUMBER() != 297)
BEGIN
THROW
END
END CATCH
SELECT
@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],
CAST(
serverproperty(N'Servername')
AS sysname) AS [Name],
CAST(
ISNULL(serverproperty(N'instancename'),N'')
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? 
- 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
SELECT
CAST(er.ArchiveNo AS sysname) AS [Name],
'Server[@Name=' + quotename(CAST(
serverproperty(N'Servername')
AS sysname),'''') + ']' + '/ErrorLog[@ArchiveNo=''' + CAST(er.ArchiveNo AS sysname) + ''']' AS [Urn],
er.ArchiveNo AS [ArchiveNo],
CONVERT(datetime, er.CreateDate, 101) AS [CreateDate]
FROM
#err_log_tmp er
ORDER BY
[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 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
It is been a long time since my last post and my list for blog article candidates constantly keep growing 
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? )
Download SQL Server 2008 SP3 RTM
And happy patching 
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 
Enjoy the video and make sure to bookmark all resources provided in the post!
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 
)
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 
There is one more cool feature on this screen – the Timeline thingy 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! 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?
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 
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 
. Well now we have pretty neat screen where we can do it just with some lame clicking 
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!
