Category Archives: SQL Server
It’s that time again, T-SQL Tuesday is here! This time Pat Wright (blog|twitter) is hosting and has put forth automating tasks using ether T-SQL or Powershell. I LOVE automating stuff. As a production DBA in some very large shops you can’t do your job unless you make your servers work for you. I’ve been using T-SQL and *GASP* xp_cmdshell, OSQL and file shares to gather stats and push configurations to servers for decades. Log before fancy things like C# and Powershell existed. These days I use a variety of home grown tools but doing things with just T-SQL can be just as powerful. I’m going to leverage this post to start a series on a pure T-SQL implementation of configuration management, data gathering and utility procedure deployment.
Where Is Your Management Database?
Every DBA should have two things, a utility, or management database on every server and a central repository where all the locally collected data in the management database is pulled back to. What surprises people when I talk about this methodology is I don’t always advocate using a licensed copy of SQL Server. By that I mean that SQL Express works just great as a central repository server. I usually put this on a virtual machine, Hyper-V or what ever flavor of virtual environment your company supports. This allows you to do things like enable CLR and xp_cmdshell on a server that technically is non-production and keep your security risk much lower. Every server that is deployed in my shop gets a local management database. From the central repository I can push out everything I need to manage a server and keep all my servers up to date as I make improvements or bug fixes to the management code. That’s all I’m really going to say about that in this post though I just wanted to give you an idea of just how deep the rabbit hole can go.
DMV’s give you the information, you have to use it.
Since SQL Sever 2005 Microsoft let of of the black box mentality and started providing crazy useful information via Dynamic Management Views. Virtual file statistics though have been around for quite a while. They got a touch up in the DMV but the basic information was available in SQL Server 2000 via function call. The DMV I’m after is sys.dm_io_virtual_file_stats. It has a ton of information in it. It’s main problem though is it is an aggregation over time and doesn’t really tell you what is different from yesterday to today. To get around that we have to build our own sampling routine.
The VirtualFileStats Table
We create a table in our local management database to collect the daily numbers. I try to keep things simple.
CREATE TABLE dbo.VirtualFileStats ( RecordID INT IDENTITY(1,1) NOT NULL, ServerName VARCHAR(255) NOT NULL, DBID INT NOT NULL, FileID INT NOT NULL, Reads BIGINT NULL, ReadsFromStart BIGINT NULL, Writes BIGINT NULL, WritesFromStart BIGINT NULL, BytesRead BIGINT NULL, BytesReadFromStart BIGINT NULL, BytesWritten BIGINT NULL, BytesWrittenFromStart BIGINT NULL, IostallInMilliseconds BIGINT NULL, IostallInMillisecondsFromStart BIGINT NULL, IostallReadsInMilliseconds BIGINT NULL, IostallReadsInMillisecondsFromStart BIGINT NULL, IostallWritesInMilliseconds BIGINT NULL, IostallWritesInMillisecondsFromStart BIGINT NULL, RecordedDateTime DATETIME NULL, IntervalInMilliseconds BIGINT NULL, FirstMeasureFromStart BIT NULL ) GO CREATE TABLE dbo.VirtualFileStatsHistory ( RecordID INT NOT NULL, ServerName VARCHAR(255) NOT NULL, DBID INT NOT NULL, FileID INT NOT NULL, Reads BIGINT NULL, ReadsFromStart BIGINT NULL, Writes BIGINT NULL, WritesFromStart BIGINT NULL, BytesRead BIGINT NULL, BytesReadFromStart BIGINT NULL, BytesWritten BIGINT NULL, BytesWrittenFromStart BIGINT NULL, IostallInMilliseconds BIGINT NULL, IostallInMillisecondsFromStart BIGINT NULL, IostallReadsInMilliseconds BIGINT NULL, IostallReadsInMillisecondsFromStart BIGINT NULL, IostallWritesInMilliseconds BIGINT NULL, IostallWritesInMillisecondsFromStart BIGINT NULL, RecordedDateTime DATETIME NULL, IntervalInMilliseconds BIGINT NULL, FirstMeasureFromStart BIT NULL )
This is what we need to gather, and later analyze the data. Since we are managing our samples we have to know when the sampling started and what the first sample is. FirstMeasureFromStart lets us know that it is the first base measurements the rest of the samples will delta off of.
GatherVirtualFileStats Stored Procedure
Next we need a stored procedure to do the sampling. One thing you will notice is the procedure executes continuously with a WAIT FOR DELAY built into it so you can get finer grained than the 1 minute limitation of the SQL Agent. Sometimes, I will do one off sampling for a short period, say 30 minutes at a 30 second interval but most often I just let it run and set the sample rate at 1 minute or larger depending on how busy the system is.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = Object_id(N'[dbo].[GatherVirtualFileStats]') AND Objectproperty(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[GatherVirtualFileStats] GO -------------------------------------------------------------------------------------- -- GatherVirtualFileStats -- by: Wesley D. Brown -- date: 02/08/2011 -- mod: 04/14/2011 -- description: -- This stored procedure is used to sample sys.dm_io_virtual_file_stats to track -- performance at a database file level. This is useful for finding -- hotspots on SAN's or under performing IO systems. -- parameters: -- @Duration = '01:00:00' How long to run before exiting -- @IntervalInSeconds = 120 Number of seconds between samples --@DB = -1 DB_ID to monitor, -1 for all --@DBFile = -1 File_ID of file to monitor, -1 for all -- usage: -- DECLARE @RC INT, -- @StartTime DATETIME, -- @databaseID INT -- SELECT @StartTime = Getdate(), -- @databaseID = Db_id() -- EXEC @RC = Gathervirtualfilestats -- '00:45:30', -- 30, -- 10, -- -1 -- SELECT * -- FROM dbo.VirtualFileStats -- WHERE DBID = 10 -- ORDER BY RecordID -- platforms: -- SQL Server 2005 -- SQL Server 2008 -- SQL Server 2008 R2 -- tested: -- SQL Server 2005 SP2 -- SQL Server 2008 R2 --------------------------------------------------------------------------------------- -- *** change log *** -- Added history table and perge on start up if there is data in the main table -- *** end change log *** ------------------------------------------------------------------------------------- CREATE PROC dbo.Gathervirtualfilestats @Duration DATETIME = '01:00:00', @IntervalInSeconds INT = 120, @DB INT = -1, @DBFile INT = -1 AS SET nocount ON DECLARE @StopTime DATETIME, @LastRecordedDateTime DATETIME, @CurrentDateTime DATETIME, @ErrorNumber INT, @NumberOfRows INT, @ErrorMessageText NVARCHAR(4000), @CurrentServerName VARCHAR(255), @DifferenceInMilliSeconds BIGINT IF EXISTS (SELECT 1 FROM dbo.VirtualFileStats) BEGIN IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = Object_id(N'[dbo].[VirtualFileStats]') AND Objectproperty(id, N'IsTable') = 1) BEGIN INSERT INTO dbo.VirtualFileStatsHistory SELECT * FROM VirtualFileStats; TRUNCATE TABLE dbo.VirtualFileStats; END END SELECT @CurrentServerName = Cast(Serverproperty('servername') AS VARCHAR(255)) SET @DifferenceInMilliSeconds = Datediff(ms, CONVERT(DATETIME, '00:00:00', 8), @Duration) SELECT @StopTime = Dateadd(ms, @DifferenceInMilliSeconds, Getdate()) WHILE Getdate() <= @StopTime BEGIN SELECT @LastRecordedDateTime = @CurrentDateTime SELECT @CurrentDateTime = Getdate() INSERT INTO dbo.VirtualFileStats (ServerName, DBID, FileID, Reads, ReadsFromStart, Writes, WritesFromStart, BytesRead, BytesReadFromStart, BytesWritten, BytesWrittenFromStart, IostallInMilliseconds, IostallInMillisecondsFromStart, IostallReadsInMilliseconds, IostallReadsInMillisecondsFromStart, IostallWritesInMilliseconds, IostallWritesInMillisecondsFromStart, RecordedDateTime, IntervalinMilliseconds, FirstMeasureFromStart) SELECT @CurrentServerName, vfs.database_id, vfs.[file_id], vfs.num_of_reads - dbaf.ReadsFromStart AS Reads, vfs.num_of_reads AS ReadsFromStart, vfs.num_of_writes - dbaf.WritesFromStart AS Writes, vfs.num_of_writes AS WritesFromStart, vfs.num_of_bytes_read - dbaf.BytesReadFromStart AS BytesRead, vfs.num_of_bytes_read AS BytesReadFromStart, vfs.num_of_bytes_written - dbaf.BytesWrittenFromStart AS BytesWritten, vfs.num_of_bytes_written AS BytesWrittenFromStart, vfs.io_stall - dbaf.IostallInMillisecondsFromStart AS IostallInMilliseconds, vfs.io_stall AS IostallInMillisecondsFromStart, vfs.io_stall_read_ms - dbaf.IostallReadsInMillisecondsFromStart AS IostallReadsInMilliseconds, vfs.io_stall_read_ms AS IostallReadsInMillisecondsFromStart, vfs.io_stall_write_ms - dbaf.IostallWritesInMillisecondsFromStart AS IostallWritesInMilliseconds, vfs.io_stall_write_ms AS IostallWritesInMillisecondsFromStart, @CurrentDateTime, CASE WHEN @LastRecordedDateTime IS NULL THEN NULL ELSE Datediff(ms, dbaf.RecordedDateTime, @CurrentDateTime) END AS IntervalInMilliseconds, CASE WHEN @LastRecordedDateTime IS NULL THEN 1 ELSE 0 END AS FirstMeasureFromStart FROM sys.Dm_io_virtual_file_stats(@DB, @DBFile) vfs LEFT OUTER JOIN VirtualFileStats dbaf ON vfs.database_id = dbaf.dbid AND vfs.[file_id] = dbaf.fileid WHERE ( @LastRecordedDateTime IS NULL OR dbaf.RecordedDateTime = @LastRecordedDateTime ) SELECT @ErrorNumber = @@ERROR, @NumberOfRows = @@ROWCOUNT IF @ErrorNumber != 0 BEGIN SET @ErrorMessageText = 'Error ' + CONVERT(VARCHAR(10), @ErrorNumber) + ' failed to insert file stats data!' RAISERROR (@ErrorMessageText, 16, 1) WITH LOG RETURN @ErrorNumber END WAITFOR DELAY @IntervalInSeconds END
I Have Data, Now What?
This is where the fun begins! If you just query the table the data doesn’t make much sense.
We need to do some simple math and get the data into a metric that is meaningful.
SELECT TOP 10 Db_name(dbid) AS 'databasename', File_name(fileid) AS 'filename', Reads / ( IntervalInMilliSeconds / 1000 ) AS 'readspersecond', Writes / ( IntervalInMilliSeconds / 1000 ) AS 'writespersecond', ( Reads + Writes ) / ( IntervalInMilliSeconds / 1000 ) AS 'iopersecond', CASE WHEN ( Reads / ( IntervalInMilliSeconds / 1000 ) ) > 0 AND IostallReadsInMilliseconds > 0 THEN IostallReadsInMilliseconds / Reads ELSE 0 END AS 'iolatencyreads', CASE WHEN ( Reads / ( IntervalInMilliSeconds / 1000 ) ) > 0 AND IostallWritesInMilliseconds > 0 THEN IostallWritesInMilliseconds / Writes ELSE 0 END AS 'iolatencywrites', CASE WHEN ( ( Reads + Writes ) / ( IntervalInMilliSeconds / 1000 ) > 0 AND IostallInMilliseconds > 0 ) THEN IostallInMilliseconds / ( Reads + Writes ) ELSE 0 END AS 'iolatency', RecordedDateTime FROM management.dbo.VirtualFileStats WHERE DBID = 10 AND FirstMeasureFromStart = 0 ORDER BY RecordID
This gives us reads, writes and io latency per second results.
Now we are cooking! We can now see that on this database we are seeing some spikes in latency, the number of milliseconds it takes to complete a single IO request, and may warrant investigation. As a general rule of thumb if I see IO latency above 20 milliseconds consistently I start looking deeper into the IO system to see what is wrong. A single modern hard disk is capable of 130 random IO’s a second. Another thing to consider is how many databases are on the same disk. I will pull the database files together with the volume they are on to get a true aggregate number of IO’s and latencies. You may find that a single database is dominating the disks and causing other databases to slow down even when the number of IO’s for those databases is small. On a SAN being able to get to the file level can help you locate the physical disks for that LUN and help your SAN administrators look at a very specific spot instead of the overall health of the SAN and actually fix your performance issues.
Lastly, I run a nightly job that performs these aggregations for me and moves them into a table that I keep long term so I can see performance over time. This is a great way to see if you are getting near your IO capacity and if the steps you have done correct the issues.
So, That’s it?
Not by a long shot. Measuring IO latencies is just one piece of a much larger puzzle for troubleshooting performance problems. It is a valuable one though, and keeping this information over time is just priceless.
I added a history table an a check at the beginning to move all data to history before starting up. Mike Kaplan reported below that multiple runs caused issues if there was data in the table from a previous run.
SQL Server is a huge product with lots of moving parts. Bugs happen. Microsoft has a place to voice your issues or problems. They allow you to vote on the issue and then decide when or if it will get fixed. I’ve used Connect when I hit a bug and I have voted on items that were important to me. Recently I hit a bug in sp_createstats. I use this system stored procedure generate statistics in an automated process I’ve got that manages statistics. I added a new vendor database to the system and on the first run hit “Column ‘DAYSOPEN’ in table ‘dbo.TBL_OPPORTUNITY’ cannot be used in an index or statistics or as a partition key because it is non-deterministic.”. Well, we all know you can’t create stats on a computed column! I quickly went to the connect site and someone else had already entered it. The down side was it had so few votes it was only slated to go into the next cumulative update/service pack. When I hit this issue they hadn’t yet announced service pack 4. I already had this procedure coded into my routines and really didn’t want to rewrite them to get past this one problem.
By doing what I am about to describe could break at a later date or randomly kill baby kittens.
Since it is a system stored procedure I am loathe to make any changes to it directly. There are ways to modify some system stored procedures but they involve the installation CD and creativity. With that door closed there was only one avenue open to me. Create my own system stored procedure with the fix in it. There is a problem with this solution as well, if it gets dropped due to a service pack or an upgrade anything calling it will break. The first thing I did was to see if the procedure text was available by executing sp_helptext sp_createstats. Luckily it was! Now all I had to do was figure out where it was broken. The procedure is pretty simple and uses some cursors to loop through all the objects and create column statistics where they don’t exist.
declare ms_crs_cnames cursor local for select c.name from sys.columns c where c.object_id = @table_id and (type_name(c.system_type_id) not in ('xml')) and c.name not in (select col_name from #colpostab where col_pos = 1) and ((c.name in (select col_name from #colpostab)) or (@indexonly <> 'INDEXONLY')) -- populate temporary table of all (column, index position) tuples for this table
It was pretty easy to spot. The weren’t checking to see if the column was computed so I added a line to the where clause.
and c.is_computed = 0
That’s it. One little check to see if it is a computed column. Now that I had fixed it I created a new procedure named sp_createstats_fixed in the master database. Just creating it in master doesn’t make it act like the original procedure or make it a system stored procedure. For that I had to execute EXECUTE sp_MS_marksystemobject ‘sp_createstats_fix’. This is an undocumented stored procedure and could change or go way any time. The only way to unmark it in SQL Server 2005 is to drop the procedure and recreate it. Now it acts just like the old procedure. Next I had to replace all references to the old proc with the new one. I made an entry into our bug tracking system about the change so we would have a record of what I did and why.
This wasn’t the most elegant solution. It could break later. The upside is it only took me about 30 minutes to fix and deploy versus the hours of re-coding and then testing that I would have had to do before. Do I think you should go around creating your own system stored procedures? Not at all. I don’t recommend you put anything in the master database period. If the problem had been more complex I would have redone the original routines to exclude the broken procedure. This time it just happened to be a very quick fix to a non-critical part of our system.
Over and over again we are told that the DMV’s only hold data since your last reboot. So, how do you know when your server was last rebooted? Well, every time your SQL Server service restarts tempdb is recreated every time. With a quick query to sys.databases we can get the creation date of tempdb! Armed with that little nugget you can then analyze what is in the DMV’s relevant to the last system restart.
I’ve toyed with the CLR in SQL Sever 2005 off and on since the first Yukon beta had it enabled. And I’ll be honest with you, I was not a fan.It wasn’t like “YOU got chocolate in my peanut butter!” kind of moment for me. I really thought it was going to be a disaster of biblical proportions. As SQL Server DBA’s we caught a break, adoption wasn’t exactly stellar. The problem was there are enough restrictions and little gotchas to keep developers from whole sale abandoning Transact SQL for something more familiar. Fast forward a few years and now I’m not so scared.My biggest worry back then was memory usage. I’m still not very comfortable with it, but on a 64-bit platform you can mitigate those issues by adding more memory. On a 32-bit platform you could cause all kinds of damage by squeezing the lower 4GB memory space to the point you could have connection and backup failures due to lack of memory. Oh and the fix is usually restarting SQL Server. An example of this comes directly from http://msdn.microsoft.com/en-us/library/ms131075.aspx
Scalable Memory Usage
In order for managed garbage collection to perform and scale well in SQL Server, avoid large, single allocation. Allocations greater than 88 kilobytes (KB) in size will be placed on the Large Object Heap, which will cause garbage collection to perform and scale much worse than many smaller allocations. For example, if you need to allocate a large multi-dimensional array, it is better to allocate a jagged (scattered) array.
This memory thing is serious.
The other biggie is what you can, or cannot do using the CLR.
Again from MSDN http://msdn.microsoft.com/en-us/library/ms131047.aspx
SAFE is the most reliable and secure mode with associated restrictions in terms of the allowed programming model. SAFE assemblies are given enough permission to run, perform computations, and have access to the local database. SAFE assemblies need to be verifiably type safe and are not allowed to call unmanaged code.
UNSAFE is for highly trusted code that can only be created by database administrators. This trusted code has no code access security restrictions, and it can call unmanaged (native) code.
EXTERNAL_ACCESS provides an intermediate security option, allowing code to access resources external to the database but still having the reliability guarantees of SAFE.
Most restrictive to least restrictive permissions. Something you don’t worry about in general as a C# programmer but in the database its always an issue in some way.
What it boils down to:
If you are just talking to SQL Server using basic C# stuff leave it in SAFE which is the default.
If you need access to the file system or the registry and some other limited stuff EXTERNAL_ACCESS is the way to go.
IF you want to have the ability to completely tank a production SQL Server UNSAFE puts it all into your hands. You can call unmanaged code via P/Invoke, all bets are off.
Some additional light reading on what libraries can and can’t be called in the CLR.
Fun stuff, no Finalizers or static fields, read-only static fields are ok though. You will see why this is important to me a little later on.
T-SQL vs. CLR
The other thing I had been promoting, and not always correctly, is putting complicated math functions in CLR. Generally, I’ve found that most math problems run faster in the CLR over native T-SQL. And I’ve found for the most part that holds true for the core algorithm. Once you add data retrieval into the mix things shift back in T-SQL’s favor for a lot of operations. Like everything else, test your ideas using real world scenarios or as close as you can before deciding on one technology over another. I prime example for me was coding up Pythagorean and Haversine equations for the classic distance between two zip codes in T-SQL and C# via CLR. Running test data through an array in the C# solution it ran rings around the T-SQL function I had coded up but once it had to start pulling and pushing data back to the database the T-SQL solution was the clear winner.
Another aspect where the CLR can be much better is string manipulation. I’ve written a couple of small UDF’s to handle some of this since using the LIKE ‘%’ would cause a table scan anyway the CLR UDF was faster internally when dealing with the string than T-SQL was using all the string handling functions.
I’m also seeing quite a bit on using the CLR for rolling aggregates and other kinds of aggregation problems. I don’t have any personal experience in that yet with the CLR.
There are also some things that aren’t practical at all using T-SQL, some would say you shouldn’t be using the database for some of this stuff in the first place but that is an argument for a different post.
And Now for Something Completely Different…
I’ve recently started working on my most complex project using the CLR, some aspects have been covered by other folks like Adam Machanic, Robin Dewson and Jonathan Kehayias but there was some specific requirements that I needed.
Thus was born….
This is a codeplex hosted project and all the source code is available there for your viewing pleasure.
I’ve done a lot of C# stuff but this was my first hard core CLR app for SQL Server.
What the assembly does is pretty simple, store files in the database ether native, encrypted or compressed.Yoel Martinez wrote up a nice UDF that does blob compression using the CLR. Between this and examples in Pro SQL Server 2005 on storing files in the database I knew I could do what I needed to do.
The wrinkle in my project was not just reading the file and storing it compressed it was putting it back on disk compressed as well. Enter #ziplib (SharpZipLib). This library allows you to pretty easily create standard zip files that even Windows Explorer can open and extract from. So with all the bits in place I set out to build my little tool.
The first thing I did was put together all the samples I’d found build them up as a set of stored procedures instead of UDF’s and just got the file in and out working. Next I added compression via C#’s DeflateStream to see what it would take to get the data flowing in and out and what the performance hit in memory and time would start looking like. At this point I was pretty optimistic I could knock this thing out in a day or two tops. That was all fine and dandy until I started integrating the #ziplib library. My initial goal was to have the assembly set to EXTERNAL_ACCESS since that was the most restrictive security model.
Since the guys that wrote #ziplib didn’t have the CLR in mind there are several things that break without UNSAFE set. As I mentioned earlier the use of finalizers and static fields were the two big ones. I will at some point recode those parts but for now they are still in place. The second thing is the library covers a lot more functionality that I actually need, So I’ve removed the bits I can without refactoring the library. The resulting DLL isn’t horribly big at this point but I figure when I get around to coding up the finalizers I’ll refactor down to what I need then. One big plus for me though is #ziplib is all managed code written in C# so it is pretty easily added directly into my DLL so I don’t have to register two assemblies or call down to the file system to a unmanaged DLL. Compression is handled by RijndaelManaged which is a built in .net 2.0 libraries.
The big downer for me was trying to debug the the code in Visual Studio 2008, when it did work it was ok but It would fail to connect or fail to register the assemblies so I just fell back to injecting debug messages and running tests manually in SSMS.
One thing I really like about programming languages like C# is method overloading, I really wished you could do that with stored procedures! Since I can’t there were only two options, a stored proc that had lots of flags and variables that may or may not be used and handle it all under the covers or just build each option into a proc with simple callers and a descriptive name. I voted for option two. Some of the T-SQL procedures are used internally by the CLR procedures while all the CLR procedures are called by the user.
Here is the list procedures and what they do.
Called by CLR procedures as helpers
Is called by every proc that inserts a file into the database.
Called by StorePassPhrase to handle insert into database.
Called by any proc that has to decrypt a file stream
Called by any proc that retrieves a file from the database
Called by Users
Called by User supplying a file id and list of key words or “tags” as a search helper other than file name.
Get details on a single file or every file stored in the database.
Give it a password and it generates a secure hash stored into the database for symmetric encryption
Below all store a file from the file system into the database.
Below all retrieve a file from the database back to the file system.
Below all retrieve a file from the database but returns a record set with the file name and the BLOB data.
And lastly, I put in an xp_getfiledetails clone since I wanted a way to verify the file is on disk and get attributes it seemed pretty straight forward since I’m getting the same details when i write the file to the database anyway.
This project isn’t done yet. there are a few more things to be added other than the code cleanup I mentioned already.
Off line decryption tool so the files dumped to disk still encrypted can be worked with.
Additional stored procedures for searching for files by tag or by attributes like name, size, etc.
A real installer and not a zip file with T-SQL scripts.
After that it goes into maintained mode with no new features but work on speeding it up, reducing the memory impact and fixing any bugs that are found. I really want to avoid this growing into a huge library, Keep it simple, do one thing and do it well.
Here are some things that helped me along the way.
Visual Studio 2008
could have used notepad but hey I’m getting lazy in my old age.
JetBrains ReSharper 4.5
If you are using Visual Studio ReSharper is a must. I feel like I’m programming the the stone age without it.
Free tool to help you document your C# code using XMLDoc. Yet something else I wished I could do with stored procedures
If you are building documentation and have XMLDoc in your code this can make it easier to gather it all together. It isn’t perfect but it is free.
Both solid text Pro SQL Server 2005 has a chapter on CLR
|Pro SQL Server 2005|
This one is dedicated to just CLR and was also invaluable to me.
|Pro SQL Server 2005 Assemblies|
On The Web
Devoted to the CLR with some nice resources.
Until next time!
Out of all the problems you can have with SQL Server troubleshooting connectivity issues can be the most challenging. When you factor in the complexities of Active Directory and SQL Server’s interaction with it fixing SSPI errors can be down right baffling.
At my company we are moving onto new hardware and along the way standardizing on SQL Server 2005 x64.Since this is all happening on new hardware I have the luxury of doing most of the work before we flip the switch. We had one migration under our belt and the second one was looking good when the SSPI came and decided to make sure I spent all my Saturday working.
I ran down my list of things to check:
Date out of sync with domain more than 30 minutes – Nope.
Bad DNS entry – Nope.
Miss configured service account – Nope.
Log on locally with Windows account – Yep.
Now I was stating to get frustrated and kind of worried that a roll back may be called for. I did what all good panicked DBA’s do, I searched the Internet. Most of it I had already tried and some of it just didn’t apply. It’s not often a solid web search is such a complete strike out. When all else fails, step back look at the problem as a whole and start from the beginning.
What do we know?
We can’t establish a trusted login between SQL Server and the domain.
The OS and domain are just fine. You can log in locally with a domain account and you can remotely access other server resources with a domain account.
SQL Server services start up just fine under a domain account.
You can log in locally to SQL Server but not remotely.
SQL authentication works just fine.
That pretty much leaves a configuration issue somewhere. Since we had set this server up it initially had a different name and IP address that would be changed to the old server name and IP address. There are alot of known problems with other parts of SQL Server like reporting services when you do this kind of rename but generally SQL Server is just fine.
You drop the old SQL Server name
sp_addserver new_name, local
sp_addserver [new_name\instancename], local
Verify everything with these two queries
SELECT * FROM [servername].msdb.dbo.sysjobs
If you have a problem with the @@Servername you get back nothing or the wrong server name. The four part select should return data if not you usually get this linked server is not configured for blah blah blah. Which means you may have forgotten the ,local part. After checking all of these things off again, we still had the issue! Now I really was stumped. I didn’t have a lot of other choices and time was running out. I just started going through EVERYTHING that was network related in the SQL Server configuration. Eventually, I saw the issue. The server IP was correct but the IP in the SQL Server Network Configuration was wrong! I’ve done a ton of renames like this and hadn’t encountered this particular setting not changing when the server IP changed. I reset it to the new server IP and just like magic SQL Server could authenticate to the domain. There was much cheering from my peers, ok not really but I was cheering. The server was up and everyone was able to get back to doing other things as the database faded into the background once again. I just keep telling myself that SQL Server one of the easiest RDMS’s on the market.
Microsoft has always been pretty good at one thing, ease on install.
One of the things I always says is the greatest thing about SQL Server is any idiot can install it. The other thing I also say is the worst thing about SQL Server is any idiot can install it.
This has carried over to the other fine products that ship with SQL Server. The one thing I’ve had pretty good luck using as a novice since it was introduced is SQL Server Reporting Services. As someone who hasn’t had the best experience over the years with crystal reports SSRS was a breath of fresh air.
One of the things I’ve never had to do though is move SSRS to a new server. To start with I did what I always do; I went to MSDN and read what I could find on moving or migrating to a new server. I was feeling pretty confident that I could pull this off.
I had backed up my databases and my key so I could import it on reinstall.
My first mistake was hubris that I could do what I thought was correct and second guess the documentation.
I installed SQL Server plus all the other services, including SSRS just like I was doing a clean install. The server didn’t start out with the same name and that was part of the problem. I copied all the files I needed from the old server and shut it down. Next we renamed the server and I renamed the SQL Server instance to its new name. Once that was done I restored the old SSRS databases and the encryption key. I fired up SSRS when to look at a report and got an odd error.
This version doesn’t support multiple instances.
My second mistake was assuming the documentation on MSDN was complete. After staring over and following the instructions there I ended up variations on the previous error, or the reports wouldn’t generate period.
What should have been a two hour migration was stretching into two days. I had to pull the trigger and bring the old hardware back on line.
Having a fall back plan saved us from having any impact on the business but it did set me back a week until the next available maintenance window was available.
I now had a whole week to research the issue and make sure I had a successful migration, so I got cracking.
I went to my buddy Mr. Google and asked him where the hell I messed up and if anyone else had come up against this. As I thought I wasn’t the first, but there was a twist it wasn’t just one thing it was a few things that I was bumping up against. Luckily between MSDN and some of the fine folks on the SQLServerCentral forums I was able to piece together a successful migration strategy.
The list of obstacles I had to overcome.
This was a named instance and the $ played havoc with the Reporting Services Configuration Manager.
This was a migration of existing data plus the security key to the server.
The server name was also being changed after install to reflect the old server name.
The instructions from MSDN tell you not to choose SSRS to be configured during the install with the default values.
This causes a key part not to be configured, IIS. This is a problem because without IIS configured you can’t view the reports!
This by its self isn’t an issue if you are working with a default instance of SQL Server. But, as I indicated, I’m dealing with a named instance.
After you have installed SQL Server and SSRS you are suppose to use the Reporting Services Configuration Manager to setup IIS. But, since the named instance has the $ in it, the Reporting Services Configuration Manager kicks back the virtual name with:
ReportServicesConfigUI.WMIProvider.WMIProviderException: The virtual directory specified is not valid. Make sure the specified name is not too long and doesn’t contain illegal characters. (example: ;)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.CreateVirtualDirectory(String virtualDirectory, String path)
Very nice, if you let the installer configure the site at install time it gladly puts the $ in the name for you like http://<servername>/ReportServer$<instancename>.
I had puzzled out the IIS part and gleaned the rest from Tim Benninghoff and his post on SSC.
Also, the bits about restoring the encryption key came from MSDN, none of the other articles talk about a named instance at all and assume it is a default instance you are moving.
So, I followed these steps to restore the IIS folder structure and move my instance of SSRS to the new server.
Remember, please validate any changes you make to your server, and always your success isn’t guaranteed just because you followed these instructions.
On the original source server:
- Back up the ReportServer database, the ReportServerTempDB database.
- Back up the encryption key by using the Reporting Services configuration Manager. It will ask you for a password to backup the key DON’T FORGET IT!
- Using the Internet Information Services Manger expand the Application Pools folder, right click on the ReportServer$<instancename> go to all tasks and Save Configuration File.
I recommend giving it a .xml extension to make things easier on the restore side.
- Go to the Web Sites folder and usually under the Default Web Site there will be two entries with gears next to them one named Reports$<instancename> and the other ReportServer$<instancename>.
Right click on them and save the configuration file like you did with the application pool.
On your new destination server:
- Stop IIS service by running the iisreset /stop command at the command prompt.
- Stop Reporting Services using Reporting Services Configuration Manager click Server Status, and then click Stop on the Report Server Status page. If Stop is unavailable, the service has already been stopped.
- Restore the backup databases from the source server.
- Import the IIS application pool configuration to IIS on the new server. From Internet Information Manager, right click on your Application Pools folder and select New>Application Pool (from file)… Use the Browse button to locate your .xml file you should see the new pool in the list.
- Import the IIS virtual directory configuration to IIS on the new server. From Internet Information Manager, right click on your Default Website and select New>Virtual Directory (from file)…
Use the Browse button to locate your .xml files, and then click Read File Select the Virtual Directory name that appears in the white box and click the OK button.
You should see your new Virtual Directory appear with the name Reports$<InstanceName> and ReportServer$<InstanceName>
- Modify the following registry settings under HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.(instance#)\Setup\:
RSVirtualRootApplication – Reports$<InstanceName>
RSVirtualRootApplicationPath – IIS://(ServerName)/W3SVC/1/Root/Reports$<InstanceName>
RSVirtualRootServer – ReportServer$<InstanceName>
RSVirtualRootServerPath – IIS://(ServerName)/W3SVC/1/Root/ReportServer$<InstanceName>
Also, you may need to add the $<InstanceName> to the following keys if they don’t already exist:
- Modify the following .config files for Reporting Services:
In C:\Program Files\Microsoft SQL Server\MSSQL.(instance#)\Reporting Services\ReportManager\, modify RSWebApplication.config so that the ReportServerVirtualDirectory node contains ReportServer$<InstanceName>
In C:\Program Files\Microsoft SQL Server\MSSQL.(instance #)\Reporting Services\ReportServer\, modify rsreportserver.config so that the UrlRoot node contains http://(ServerName)/reportserver$<InstanceName>
- In the Reporting Services Configuration Manager confirm the new paths.
- Start Reporting Services from the Reporting Service Configuration Manager.
- Start IIS service by running the iisreset /start command at the command prompt.
- Remove the encryption key from the source server. I used the rskeymgmt utility to do this.
- Examine the encryption keys that exist. Based on the type of the instance of SQL Server 2005 installed on the destination server, you type the following in the command prompt window.
Type rskeymgmt -l, and then press Enter
Type rskeymgmt -l -i InstName, and then press Enter
You will see two encryption keys listed. One is from the source server, and the other is from the destination server. The format of the encryption keys listed is as follows:
Note ComputerName is the name of the destination server.InstName is the placeholder for the name of the instance of SQL Server 2005 installed on the destination server. EncryptionKeyID is the placeholder for the ID of the encryption key.
- Delete the encryption key from the source server. Based on the type of the instance of SQL Server 2005 installed on the computer, you type the following in the command prompt window.
Type rskeymgmt -r SourceEncryptionKeyID, and then press Enter
Type rskeymgmt -i InstName -r SourceEncryptionKeyID, and then press Enter
Note SourceEncryptionKeyID is the placeholder for the ID of the encryption key from the source server that is obtained in step 2.
- Repeat step 2. You will see that only the encryption key of the destination server exists now.
- Restore the backup encryption key from the source server.
In the Reporting Services Configuration Manager, click Encryption Keys, and then click Restore in the Encryption Key page.
In the Encryption Key Information window, type the password you use to back up the encryption key from the source server, locate the backup encryption key from the source server, and then click OK.
- Make sure that the same user account as the ASP.NET service account is set on the Rsreportserver.config file. To do this, follow these steps:
In the Reporting Services Configuration Manager, click Web Service Identity. Notice the ASP.NET Service Account information.
Open the SQLInstall:\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportServer folder. Right-click the Rsreportserver.config file, and then click Properties.
Note SQLInstall is the placeholder for the drive where SQL Server 2005 is installed. MSSQL.X is the placeholder for the Instance ID of the instance of SQL Server 2005 Report Services that is running on the destination server. To obtain the Instance ID, click Server Status in the Reporting Services Configuration Manager, and then notice the Instance ID information. Usually it’s ID 3 if you are running multiple instances or ID 2 if not.
In the Rsreportserver properties dialog box, click the Security tab.
if the ASP.NET service account is not listed, add it. Make sure that the ASP.NET service account has Read and Read & Execute permissions.
Click OK to close the Rsreportserver properties window.
- If you did not have an instance of SSRS already configured then you will need to execute the following script to add the RSExecRole and the appropriate permissions.
--create RSExecRole role in master and assign permissions
CREATE ROLE [RSExecRole] AUTHORIZATION [dbo]
GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole
GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole
GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole
--create RSExecRole role in msdb and assign permissions
CREATE ROLE [RSExecRole] AUTHORIZATION [dbo]
-- Permissions for SQL Agent SP's
GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole
GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole
GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole
-- SQL Server 2005 requires that the user is in the SQLAgentOperatorRole
IF EXISTS (SELECT * FROM sysusers WHERE issqlrole = 1 AND name = N'SQLAgentOperatorRole')
EXEC msdb.dbo.sp_addrolemember N'SQLAgentOperatorRole', N'RSExecRole'
Once all that is done, if you had an instance already configured you may need to remove the virtual folders from the default website.
I also restarted everything again and tested it all out.
Good luck and I hope you find this useful; trying to glean all of this from different resources and separate what was really going on by the cryptic messages SSRS pumps out can be difficult in the best of times!