Sql-server – Final steps of data center migration

migrationsql server

Just to preface this, I am just a developer with some DBA experience but not an expert.

We are migrating from one data-center to another. Basically we are doing this:

  1. Create a full copy of existing database
  2. Use the copy to get the target database up to date of the backup.
  3. Turn on Mirroring from the existing database to the new one.
  4. The web app will continue to use the existing database ( they are being migrated as well. ) So the old web app server and the new one will both point to the old data center.

ok, after step 4 , we are ready to switch the connection string for the database over to the new one. My concern is… if mirroring is still on and we start to have all read/write operations happen on the target of the mirror… is that going to cause any problems??

ex. say the target of the mirror starts adding rows, will the mirroring catch that the two dbs are not in sync and start removing changes? or… say a row gets deleted in the target db, is the mirror going to catch that it is not in sync and add that row back?

Basically my question is this: If database mirroring is on can we start to read/write to the target of the mirror in preparation for turning the target into the primary database.

Best Answer

Below steps will help you :

  1. Create full backup with compression
  2. Ship the compressed backup to the new data center
  3. Configure database mirroring in async mode
  4. When you are ready for failover, change the mirroring mode to sync mode.
  5. Initiate failover to secondary (new data center) alter database db_name set partner failover
  6. change the web.configs for your application to the new server.
  7. Follow the steps from my answer - it covers migrating your logins/users/jobs/ssis packages and other post restore steps.

If database mirroring is on can we start to read/write to the target of the mirror in preparation for turning the target into the primary database.

When you initiate a failover, this is where your downtime is - your application end users will be getting an error and they will be able to reconnect to the new data center database server (provided you have changed the conf setting).

I am assuming that you are using enterprise addition since standard edition of sql server has limitations on database mirroring.

Also, database mirroring is deprecated and you should look into moving to AlwaysON (depending on edition and version of sql server you are using).