Written by - Sagi Amichai | Edits and inspiration - Noa Brodezky
The other day I wanted to know how Ola Hallengren’s implementation behaves with AO and Backup Preference.
I found this blog of Brent Ozar from 2015: How to Configure Always On AG Backups with Ola Hallengren’s Scripts For those of us who are not familiar with Copy Only Backups just like I wasn't: Copy Only backups, Full or Log, are the same as "regular" Full/Log Backup, but without cycling the log file. They read the log file and backup it, but not changing the LSN, just like there was no backup at all. They are being used mostly to take a backup without interfering with the backup plan, so the backup taken won't be needed in case of a disaster, for restoring rolling logs. Now, let's take an AlwaysOn example. Let's say we have 2 servers: Primary and Secondary. The Backup preference is to backup on the Secondary, but regular full backups on the secondary replica are not supported, however, Copy Only Full backups are!
and what about log backups? Using Copy Only Log Backup is not supported for Secondary replicas. And, even when used on the primary, it still doesn't truncate the log files. so we need to go back to the old and good regular Log Backups. Meaning, Copy Only Full Backup on the secondary, Regular Log Backup on the primary. Now lets jump back to our question: Ola Solution in Brent's post. What to do, how to use, and what shall we config? It is mentioned we need to use the copy only backups to perform backups on the secondary and explained in details for full backups. All good till here. But regarding the log backups, it is said we "don't have to" add the "CopyOnly='Y' ". It is also mentioned, and in other blogs, that there is no such thing as Copy Only Log Backup for AO. If you execute a log backup with CopyOnly, it behaves just like a regular log backup – but in a single server, not in Always on! The blog post, made me think keeping the CopyOnly='Y' would perform as a regular log backup, the log file will truncate and I'll be happy. I wasn't so happy though at midnight 00:06 when our monitoring system called me with "log file is about to be full" warning. I tested it right away and saw that manually executing Copy Only Log Backup doesn’t truncate the log file. I manually executed a regular Log Backup and things got back to normal – the log file has been truncated. To conclude, We don't "don't have to" (bad grammar intentionally) use CopyOnly='Y' for log backups.
We must not use CopyOnly Log Backups.
For AO Backups using Ola solution: To perform the Full Backup on the secondary replica - Use CopyOnly full backups For Log Backup on the secondary replica – Use a regular Log Backup and that way the log file will be cycled. Just to make it clearer, Ola has a single stored procedure for all backup types with lots of parameters, and it has default values for these parameters.
Then there is a job for each backup type – one for full backups and another for log backups.
Each job should be configured with different parameter values, like the Copy Only parameter.
Don’t modify the parameter default values in the stored procedure itself, but rather configure the appropriate parameter values in each job.
