Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2008 Upgrade Testing at the Microsoft Technology Center


By:   |   Read Comments   |   Related Tips: More > 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.

prerequisite workflow

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.

capture playback

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.

Source - SQL Server Upgrade Assistance 2008 User Guide

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.

capture playback

As a point of reference, below is a screenshot comparing the trace output between the baseline and test environments.

replay performance

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


Last Update:






About the author
MSSQLTips author Kun Lee Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools