Backing up SQL AG clusters

[jcofin13]

I am having a hard time understanding this.

We are debating moving from stand alone SQL server instances to SQL Always on Availability Groups for the benefits they provide. Mainly we can do maintenance on vms without taking the databases offline.

That said, i am confused about how to setup veeam for this and if it is worth the time and trouble.

Clearly we would be going from 1 db server which gets backed up nightly to 2 new db servers that would get backed up nightly.

I assume that means our data size will double as each server has a copy of the databases or does veeam realize its a cluster and only back up 1 instance of each db?

As far as setup goes for backing up, the documentation is very vague. It says you need to create a projection group with the type of active directory objects. Do you then add both servers to that protection group or only the cluster service account from the active directory? Also.....it seems you need to load the backup agent to backup the SQL servers themselves. Is there a guide to setting this up properly? We have quite a few databases (hundreds) across many database servers. I just want to make sure im clear on how all this works and what our options are. WE have customers starting to inquire about this as well.

[david.domask]

Hi jcofin13,

Our User Guide has details on doing this as an image level (read: snapshot based) backup -- main idea is be sure to include all the nodes in the job for proper quiescing, and note the limitations on the page as well.

Your idea sounds like you've found instructions for doing this with Veeam Agent for Windows, and there is a similar process in the respective user guide.

Alternatively, please consider our Plugin for Microsoft SQL backup which protects the SQL databases directly for restore to the same or new SQL server.

Multiple options depending on how you want to protect it and your intended recovery narrative. The plugin is the "lightest", but won't include the OS data for recovery, though this can be handled by restoring the data to a newly deployed node or existing nodes.

[jcofin13]

Ah yes..i didnt realize the one that i was reviewing was for the Agent / Physical server. Thank you for that clarification. I had missed the "agent" portion in the title.

So per the link you posted it does appear that we can backup clusters and veeam is aware of the cluster but it cant backup "Always On Availability Groups based on SQL Server Failover Cluster Instances" because they are not supported (per the article).

I will need to get more clarification on what we have or are wanting to setup for clustering. It does sound like we should as long as we are not using "Always On Availability Groups based on SQL Server Failover Cluster Instances"

[david.domask]

Glad I could help clear it up a bit. Indeed, previously you were focusing on agents, but Veeam has many ways of protecting the SQL data, it's just a matter of what works best for you and your DBA team

I would check out the Microsoft SQL plugin as well with your DBA team, see if it better matches their interests for protecting the SQL data directly, and the supported configurations will likely cover your needs while also allowing local control of the backups/restores for the relevant DBAs.

[PetrM]

Hello,

A brief addition:
The deployment of "Always On Availability Group based on SQL Server Failover Cluster Instance" is described on this page. The main idea is that, in this case, an FCI is a single instance of SQL Server installed across Windows Server cluster nodes. Regarding the supported Always On configurations, you may refer to the diagram on this page, which shows a SQL instance on each WSFC node.

Thanks!

[Rumple]

We are debating moving from stand alone SQL server instances to SQL Always on Availability Groups for the benefits they provide. Mainly we can do maintenance on vms without taking the databases offline.

off topic from veeam, but FYI,

I moved a ton of databases to SQL AG groups and there are a few things you are going to run into that are not going to work as you expect out of the box, primarily the fact that users are not replicated between the Nodes of the cluster which means when you failover, nothing is actually going to work. Sure the databases are online, but none of the applications/users can't access them.
Creating Containerized SQL users seems like the obvious solution, but most applications don't know how to use containerized accounts and still interact with the global server security users section of the server itself. Hard to explain but you will figure it out when you try and connect an app to a database where the user is containerized.
Windows based login's also do not replicate so you are going to need to run a script on a regular basis that copies the users/passwords between the nodes. (https://www.sqlshack.com/synchronize-lo ... ity-group/)

Another thing that doesn't work is automatic seeding...never worked in 2017-2022 versions of SQL...We've always had to do the backup and restore to get them to seed....for empty databases, that's fine, for large databases, it really sucks.

Busy databases take FOREVER to get back in sync before you can fail back...we have a Solarwinds Orion database that literally takes an hour to get back in sync so we can failback after a failover. High transactional databases really are not suited to AG clusters it seem even with redundant 10G networks between nodes all running on Flash.

Full backup mode...if you have any databases in basic mode (like a data warehouse where large transactional actions happen generating massive TRN files so were left in basic mode, those cannot be part of the failover cluster....we had to move those to standalone database servers to make management simpler and avoid alerts of databases not sync'd (since they were not part of cluster)

No real native failover notification...we've had AG's just failover magically for no apparent reason and after 30-40 hours with Microsoft they didn't have answer either., except blame environment...there is not native alerting to let you know, it's usually scream test Especially if someone has configured their app to talk to the SQL server name instead of the Cluster name...

Performance...one thing that might suprise you, if you have a DR environment with a node and it suffers performance problems (say the disk is overloaded), this WILL slow down your production system if running in synchronous mode (which you most likely will want to do) as the transactions are not committed on prod until they are committed on the DR side...

Those are the highlights of things I can think of right off the top of my head...

[PetrM]

Hello Mark,

I really appreciate your willingness to share this information with us. This post will be useful for me as well, I may need to convert a couple of standalone instances to Always On in my lab in the future. Thank you again, and congratulations on the upcoming New Year!

Thanks!

[mortenr]

Another thing that doesn't work is automatic seeding...never worked in 2017-2022 versions of SQL...We've always had to do the backup and restore to get them to seed....for empty databases, that's fine, for large databases, it really sucks.

Funny, automatic seeding works for me, even on 2TB+ databases. I remember having issues in the past, but currently on mostly SQL2019 it works just fine. Not sure if it has been fixed or it may be environment dependent.

Busy databases take FOREVER to get back in sync before you can fail back...we have a Solarwinds Orion database that literally takes an hour to get back in sync so we can failback after a failover. High transactional databases really are not suited to AG clusters it seem even with redundant 10G networks between nodes all running on Flash.

Could this be an issue in your environment? Slow storage or network configuration?
For me, failover normally completes and is fully synced within a minute on semi-busy systems like BizTalk.

[Rumple]

Maybe it does work for seeding now but I've never had much luck to be honest...but I have tried 2022 yet. Microsoft consulting themselves setup the 2019 environment (i did original 2017) and I could tell it to seed but it just always seemed to be stalled out.backup and restore worked flawlessly everytime...

Nothing in the environment is slow, but it's not nvme fast either...its all flash netapps with 10 and 25gb networking throughout....most database are pretty good but solarwinds Orion (for literally logging performance stats of 400+ vms generates so many transaction logs it has always had trouble catching up after a failover. Once it's caught up,,not a problem but the catchup after patching a node always took it an hour....