December 2, 2019 at 5:43 pm
Hello,
hopefully a simple question and easy thing to implement... so i have an 2 node always on, Server A and B. A is primary, B is secondary, i changed my preferences to have B setup for backups, but also transaction log backups, however thinking about this, if i backup transaction logs on B, will it commit and keep my transaction logs small on A? and if not, what and how do i implement so A transaction logs dont grow out of control when B is taking the transaction log backups?
thanks in advance
December 2, 2019 at 6:49 pm
I did find this article:
https://www.sqlshack.com/sql-server-always-on-availability-group-log-backup-on-secondary-replicas/
I made sure the backups are set on the secondary, also on the Always on AG group, in properties, i set backup preferences to "Prefer SEcondary" and made a backup using Ole Hallengen scripts... but it never backups the always on... is there anything else i should be looking for?
December 3, 2019 at 3:06 pm
You can't do a full backup on a secondary replica, just a copy only backup: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-backup-on-secondary-replicas-always-on-availability-groups?view=sql-server-2016
My experience (using the same scripts) was that the full backups were taken on the primary replica, and the transaction log backups were taken on the secondary replica. When an automatic failover happened, I got the backup files generated the other way around. IMHO the backup preference feature of availability groups isn't what it is cracked up to be.
HTH
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply