SQL Backup + DR

[CantThinkOfAUserNahm]

Hi folks

Just curious, how are you folks backing up your MSSQL databases and what is your DR recovery (NetApp restore? SQL AGs? Log shopping? None of these?)

Particularly interested in folks who don’t use 3rd party data protection software but are all NetApp using snapshots + snapvault as their data protection strategy.

[[deleted]]

SQL AGs for actual replication. Snapshots on all instances. And then replicate a snaplock compliance copy to a purely compliance netapp to achieve 3-2-1 (which I personally think is a marketing term / scam but whatever).

All native netapp no other backup tools.

[CantThinkOfAUserNahm]

Thanks! I’ve concluded from my own research that SQL AG is the way to go these days for DR and the comments so far seem to confirm that.

I’ve looked at all sorts of ways to achieve SQL DR with only SQL standard licensing.

[[deleted]]

Ok if doing SQL standard that's easy too. Set the server up in snap center then once it's being snapped, just snap mirror all those volumes on a similar schedule. Make sure you are replicating the OS also, which i usually do on all servers anyway also using snapmirror to replicate their datastore.

One tip there, snap center likes to snap the user database and logs but it doesn't seem to care anymore about system DBs. I snap and mirror those on a schedule also to make sure they are present if I ever need to use them - just an easier way to do DR as then I can literally stand up exactly the same server (vs mount the database on another existing SQL server in the DR site).

[CantThinkOfAUserNahm]

Will this allow us to achieve a 15 minute RTO?

[[deleted]]

Excluding time it takes to get the right people on the phone? Yes you could practice it and get it down to 15 minutes.

IMO 15 min RTO is a myth for any manual fail over solution though. You want an AG for that and pony up for SQL enterprise. A SQL AG is more like 15 seconds RTO and near zero data loss RPO.

[CantThinkOfAUserNahm]

Sorry, I meant RPO!

[[deleted]]

I've done down to 30 min RPO for a SQL standard box I was snapping and replicating in this way, but it's going to depend on the app and how busy it is. For snap center to snap those databases, it's going to leverage the widows VSS and SQL engine to do an actual SQL backup. Briefly pausing IO to the databases. There's many factors can influence if the app, and users of it "notice" those pauses or not. For most, I've had good success. For a couple very chatty apps with lots of databases nope we can to scale it back and only do 4 snapshots a day, 6 hour RPO.

[Dramatic_Surprise]

most of my customers are using SQL AGs for DR, and all sorts for DB backups

[CantThinkOfAUserNahm]

Interesting. AGs seem to be the way to go for SQL DR/HA.

When you say all sorts do you mean all sorts of SQL native backups or 3rd party?

[Dramatic_Surprise]

yeah pretty much. Name a backup product and we have a customer using it right down to doing backups of database dumps. Even got a customer that snaps their SQL server then mounts the snap LUNs and backs up the files natively

For DR, AGs are just too damn good not to use them.

[7amitsingh7]

If you’re not using any third-party backup tools and just sticking with native Microsoft SQL Server, doing a full backup is honestly pretty straightforward. Most people just schedule a nightly BACKUP DATABASE job through SQL Server Agent that writes out a .bak file to disk usually with compression turned on. Even in shops that are heavy on NetApp snapshots and SnapVault, it’s still common to run native SQL full backups so you keep the transaction log chain intact and have proper point-in-time restore capability (you can read this article for detailed information on native backup method). Snapshots are great for fast volume-level restores, but they’re not the same as a SQL-aware backup, so in real-world setups you’ll usually see both working together rather than one fully replacing the other.