0

One of our two datacenter's is undergoing maintenance for a minimum of 72 hours, but it could be up to 5 days, and I have a very old legacy SQL Server running a production service as a mirror across both data centers.

I'm thinking that for the duration of the outage, I can pause the mirror, but I'm wondering what the effect will be on the transaction logs of the principal? We have scheduled t-log backups running hourly, so this should keep them from growing too large right?

How then does that affect replication when restarting the mirror? Would it be easier to break the mirror completely, put the database into simple recovery mode, and then rebuild the mirror afterwards?

1
  • So you have sync mirroring configured I assume ? How big is the database which is mirrored ?
    – Shanky
    Oct 3 '19 at 11:01
3

I would recommend that you strongly consider breaking the mirror and rebuilding it once the maintenance is complete.

When you pause the mirror, the transactions that need to be sent over will build up in your transaction log. Taking a transaction log backup will not free this space for reuse, although the log backups will be consistent and usable for their intended purpose (point in time recovery).

The same type of consideration needs to be made for when using Availability Groups. In an outage (planned or otherwise), how many (minutes/hours/days) of transactions can my log file hold? How long will it take that information to be sent over? How long will it take to rebuild the mirror/availability group?

I usually make sure that there is enough free space for the time for (error to happen, notifications to bubble up (phone calls, text, etc.) then allow an hour or two for investigation/corrective action. I fight for at least that much disk space.

You can look at the transaction log backup size (uncompressed) for an idea of how much data you are talking about. Then plug in guesses for the rest. You can buy yourself extra time by turning off any maintenance (index defragmentation, for example) while the mirror is paused.

But since you are going to measure this outage in days, I would break the mirror and rebuild it once it's back online.

1

I wouldn't put it into Simple Recovery Mode. I would keep the transaction log backups as-is. I would recommend considering decreasing the frequency of the backups unless you're okay with losing an hour's worth of data if stuff hits the fan.

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.