0

We have multiple multi-site always on failover cluster instances in our env. We have an enforced internet outage being imposed on us and at the time the link between the data centers will be down for up to 6 hours...

Our plan is to remove the Databases from the AGs on the primary instances, but leaving the listener in place so the applications should still connect to the DBs via the VIP/DNS.

We have tested this and did not encounter any issues.

However we are struggling to find any information to say that this approach is best practice. We cannot suspend movement as the outage could be long and MS recommend movement is suspended for a short period only.

Anyone else had to deal with this situation? If yes - is our approach feasible?

Once the outage is complete we will disable log backups on the Primary nodes, delete the non-synched DBs on the secondary nodes and then add them all back in on the primary.

Any thoughts or input would be greatly appreciated.

Improve this question

2 Answers 2

Reset to default
0

Our plan is to remove the Databases from the AGs on the primary instances, but leaving the listener in place so the applications should still connect to the DBs via the VIP/DNS.

We cannot suspend movement as the outage could be long and MS recommend movement is suspended for a short period only.

I would not remove the databases on the primary node from the availability group.

I would, however, remove the affected secondary replicas from the availability group. Removing them would accomplish two things:

  1. Since these are secondary replicas the databases will return to a restoring state. This will be helpful in the future.
  2. Allows the primary and any unaffected secondary replicas to stay in the availability group and continue with log backups and re-use.

During the time the secondary replicas are removed from the AG, continue to take log backups as normal. This will facilitate the reuse of the log so that it doesn't grow out of control. Keep these log backups handy and ready for action.

Once the affected secondary replicas are no longer affected, copy all of the log backups taken while the secondary replicas were out of the AG and apply the log backups to those databases. When applying the log backups make sure to keep the databases in a restoring state by choosing WITH NORECOVERY on each log restore.

Finally, suspend the log backups and restore any final ones that were taken while restoring the older ones. This will bring the databases on the previously removed secondary replicas to the same time frame as the primary and any other secondary replicas.

Once the final log backup has been applied and the databases still left in a restoring state, add the replicas back into the AG. When this happens, since the databases are still in a restoring state and have been restored to the last log backup the AG will be able to join the replicas and databases without issue. There will be a short period of time where the replicas will need to catch up.

Once the secondary replicas and databases are rejoined, resume log backups as normal.

This would be the ideal process as it keeps your AG intact (for any unaffected secondary replicas), continues to leverage the listener for your applications, can still provide HA and to some extent DR depending upon the replicas available, continues to allow for log backups and re-use, stays transparent to the end user.

Improve this answer
0
0

How large are your transaction log files? I'd be prepared to try going with leaving them disconnected, provided no reindexing, bulk ops etc go on during the outage, and provided you can be 100% confident it will only be 6 hours.

In my experience OLTP transaction log growth is normally modest, and provided your t-logs are a reasonable size, you should be ok.

Difficult to test though as normal OLTP transactional throughput is tricky to simulate, although profiling for replay could be a way of measuring it.

Improve this answer
2
  • Thanks Thomas! They range from tiny to max of about 35 GB. We cannot stop any overnight jobs and some them do bulk ops.
    – jrd1975
    Commented Jul 12, 2016 at 13:02
  • Yeah I thought that might be the case.It still might be worth testing how much t-log is generated overnight for interest, it may still be less that you anticipate. You could do this by measuring the used size of your log regularly and/or examining the size of your log backups (although if you're using compression on them it's not easy to correlate). Full db backup/copy/restore is the pain you can't avoid though for re-adding them to the AG.
    – Thomas Pullen
    Commented Jul 12, 2016 at 13:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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