Best way to backup Always ON SQL cluster Multi Istance

[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]

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]

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]

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!