Side-by-Side Migration from SQL 2005 to SQL 2014
First, you need to review what has changed with each versioned release to understand how those changes could affect your application.Below is a breakdown of the categories of each change:
- Discontinued Features: Features that are no longer available
- Deprecated Features: Features that will be removed in a future release
- Breaking Changes: Database engine changes that will affect applications
- Behavioral Changes: Database engine changes that could affect applications
- Run the SQL Server Upgrade Advisor
The SQL Server Upgrade Advisor is a great tool provided by Microsoft to help you understand what needs to be changed within the database before and after migration. The Upgrade Advisor will capture the majority of issues to help make your migration successful, but keep in mind, it is not going to provide you each and everything in detail. Be sure to review all the breaking and behavioral changes mentioned above.
- Back Up the Database
Protect yourself and your data by taking a full backup of the database prior to any migration. This way you have the ability to recover the database in the event anything goes sideways with the migration.
With planning work complete, we can now move on to the migration. Each scenario below assumes there is a shiny new SQL Server 2014 instance as the migration target. In-place upgrades should typically be avoided as they can be very risky with minimal rollback options.
With that said, there are few ways we will discuss to migrate a database to SQL Server 2014:
- Backup and Restore
This is the safest and simplest migration, as a full database backup can be restored to the 2014 instance without affecting the 2005 instance, leaving us with a stable rollback option. To minimize downtime, the 2005 instance can remain online while the full backup is restored to the 2014 instance. Once the restore of the full backup has completed and we are ready to migrate, a differential backup can be used to further synchronize the 2014 instance. At this point you’ll need a maintenance window to take the 2005 instance database offline, take a final transaction log backup on the 2005 instance and restore the transaction log backup to the 2014 instance. Now that the databases are fully synchronized we can point the application to the 2014 instance and validate.
This approach helps to mitigate risk as there is a simple rollback to the 2005 instance if anything goes wrong during the migration window.
- Detach and Attach
Another simple migration option is to detach the database from the 2005 instance and then attach the database to the 2014 instance. This is a good option if your storage system is not changing and your database size might make it prohibitive to consume storage for duplicate copies of multiple databases. (Please note: This won’t allow for a quick rollback option if something goes wrong with the migration. You can restore your initial backup before migration to the 2005 instance, but that will take time depending on the size of the database.).
- Server Objects
Be sure to identify and migrate server logins, SSIS packages, SQL Jobs, and linked servers. This can be a great time to clean up and carry forward only the needed server level objects. For assistance with identifying and scripting logins and roles for a migration see here.
But what about migrating DTS packages? Unfortunately, DTS is a discontinued feature that is no longer supported, which means all DTS packages need to be converted to SSIS packages moving forward. Microsoft shipped a DTS Package Migration Wizard in SQL Server 2008 and 2008 R2 but it only worked for very simple packages, resulting in rewrites for most packages.
DTS xChange is a Microsoft-recommended third-party tool and available from Pragmatic Works. While this tool does have an associated cost, it is more successful at converting DTS packages to SSIS packages and can help avoid long hours rewriting packages manually.
After the migration is complete it is important to perform the following tasks:
- Upgrade Database Compatibility Level
- The database compatibility level needs to be manually changed on the 2014 instance
- Run DBCC CHECKDB WITH DATA_PURITY
- Checks for invalid data values based on column data type
- Run DBCC UPDATEUSAGE
- Corrects page count inaccuracies to reflect accurate information when using sp_spaceused
- Run sp_refreshview
- Ensures that views are up to date
- Update Statistics
- Brings database statistics are up to date to allow the query optimizer to choose the best query plan
- Take a Full Backup of the Database
- Make sure you can recover the database with all of the migration efforts