SQL Server Upgrade Assistant

If you feel comfortable with the content we have covered to date, feel free to skip to the post-upgrade checks and best-practice section of this chapter. In this section, we discuss a tool for the overachievers and Type-A personalities: the SQL Server Upgrade Assistant (SQL UA).

SQL UA was developed for use in the SQL Server 2005 application-compatibility lab engagements run as part of the Microsoft Ascend (Yukon customer training) and Touchdown (Yukon partner training) programs. The purpose of these labs was to help customers analyze their SQL Server 2000 applications to understand the impact of upgrading to SQL Server 2005 and to provide guidance on any changes that may be necessary to successfully migrate both their database and their application. The labs helped improve the quality of SQL Server 2005 by running upgrades and performing impact analysis on real customer workloads against SQL Server 2005. The labs themselves were run by Microsoft personnel and staffed by partners such as Scalability Experts. Nearly 50 labs were run worldwide, and hundreds of SQL Server 2000 applications were tested. Scalability Experts will continue to offer their expertise and the tools used to conduct the application-compatibility labs to interested parties at no charge. For more information, see the www.scalabilityexperts.com.

From a conceptual standpoint, the difference between this tool and Upgrade Advisor is that SQL UA naturally encompasses the essence of a true upgrade and testing process. By reviewing the results of a SQL Server 2000 workload against the results of the same workload being run against SQL Server 2005, you can identify upgrade blockers and application-coding changes that may be required.

We will walk you through an overview of this process and then provide details of the steps contained in the wizard-driven toolkit, which you can see in Figure 3-7. By using the wizard, you will back up all databases and the users and capture a subset of production workload. You will then restore the databases and users you just backed up and process the workload you captured. The goal of this is to develop a new output file, also known as a baseline. You will then upgrade the test server to SQL Server 2005 and rerun the workload to capture a SQL 2005 reference output for comparison.

Figure 3-7

Note

At the time of this writing, the tool is still in development, so we will not be able to provide screenshots beyond this one.

Capturing the Environment

You should establish your baseline by running DBCC CheckDB on each database and by backing up all SQL Server 2000 systems and user databases from your server. Following this step, you need to start capturing your trace file to avoid gaps in the process. When you capture a trace file, it needs to be a good representation of the workloads that characterize your environment. To do this, it might be necessary to create an artificial workload that better represents the workloads of the application over time. While capturing the trace file, it's a good idea to avoid multiserver operations such as linked server calls or bulk-copy operation. It is also equally important to know that there is a performance cost of six to ten percent while tracing. The sum of the trace files and database backups represent a repeatable and reusable workload called a playback.

Setting up the Baseline Server

Now that you have captured the playback, you can set up the baseline system that will be used for the remainder of the test. This server should be loaded with SQL Server 2000 with SP3a, which is the minimum requirement for upgrading to 2005. In reality, it should be identical to the source system in collation and patching level. The tool will then check your server for this matching. If necessary, you will be prompted to patch or rebuild the master database. It will then restore your databases in the correct order so that your DB IDs are matched to production (this will also include padding the DB creation process to accomplish this). Finally SQL UA will recreate your logins and ensure that the IDs match production as all of this is necessary to run the trace file. The next step in the process is to run the Upgrade Advisor as described earlier in this chapter. Once the environment has been remediated, you can then proceed to the next step of replaying the trace.

Running the Trace

When you run the trace, first the statistics will be updated on all databases. The replay tool will then use the API and run all of the queries within the trace file in order. The thing to keep in mind with this tool is that it is single-threaded, so it's possible that blocking can occur because we captured a multithreaded workload. To compensate for this, the tool has a blocking-detection process that runs every minute (this value is configurable). This is accomplished by monitoring and killing queries that have been running longer than two minutes. The output from this step will generate a trace-output file for comparison in the final analysis.

Upgrading to SQL Server 2005

At this point, you have two choices. You can use the tool to restore the state of the server to its baseline and then upgrade to SQL Server 2005, or you can do the old "cooking show switch" by restoring the baseline to an existing SQL Server 2005 or instance. The thing to remember here is that you are not measuring performance metrics, so these servers don't have to be identical. You are measuring workload behavior between two versions of SQL Server. After restoring the baseline on a SQL Server 2005 platform, you will go through the "Running the Trace" step again.

Final Analysis

After completing all of these processes, you can get to the final steps of comparing the output files by filtering and comparing all batches in both trace files for discrepancies. The output file generated from this comparative process is actually an XML document that can be exported or read by the trace viewer application. The viewer shows one error condition at a time by showing the last correct step, the error step, and the next correct sequences of the batch files. Once a condition has been identified, it can be filtered from the error-reviewing process to allow the DBA to focus on identifying new error conditions.