1

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?

  • 20GB is not that big, especially if u use compression. What version of SQL server are u using - 2008R2, 2012? – Kin Shah Sep 24 '14 at 10:38
  • 1
    Adding few more points to max vernon answer,you can use snapshot to verify data on mirror considering below caveats instead of breaking the mirror msdn.microsoft.com/en-IN/library/ms175511.aspx – TheGameiswar Jun 2 '15 at 14:46
2

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.

1

As far as I'm aware, a mirrored database needs to have a full backup and then a transaction log backup restored (with norecovery) in order for mirroring to initialise.

I would strip the full backup of the database into multiple files and then transport over the network. If you are using 2008 and upwards, use compression to get the backup files as small as possible. If you are using 2005, there are plenty of third party tools that will stripe and compress a backup. Have a look online.

A final resort would be to backup to an external drive and then physically take that drive to the other data centre and plug into the secondary server (but this may not be possible for you).

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.