Need to discuss SQL HA/DR strategy upgrade

[tom11011]

Hi Group, need to discuss some strategy with those who are familiar.

We have been replicating SQL Server offsite to a second geographically diverse data center for some time. Our agreed upon RPO is 1 hour and our agreed upon RTO is 2 hours. We meet this requirement in DR ok right now with a high speed link between the two datacenters. In addition, we do hourly backups locally with veeam on the same servers. Essentially, backups and replications run continuously on the sql server 24 hours per day.

This has work well for us but now we might be looking at a new requirement whereby we are looking to reduce downtime during maintenance windows to almost nothing. During windows patching every few weeks it seems the sql servers can be down for a good hour as windows 2016 seems to really take a long time to patch as compared to 2012 and earlier.

What I'm thinking might be the answer is mirroring. The idea is to windows patch the mirror server first then switch over to this server afterwards and then patch the other server. My concern is just how much server and san disk space I'm going to need. I figure right off the bat I'm going to need more server to essentially double the hardware capacity needed to have pairs of mirrored servers running.

But what about storage? What's common here? Do you duplicate the storage as well and have the same data live on the same san twice? I guess they wouldn't call it mirroring then if there wasn't 2 sets of data. We don't have the appetite to go back to clustering sql and think that is the wrong path to go down.

The other question is this, what to do about the DR environment? We would still replicate the sql server, but when it powers up, is it expecting to find its mirror partner?

Would really like to hear about how others are doing this.

[HannesK]

Hello,
not really a Veeam topic but I would go for 2x server SQL Always ON Availability synchronous mirror for HA for the primary location and an async copy at the second location.
https://docs.microsoft.com/en-us/sql/da ... erver-2017



SQL Always ON is what I see since many years at customers and some older customers still have failover clustering with shared storage. Both solution support upgrades without downtime.

Database mirroring seems to be removed in future. So I would not go for that

Best regards,
Hannes

[skrause]

+1 for using Always On. We have been using it for years and it is great. It also doesn't "cost" any extra if you are doing asynchronous replication with just a pair of servers as the copy is considered a DR copy.

One thing you want to make sure of is that your asynchronous targets have similar resources (especially disk speed) to your "active" node so the transactions waiting to commit don't pile up.

[nmdange]

I use SQL AlwaysOn with SQL 2016+ Standard Edition. With Standard Edition only Synchronous Commit is supported and only 2 replicas, but it works well for us for DR/HA with 1 server in the primary DC and 1 in the DR DC. We use a file share witness in a 3rd location to allow for automatic failover.

[tom11011]

I'll have to come up to speed on the differences between AlwaysOn and Mirroring. With AlwaysOn, is there a performance penalty if you are waiting for mulitple write commits? Also, with async copy to the remote DR site, we would no longer use Veeam to replicate the SQL servers?

[skrause]

There is potentially a performance penalty with synchronous replication if it is waiting for a write commit on the secondary copie(s). So latency is obviously a concern.

Yes with asynchronous replication you would not use Veeam to replicate your servers any more, you don't need to. A failover is simply changing the primary in Failover Cluster Manager in Windows. Depending upon your setup it should be very quick to flip over. The connection strings of your applications all point to an AD object that is for the cluster listener, not the individual SQL servers.

[tom11011]

Thank you everybody for the helpful information!

[tom11011]

Hi again, I wanted to revisit this for a few more questions. Thanks again in advance.

In a single data center, is it feasible to have 2 nodes in the synchronous configuration for HA, and then have a third node setup in an async configuration to run veeam backups and replications against. The replications would be to a second datacenter.

Essentially, we would still be utilizing veeam and its many management benefits but the with the ability to remove load caused by veeam from the primary database servers.

If this feasible, how do you feel about single HA san in one datacenter and then of course another san in the DR datacenter.

Or, do you feel with veeam and vmware HA, we could simply have 2 nodes instead of 3 connected via async, whereby we use the node2 for our veeam work.

[skrause]

You cannot run any SQL tasks (SQL Backup/log truncation, etc.) on a node that is not the "primary" in an Always-On setup. This is a limitation of SQL itself, not anything related to Veeam.

If you are just concerned with getting an image level backup of your SQL server without the Veeam AAIP SQL processing (by either selecting Copy Only in the SQL processing settings or just disabling AAIP entirely) you can run that against an asynchronous replica in another datacenter just fine.

One thing to keep in mind with the "three-legged" method: if you lose both nodes in the first datacenter, there won't be enough nodes in the second datacenter to have a quorum so you will end up having to do some extra work to fail over to the async location. We learned this the hard way as that was our original setup. We have moved to a "2+2" setup to avoid the quorum issue. Since you get one "DR" instance per SQL licensing, the cost for 4 nodes is the same as 3 when it comes to SQL.

[nmdange]

It is possible to backup a SQL database from a secondary copy if you are running SQL enterprise, but I do not know if Veeam can use this feature
https://docs.microsoft.com/en-us/sql/da ... rver-ver15

[tom11011]

Veeam sent me this today, but I confess I'm not 100% confident in my understanding.

https://helpcenter.veeam.com/docs/backu ... l?ver=95u4

[HannesK]

Hello,
@nmdange : I believe backup from secondary copy is what skrause mentioned. Just add the async VM in "perform copy only" mode

@tom11011: I'm not sure what the questions is... but probably it's also about "perform copy only" backup.

https://helpcenter.veeam.com/docs/backu ... l?ver=95u4

The question is, whether it's really worth to backup old data from the async node.

Best regards,
Hannes

[tom11011]

When you say old data, its old the moment you start the backup anyway whether its the primary or the secondary node?

Does the copy only mode backup the database in a ready to go state as opposed to a crash consistent type of state?

[HannesK]

async & old... well, ok

copy only is in the application aware image processing settings. so it's not crash consistent. but I'm not a SQL expert at that level and do not know, whether any additional operations are required when restoring from an async database.