Backuping up Microsoft SQL Availability group clusters

[ARycroft]

Hi,

Please can you advise me? We are backing up Microsoft Availability Group clusters.

The Database team requires that in a Microsoft SQL availability group consisting of 4x SQL DB servers they would like to process backups as follows;

1) the FULL backups occur once a week on the PRIMARY ONLY.
2) The DIFFERENTIALS must occur once per day on the PRIMARY ONLY.
3) The TLOGS should occur every 15 minutes with truncation on the SECONDARY ONLY.

We have managed to get the full backups and differentials to only run on the primary. But the tlogs backups continue to run on both the primary and secondary with Veeam. Is there a way to configure Veeam to backup the Transaction logs only on the Primary?

Any suggestions appreciated.

Kind regards
Andrew Rycroft

[PetrM]

Hi Andrew,

Are you talking about the Veeam Plug-in for Microsoft SQL Server? If so, the plug-in follows the SQL Server backup preferences settings. My only suggestion is to change the backup preferences (f.g. AUTOMATED_BACKUP_PREFERENCE = { PRIMARY | SECONDARY_ONLY| SECONDARY | NONE } ) within the execution step of the SQL Server Agent job that precedes the plug-in backup command. For example, you can set the 'Prefer Secondary' option and change it to 'Primary' when you run full or differential backups.

Thanks!

[ARycroft]

Hi,

Thank you for your response. The steps for the backup would then be:-

1. Create a script on the SQL Server to set the desired backup preference setting
2. Create the Veeam SQL Server agent job, and define the pre run script as the SQL Server script that sets the SQL Backup preference parameter
3. Run the Backup job that uses the plug-in backup command with the define pre-run script

Do you perhaps have an example of such a script? The Microsoft documentation is very detailed, and I am not an SQL person.

Kind regards
Andrew Rycroft

[PetrM]

Hi Andrew,

I don't have an example of such a script, but you may refer to the article mentioned in my previous post to change the backup preference settings. The plug-in backup command can be generated automatically either by:
1. Using the "Create SQL Agent Job" button -> just add one more step to the job that changes the backup preference.
2. Using the "Script" button -> create a new job on your own and simply add the generated command there.

Thanks!