We are using SQL Server 2017 Standard Edition. Currently, we use tlog shipping to a standby server as our primary DRP. I also take full, differential, and tlog backups as a secondary precaution. The problem is that when the full and differential backups run over night they put massive strain on the disks causing large latency issues and, though traffic is lower at night, we are a 24/7 operation. Is the tlog shipping sufficient for DRP? I could do tlog shipping to a second standby server as an alternative secondary DRP.
Thoughts?
Thanks.
SQL Server 2017 Standard Edition Supports Basic Availability Groups. That's what I would use instead of log shipping. What does your full and differential backup schedule look like?
Are you using compressed backups?
Are you staggering full and diff backups throughout the week by db?
Do you stagger the diff backups throughout the night?
Have you tried writing multiple files for a single backup, esp. the large ones? I've gotten some big-time speed gains that way at times.
Very interesting. We have about 50 databases and our plan is to spread them across about 10 servers. You can only have one database per AG with Basic Availability Groups but can you have multiple AGs per server? So it would look like this...
10 primary servers housing 50 databases. Each server would average 5 databases. There would be 5 AGs per server. All AGs would point to 2 secondary servers.
Does that architecture work?
Hi Scott. We do all of those things except compressed backups. I presume that would cost more in CPU but would save IO. Worth a try!
Absolutely! You should get a huge I/O savings from compressed backups (assuming the dbs are not TDE).
With only 5 dbs per server, log shipping is still viable. Yes, it's older and not as robust as AG, but it's way simpler to implement and maintain. It's a good idea overall to switch to AGs, but be sure to allow yourself some time to read up and "tech up" on them before using them in a live prod system. Unlike log shipping, AG can cause prod downtime if things go really badly.
Yes. We recently migrated to Enterprise and implemented AGs in production. We had 50 databases running through one AG (the documented max recommended limit) and found that performance was significantly degraded (lots of HADR waits, etc.). We didn't have the technical expertise to do that and should not have done it. So we are currently sitting on two massively expensive Enterprise boxes on AWS and are using old school tlog shipping. Thus the discussion happening in this thread...
Not sure what your DR licensing postion is but have you thought of log shipping and bringing the copy database online, you could then run your full and diff backups on your secondary server thus eliminating the I/O hit on the primary server?