SQL Server – How to Restart Database Mirroring After Intentionally Breaking It

mirroringsql server

I have setup database mirroring using SQL server on two servers. I have Enterprise Edition on the primary server and Standard Edition on the secondary server.

In order to verify the data on the secondary server I manually broke the mirroring, so I could inspect data on the mirror. Now after the verification process, I want to enable mirroring again.

The database is larger than 200 GB so it is not feasible to take a backup of the primary, send it over the network, and restore it on the secondary server because of the long distance between the two nodes, and the network configuration.

What options are available so I can restart mirroring?

Best Answer

Once you have broken the database mirror, you have no other option than to re-initialize it using a full backup and transaction log backup of the principal.

The steps you must take are:

  1. Restore a recent full database backup of the principal database, after making sure that the principal database was already using the full recovery model when the backup was taken. The mirror database must have the same name as the principal database.

  2. If you have taken any differential backups of the database since the restored full backup, restore your most recent differential backup.

  3. Restore all the log backups done since the full or differential database backup.

  4. Complete the remaining setup steps as soon as you can after taking the backup of the principal database. Before you can start mirroring on the partners, you should create a current log backup on the original database and restore it to the future mirror database.

  5. Establish the mirroring session using ALTER DATABASE <database_name> SET PARTNER =<server_network_address> on the secondary server first, then on the principal server.

I would advise you use the native backup compression feature in SQL Server to compress the database and log backups (and perhaps subsequently compress them with 7-Zip); then ship the files using the fastest overnight shipping method you can find. Have someone at the secondary location put those files onto the secondary server in a mutually agreeable location where you can subsequently restore them using WITH NORECOVERY.

Probably the most important piece of advice is to understand there is no need to validate the data at the secondary side since SQL Server commits the data at the secondary the same way it commits data at the primary; that is it uses ACID-compliant methodologies. As such you can be assured the data at the secondary is an exact copy of the data at the primary. The best way to confirm the secondary is a functional copy is to fail-over the mirroring session so the secondary becomes the primary. You can do this during a maintenance window, with testers on-hand ready to validate the system works as expected. Clearly, you'd take a complete set of backups prior to the first failover, just in case things don't work as expected.

Related Question