I have Sql Server Standard running on an AWS VM
For one of my DBs on this server, I perform:
1x daily backup
And ship logs every 15mins to another DB Server VM, dedicated to log shipping for this DB.
Longer term, I had intended the log shipping VM to be a reporting DB and play a part in reducing load on the primary server. But this requirement is now secondary to saving money.
Recovery Criticality (RTO/RPO)
There is no offcial SLA. But the following is the general concensus: Recovering to within minus 1 hr of live data is seen as pretty much acceptable for this system. Although if we had to go to last night’s backup then we would still survive, after taking some abuse. Recovery within 2 hours would be ideal, but up to 12 could be tolerated.
New approach to save money – VM Snapshots
I am considering switching off the log shipping VM and switching to snapshotting the production DB VM every 1hr. (This will save many $1,000s per year.). Note I am referring to full AWS EC2 VM EBS snapshots, not Sql DB Snapshots.
I have been very happy spinning up these VM snapshots in the past for non-critical investigations into old data. But I haven’t relied on them as part of a DR plan.
-Given the DB recovery profile I have alluded to above, would you switch to snapshotting, or do something else?
– Has anybody had issues spinning up old Sql Server VM Snapshots?
(This answer below was kind of helpful, but I would be interested in real-life experiences too.
Are VM snapshots enough to backup SQL?)
The problem with VM snapshots is 1) they aren’t the cheapest option. They generally cost more to store than your database backups. and 2) they don’t protect you from as many things.
To save money, just continue with your Full and Log backups, and keep a cold standby VM that you can start up and restore to in case of a disaster. Just boot it up every couple of weeks to get patches, and test the restore process.
You can probably get this done with minimal changes to your current setup by perhaps modifying the log shipping jobs and shutting down your secondary VM. You can bring it up every week and apply all the logs. Then you’re always just a few restores away from a complete database recovery.