1

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.

2

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).

  • sorry if this is a stupid question , but what exactly is step 4 referring to ? I'm not 100% clear on what the term failover is – Scott Selby Oct 30 '15 at 5:54
  • failover means switching from your old data center to new data center. The Sync mode ensures that both the old and new databases are same exact copy and when you failover there wont be any data loss. – Kin Shah Oct 30 '15 at 5:55
  • got it , so run command to set failover , then change web.config in app , and to answer my original question - I'm assuming - yes there would be problems with what I originally wrote by keeping mirroring on and just starting to use the new database? – Scott Selby Oct 30 '15 at 5:57
  • 1
    You wont be able to write to target unless you failover. Suggest you to read up on database mirroring - so that you understand what you are doing and the implications of it. – Kin Shah Oct 30 '15 at 6:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.