Manage Learn to apply best practices and optimize your operations.

SQL Server Upgrade Advisor, Upgrade Assistant can ease transition pains

You probably think you’re ready to upgrade to the new release, but you might not be as prepared as you think. Before you upgrade, there are some tools to become familiar with, says Bob Sheldon.

You've been waiting for SQL Server 2012 to arrive, and now that it's here, you're ready to make the switch. You've...

verified software and hardware requirements. You've put the resources necessary to test and implement a new system into place. But you might be forgetting something important -- the client applications that rely on SQL Server data.

That's where the Upgrade Assistant for SQL Server 2012 comes in. The Upgrade Assistant provides an automated mechanism for testing application compatibility when upgrading from SQL Server 2005, 2008 or 2008 R2 to SQL Server 2012. You can use the Upgrade Assistant to detect functional and performance issues that might arise as a result of your upgrade. The Upgrade Assistant verifies how your application's Transact-SQL queries will function in SQL Server 2012, allowing you to detect and correct compatibility issues that might be buried in the application's source code.

The Upgrade Assistant is the result of a joint effort between Microsoft and Scalability Experts Inc. (Download it for free at Scalability Experts' website.) The Upgrade Assistant uses workload testing to compare the application's performance between the earlier version of SQL Server and SQL Server 2012. When you follow the steps outlined in the product documentation -- and reflected in the structure of the user interface -- you can identify issues that might affect the application, such as deprecated features or changes to the Transact-SQL syntax.

Upgrade Assistant testing process

The Upgrade Assistant guides you through the steps necessary to verify your application's compatibility with SQL Server 2012. Before you get started, set up the test environment necessary to run your sample workload. In theory you can run your tests against production databases, but that strategy is risky at best. Not only could you negatively affect the performance of your live applications, the back-end databases and the network itself, but you also risk breaking applications and tainting the integrity of the data if naming conflicts arise or transactions become disrupted.

For more information about SQL Server upgrades

Find out five mistakes companies make when performing SQL Server upgrades

Get three tips for SQL upgrades and migration

At the least, your test environment should include a database server on which you can install your current version of SQL Server (2005, 2008 or 2008 R2) as well as the Upgrade Assistant. The server must also support an upgrade to SQL Server 2012. In addition, you must be able to connect to the database server from the application being tested. The application should be a test system so you can execute automated procedures that run the full complement of the application's features. If you use the production application to run your tests, you'll likely be checking a smaller percentage of the functionality.

Once you've set up your test environment, use the SQL Server Upgrade Assistant to carry out the following steps:

  • Back up the relevant system and user databases and capture a sample workload. The Upgrade Assistant captures the workload as trace files that record a representative sample of application queries running against the database.
  • Set up a baseline environment based on the backup and trace files created in step one. As part of this process, run the SQL Server Upgrade Advisor to identify any upgrade issues present in the database trace files and backup files. At this point, you should address any issues that arise before proceeding with the Upgrade Assistant.
  • Replay the sample workload trace created in step one to establish a baseline trace. In a later step, the baseline trace will be compared with a trace against SQL Server 2012.
  • Upgrade the SQL Server instance to SQL Server 2012.
  • Replay the sample workload trace created in step one to establish the upgrade trace against SQL Server 2012.
  • Compare the baseline trace with the upgrade trace to identify any potential issues that resulted from running the sample workload.

Not surprisingly, these steps provide only an overview of the testing process, but they should give you an idea of how the Upgrade Assistant can help you review your data-driven applications before implementing SQL Server 2012.

SQL Server upgrade technologies

You might have noticed that in step two you run the SQL Server Upgrade Advisor. Although not a component of the Upgrade Assistant, it is an important part of the process.

The Upgrade Advisor, which unfortunately has a name very similar to the Upgrade Assistant, will analyze the installed SQL Server components that you're considering upgrading to SQL Server 2012. The Upgrade Advisor generates a report that identifies any issues you should fix in your SQL Server instance before performing your upgrade. The analysis includes database objects such as triggers, stored procedures, scripts and trace files. When you use the Upgrade Advisor in conjunction with the Upgrade Assistant, include the sample workload trace you created in step one.

Another SQL Server 2012 technology important to the Upgrade Assistant testing process is Distributed Replay, a tool used to assess the impact of hardware and operating system upgrades. Like SQL Server Profiler, Distributed Replay can replay a captured trace against an upgraded test environment. The Upgrade Assistant incorporates Distributed Replay functionality to produce more realistic performance testing than could be achieved in previous versions of SQL Server.

The Upgrade Assistant could prove to be a valuable tool for any organization that supports data-driven programs. The last thing you want to do is to upgrade to SQL Server 2012 and break all your applications. Keep in mind, however, that SQL Server 2012 will run a legacy database at a compatibility level equivalent to that of the SQL Server version on which the database was created. This lets you implement SQL Server 2012, yet take the time you need to get your applications up to speed. Your goal should be to get those apps up to the same level as SQL Server 2012, but until then, you have a viable alternative. And when you're ready to update your applications, you'll find the Upgrade Assistant to be invaluable.

Dig Deeper on SQL Server Migration Strategies and Planning