Search code examples
sql-serverdevopsdatabase-administration

MSSQL backup strategy for Disaster Recovery

Our database is MSSQL and we are currently using High Availability Group with fail over in multiple node cluster so the idea of redundancy and backup is already there.

All of our current servers are in the same location; imagine a scenario where an earthquake takes out the entire hosting facility then we are sitting duck.

I'm exploring a disaster recover (DR) strategy to have another DR backup at a different location so when this happens I can bring back the entire database using the DR's backup set with a minimum down time and the data needs to be guarantee to be up to the minutes if possible.

I've read around the Microsoft doc but I don't really see one that talks about this in details.

I need a true backup that is up to the minutes, do I need to do this full backup (once every day) along with transaction log backup (one every minute) and then save it to the other different geological location? Can you point me to a guide or best practice documentation on how to achieve this?

Solution

  • I'm exploring a disaster recover (DR) strategy to have another DR backup at a different location so when this happens I can bring back the entire database using the DR's backup set with a minimum down time and the data needs to be guarantee to be up to the minutes if possible

    Following are your options, since you already have Availability Groups in-place.

    1. Multi sub-net WSFC You need to add additional node into WSFC, that work as DR Replica in availability group, consider it's another copy of secondary that you already have in same location but this copy laying different geographical location, since it's multi sub-net WSFC required attention for care-full Quorum configuration.
    2. Log-Shipping it's simple solution compare to Multi sub-net WSFC and easy to administrate. It basically takes log backups on scheduled basis from Primary replica in you current Availability Group, and restores into secondary replica. You can have multiple secondary replicas and each of them different geographical locations depending on network bandwidth.

    I need a true backup that is up to the minutes, do I need to do this full backup (once every day) along with transaction log backup (one every minute) and then save it to the other different geological location? Can you point me to a guide or best practice documentation on how to achieve this?

    This post at DBA.SE would help you..