Backing up Always On MSSQL with Veeam

[stewsie]

Should anyone be interested here are some results following the backup and restore testing of SQL Availability Groups

1. Following a restore of a database then obviously an image level backup needs to be taken for the transaction log backup to work
2. Restoring a full VM has worked with no issues apart from the restored VM not being available in Enterprise Manager for around 24 hours. I have a support job open for this. If I rebuild the roles the job fails. The first time I restored a VM (16th) the issue resolved itself after around 24 hours so maybe there is some sort of job that updated this
3. Following the restore the VM needs to be removed from the backup job and added again as it is essentially a new object
4. Run the image level backup job again to add the restored node back to the transaction log backup.

I appreciate this will be teaching most of you to suck eggs but I found it useful hence sharing it.

I still haven't tried the registry key mentioned above as we are still discussing this internally. If we disable the copy only backup on the second node and then this becomes the primary we could end up in a situation where we do not have a backup of the SQL database.

[foggy]

This is a fair reason for adding both nodes to the job.

[tom11011]

Currently have a single sql enterprise server that we backup just fine with veeam. But veeam is starting to put a load on this sql server, especially during the recombining of the snapshot process. Not that veeam is responsible, but as the server continues to get more busy, the veeam backup process can be felt.

My thought was to enable SQL Always On. The plan would be to have a 2 node cluster utilizing a-synchronous replication. We would like to have the primary node serve up sql like it normally does, but then to backup the secondary replicated node with veeam to keep the load of the primary server. Does this work? How would logs be truncated etc.. on the primary node? The reason for the a-sync operation would be prevent the double write-commit on both sql servers as that would be self defeating I would think given the goal.

Does anyone use this kind of strategy or recommend a different strategy?

[its-user01]

I need a guide in setting up Veeam backup for SQL Always On Availability Groups.
Can someone guide me to a steb by step guide?

[wishr]

Hi Brian,

I'm merging your post into an existing thread - should give a few tips.

Thanks

[albertwt]

Hi People,

I'm running SQL Server 2017 Enterprise Edition Always On
2x Microsoft Windows Server (MSCS) Cluster VMs.

The Storage Array I am using is HPE 3PAR with the capability of Storage Array Snapshot. [https://helpcenter.veeam.com/docs/backu ... ml?ver=100]

May I know how can I perform the SQL backup without causing the Cluster node failover?

Because at the moment I have to install Veeam Endpoint Backup Agent and treat it as 2x Physical server.

So I wonder what's the best steps and procedure to utilize the latest Veeam Backup v10a to avoid the database failover during the Veeam backup?

Thanks,

Case #04536738

[albertwt]

Hi All,

I wonder if there is an updated Best Practice or methods to successfully backup 2x SQL Server CLuster (MSCS) with Veeam Backup v10, using Storage Array Snapshot?
Because at the moment I had to resort into Veeam Agent backup to avoid the cluster failover during the backup snapshot consolidation process.

[HannesK]

Hello,
the best practice since "always" is to put both VMs in the VMware backup job to ensure that log shipping works fine.

https://helpcenter.veeam.com/docs/backu ... ml?ver=100

If the backup job does not include all VMs from the cluster, an information message will be issued.

Agents are not needed. Agents are only relevant with RDMs / iSCSI LUNs or similar. And even then, they would be configured to back up the cluster: https://www.veeam.com/kb2463

Best regards,
Hannes

[Vitaliy S.]

Hi Albert, I could find any updated BPs, so I guess your current approach should be a way to go given that you don't have issues what that.

[albertwt]

Hi Vitaliy,

I need to confirm the following:

A: In Veeam 10, if one of our nodes in an FCI fails over, the transaction log backup will not stall and pick up where it left off

B: FCI nodes do not need a reboot if the agent requires an upgrade

C: That databases can be hot mounted to an Azure instance for read/write level access with commit to the original on-premises server once it has been restored

We are currently trialling SQL backup alternatives and would like to know how Veeam can handle databases which are High Available and need to have 24X7X365 uptime in the event of a failover scenario.

Case #04537356

[albertwt]

Hi Hannes,

Thank you for the update.

Upon further searching, I've found: https://bp.veeam.com/vbr/VBP/4_Operatio ... erver.html
which means the SQL agent is still needed to prevent the Failover Cluster Instance during the snapshot backup?

[HannesK]

which means the SQL agent is still needed to prevent the Failover Cluster Instance during the snapshot backup?

no. the agent is only needed if you have a shared disk. and shared disks are on RDMs or iSCSI LUNs which you did not mention. A "normal" SQL Always on is "shared nothing" architecture. That means "real" VMs that allow storage VMotion.

B: VM based backup never requires reboot as there is no persistent agent.

C: Do you maybe have a link / description to what you mean? "Hot mount" sounds like instant SQL recovery we will be available in V11. But I'm not sure how Azure comes into play here. I don't understand how you want to read / write in an Azure VM and then commit data to a different server on-prem.

Best regards,
Hannes

[HannesK]

A: log backup continues on another node, yes.

[albertwt]

Does the new Veeam v11 CDP feature https://helpcenter.veeam.com/docs/backu ... ml?ver=110 can now support Microsoft SQL Server Always On backup without causing the node to failover?

[HannesK]

@albertwt: that question looks unrelated. If SQL clusters fail over during backup, then please contact support. That's unexpected behavior.

CDP is asynchronous storage replication based on VM level. From my point of view, that makes little sense for a cluster (no matter whether it is SQL or any other clustered software)

[albertwt]

Hi @HannesK,

Thank you for the clarification, I have already created Case # 04536738.
However, the Tech Support team cannot resolve the case, hence it is already closed.

I'm now seriously looking for the SQL server cluster backup solution that does not cause the node failover during the backup.

[HannesK]

CDP is for replication. Not for backup.

Case 04536738 has an internal comment "customer confirmed that the issue has been resolved". If it is not resolved, I recommend to open a new case and make sure the problem gets resolved.

Node failover is NOT normal. Not with VM based backup and not with agent based backup.

[JeroenL]

I have the same question.
A rather tricky application based on SQL with an SQL AllwaysOn two node cluster.
At the moment we user B&R 9.5U4b with only the passive node added to a backup job that also has transaction log shipping enabled.
I am told, then when both nodes are added to the job, the VSS integration will cause a minor disruption in availibility with clients dropping their connections.
So now only the passive node is configured with log shipping enabled.

We are configuring a new backup solution using Veeam B&R v11 and we are
We want to use transaction log shipping, but also want both nodes added to the job.

Is V11 any different from V9.5U4b in the way it handles SQL AllwaysOn clusters?

[HannesK]

We did some bugfixes in SQL processing since U4, but in general it works the same.

I always recommend starting with the default settings and if it causes issues, then tuning of timeouts is possible: https://www.veeam.com/kb1744 (cluster timeout setting are the same for SQL)