Checklist for SIde by SIde SQL server migration project tasks

Hello Readers!

As a senior DBA, one has to become familiar with checklists for each of the planned activities like migration, so as to avoid mistakes.

Below I am trying to list down the steps one should not miss while performing a side by side SQL server migration , that are common for most of the SQL versions. Please comment below,  if you got anything more to add, which will help us to learn more together. 

Pre Activity Checklist

1.Raise tickets/Inform stakeholders as needed and request a realistic downtime as required which includes a rollback time incase of failure.

2.Get the target system ready with the help of server team and make sure it has got the right windows OS, enough storage, memory and CPU assigned with the desired I/O Throughput.

3.Install the SQL server with the desired version and edition (equal or higher than the Source server instance)

4.Collect the information about the Database properties (Auto Stats, DB Owner, Recovery Model, Compatibility level, Trustworthy option etc)

5.Collect the information of dependent applications, Scheduled batches and  make sure application services and batches  will be stopped during the database migration

6.Collect the information of database logins (Revlogin Script output), database users and their permissions including the DB role memberships

7. Check the databases for the Orphan users to see if there are any already existing in the database before migration.

8.Check the SQL Server for any dependent objects (Example : a job working with an external file)

9.Backup  Linked servers, SQL Server audit specifications, credentials and Certificates

10.For bigger database servers , plan for Full back up prior to the activity and a differential during the activity window.

11.Back up Sp_configure options with 'Show advanced options' from the source server.

12.Put the server in maintenance mode for the scheduled window in monitoring tools to avoid unwanted alerts or intimate the team who is responsible to do it.

13.Make sure to test the migration in DEV,UAT boxes before going for Prod instance migration.

14.Make sure to note down the recent Security patch updates (GDR/QFEs only) which could be applied during the activity and incorporate security patching in the migration plan.

15.Note down Protocols, ports,service accounts credentials and start up options in SQL configuration options.

16.Note down Alerts, Operator, Mail configurations in the source server

17.Make separate plans for report service and SSIS package migrations.

Checklist to follow during the activity window

1.Intimate stakeholders that the activity is about to start ,over email.

2.Backup the database on the source with compression over to the relevant server/sharepath

3.restore link servers, credentials, and instance audit specifications and make sure to enable the instance audit in both instance and DB level.

4.Check backup headerfor date and source before restoring, to confirm if the right backup is being restored

5.Restore backups and  jobs, linked servers with 'if not exist' clause on to the target machine.

6.Change the compatibility Levels to relevant values in the target server, if the source databases are from server with lower SQL version.

7.Migrate the logins mapped to the users of the migrated databases using Sp_help_revlogin script generated earlier.

8.Fix the orphan users if reported

9.If the windows server get renamed, Please do change the instance name by sp_dropserver and sp_addserver method

10.Compare and reapply Sp_configure options backed up from the source server

11.Compare the protocols, Ports and start up options  of the source server with the new server instance and adjust the settings  accordingly.

12.Make sure the mail configurations, alerts and Operators are set as required.

Post -Activity Checklist

1.Confirm connection to the database server from a report machine / Telneting with SQL port.

2.Update the CMDB and the inventory sheets .

3.Do an overall sanity test for the instance before handing it over to the application team

4.Email and get confirmation from the application team that there are no issues reported.