SQL Server Always ON Availability Group Log Backup on Secondary Replicas
SQL Server Always ON Availability Group Log Backup on Secondary Replicas
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolSQL Server Always ON Availability Group Log Backup on Secondary Replicas
May 10, 2019 by Rajendra Gupta 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. 123456 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.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 OnlyScenario 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 OnlyScenario 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 ReplicaConclusion
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. Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022