• Home
• Topics
• Microsoft SQL Server Management Issues
• SQL Server Migration Strategies and Planning
• SQL Server Upgrade Advisor, Upgrade Assistant can ease transition pains

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.

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.

More from Related TechTarget Sites

• Business Analytics
• Data Center
• Data Management
• Data Management UK
• Oracle
• Content Management
• Windows Server

• Business Analytics

  • NASA uses text analytics to bolster aviation safety

    NASA relies on numeric data to explain what incidents occur, but text data from incident reports and warning signals reveals why they do.

  • Advanced analytics helps US Open build online sweet spot

    Advanced analytics play a key role in combining entertainment, sports and business at the US Open.

  • Download Big Data and its Impact on Data Warehousing

    The “big data” movement has taken the information technology world by storm. Fueled by open source projects emanating from the Apache Foundation, the big data movement offers a cost-effective way for organizations to process and store large volumes of any type of data: structured, semi-structured and unstructured.

• Data Center

  • Businesses can grow through workload automation evolution, Podcast Q&A;

    The workload automation evolution has helped IT stay on top of evolving business needs. Drag your feet and get left behind.

  • Is direct current power the silver bullet for data center efficiency?

    Direct current power shows promise for better data center energy efficiency, but IT shops may balk at upfront costs and hardware lock-in.

  • A deeper look inside the zEnterprise EC12's mainframe architecture

    Upgrades to IBM's mainframe may prompt z196 customers to switch. Now that IBM's zEC12 is here, we take a look inside the new mainframe architecture.

• Data Management

  • New Gartner Magic Quadrant finds demand rising for data quality tools

    Organizations are using data quality tools to support a growing range of use cases, according to a new Gartner Magic Quadrant report.

  • VoltDB, Actuate team up to offer speedy 'big data' management

    VoltDB and Actuate have partnered to offer rapid-fire 'big data' management, plus more data management technology news from around the Web.

  • Misconceptions holding back use of data integration tools

    Integration software has matured in recent years, but consultant Rick Sherman writes that many IT managers aren't aware of the increased capabilities.

• DataManagement UK

  • Dynamic Markets: CFOs see worth in data on balance sheet

    About 20% of UK companies are assigning a financial value to their data, according to research sponsored by SAS. But only 16% of UK companies have a formal data management strategy.

  • Data ethics: Author warns of ethical pitfalls of data collection

    Heedless data collection in business analytics programmes is breeding moral hazards, according to author Frank Buytendijk.

  • Data visualisation success hinges on solid storytelling skills

    Tableau's Jock Mackinlay explains why data is inert and worthless without the twin practices of visualisation and storytelling.

• Oracle

  • All SAAS is Not Created Equal

    SaaS CRM has evolved into a mature software category. Yet unlike many other industries where products become more commoditized over time, the differences between SaaS CRM offerings have never been more pronounced. Given this fact, what are the best criteria on which to base a purchase of SaaS CRM? This resource gives you the top 5 areas to consider.

  • 4 Steps To Build Loyal High-Value Customers

    Loyal customers are your company's most valuable asset. Not only is it less expensive to retain a customer than to acquire a new one, loyal customers spend more, try other brand offerings, purchase more profitable products and services, and are more likely to refer customers to your business. Check out “The Customer Loyalty Playbook” to learn four steps to help guide your customer loyalty strategy.

  • Book excerpt: Effective MySQL Backup and Recovery

    As a DBA, you need to create an effective strategy. How? Read this excerpt from Ronald Bradford's 'Effective MySQL: Backup and Recovery' to find out.

• Content Management

  • Ektron increases conversion rates with dynamic Web content technology

    With Demandbase's Real-Time Identification, Ektron generates dynamic Web content for visitors to its site, increasing leads and online conversions.

  • Gartner Magic Quadrant: Mobile, social trends alter WCM market

    The new Gartner Magic Quadrant for Web content management adds Acquia, points to needs of social media and interoperability as altering WCM market.

  • Schema.org site aims to standardize tags for Web content managers

    Schema.org is a group working toward a standard for HTML tags to help Web content managers develop metadata for better search results across the Web.

• Windows Server

  • Why does a PowerShell command run fine locally, but not remotely?

    Admins starting to use PowerShell's Remoting features may run into some growing pains with remote commands. Combat these with a few simple tricks.

  • Microsoft details SCCM SP1, new Intune version, September patches

    System Center Configuration Manager 2007 receives a patch, plus Microsoft delivered a beta version of a service pack for the 2012 edition of the product.

  • Microsoft Online Backup Service in Windows Server 2012: Under the hood

    Weigh the pros and cons of Microsoft Online Backup Service in Windows Server 2012. Some features could benefit IT shops.

• About Us
• Contact Us
• Site Index
• Privacy policy
• Advertisers
• Business partners
• Events
• Media kit
• TechTarget Corporate site
• Reprints
• Site map