SQL Server Always ON Availability Group Log Backup on Secondary Replicas

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 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.

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.

Two consecutive log backups on the primary replica
Two consecutive log backup on Secondary replica with Synchronous data commit
One log backup on Secondary replica with asynchronous data commit
One log backup on the primary replica
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.

SELECT

,first_lsn

,last_lsn

,backup_start_date

,backup_finish_date

FROM msdb.dbo.backupset

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.

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

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.