AG backup preference behavior
-
Hi All,
We have 3-node AG setup in our Production environment. Primary site (2 nodes), (DR site 1node).
SQL Version is :Microsoft SQL Server 2017 (RTM-CU23) Enterprise Edition:(64-bit)
We have 3 user databases as part of AG. We had some performance issues and decided to offload the backup load to secondary node of AG.
For taking automated backups (Full and log), we use ola hallengren job script.
One day back we have changed the AG property Backup preferences to =>Secondary only.
For full backups, we added a COPY_ONLY option and full backup job is running fine on Secondary. However, the log backups which are supposed to run on primary every 1 hour is running successfully but actually it is not taking the log backups.
Today, we got an alert that transaction log file is full. For now, we manually. issued the BACKUP LOG CMD in SSMS new query window and it shows estimation completion time of 6 hours to take the log backup.
Question1) Why log backup is not happening on Primary.
Question2) Since backup preference is changed to Secondary, the log backups also should be running on Secondary with COPY_ONLY option like we changed for Fullbackup job in Secondary?
Question3) Since we are using COPY_ONLY, is there any implications, if we have to restore the database going forward?
Question 4) In backup preferences page, when we choose the Secondary only, it says, All automated backups must occur on Secondary. So, even log backup job has to has to run only Secondary with COPY_ONLY option?
Regards,
Sam
-
When the preference is set to secondary only - log backups must occur on the secondary node without copy_only. When you perform the log backups on the primary node Ola's scripts will either not run the log backups or they will add copy only.
If you change the setting to prefer secondary - then Ola's scripts will automatically select the secondary node based on the backup priority option and will not perform the backups on the primary node. If a secondary is not available then they would be run on the primary. This goes for either full backups or transaction log backups - but not for differentials, which can only ever run on the primary node and would require the full backups also be run on the primary node.
My guess is that either copy only was set for the log backups running on the primary - or the job is running but not actually backing up the log.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thanks Jeff. Gone through the article and changed the percentages.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply