I want to mention another tool for obtaining a more thorough analysis of upgrade issues developed in conjunction with Scalability Experts and Microsoft, The SQL Server Upgrade Assistant (SUA). This tool is free but expensive. By that I mean that although it is free to use, it requires a lot of effort, even though it’s as automated as possible, and that’s what makes it expensive. It’s probably best suited for application developers to thoroughly test every feature against the back-end database.
So how is this done?
Basically by following a simple wizard in the tool, the hardest step of which is to create a playback to be used for testing. This consists of a backup of the database to be upgraded and a trace file capturing the spread of work performed against that database. The tool helps you with the backup and sets up the trace profile, but you need to have the workload to run while the trace is on. Of course you could just watch a production system but this is not recommended for two reasons:
Ideally you would have some sort of standard test harness that is used for any regression testing as you develop the application.
Below is the SUA in action
The rest of the steps for using the SUA are as per the left hand side of the dialogue above. The key step in the process is the final analysis where you compare the traces for the playback against the database before and after upgrade:
Note that I have shown the the 2008 version here but there is also one for 2005, although the user guide for this seems to have vanished form the scalability experts site.
So if you are in an environment organisation where you do need this kind of thorough test of an upgrade then download the tool and user guides here.