Prerequisites

These steps are for migrating single instances with no clustering, mirroring or availability groups. Additional documents will be added to cover checklists for these but as a general rule you should tear down any high availability/scale-out features, migrate databases to the new instances and rebuild the feature.

In place upgrades are not recommended. You should build a new instance on a new server, ideally with a newer OS and hardware and migrate databases to this new instance during a downtime window.

It is assumed that the new instance has been configured in advance with appropriate settings for tempdb files, max memory, user database default data and log locations, trace flags, database options and so forth.

Migration methods to move databases to a new server

1. Backup the database and restore it to the new server using the WITH MOVE command if required to place the database files in a different file path.
2. Detach the database and move the files to the new server either by copying them or having a storage administrator move the LUN.

Migration Steps

Note when scripting database objects using SSMS, it is a good idea to configure appropriate Scripting settings under the options menu.

Pre move steps
On old instance pre-migration day
  1. Run DBCC CHECKDB WITH DATA_PURITY (if there are any errors fix these before migration). This step is particularly relevant if you have a database that has been upgraded from prior to SQL 2005.

All steps should be scripted in advance.

Migration
  1. Apply logins to the new instance. This is a good time to review carefully and remove any unnecessary logins.
  2. If you linked servers, recreate these on the new instance and test them.
  3. Create database/application roles
  4. Create local jobs on the new instance. Leave the SQL agent offline.
  5. Shutdown the SQL agent on the old instance.
  6. Teardown replication if in place.
  7. Take tail log backup of the databases on the old instance, placing them into a read-only standby state.
  8. Detach databases and copy to the new instance or have the LUN migrated to the new server.
  9. Attach the databases to the new instance. A before and after count is recommended to confirm all databases have been migrated and upgraded successfully.
  10. Make databases read-write and take them out of standby mode.
  11. Apply any configuration changes required for database maintenance (e.g. you may have a maintenance database and need to update backup paths or similar meta-data).
  12. Change the database owner to SA on all databases.
  13. Run DBCC CHECKDB WITH NO_INFOMSGS against all databases
  14. Update statistics on all indexes and statistics objects, ideally WITH FULLSCAN
  15. Fix orphaned logins
  16. Apply security permissions to database/application roles, logins, users etc.
  17. Rebuild replication if required
  18. Set the correct compatibility level for each database
  19. Update page verification to CHECKSUM, if not already set.
  20. Start the SQL agent on the new database
  21. Take old database offline to avoid reports running on old database by mistake (typically excel spreadsheets are run on Ad hoc basis).

Last edited Jan 26, 2014 at 10:28 AM by geoffrclark, version 9