Comprehensive data protection for all workloads
stewsie
Expert
Posts: 247
Liked: 20 times
Joined: May 22, 2015 7:16 am
Full Name: Paul
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by foggy » 1 person likes this post

This is a fair reason for adding both nodes to the job.
tom11011
Expert
Posts: 192
Liked: 9 times
Joined: Dec 01, 2010 8:40 pm
Full Name: Tom
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by 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
Enthusiast
Posts: 50
Liked: 1 time
Joined: Dec 10, 2017 10:00 am
Full Name: Brian Kristensen
Contact:

[MERGED] Veeam agent backup of SQL Always On Availability Groups

Post by 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
Veteran
Posts: 3077
Liked: 453 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: Veeam agent backup of SQL Always On Availability Groups

Post by wishr »

Hi Brian,

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

Thanks
albertwt
Veeam Legend
Posts: 879
Liked: 46 times
Joined: Nov 05, 2009 12:24 pm
Location: Sydney, NSW
Contact:

Backing up SQL Server cluster nodes without failing over issue?

Post by 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
--
/* Veeam software enthusiast user & supporter ! */
albertwt
Veeam Legend
Posts: 879
Liked: 46 times
Joined: Nov 05, 2009 12:24 pm
Location: Sydney, NSW
Contact:

[MERGED] Backing up Always On MSSQL with Veeam

Post by 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.
--
/* Veeam software enthusiast user & supporter ! */
HannesK
Product Manager
Posts: 14287
Liked: 2877 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

Re: Backing up SQL Server cluster nodes without failing over issue?

Post by HannesK » 1 person likes this post

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.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by Vitaliy S. » 1 person likes this post

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
Veeam Legend
Posts: 879
Liked: 46 times
Joined: Nov 05, 2009 12:24 pm
Location: Sydney, NSW
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by 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
--
/* Veeam software enthusiast user & supporter ! */
albertwt
Veeam Legend
Posts: 879
Liked: 46 times
Joined: Nov 05, 2009 12:24 pm
Location: Sydney, NSW
Contact:

Re: Backing up SQL Server cluster nodes without failing over issue?

Post by 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?
--
/* Veeam software enthusiast user & supporter ! */
HannesK
Product Manager
Posts: 14287
Liked: 2877 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by HannesK » 1 person likes this post

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
Product Manager
Posts: 14287
Liked: 2877 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by HannesK »

A: log backup continues on another node, yes.
albertwt
Veeam Legend
Posts: 879
Liked: 46 times
Joined: Nov 05, 2009 12:24 pm
Location: Sydney, NSW
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by 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?
--
/* Veeam software enthusiast user & supporter ! */
HannesK
Product Manager
Posts: 14287
Liked: 2877 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by HannesK » 1 person likes this post

@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
Veeam Legend
Posts: 879
Liked: 46 times
Joined: Nov 05, 2009 12:24 pm
Location: Sydney, NSW
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by 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.
--
/* Veeam software enthusiast user & supporter ! */
HannesK
Product Manager
Posts: 14287
Liked: 2877 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by 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
Influencer
Posts: 10
Liked: 1 time
Joined: Feb 03, 2020 2:20 pm
Full Name: Jeroen Leeflang
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by 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
Product Manager
Posts: 14287
Liked: 2877 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by 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)
Post Reply

Who is online

Users browsing this forum: Paul.Loewenkamp, ThomasIkoula and 184 guests