SQL Server 2008 Upgrade Lab at Microsoft
They knew I was coming
First, you recieve a very explicit set of pre-requisites. You need to install the SQL Server Upgrade Assistant, a tool that Microsoft licensed Scalability Experts to create for them. You have to run this against a small database, >25gb. The tool backups up all the databases from the server (so you need to put it on to a test box, rather than try to move an entire production system worth of databases). It then starts a trace that captures all the calls made to the database. I spent two days working with one of my application teams to get a server set up, the app connected, and a good set of tests run on the server to capture about an hour’s worth of trace data. It was at no point hard to meet the requirements, it just took time to get everything set up just right. They recommend you single thread the trace, meaning, just have one user run all the tests. This is because, when run without any extra work, Profiler, which replays traces, is single threaded. This can lead to unrealistic errors, especially blocking and deadlocks.
Once I had everything, I went to Waltham (a two-hour commute… the less said the better) to run the tests. The lab set up was impressive. They had a seperate room for each of the four companies that sent someone to the testing facility. We had a solid workstation (running Windows 7 by the way, fun stuff) and a set of servers on a seperate lan inside each room. The servers were running on HyperV, Microsoft’s virtual server software. Unfortunately, we did run into a snag here. Each server was supposed to have 100gb of space to accomodate the copy of the database as well as a restore of it and some more room besides. The virtual machines were configured to run on a system that only had 140gb of storage to start with. I filled that with my database during the initial set up (I ran the processes on three servers simultaneously). That put us out of commission while the MS techs went about fixing everything (which they did, quickly). It was just the pain of being first.
MS provided nice work stations
The documentation on the labs was very complete. Few steps were left to the imagination. Any where that had ambiguity, a second set of documentation cleared up nicely. With one exception. They did want to you to restore the System db’s. It made sense to do it, but I checked both sets of documentation and it wasn’t there, so I thought, hey, what I do know, MS is on top of this… Wrong. Had to restart, again.
Once all the initial configuration issues were done, it was simply a matter of walking through the lab. The first step was to establish a baseline, so I played back the trace on a 2000 server. Then I did an in place upgrade to 2008 and ran the trace and an upgrade to a new install using a restore and ran the trace there. All the results could then be compared.
Over all, it was a good session. Rich Crane, Rob Walters and Sumi Dua from Microsoft were very helpful. I picked up a few tips on doing upgrade testing and got to do it away from managers and developers, making quite a few mistakes along the way. Now maybe I can do it in front of them with fewer mistakes. I liked the Upgrade Assistant tool since I’m pretty lazy, but it didn’t do anything earth shattering that you couldn’t do on your own.
One tip worth repeating, if you’re using the Upgrade Assistant to capture a trace, it doesn’t put filtering in place. You can open the trace file, filter out the databases, by ID, that you don’t need, and then save a new copy of the trace file, just for the database you’re interested in. Thanks for that one Rich.
Gentlemen, you did a nice job. I appreciate your time and your help. Sumi, nice to meet you. Rob, good to see you again. Rich, thanks again for everything, great chatting and good to see you again as well. Rich and I used to work at a dot com “back in the day.”
SQL Server 2008 Upgrade Whitepaper
One of my tasks for the coming year is to evaluate each and every SQL Server 2000 system, identify all the databases, the applications they belong to, and provide an upgrade to SQL Server 2008 cost estimate. I’ve only started reading this new white paper from Microsoft, but I can already tell it’s going to be a huge help.
XML to Multiple Queries
One of our development teams created a set of queries that are receiving some rather large XML parameters for processing. Unfortunately, the developers didn’t think things through entirely. They passed the same, large, XML string in to the server five times in a row. Needless to say, the performance was substandard since the XML parser had to be instantiated five times. My initial suggestion was to use a wrapper procedure to perform a single load of the XML data and then call the other 5 procedures. I further suggested to load the XML into a temporary table and use that within the other procs. Unfortunately this is all taking place within SQL Server 2000. When my initial set of recommendations was done, we had solved one problem and introduced another. We were getting serious recompiles. If we could move this to SQL Server 2008, there are a number of possible solutions. However, my co-worker, Det (name changed to protect the guilty), came up with a solution that just hadn’t occurred to me. He dropped the temporary table and instead simply passed the XML handle generated in the wrapper procedure to each of the calling procedures. It works like a charm and supplied a huge performance improvement. I wish I had thought of it.
The wrapper proc now looks a bit like this:
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlCollection,
‘<ClientAndIndustriesCollection
xmlns:cl=http://Our.local.def
xmlns:i=http://www.w3.org/2001/XMLSchema-instance
xmlns:a=”http://thds.fmglobal.com/v1.0.0/ClientDataContract”/>’
EXEC myProc1 @hDoc
EXEC myProc2 @hDoc
Home of the Scary DBA 