By: Kun Lee | Comments | Related: > Upgrades and Migrations
Problem
It has been a year since SQL Server 2008 was launched and like many other companies, my company is also trying to upgrade all the major production database servers from SQL Server 2000 to SQL Server 2008. In order to build a more accurate Project Plan, we wanted to quickly find out if our application will perform acceptably after the upgrade and also make sure we conduct our due diligence. How can we go about taking these steps to ensure we have a smooth upgrade process?
Solution
One of the many things that Microsoft is doing well is providing different types of support for free when you are upgrading to SQL Server 2008. After our company decided to upgrading to SQL Server 2008, we contacted the local Microsoft TAM (Technical Account Manager) and my manager and I attended the "Microsoft SQL Server 2008 Upgrade and Application Compatibility Lab" at the Microsoft Technology Center in Reston, Virginia. In this tip, I would like to share the experience with the MSSQLTips community.
Prerequisite workflow
Below is the general workflow for preparing for the labs at the MTC. Depending on the size of your database and activities, these steps can take a day or more to complete. My databases were a little bigger then the recommended size, so it took a long time to generate test files and to copy over the files to USB hard drives. As a safety precaution, they recommend copying the files to two different external hard drives.
In this workflow, I mention the "SSUA" which is the "SQL Server Upgrade Assistant (SSUA)" tool. I will explain more about this too later in this tip.
Lab work flow
Below outlines the steps in the overall workflow for the main portion of the lab.
SQL Server Upgrade Assistant (SSUA) Tool
The SQL Server Upgrade Assistant is a tool that you can download from Scalability Experts that does the following:
SQL Server Upgrade Assistant 2008 (Upgrade Assistant) allows you to verify how an application designed for SQL Server 2000 or SQL Server 2005 will run on SQL Server 2008. Upgrade Assistant guides you through the steps to setup and obtain baseline data on an instance of SQL Server 2000/2005, upgrade the database to SQL Server 2008 and obtain the same data to identify differences, if any. In this process, Upgrade Assistant provides automation for backing up and restoring necessary databases, capturing and replaying workload traces and recoding relevant playback data. Finally, Upgrade Assistant compares the trace results and identifies areas where the workload replayed different on SQL Server 2008 from SQL Server 2000/2005.
Below is a screen capture of the tool where you can work through most of the processes listed in the previous section (i.e. backup, run Profiler, setup the baseline, etc.) in a point and click manner. Depending on your environment you may need to handle some of the processes via T-SQL commands or use third party products to complete the steps. However, this tool provides the concepts to complete the testing and once you understand how those items relate to your environment, you should be able to follow the step by step user guide.
As a point of reference, below is a screenshot comparing the trace output between the baseline and test environments.
Conclusion
Working at the MTC was a great experience. I wish I had more time to prepare for the labs, but I had the opportunity to learn many great tips and suggestions from the visit for our upgrade. I would like to thank Jim Dugan and George Huey for providing all the information above in addition to George sharing the URL to the Ultimate Guide for Upgrading to SQL Server 2008 which is a 490 page technical white paper on upgrading to SQL Server 2008. Enjoy!
So how do I get started?
I have asked one of the Microsoft support team members about how another company could work through the same upgrade process as our company and this was their response:
"Note, that holding these labs at the MTCs is a resource investment by Microsoft for our enterprise accounts and managed ISV accounts. If people are interested in the lab, MSFT suggested that you to contact their Microsoft Account Manager and have the account manager work with them. There are some companies that just want to go through the process so that they can see how it is done. I am currently working on setting up a Hands-On-Lab (HOL) for anybody on the internet to run. I have written two HOLs, one for in-place upgrade and one for side-by-side upgrade. Basically, the user runs in a VPC environment where I have everything setup and all they have to do is walk thru the process. Kind of like you did at the MTC except with canned data that I provided. Then they can take their learning's and apply it in their environment. "
Once I get the announcement about the hands on labs, I will post them in the forums. So stay tuned...
Next Steps
- Capture Playback feature is very easy and useful tool and it is not only useful for upgrade but also useful for capturing performance bottleneck as well as doing load testing. I may write more about this topic later if any other people already wrote about it.
- Here are references that I have used and good to read before you do upgrade.
- Check out these related resources:
- Check out these related MSSQLTips:
About the author
Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
