Discussions specific to the VMware vSphere hypervisor
Post Reply
milonet
Novice
Posts: 9
Liked: never
Joined: May 22, 2018 12:50 pm
Full Name: Marco Milone
Contact:

Best way to backup Always ON SQL cluster Multi Istance

Post by milonet »

Hi,
i have two node (VM vsphere) with windows 2012 r2 WSFC and Sql Enterprise 2016.
I have some instance of SQL configured in Always On mode with Failover cluster.

I'm looking for the best solution to Backup SQL cluster (all instances) with transaction logs. I want use veeam B&R.
Instances are spread across both nodes and can switch based on load.
Some istance are primary on first node, others on seconds.

- How should I configure the recovery models?
- How many Backup Jobs do I need to configure? One for both nodes? One for each node? A single node job?
- How should I set up the Aware application on the job (s)?

It is currently not working very well. Log backups (log shipping every 15 minutes) often fail. "Collected SQL Server transaction logs do not match any existing database backup:"

Parent job always has a Waring "08/02/2021 15:27:15 :: VM DB-xxxx-02 is configured for SQL transaction logs truncation, while another AlwaysOn node is configured to log backup

Thank you and best regards

PetrM
Veeam Software
Posts: 1250
Liked: 192 times
Joined: Aug 28, 2013 8:23 am
Full Name: Petr Makarov
Location: Prague, Czech Republic
Contact:

Re: Best way to backup Always ON SQL cluster Multi Istance

Post by PetrM »

Hi Marco,

A database must use full recovery model to be eligible to be added to an availability group. I'd recommend to have a look at the prerequisites checklist in this article to get more information.

You need to add all nodes into a single job as it's stated on this page of our help center. An information message will be issued if a job does not process all cluster nodes.

I would select "backup logs periodically" option. Veeam will automatically determine a cluster node for which transaction logs will be backed up. This approach should address your issue with the warning message. However, it would be best to contact our support team to clarify the issue related to intermittent log backup failures. Please don't forget to share a support case ID with us.

Thanks!

milonet
Novice
Posts: 9
Liked: never
Joined: May 22, 2018 12:50 pm
Full Name: Marco Milone
Contact:

Re: Best way to backup Always ON SQL cluster Multi Istance

Post by milonet »

Thanks for the reply. I still have a question. I put both DB Servers in the same job. All right. Should application Aware be set up for both vm's? or just one? Should the backup settings on Microsoft's always on configuration be "Backup only from replica" or "Backup prefer from replica?"

PetrM
Veeam Software
Posts: 1250
Liked: 192 times
Joined: Aug 28, 2013 8:23 am
Full Name: Petr Makarov
Location: Prague, Czech Republic
Contact:

Re: Best way to backup Always ON SQL cluster Multi Istance

Post by PetrM »

Hi Marco,

You can set AAIP for both nodes. It's up to you to decide how to setup preference settings at the level of SQL itself, basically this setting is transparent for Veeam.

Veeam retrieves the list of preferred nodes from SQL and will trigger transaction log backup from a VM if it's in the obtained list. Any node can be chosen if there are no preferred nodes.

Thanks!

Post Reply

Who is online

Users browsing this forum: No registered users and 21 guests