SQL Server database migration is considered as a process that helps to move the previous implementation to a newer version of SQL Server, and require different locations with a strategic decision. Therefore, in this blog, we are going to discuss most important aspects that need to be examined before and after migrating to the SQL Server. This article is intended to provide guidance that can help to create the migration plan.
Along with this, we have explained some best practices for ensuring a successful SQL Server database Migration in a clear manner.
Methods to Migrate SQL Server Database
1. SQL Server Database Migration Paths
Firstly, we should consider that migration is used to mention the Migration Path. To specify a proper Migration route we need to specify the following terms:
Data Source: To know the version of Microsoft SQL Server and Capabilities of the latest SQL Implementation like SQL Server 2000, 2005 or Enterprise, Standard or 32bit, 64bit or SP1, SP2 and so on.
Data Destination: It is intended for MS SQL Server version that users would like to reach. For example: SQL Server 2012 Enterprise
Supported Path: Every actions and step required to reach the final destination. The following paths are supported to reach MS SQL Server 2012:
Microsoft SQL Server 2005, 2008 and R2 2008 can directly transfer to MS SQL server 2012 after achieving these below Service Packs:
- SQL Server 2005: SP4.
- SQL Server 2008: SP2.
- SQL Server 2008 R2: SP1
Although SQL 2000 will need to upgrade to SQL server 2005 reached before SQL Server 2012 before that Service Packs should be installed:
- SQL Server 2000: SP4 is required.
2. Migration Planning Strategy
Once the Migration Path is defined then, you have to mention the Migration Strategy. Here are some information related to Migration Strategies that can be supported by SQL Server:
- In-place: With the help of SQL Server Setup program users can directly upgrade the running instance of Server 2000/ 2005/ 2008 to SQL Server 2012. An earlier instance of MS SQL Server will be replaced.
- Side-by-side: With steps to migrate multiple or some data from an instance of SQL Server (2000,2005, 2008) to the separate instances of SQL Server 2012. There are two types of variations of the side-by-side upgrade strategy:
- Single Server: Each new instance can exist on the same server as the target SQL server instance.
- Dual Servers: A new instance can exist on a different server than this target instance.
3. SQL Server Database Migration Approaches
Well, after defining the location and strategy the next question arises in users mind is how to perform the migration, and what we learned by Migration Methods.
Following methods can be used to migrate SQL Server Database:
- Backup or Restore
- Detach or Attach
- Manual schema rebuild & data import/export
- Log shipping
- Copy Database Wizard
- Third Party SQL Database Migration Tool
You should consider other components of SQL Server like SQL Server Reporting Services, SQL Server Analysis Service & SQL Server Integration Services that fall outside the scope of this post.
Application Supportability & System Reconfiguration
Many times SQL Server uses few applications that can handle a database within backend i.e., SharePoint, Exchange, Custom Developed Application, etc.
4. Migration Backups
Before preceding migration, we have to make a plan for database backups in a proper manner. We should perform a full database backup in an instance (including system database). Some of the following points are discussed below:
- Create a full database backup after the user out of system and before upgrading the process has started. Make daily backups of all the system databases at some point. Keep in mind that the media intact so that users can restore a backup copy if necessary.
- When upgradation is done, but before doing any kind of configurations or changes, perform backups in SQL Server 2012. This helps user to roll back where MS SQL Server 2005, 2008, and 2008 R2 upgrade successfully completed but the error message was introduced many years after.
Many SQL databases are in thousands of gigabytes or terabytes range. This signifies various challenges in the actual upgrade process because of time constraints and free disk space when dealing with large number of data in the maintenance window. As the database consists data in terabyte range, which takes few days to copy across a network with some of the fastest internet speed. These VLDBs may be database powering a mission-critical application used for business purpose. When users want to update over the weekend, they require several techniques and put few hours to meet the realistic time frames for success. You must have to revise the upgrade window if the upgrade does not fit within it.
Perform Task Before & After Migration
1. Pre-Migration Tasks
Before doing the migration users need to perform some operational tasks as follows:
- Rollup All required Service Packs, Updates, and Hotfixes.
- Run DBCC CHECKDB command on all databases to be sure that they are in the consistent state.
- Check the Backup of All System and User Databases
- Run the Upgrade Advisor Tool
2. Post-Migration Tasks
After completing the data migration, the below steps are performed:
- Integrate one new SQL Server Instance and database within the application and database SQL server environment.
- Determine whether an upgrade can be successful or not
- Decommissioning and removing an unused Server
Migration is not an easy task to transfer database from a single or multiple user databases to the different server. Moreover, we have discussed a manual solution to perform SQL Server database migration. If the database is too large and need to migrate in small down time, then one can go with SysTools SQL Server Database Migrator . Therefore, in this post, we have covered some best practices for database migration in an efficient and convenient way.