Rajendra Gupta
SQL Always On Availability Group Backup Preference

SQL Server Always ON Availability Group Log Backup on Secondary Replicas

May 10, 2019 by

In a high OLTP environment, we may observe strain on CPU and IO due to frequent backups. It might include further complexity due to backup compressions. SQL Server Always On Availability groups provides the capability to perform database backups from the secondary replicas. This article explores Log backups in SQL Server Always On Availability Group.

Supported backups on Secondary Replicas in SQL Server Always On Availability groups

In the following table, we can see the supported backup type on Primary and Secondary replica.

Backup Type

Primary Replica

Secondary Replica (Synchronous and Asynchronous)

Full

Yes

Yes but with Copy_Only option)

Log

Yes

Yes (we cannot take log backup with COPY_ONLY on secondary replica)

Differential

Yes

No

To take database backup on Secondary replica, it should meet the following conditions.

  • Primary and Secondary replica should be connected
  • Database status should be either SYNCHRONIZED or SYNCHRONIZING
  • We cannot use secondary availability group database having status Not Synchronized, disconnected or resolving

In this article, we will explore, in detail, about the Log backup on Secondary replica.

Log Backup on a Secondary Replica in SQL Server Always On Availability groups

In the backup table, we can see that log backup can be taken on both primary and secondary replica. Suppose we have three nodes in SQL Server Always On Availability Groups configuration. In the following image, you can see two nodes are in Synchronous data commit mode, and one node (DR node) is configured with asynchronous data commit mode.

SQL Server Always On Availability groups  Windows failover clustering

SQL Server ensures consistent log backup chain regardless on which replica we took log backup. It is also independent of the synchronous or asynchronous data commit mode.

Suppose we want to configure log backup on a secondary replica. It follows the following steps to do log backup a Secondary replica.

  • Firstly, it informs the primary replica that it needs to start a log backup
  • Once the Primary replica receives the request, it attempts to take a Bulkop lock on the database for which backup needs to be taken. It prevents to take a backup from multiple replicas at the same time. A primary replica can work on only one request at a time to take Bulkop lock from the secondary replica
  • Once the primary replica acquires Bulkop lock, it informs to secondary replica to start log backup. It takes log backup after the last log backup LSN
  • The secondary replica starts the log backup and once finished, it sends backup completion notification to the primary replica. It also gives information about the last log sequence number in the log backup to the primary replica
  • The primary replica updates the LSN information received from Secondary replica and updates to all Secondary replica. It ensures all secondary replicas are in sync. We do not need to take a backup from a specified secondary
  • The primary replica releases the BulkOp lock on the database. Once this lock is released, any other replica can initiate the backup
  • Each secondary replica can truncate the logs based on LSN

You can understand the whole log backup process from Secondary replica in the following image.

SQL Server Always On Availability groups Windows failover clustering on the secondary replica

We looked at the overall log backup process in the above section. Let us try to explore this using an example.

Example of a Log backup process in SQL Server Always On Availability Groups

For this demonstration, we will perform multiple log backups in the following sequence.

  1. Two consecutive log backups on the primary replica
  2. Two consecutive log backup on Secondary replica with Synchronous data commit
  3. One log backup on Secondary replica with asynchronous data commit
  4. One log backup on the primary replica
  5. One log backup on Secondary replica with Synchronous data commit

To take log backup, you can use either SSMS or t-SQL query. Once all the backup gets finished, execute the following query on each replica, collect LSN information of all recent log backups (backup taken in step 1 to 5) and put them in an excel sheet for comparison purpose.

Primary Replica

Backup Sequence

First_LSN

Last_LSN

Backup_start_date

Backup_finish_date

1

34000000013000000

37000000110900000

04/30/19 12:33:21 PM

04/30/19 12:33:21 PM

2

37000000110900000

37000000113000000

04/30/19 12:33:30 PM

04/30/19 12:33:30 PM

6

37000000114800000

37000000115400000

04/30/19 12:34:34 PM

04/30/19 12:34:34 PM

Secondary Replica – Synchronous

Backup Sequence

First_LSN

Last_LSN

Backup_start_date

Backup_finish_date

3

37000000113000000

37000000113400000

04/30/19 12:33:43 PM

04/30/19 12:33:43 PM

4

37000000113400000

37000000114100000

04/30/19 12:33:46 PM

04/30/19 12:33:46 PM

7

37000000115400000

37000000115800000

04/30/19 12:34:40 PM

04/30/19 12:34:40 PM

Secondary Replica – Asynchronous (DR replica)

Backup Sequence

First_LSN

Last_LSN

Backup_start_date

Backup_finish_date

5

37000000114100000

37000000114800000

04/30/19 12:34:27 PM

04/30/19 12:34:27 PM

Let’s represent these log backups in a graphical representation to have a better view.

Log backup in SQL Server Always On Availability Groups

In the above image, we can see it does not matter from which replica we are executing log backups. Log backup LSNs are in sync with each replica. For example, once we execute log third on the secondary replica, it takes log backup after the last LSN of 2nd log backup. 2nd Log backup was completed on Primary replica.

If any replica goes down, it does not impact the log backup chain. Once the replica comes online and sync with the primary replica, we can execute log backups from that node as well. It will get last LSN information while communicating with Primary replica before starting the log backup. Primary replica plays an important role in taking backups in SQL Server Always On Availability Groups.

We can take a backup from any replica in SQL availability groups but we need to store all log backups at a shared location. We need to have all log backups after last full backups for any restore requirement. If any node goes down and we cannot access the log backups from that replica, it won’t allow us to do database restore.

Log backup configuration in SQL Server Always On Availability groups

In the previous section, we explored that you can use secondary replicas (both synchronous and asynchronous) to take log backups. Now, we will look the backup configuration options in always on along with backup priority.

Connect to Primary replica instance in SSMS and go to properties. In the properties, you can see Backup Preference contains many backup options.

In this article, we will have a quick overview of backup preference in SQL Server Always On Availability group. You can refer articles from TOC section for more detail.

Backup Preference

SQL Always On Availability Group Backup Preference

Prefer Secondary: Automated backup for the SQL availability group should occur on a secondary replica.

  • If we have multiple secondary replicas, it executes backup on secondary replica having high backup priority
  • We can have multiple secondary replicas with the same priority. In this case, it takes backup on the replica that comes first in a list of replica backup priorities
  • If no secondary replicas are available, it takes backup on the primary replica
  • It is the default backup preference

Secondary Only: Automated backup for the SQL availability group must occur on the secondary replica.

  • If we have multiple secondary replicas, it executes backup on secondary replica having high backup priority
  • We can have multiple secondary replicas with the same priority. In this case, it takes backup on the replica that comes first in the list of replica backup priorities
  • If no secondary replicas are available, it does not take backup on the primary replica

Primary: Automated backup should occur on Primary replica only.

  • We cannot take differential backup on the secondary replica. With this preference, we can set automated differential backup as well

Any Replica: With this option, we can take backup on any replica (primary as well as on secondary). It checks for the backup priority to take an automated backup.

We talked about the replica backup priority in SQL Server Always On Availability Groups. Let us understand it in detail.

Suppose we have three replicas in the existing setup and we have chosen backup preference as Prefer Secondary.

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

Conclusion

In this article, we explored the log backups on Secondary replica in SQL Server Always On Availability Groups. We also covered replica backup preferences and replica priorities as well. I will cover mode on SQL Server Always On in my upcoming articles.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
820 Views