SQL Server 2012 Upgrade and Application Compatibility
Introduction
The [[SQL Server 2012 Developer Training Kit Content]] is designed for developers who want to exploit all the great new improvements in this release. But what about all of those applications that were developed for previous releases of SQL Server? This wiki is for developers, testers and service professionals who are responsible for certifying that their application supports SQL Server 2012.
In a perfect world, applications that were developed for previous versions should just work in SQL Server 2012. Believe me, Microsoft puts a lot of time and energy into making this possible for a large range of applications. If you have a relatively simple application that just uses the database engine and doesn't use a lot of advanced features, and you aren't worried about the possibility of upgrade related downtime, then you may want to just pull the trigger and try an in-place upgrade of your SQL Server instance.
IT shops often use version upgrades as an opportunity to upgrade or consolidate hardware. If you want to be a little bit more conservative, you can back up your existing SQL Server databases, and restore them on a shiny new SQL Server 2012 installation, and just repoint your connection strings to the new instance. You can always switch back to the old instance since it wasn't touched during the upgrade process.
Sadly, magic fairy dust does not always suffice for mission critical applications. If your business operations depend upon the availability of a SQL Server application that you intend to upgrade, you should probably be putting together a test plan that ensures that your application will work properly against SQL Server 2012. You might even want to do a few dry runs of an actual upgrade before you do the "real" upgrade so there aren't any surprises or unexpected down time.
This wiki is for those poor paranoid souls who don't believe in magic fairy dust. If you want to be more predictable and systematic about your upgrade, you really need to test the applications that will be impacted. We call this Upgrade and Application Compatibility testing, or just AppCompat for short. We have developed tools and methodologies to make AppCompat testing easier, but you need to understand up front that it will require a significant commitment of time and resources to complete the testing process. You will be subjected to a fairly steep learning curve when testing your first application, but once you have that under your belt the next one should be a snap!
Testing business critical applications for compatibility with SQL Server 2012 is an important part of any comprehensive upgrade plan. Whether you've developed the application yourself or purchased it from a third party, there is an extensive set of tools and strategies you can use to identify potential upgrade blockers so they can be addressed before an upgrade is attempted.
This wiki is focused primarily on SQL Server 2012 Upgrade And Application Compatibility Testing (AppCompat) and describes how to systematically identify potential upgrade blockers in applications that have a dependency on the SQL Server database engine. We will provide some general upgrade guidance focused on the SQL Server database engine, but you may need to find some additional resources to plan your upgrade if you are using advanced capabilities like replication, database mirroring or failover clustering. We do not address upgrades of Integration Services, Reporting Services or Analysis Services in this wiki.
Here's a high-level outline of the steps involved in a typical SQL Server 2012 database engine upgrade:
- Identify Upgrade Targets
- Identify and Fix Upgrade Blockers
- Choose an Upgrade Strategy
- Perform Your Upgrade
Identify Upgrade Targets
Upgrading to SQL Server 2012 may be as simple as upgrading a couple of instances for a small company or department, but large companies might have hundreds of SQL Server installations, some of which the IT department may not even be aware of. So step one is to get an inventory of all the SQL Server installations that are potential upgrade targets. If you need help identifying upgrade targets that you aren't aware of, check out the Microsoft Assessment and Planning Toolkit (also known as the MAP Toolkit).
Once you've found an upgrade target, you also need to identify the applications that use it. Applications that are critical to business operations should be flagged for AppCompat testing.
Identify and Fix Upgrade Blockers
Once you've identified the SQL Server installations and associated applications that you intend to upgrade, the next step is to identify anything that would prevent a successful upgrade. The following sections describe three different (and complimentary) approaches for detecting upgrade blockers. You may decide to use some, all or none of them, the choice is yours. The amount of testing and preparation you do in advance will have a direct result in how prepared you are to deal with problems that occur when the time comes to perform your upgrade.
Perform Initial Health Check
You can start with the basics by checking the physical health of the SQL Server installation and its associated databases with tools like DBCC (database consistency checker). Next, you may want to consider shrinking the physical size of the database which can be handy if your upgrade strategy involves moving the database to new storage.
You may also consider using the SQL Server 2008 R2 Best Practices Analyzer to scan your instance and databases for potential violations of best practices. It's a good idea to clean these up before doing an upgrade. You should also check to see if you are starting the instance with any special trace flags and whether you still need them after you upgrade. It's a good idea to eliminate these if possible.
Run Upgrade Advisor
Next, you should scan the instance and all of its databases for potential upgrade blockers using SQL Server 2012 Upgrade Advisor. This tool runs a set of rule-based checks to identify common upgrade blockers, and produces a nice actionable report as its output. You should then systematically address all of the rule violations before attempting to upgrade. It's important to note that Upgrade Advisor uses static analysis, and will not detect issues that are hidden in application source code or that may only occur at runtime.
Perform AppCompat Testing
The final step is to use the [[Upgrade Assistant Tool for SQL Server 2012]] (UAFS) to perform AppCompat testing for applications that are critical for business operations. UAFS allows you to capture the interaction between your application and SQL Server and save it as a test workload. The tool then walks you through replaying the test workload against the original SQL Server version to establish a baseline, and then again against SQL Server 2012.
The output of these two workload replays is then systemically examined for differences. In some cases, differences may be the result of an upgrade blocker that must be addressed in your application source code. AppCompat testing with UAFS is only as good as the test workload. If your workload covers most of the different types of interaction that your application has with SQL Server, it should be fairly comprehensive. But if you only test a "hello world" workload, you won't get much useful information out of it.
For instructions on how to build a virtual machine configuration for AppCompat testing, see [[How to Build a VM for Application Compatibility Testing for SQL Server 2012 (WEKA build)]].
Important: In order to maintain backward compatibility, setup preserves the existing compatibility level of each database during an upgrade. The ultimate goal of AppCompat testing is to ensure that your application functions correctly using the database compatibility level for SQL Server 2012 (110), so make sure you are testing your workloads against this compatibility level. Make sure you bump your application databases to this new compatibility level post-upgrade. This will ease future migrations after SQL Server 2012 by ensuring that you aren't using deprecated features. See this article in SQL Server Books Online for a comprehensive discussion of backward compatibility including discontinued and deprecated functionality in SQL Server 2012.
Disclaimer: I wish I could claim that AppCompat testing with UAFS was easy. It is not. It has never been easy, and has always required a significant investment of time and resources to do properly. To further complicate matters, we re-engineered UAFS to leverage new Distributed Replay functionality in SQL Server 2012. The goal here was to expand the capabilities of AppCompat testing to include not just T-SQL functionality, but performance as well. Not a bad goal, and UAFS is our first attempt to try to enable this. Unfortunately this new dependency makes simple functionally testing 10 times harder. So, in the interest of full transparency, here is some guidance to consider before you embark down this arduous path. AppCompat testing with UAFS should only be undertaken by experienced QA engineers with access to ample lab hardware for the most mission critical SQL Server applications. That's the kind of scenario this tool was designed for. If you have a relatively new application that was built for SQL Server 2008 or later, you may want to skip AppCompat testing with UAFS altogether and focus instead on using Upgrade Advisor and anecdotal smoke testing to uncover upgrade blockers.
Choose an Upgrade Strategy
There are as many upgrade strategies as the day is long, and it's not our intent to enumerate all of them here but to give you a high-level summary of a few of the most common approaches. One thing that is common to all of these approaches is the need to run SQL Server 2012 Setup to install new bits. It's worth mentioning that setup now supports downloading product updates during installation so you won't have to spend extra cycles downloading and installing service packs after you finish your upgrade.
It's a good idea to go through one or more test runs of your upgrade before you do the real thing provided you have the hardware and resources to do so.
**Important: ** Back up all of your databases before starting your upgrade! Do a test restore of your backups to make sure they are good!
In-Place Upgrade
This is probably the easiest of all of the upgrade strategies. As long as the computer meets the minimum system requirements, setup will allow you to upgrade a SQL Server installation in-place. The instance name is preserved so you don't have to update the connection strings of dependent applications.. You can only upgrade to the same or higher edition using this approach.
In-place upgrade is only supported for the following versions of SQL Server (note minimum required service pack level):
- SQL Server 2005 (SP4)
- SQL Server 2008 (SP2)
- SQL Server 2008 R2
If you are running SQL Server 2000, you will need to upgrade to SQL Server 2008 R2 first before attempting to upgrade to SQL Server 2012.
Note that you cannot change platforms as part of your in-place upgrade. For example, if you are currently running SQL Server 2008 R2 (x86) on Windows Server 2008 R2 (x64), you cannot perform an in-place upgrade to SQL Server 2012 (x64). You can only upgrade in-place to SQL Server 2012 (x86).
The disadvantage of this approach is that there is no going back short of uninstalling SQL Server 2012 and re-installing your previous edition or restoring the whole server from a backup, so this is probably only suitable for servers that don't affect business operations in any substantial way.
Side-By-Side Upgrade
This approach is also fairly easy. The idea is to install a new instance of SQL Server 2012 side-by-side with your old instance on the same computer. Once your new instance is up and running, you can simply detach the user databases from the old instance, attach them to the new instance, and connect your applications to the new instance name.
If you encounter problems or issues, you can simply restore backups of your databases to the old instance and reconnect your applications. Once you are confident that all is well, you can uninstall the old instance.
Upgrade By Migration
This is the ideal way to do an upgrade if you have the resources to pull it off. Basically you provision a new computer and install a fresh copy of SQL Server 2012 on it, restore backups of your application databases, then reconnect your applications to the new instance. If you encounter problems you can always go back to using the old computer until you've sorted things out.
This approach is much cleaner, ensuring that only the latest bits are in use on the new computer which cuts down on servicing requirements since you don't have to worry about maintaining old and new bits. There are all kinds of tricks that you can play to make the failover to the new computer as fast as possible to minimize downtime resulting from the upgrade.
Lots of IT shops prefer this model so they can upgrade hardware and software in the same motion and possibly consolidate servers at the same time.
Perform Your Upgrade
Once you have finished all of your pre-upgrade planning and testing, it's go time! Hopefully the tools and strategies we've outlined in this wiki will ensure that you are prepared for any issues or problems that might arise. Once you've finished your upgrade, don't forget to bump up the database compatibility level of your user databases to SQL Server 2012 (110). It's also a good idea to monitor the performance and behavior of your applications more closely than usual for a while once your upgrade goes live. Even the best planned upgrade can miss a few subtle things that might affect application performance or functionality.
Return to [[SQL Server 2012 Early Adoption Cook Book]] wiki article.
Other Languages
This article is also available in the following languages:
Brazilian Portuguese (pt-BR)
- [[Compatibilidade de aplicativos e atualização para SQL Server 2012 (pt-BR)]]