Sql-server – Please confirm pre and post sql server instance migration steps

migrationsql server

I would appreciate some help please.

We are migrating a number of VMs with different versions of SQL instances from site A to site B, one instance per VM. Site A and B are in different domains. In Site B, the user ids are going to be different from what they are in Site A, so logins will need to change.

I have the following steps as my Pre & Post migration steps, could you kindly comment on them please, to see if I'm missing any thing? Please note also any question beside any of the steps listed below:

Pre-Migration tasks:

  1. In the source instance (in Site A), enable mixed mode authentication and set 'sa' password, in case it is required for login in to SSMS in Site B.

  2. Script out the existing SQL logins using Microsoft's script

  3. In a text editor, edit the above scripted out create login script to replace the existing Site A login ids with the new Site B login ids, the hashed passwords in the script are retained.

  4. Take a full backup of all the databases before the work begins

  5. Stop all SQL Server services and change their start to manual

Migration

  • The VM is cloned in Site A and migrated as-is to Site B by the server team.
  • Question – Would you agree that the SQL services remain shut down while this server clone is taken?

Post-Migration steps:

  1. Login to host server in Site B with a new AD account. New AD account must have local admin rights on the database host server

  2. Ensure availability of correct service accounts to run the SQL services in Site B

  3. In SSCM, start the SQL Server service in single user mode.

  4. Start SSMS as an administrator and login with Windows authentication (new AD account with local admin rights). This should work but if not, use 'sa' account with the password set up above, login in with SQL Server authentication.

  5. Run the edited create login script that was prepared in pre migration steps. It may be necessary to delete the old logins before running the edited script, to avoid it complaining about the SID already existing. Once the new logins have been tested successfully, log out of Management Studio.

  6. In SSCM, stop services, change them to their original start settings i.e. Automatic etc and start them in muti-user mode

  7. Once everything is running properly, disable 'sa' account and return authentication mode back to what it was in Site A.

Would you agree with these steps and are there any other steps I should be thinking of?

Thank you.

Best Answer

There are many ways to skin a cat.

Your checklist looks fairly good but as you can imagine it implicates stress and an enormous amount of job.

It also requires a huge downtime during the migration.

Have you considered using Always On Distributed Availability Groups:

enter image description here

If you go through this pattern your checklist might be reduced to:

  1. Pre-migration: Setup the Always On Distributed Availability Groups from site A to site B
  2. Migration: Failover from site A to site B
  3. Post-migration: Done, check if everything works fine

Eventually, once you have migrated implement a new Always On Distributed Availability Groups for your disaster recovery.

Related Question