Introduction

SQL Server Migration is the process of stirring a previous implementation of SQL Server to a newer version, different location (or both) due to  a strategic decision (such as SQL Server Consolidation). In this post I’m trying to highlight some of the important aspects that we need to consider Before, During & After having an SQL Server Migration. This post is intended to be a general guidance to help you create a migration plan. Additional useful references are provided at the end of this post to give you more details.

Migration Paths

The first thing that we need to consider when having a migration is to define the Migration Path. To define a proper Migration Path we need to specify the following:

  • Data Source: To understand the SQL Server Version and Capabilities of the current SQL Implementation (such SQL Server 2000, 2005 or Enterprise, Standard or 32bit, 64bit or SP1,SP2 … etc.)
  • Data Destination: Is the intended SQL Server Version that you would like to reach (ex: SQL Server 2012 Enterprise)
  • Supported Path: Are the steps and actions needed to your destination. The below example show the possible supported path to reach SQL Server 2012:

SQL Server 2005, 2008 & 2008 R2 can directly migrate to SQL 2012 after achieving the below Service Packs:

  • SQL Server 2005: SP4.
  • SQL Server 2008: SP2.
  • SQL Server 2008 R2: SP1

However SQL Server 2000 needs to be upgraded to SQL 2005 (or later) before reaching SQL Server 2012, below Service Packs must be installed:

  • SQL Server 2000: SP4 is required.

Migration Strategy

After defining the Migration Path you need to define the Migration Strategy. Below are some details of the Migration Strategies supported by Microsoft SQL Server:

  • In-place: Using the SQL Server Setup program to directly upgrade an instance of SQL Server 2000, 2005, 2008 to SQL Server 2012. The older instance of SQL Server is replaced.
  • Side-by-side: Using steps to move all or some data from an instance of SQL Server (2000,2005, 2008) to a separate instance of SQL Server 2012. There are two main variations of the side-by-side upgrade strategy:
  • One Server: The new instance exists on the same server as the target instance.
  • Two Servers: The new instance exists on a different server than the target instance.

Migration Methods

So after defining the Path & Strategy the next logical question that comes in mind is how to do the migration, and its exactly what we mean by Migration Methods. Below are some methods that we can used to Migration the SQL Server Database (for clarification purpose):

  • Backup/Restore
  • Detach/Attach
  • Manual schema rebuild and data export/import
  • Log shipping
  • Copy Database Wizard

Note:

You also need to consider other SQL Server Components (such as SQL Server Reporting Services, SQL Server Analysis Service & SQL Server Integration Services) which falls outside the scope of this post.

Application Supportability & Reconfiguration

Most of the time SQL Server Database uses and Application that handles the database in the backend (such as SharePoint, Exchange, Custom Developed Application … etc.), so we need to answer the following questions before proceeding with the migration:

  • Does the Application Supports the new SQL Server Version (such as SQL Server 2012)?
  • What are the reconfiguration needed from the application vendor to reactivate the application (such as connection strings, custom code … etc.)?

Migration Backups

Before preceding with the migration we need to plan properly for database backups. We need to perform a full backup of each database in an instance (including system databases) at the following points in the upgrade process:

  • Make a backup of the user databases and data after all users are out of the system and before the upgrade process has begun. Back up all system databases at this point. Make sure that the media is intact so that you can restore the backups if necessary.
  • When the upgrade is complete, but before you do any configurations or changes, perform backups under SQL Server 2012. This lets you easily roll back to a point where the SQL Server 2005/2008/2008 R2 upgrade completed successfully but where an error was introduced after that point.

    VLDBs

    Many SQL Server databases are in the hundreds of gigabytes or terabytes range. This presents special challenges in any upgrade process because you have to account for constraints in time and hard disk space when you deal with large amounts of data in a short window of maintenance time. Databases in the terabyte range can potentially take days to copy over a network—even the fastest networks. These VLDBs might be mission-critical databases powering the largest systems in your business and might tolerate very little downtime. When an upgrade has to occur over a weekend, you might have to use several techniques and put in many preparation hours to meet the required time frames for success. You might have to revise the upgrade window if the upgrade will not fit within it.

    Pre-Migration Tasks

    Below are some operational tasks that needs to be considered in your plan before starting the migration:

    • Rollup All Needed Service Packs, Updates & Hotfixes.
    • Run DBCC CHECKDB on all databases to make sure that they are in a consistent state.
    • Verify Backup of  All System & User Databases
    • Run the Upgrade Advisory Tool

    Post-Migration Tasks

    After the completion of the migration, the below steps are required:

    • Integrate the new SQL Server Instances & DBs into the application and database server environment.
    • Determine whether the upgrade was successful (Agree on a success criteria with the application original vendor).
    • Decommission and Uninstall the unused servers

    Additional References:

    Below are some useful references that provides more information about the items mentioned in the above post: