Hello dear DBAs,
Consider sparing some time and check the resources below, you will find three very nasty but very interesting and deep walk-through in migration from FCI+Mirroring to SQL 2012 AlwaysOn.
Hello dear DBAs,
Consider sparing some time and check the resources below, you will find three very nasty but very interesting and deep walk-through in migration from FCI+Mirroring to SQL 2012 AlwaysOn.
In one of my numerous subscriptions these days, I found one very interesting post with a collection of over 85 different free e-books for Microsoft technologies – from Windows development, to Sharepoint and SQL Server. Most of the book in the SQL Server 2012 section are really worth reading. The collection you can find here.
Some of the books are covering quite basic stuff, but hey – those are still good reads. All the SQL Server books are available in .PDF format as well as e-reader formats so you can direct use them for your Kindle for example. So enjoy reading :)
This is a very simple approach to generate mass script for changing database owner of all user databases. Why would you need that? Well there are couple of reasons – you might have mass detached\attached all user databases (in an upgrade scenario for example) or you might have restored numerous databases on a brand new instance. Then for sure you will not have the ‘sa’ as owner of the database. In this case, use the below script to get the ownership fixed. Note that it only works for SQL Server 2005 and above!
select 'ALTER AUTHORIZATION ON DATABASE::[' + name + '] TO [sa]' from sys.databases where database_id>4
This weekend I had quite a lot of activities related to a migration of Analysis Services 2000 from one machine to another. With the limited capabilities of AS 2000 and the approach we chose – backup/restore, I thought it will be a quite straight forward process. Well…it wasn’t. I had to discover so many different things that I even surprised myself. In this post I will try to summarize them, despite the fact that they might not ever get handy for you.
Number 1: Backup and restore for
The archival process of AS 2000 is quite easy and straight forward – you have the button within the context menu and the only thing you should configure is the path and filename of the backup file and the actual data location (the folder structure of the AS databasesm which is usually configured on instance level). Here we have several “gotchas”:
Remote backup – you can initiate a remote archival process (from a remote machine, having Analysis Manager installed). But within the data folder path you must be careful and enter the accessible network path of the AS 2000 instance, being source for the backup. Note that if you do not point it correctly you will not include the data in the backup.
Backup speed – it is just awful! First of all, all archives are compressed into a CAB files…and you cannot change that at all…you are just stuck with the archive. But this is not the problem…the problem is that the archival process is using only one logical CPU. Lame, uh? This whole stuff made the backup I was running to run for 5GBs/hour…what a speed!!!
Restore speed – as any other SQL, once you initiate restore, the process first must allocate the space before starts “unpacking” the data. And if your service account does not have the “Perform volume maintenance tasks” privilege enabled, you will have to wait quite a lot of time and for the restore. (Info on “Perform volume maintenance tasks” can be found here).
The backup does not start – well, yeah…I configure the backup, files, paths, the whole bunch of settings required, but at the end it is just not starting…I was checking the registry, installation versions, tried the command line, etc….it was just not starting….Then I went to check the settings of the AS instance. It seemed that the instance did not have default DATA and TEMP dir:
AS2000 lets you save such configuration. And does not really have an issue with it being empty. But if you run a backup, it is just not starting if you are running the archive locally on the server. For remote backup – it is just fine, because there you can point the UNC path to the DATA folder.
Error Log free AS 2000 – this part bugs me the most! It is just not very pleasant to have something not working/starting/failing, and not to have any detailed log to dig into and find the root cause. With AS2000 you just have to look around and test all kinds of possible solutions. I suppose you understand how risky is that. Good thing is that the engine does not really have that much options to play with :D
Well, for two days in a row we managed to migrate(move) en entire instance from one machine to another, but the 200GBs of data took us forever to backup and restore. And imagine, that some of the archives were corrupted – it was just a pain in the a** to have to start the archival process once again.
Oh, I forgot to mention one more thing – once the app team started testing, the processing of one of the databases was causing the entire AS instance to stop with the following error:
“The MSSQLServerOLAPService service terminated unexpectedly” and “There was a fatal error during transaction commit in the database <DB NAME>. Server will be restarted to preserve the data consistency”
I found some articles saying that a restore might help, tried it, failed once again…it seemed that the backup was corrupted. It was a 50+ GB database and if I have to start the archiving again it was another 10 hours of waiting and getting close to the deadline. So I took another approach – copied directly the files from the source server and overwritten the ones on the destination. The AS databases are holding all their structures and data within a single folder so it was a straight forward copy/paste job. And it was fine as the database was already restored and part of the AS metadata databases. Luckily it was also fine with the app team who managed to process the database.
Quite a lot of fun with archeological (:D) AS instances where I had to learn and observer so many things during a weekend. The good thing is that it almost made it up for the lack of sleep and frustration I had last two days :)
After the recent release of SQL Server 2012, Microsoft have been working on developing quite some whitepapers on the subject of AlwaysON. Within this post I will share some very interesting ones that can serve you as a starting point for your learning and consultation on the subject of HA/DR solutions using SQL Server 2012 AlwyasON feature.
Note: the last whitepaper is still under its way and Microsoft has not yet released it. However I will keep it here as a container, as once released, those 4 whitepapers will go hand in hand.
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:
[sourcecode language='sql' padlinenumbers='true'] 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 [/sourcecode]
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.
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!
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:
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:
[sourcecode language="sql" padlinenumbers="true"] 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'SOFTWAREMicrosoftMSSQLServer' select @InstanceRegPath=@MSSqlServerRegPath + N'MSSQLServer' select @FilestreamRegPath=@InstanceRegPath + N'Filestream' select @SetupRegPath=@MSSqlServerRegPath + N'Setup' select @RegPathParams=@InstanceRegPath+'Parameters' -- Services select @ServicesRegPath=N'SYSTEMCurrentControlSetServices' select @SqlServiceRegPath=@ServicesRegPath + N'MSSQLSERVER' select @BrowserServiceRegPath=@ServicesRegPath + N'SQLBrowser' -- InstanceId setting select @InstanceNamesRegPath=N'SOFTWAREMicrosoftMicrosoft SQL ServerInstance NamesSQL' -- Network settings select @NpRegPath=@InstanceRegPath + N'SuperSocketNetLibNp' select @TcpRegPath=@InstanceRegPath + N'SuperSocketNetLibTcp' 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] [/sourcecode]
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:
[sourcecode language="sql"] 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 [/sourcecode]
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