SQL Server: Planning Your Migration to SQL Server 2008 R2
Moving to SQL Server 2008 R2 is a big deal. Make sure you examine your environment and carefully plan your steps to ensure a smooth migration.
Brien Posey
Moving to SQL Server 2008 R2 isn’t always cut and dry. It’s a major upgrade and a huge transition. Unlike many other Microsoft products, though, SQL Server 2008 R2 does support in-place upgrades. If you’re running SQL Server 2000, 2005 or 2008, you may be able to simply install SQL Server 2008 R2 over your existing SQL Server.
For the most part though, moving to SQL Server 2008 R2 won’t be as simple as inserting the installation disc and working through an installation wizard. There are some situations in which you’ll have to migrate rather than upgrade your SQL Server. This will explain some of your options for making the move to SQL Server 2008 R2.
Sound Advice
A thorough examination of your infrastructure is always advisable prior to a major upgrade. In this case, the SQL Server Upgrade Advisor can help you determine whether or not you‘ll be able to upgrade directly to SQL Server or if you’ll have to do a migration. The job of the Upgrade Advisor is to analyze your SQL servers and report any issues that might get in the way of a successful upgrade. You can either correct those issues prior to upgrading, or start making plans for migrating.
The SQL Server Upgrade Advisor is a free tool included with the SQL Server 2008 R2 Feature Pack (you can download it from the Microsoft Download Center). Once you’ve downloaded and decompressed the SQL Server 2008 R2 Feature Pack, you‘ll find the Upgrade Advisor in the \X64\Redist\Upgrade Advisor folder. There are x86 and Itanium versions of the Upgrade Advisor as well, located in the \X86 and \IA64 folders, respectively.
Most SQL Server installations are mission-critical, so you may be reluctant to run a utility such as the Upgrade Advisor for fear it may cause problems. Such fears may be further fueled by the fact that there are a number of prerequisite components you need to install before even running the Upgrade Advisor.
Thankfully, you won’t usually have to run the Upgrade Advisor directly on your SQL server. You can install it on Windows XP (SP2 or later), Windows Vista, Windows 7, Windows Server 2003 (SP2 or later) or Windows Server 2008. Once it’s installed, you can use it to analyze your SQL servers remotely. The one exception is that if any of your servers are running the SQL Server Reporting Service, you’ll have to run the Upgrade Advisor locally on those servers.
Although it does a good job of anticipating issues that will get in the way of an upgrade, there are a few things the Upgrade Advisor doesn’t check. For example, it can’t analyze desktop applications or Encrypted Stored Procedures. It also has trouble checking discontinued features.
Remember, SQL Server 2008 R2 supports upgrades from versions of SQL Server as old as 2000. SQL Server has evolved a lot over the years. Microsoft has added new features in each version of SQL Server, but there have also been many features removed. In some cases, the Upgrade Advisor doesn’t report on issues related to obsolete features. Another example of this is that the Upgrade Advisor doesn’t analyze Notification Services—they were removed in SQL Server 2008.
There’s a TechNet article that lists all of the SQL Server backward-compatibility issues, which include discontinued and depreciated SQL Server features, as well as some behavior changes that could potentially break SQL Server applications. That article only covers features changed or removed since SQL Server 2008. If you’re running an older version of SQL Server, you’ll have to do some additional research to find out what might have changed since your version was released.
The Upgrade Path
If you decide you want to try an in-place upgrade, you’ll need to work within the permitted Microsoft upgrade paths. Essentially, this means the version and edition of SQL Server to which you can upgrade is determined by the version you’re currently using.
For example, suppose you have one server running SQL Server 2005 (x64) Enterprise with SP2. Your only options for upgrading to SQL Server 2008 R2 would be to upgrade to either the Enterprise Edition or the Datacenter Edition. Microsoft has a detailed chart outlining which editions of SQL Server 2008 R2 are supported in various upgrade scenarios. There are many caveats to the upgrade paths, so you should look at that chart before purchasing your SQL Server 2008 R2 licenses. There are still, however, a few general rules that hold true for most upgrade paths:
- You can only upgrade to the same edition or a higher edition of SQL Server. For instance, if you’re operating the Datacenter Edition now, you can’t use the upgrade process as a way of downgrading to Enterprise Edition.
- You must continue to use the same CPU architecture. If you’re running a 32-bit edition of SQL Server right now, you must continue to do so.
- There’s no upgrade path for servers running an Evaluation Edition or Personal Edition of SQL Server.
The Upgrade Advisor is a great tool for spotting potential upgrade issues, but it isn’t foolproof. Because there are several conditions for which the Upgrade Advisor can’t test, you should perform a trial upgrade, if possible.
Make a full system backup of your SQL Server, a couple of domain controllers and any other necessary infrastructure servers. Restore those backups to a virtual server not connected to your production network, thus creating a clone of your production SQL deployment. Once this clone is running, attempt to upgrade it to SQL Server 2008 R2, and perform any necessary post-upgrade testing.
Some environments are too complex for this type of testing. In those situations, set up a test deployment that mimics your production SQL Server deployment as closely as possible. Remember, you may still discover issues during or after an upgrade. It’s much better to discover any issues in a lab environment than on your production servers.
Time to Migrate
Migrations are useful for more than just situations when an in-place upgrade is impossible. You can also use a migration as a method for changing server topologies (like moving from a 32-bit deployment to a 64-bit deployment), or to move your SQL Server to new server hardware.
Before attempting to migrate any databases to SQL Server 2008 R2, download a copy of the Microsoft Assessment and Planning (MAP) Toolkit for SQL Server 2008 R2. MAP doesn’t perform the actual migration process, but it can help you with migration planning. Some of the things the MAP Toolkit can do include:
- SQL Inventory: The MAP Toolkit can identify all instances of SQL Server running in your organization. It can also provide you with version information.
- Hardware Information: When the MAP Toolkit inventories your SQL deployment, it can also report on the hardware (physical or virtual) on which your SQL servers are running, as well as the CPU architecture (32-bit or 64-bit).
- Migration Planning: The MAP Toolkit can help you determine which SQL servers are good candidates for migration, based on your requirements.
Migration Mechanics
The exact steps you’ll use to perform your SQL 2008 R2 migration will vary depending on the version of SQL Server from which you’re migrating. Even so, here’s an overview of what’s involved in the actual migration.
At its simplest, the migration process involves setting up a server with SQL Server 2008 R2. Then you have to restore a backup of your legacy databases to that server. As with many things in life, however, the devil is in the details.
The first detail is the method by which you’ll back up your legacy SQL Server. You must back up your server in a way that preserves all transactions. Ideally, you should set the access property for the databases to “Single_User” or set the database to “Read Only.”
One problem with doing this is that the backup/restore process can be time-consuming. Depending on the migration timetable, a full backup and restoration may simply take too long. You can reduce the amount of time of the migration by doing a full backup ahead of time. Then run an incremental backup just before the actual migration.
When you need to restore the database to your server with SQL Server 2008 R2, you’ll have to specify some restore options through the SQL Server Management Studio. Specifically, make sure you name the new database exactly as the database you’re restoring. Also, be sure to select the “Leave the Database Ready to Use By Rolling Back Uncommitted Transactions” option, which you’ll find in the Recovery State section.
When your restoration is complete, there are two more things you’ll have to do. First, set the database access property to Multi-User, so your users can begin using it again. Next, set the database compatibility level to SQL 2008 (Mode 100). If you omit this step, your databases may remain in compatibility mode. Depending on the original version of SQL Server, compatibility mode may prevent features introduced in SQL Server 2005 and SQL Server 2008 from working.
As you can see, migrating to SQL Server 2008 R2 isn’t all that difficult, but it does require a lot of specific steps. You need to carefully plan your migration ahead of time to avoid any compatibility issues.
.jpg "Brien Posey")
Brien Posey, MVP, is a freelance technical author with thousands of articles and dozens of books to his credit. You can visit Brien’s Web site at brienposey.com.