Transaction Log backup in MSSQL's HADR

All we need is an easy explanation of the problem, so here it is.

I’m a resident developer who is voluntold to be a database admin of MSSQL 2016. Please bear with me as I’m still trying to self taught the art of DB admin.

I recently setup a DR solution using the architecture (Always On High Availability Group) below and things are working well for me. All databases are synchronizing.

Transaction Log backup in MSSQL's HADR

In my primary data center, I have two nodes (active & passive) that share the same disk (Log disk, data, ect..). Currently I see the following on my active node, they’re were setup there by my predecessors.

  1. All databases are all in full recovery mode.
  2. It has a Full Backup maintenance plan where a full DB backup is done at 12am daily.
  3. It has a Log Backup maintenance plan where transaction logs are backed up every 5 minutes.

I understand that Always On High Availability Group use transaction log to sync data between the Primary Data node and the Disaster Recovery Node. Can I disable the transaction log backup in #3?

Do you see any issue if I do not backup my transaction log at a 5 minutes interval? Can I use my DR node as a "backup" in case something went wrong instead of restoring my database using our last daily backup and then procedurally restore the entire transaction log backup from that day?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Can I use my DR node as a "backup" in case something went wrong instead of restoring my database using our last daily backup and then procedurally restore the entire transaction log backup from that day?

No. All changes, including ones you might like to undo, are automatically written to your AG secondaries. You should take log backups on exactly one replica and all the backups should go to a single location. You can configure a Backup Preference to help make sure that 1) backups are being taken, and 2) they are taken on your preferred node if it is available.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply