SQL Server 2008 Upgrade Lab at Microsoft

 

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.

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.”