On March 7, 2012, Microsoft went live with a big virtual launch event for SQL Server 2012. In case you missed the launch event, you can still view all the videos, download the presentation slides, and learn more information at the Microsoft SQL Server 2012 launch event website. However, is there any help if you want to test the plausibility of an existing application's upgrade from an earlier version of SQL Server to SQL Server 2012? As it turns out, there are two tools that can help you.
- SQL Server Upgrade Advisor (SSUA)—This Microsoft tool analyzes an existing older version of a SQL Server database and identifies any issues with existing objects. For example, the SSUA tool will identify any stored procedures that use old and deprecated syntax. This tool currently tops out at SQL Server 2008 R2, but it's still very useful. To learn more about this tool, see the Microsoft "Use Upgrade Advisor to Prepare for Upgrades" page.
- Upgrade Assistant for SQL Server 2012 (UAFS)—This tool, which is written by Scalability Experts and Microsoft, incorporates SQL Server 2012's new distributed replay feature to incorporate workload testing to identify compatibility issues that exist in an application's source code, such as an ASP.NET application, rather than inside the database. Because this tool is specially designed for SQL Server 2012, we'll focus on it in this article.
Remember that you shouldn't undertake an upgrade to an important business-critical database lightly. You wouldn't think that I'd need to say this, but I've received so many desperate emails from users who've jumped right into an upgrade with inadequate analysis. Preparing and testing in advance for an upgrade is the only way to be certain that the application will continue to function like it did before the upgrade. These two tools will make your upgrade much easier.
The UAFS tool walks you through the entire upgrade process, from backing up the databases that you'll test, capturing a workload trace, and replaying the trace for functionality testing. The process finally finishes by comparing the results to identify differences between the database running on the older version of SQL Server and the version running on SQL Server 2012.
This tool also makes significant use of the distributed replay feature, which makes it possible for the tool to replay a traced workload through multiple clients (see Figure 1). After running the tests and capturing the workloads' results, you can produce and review reports that quickly identify problems by comparing overall performance (located in the Summary Info section) or event by event performance (located in the Event Detail section) on the baseline and test server. As Figure 2 shows, you can also filter out events and details to better prescribe the analysis.
The UAFS tool requires several components, including the following:
- The original test or production server. The server will need to be upgraded if it was created with SQL Server 2005 through SQL Server 2008 R2.
- A baseline server that's similar to the original server, on which we can replay workloads without affecting the performance of the production server.
- A SQL Server 2012 instance in which you'll run the upgrade tests and separately host the reports and analysis.
- One or more clients to replay the workload. Please refer to the UAFS User Guide, which you can download from the Scalability Experts website, for detailed setup and workflow instructions.
To download the UAFS tool, visit the Scalability Experts website. Feel free to ask questions and provide feedback at Microsoft's SQL Server Setup & Upgrade forum. Also, there's an extensive set of documentation at the Microsoft "Upgrade Assistant Tool for SQL Server 2012" web page. As always, SQL Server Pro wants to hear your feedback at the Tool Time discussion forum.
Upgrade Assistant for SQL Server 2012
SQL Server Upgrade Advisor