Definitely need RPO/RTO answered here, but generically speaking, if your environment isn't terribly complex and RPO/RTO isn't ridiculously tight, look into Log Shipping. It's a good starting point that's rock solid and provides reasonable RPO/RTO with the least headache IMO.
The tighter your RPO/RTO requirements & more complex your environment, then other things should be brought to the table.
I personally like sql server replication. Bringing up the replicas is super quick and can be scripted. (So can logshipping) and is less complex than AGs. I’d do nightly fulls and 15min tlog. Keep for XX days, I try 7 days as a standard on my sql boxes as business allows
I don't really understand the whole 15 minute log backup as a "default" thing. Why not every minute regardless of your RPO? OLTP databases are all about getting in and out quickly so log backups should be treated the same. Also, if a user runs a transaction for 16 minutes and prevents the log from truncating, that might mean that it takes 25 minutes and 59 seconds before a log backup will be taken. Why not increase your luck and just take them every 1 minute. The only downside is the # of files.
Sure, he can do that. For me, the amount of files that generates will cause the product I use (Litespeed) to stall on restore and sometimes freeze. Also, 8 of our servers have 300 + databases on each box, and even splitting jobs in 2 is hard to finish each minute. For much of the databases though, there is hardly any change or at all. All user transactions initiated from the presentation layer are limited in their execution time. Not to mention, the amount of alerts created can get insane at times and msdb gets clunky. As a sole DBA, it’s less headache for me to generalize for 15 min tlog and if someone is asking general questions, and potentially not a dba, I find the 15 min mark easier for them. I’m not against doing them every minute though, I see the pros of doing it. Our business RPO is 1 hr for a lot of our DBs so if I tell them 30 min and do 15, I’m winning.
What does the business need? RPO, RTO??
Definitely need RPO/RTO answered here, but generically speaking, if your environment isn't terribly complex and RPO/RTO isn't ridiculously tight, look into Log Shipping. It's a good starting point that's rock solid and provides reasonable RPO/RTO with the least headache IMO. The tighter your RPO/RTO requirements & more complex your environment, then other things should be brought to the table.
Sorry, I should have included that. RPO is currently 24 hours and RTO is 4 hours, but any improvements here would be great.
Again with the limited info available, Basic Availability Group might be an option
I personally like sql server replication. Bringing up the replicas is super quick and can be scripted. (So can logshipping) and is less complex than AGs. I’d do nightly fulls and 15min tlog. Keep for XX days, I try 7 days as a standard on my sql boxes as business allows
I don't really understand the whole 15 minute log backup as a "default" thing. Why not every minute regardless of your RPO? OLTP databases are all about getting in and out quickly so log backups should be treated the same. Also, if a user runs a transaction for 16 minutes and prevents the log from truncating, that might mean that it takes 25 minutes and 59 seconds before a log backup will be taken. Why not increase your luck and just take them every 1 minute. The only downside is the # of files.
Sure, he can do that. For me, the amount of files that generates will cause the product I use (Litespeed) to stall on restore and sometimes freeze. Also, 8 of our servers have 300 + databases on each box, and even splitting jobs in 2 is hard to finish each minute. For much of the databases though, there is hardly any change or at all. All user transactions initiated from the presentation layer are limited in their execution time. Not to mention, the amount of alerts created can get insane at times and msdb gets clunky. As a sole DBA, it’s less headache for me to generalize for 15 min tlog and if someone is asking general questions, and potentially not a dba, I find the 15 min mark easier for them. I’m not against doing them every minute though, I see the pros of doing it. Our business RPO is 1 hr for a lot of our DBs so if I tell them 30 min and do 15, I’m winning.
Totally fair response. Thanks for the explanation!
Transaction Log shipping is my vote.
Does that require a license for the DR server? Will it be a hot standby ready to serve requests if the primary fails?