How To Configure Log Backup for Always ON Availability Group


When your backup starting to mess due to the replica’s priority backup is not configured properly or somehow the primary AG has failed over to secondary or vice versa and the backup priority is not pointing to the right replica, then continue reading the article below.

Excerpt from source

Scenario 1: Secondary replica with different priority and backup preference Prefer Secondary.

  • Replica 1 (Primary Replica) Backup Priority: 20
  • Replica 2 (Secondary Replica) Backup Priority: 40
  • Replica 3 (Secondary Replica) Backup Priority: 10

The backup sequence will be as follows.

  • If we execute automated log backup, it will execute on Secondary Replica 2 because it has a high priority between both secondary replicas
  • If Replica 2 is down, the automated backup will happen on Secondary Replica 3 because it is the only available secondary replica
  • If both Secondary Replica 2 and Replica 3 are down, the backup will happen on Primary Replica (Replica 1)

Scenario 2: Secondary replica with similar priority and backup preference Prefer Secondary.

  • Replica 1 (Primary Replica) Backup Priority: 20
  • Replica 2 (Secondary Replica) Backup Priority: 30
  • Replica 3 (Secondary Replica) Backup Priority: 30

The backup sequence will be as follows.

  • If we execute automated log backup, it will execute on Secondary Replica 2. Both the replicas have similar priority however replica 2 comes in the list first
  • If Replica 2 is down, the automated backup will happen on Secondary Replica 3 because it is the only available secondary replica
  • If both Secondary Replica 2 and Replica 3 are down, the backup will happen on Primary Replica (Replica 1)

If we switch the order of replica as follows.

  • Replica 1 (Primary Replica) Backup Priority: 20
  • Replica 3 (Secondary Replica) Backup Priority: 30
  • Replica 2 (Secondary Replica) Backup Priority: 30

The backup sequence will be as follows.

  • If we execute automated log backup, it will execute on Secondary Replica 3. Both the replicas have similar priority however replica 2 comes in the list first
  • If Replica 3 is down, an automated backup will happen on Secondary Replica 2 because it is the only available secondary replica
  • If both Secondary Replica 2 and Replica 3 are down, the backup will happen on Primary Replica (Replica 1)

Scenario 3: Secondary replica with different priority and backup preference Secondary Only.

  • Replica 1 (Primary Replica) Backup Priority: 20
  • Replica 2 (Secondary Replica) Backup Priority: 40
  • Replica 3 (Secondary Replica) Backup Priority: 10

The backup sequence will be as follows.

  • If we execute automated log backup, it will execute on Secondary Replica 2 because it has a high priority between both secondary replicas
  • If Replica 2 is down, the automated backup will happen on Secondary Replica 3 because it is the only available secondary replica
  • If both Secondary Replica 2 and Replica 3 are down, No backup will execute on Primary Replica due to backup preference Secondary Only

Scenario 4: Secondary replica with similar priority and backup preference Secondary Only.

  • Replica 1 (Primary Replica) Backup Priority: 20
  • Replica 2 (Secondary Replica) Backup Priority: 30
  • Replica 3 (Secondary Replica) Backup Priority: 30

The backup sequence will be as follows.

  • If we execute automated log backup, it will execute on Secondary Replica 2. Both the replicas have similar priority however replica 2 comes in the list first
  • If Replica 2 is down, the automated backup will happen on Secondary Replica 3 because it is the only available secondary replica
  • If both Secondary Replica 2 and Replica 3 are down, No backup will execute on Primary Replica due to backup preference Secondary Only

Scenario 5: Secondary replica with different or similar priority and backup preference Primary.

  • Replica 1 (Primary Replica) Backup Priority: 20
  • Replica 2 (Secondary Replica) Backup Priority: 40
  • Replica 3 (Secondary Replica) Backup Priority: 10

The backup sequence will be as follows.

  • If we execute automated log backup, it will execute on primary Replica 2 due to backup preference Primary
  • If Replica 2 or 3 are down, it will not affect backups because backup will happen on Primary replica only
  • If Primary replica is down, the backup will not happen on Secondary Replica

Huge credit to this article.