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.
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:
SQL Server 2005, 2008 & 2008 R2 can directly migrate to SQL 2012 after achieving the below Service Packs:
However SQL Server 2000 needs to be upgraded to SQL 2005 (or later) before reaching SQL Server 2012, below Service Packs must be installed:
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:
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):
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.
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:
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:
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.
Below are some operational tasks that needs to be considered in your plan before starting the migration:
After the completion of the migration, the below steps are required:
Below are some useful references that provides more information about the items mentioned in the above post: