通过

INF: How to re-setup Database Mirroring In SQL Server

  • 项目

One of my friends was asking this to me offline the other day:

"I have Database in SQL Server, which is both Mirrored as well as Log Shipped. Log backups happen every 15 minutes; Mirroring happens in Synchronous mode and does not involve a witness. I want to break Mirroring (Remove Mirroring) and then want to re-setup the Mirroring configuration after about 30 minutes; what is the easiest way to achieve this?"

I constructed a small diagram for better understanding:

Blog I said, "What is the size of the database?" The answer that came was, "More than 1 TB. Don't tell me you want me to start with a Full Backup; it's not feasible. It takes more than 8 hours to backup the database; and more than 6 hours to restore it, even if we use Instant File Initialization."

"Well", I said, "try restoring the first Transaction Log Backup (with NoRecovery) that was taken after the Mirroring was removed. If the Principal and Mirror were completely synchronized when Mirroring was removed (i.e., Unsent Log = 0 KB), it will get restored successfully. If they were not synchronized (Unsent Log > 0 KB), you will be prompted to restore an earlier backup. Once you get the starting point, restore all Transaction Log Backups taken in sequence (all with NoRecovery). Now reconfigure Database Mirroring again."

My friend did not believe this can be done.

Question to my readers: Do you believe this is possible? Well, give it a try…

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Comments

  • Anonymous
    August 25, 2010
    Good insight. Especially helpful for DBA managing SQL Mirroring for large database. Also, please share details on verifying 'unsent logs' status. Great post overall! Varund

  • Anonymous
    June 24, 2012
    Hey Suhas, How R U. I tried reaching you over the phone on GTSC phone.but no luck.

  • Jignesh Kyada

  • Anonymous
    October 26, 2012
    Not possible if you break mirror completely and bring mirrored DB online and then want to recreate mirror again.

  • Anonymous
    February 16, 2013
    Thanks for writing this post