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.
在高OLTP环境中,由于频繁的备份,我们可能会观察到CPU和IO的压力。 由于备份压缩,它可能包括进一步的复杂性。 SQL Server Always On可用性组提供了从辅助副本执行数据库备份的功能。 本文探讨了SQL Server Always On可用性组中的日志备份。
SQL Server Always On可用性组中辅助副本上受支持的备份 (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 |
备份类型 | 主副本 | 辅助副本(同步和异步) |
充分 | 是 | 是的,但具有Copy_Only选项) |
记录 | 是 | 是(我们不能在辅助副本上使用COPY_ONLY进行日志备份) |
微分 | 是 | 没有 |
To take database backup on Secondary replica, it should meet the following conditions.
要在辅助副本上进行数据库备份,它应满足以下条件。
- Primary and Secondary replica should be connected 主副本和辅助副本应连接
- SYNCHRONIZED or SYNCHRONIZED或SYNCHRONIZING SYNCHRONIZING
- Not Synchronized, 未同步 , disconnected or 断开连接或正在resolving 解决的辅助可用性组数据库
In this article, we will explore, in detail, about the Log backup on Secondary replica.
在本文中,我们将详细探讨辅助副本上的日志备份。
SQL Server Always On可用性组中辅助副本上的日志备份 (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可用性组配置中有三个节点。 在下图中,您可以看到两个节点处于同步数据提交模式,并且一个节点(DR节点)配置了异步数据提交模式。
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.
SQL Server确保一致的日志备份链,无论我们进行了哪个日志备份。 它也独立于同步或异步数据提交模式。
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 一旦主副本接收到该请求,它将尝试对需要进行备份的数据库进行Bulkop锁定。 这样可以防止同时从多个副本中进行备份。 主副本一次只能处理一个请求,以从次副本获取Bulkop锁定
- 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 一旦主副本获得Bulkop锁,它将通知辅助副本开始日志备份。 在上次日志备份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 主副本更新从辅助副本接收到的LSN信息,并更新到所有辅助副本。 它确保所有辅助副本都同步。 我们不需要从指定的辅助数据库进行备份
- The primary replica releases the BulkOp lock on the database. Once this lock is released, any other replica can initiate the backup 主副本释放数据库上的BulkOp锁。 释放此锁定后,任何其他副本都可以启动备份
- Each secondary replica can truncate the logs based on LSN 每个辅助副本都可以基于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.
我们在上一节中介绍了整个日志备份过程。 让我们尝试使用一个示例对此进行探索。
SQL Server Always On可用性组中的日志备份过程示例 (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.
要进行日志备份,可以使用SSMS或t-SQL查询。 一旦所有备份完成,就对每个副本执行以下查询,收集所有最近日志备份(在步骤1至5中进行的备份)的LSN信息,并将它们放在excel表中以进行比较。
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 |
备份顺序 | First_LSN | Last_LSN | Backup_start_date | Backup_finish_date |
1个 | 34000000013000000 | 37000000110900000 | 19/04/30下午12:33:21 | 19/04/30下午12:33:21 |
2 | 37000000110900000 | 37000000113000000 | 19/04/30下午12:33:30 | 19/04/30下午12:33:30 |
6 | 37000000114800000 | 37000000115400000 | 19/04/30下午12:34:34 | 19/04/30下午12:34:34 |
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 |
备份顺序 | First_LSN | Last_LSN | Backup_start_date | Backup_finish_date |
3 | 37000000113000000 | 37000000113400000 | 19/04/30下午12:33:43 | 19/04/30下午12:33:43 |
4 | 37000000113400000 | 37000000114100000 | 19/04/30下午12:33:46 | 19/04/30下午12:33:46 |
7 | 37000000115400000 | 37000000115800000 | 19/04/30下午12:34:40 | 19/04/30下午12:34:40 |
Secondary Replica – Asynchronous (DR replica)
辅助副本–异步(DR副本)
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 |
备份顺序 | First_LSN | Last_LSN | Backup_start_date | Backup_finish_date |
5 | 37000000114100000 | 37000000114800000 | 19/04/30下午12:34:27 | 19/04/30下午12:34:27 |
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.
在上图中,我们可以看到从哪个副本执行日志备份都没关系。 日志备份LSN与每个副本同步。 例如,一旦我们在辅助副本上执行了第三次日志,它将在第二次日志备份的最后一个LSN之后执行日志备份。 第二次日志备份已在主副本上完成。
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.
如果任何副本出现故障,则不会影响日志备份链。 一旦副本联机并与主副本同步,我们也可以从该节点执行日志备份。 在开始日志备份之前,与主副本通信时,它将获得最新的LSN信息。 主副本在SQL Server Always On可用性组中进行备份方面起着重要作用。
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.
我们可以从SQL可用性组中的任何副本中进行备份,但是我们需要将所有日志备份存储在共享位置。 对于任何还原要求,我们都需要在上次完整备份之后进行所有日志备份。 如果任何节点发生故障,并且我们无法从该副本访问日志备份,则它将不允许我们进行数据库还原。
SQL Server Always On可用性组中的日志备份配置 (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.
连接到SSMS中的主副本实例,然后转到属性。 在属性中,您可以看到“ 备份首选项”包含许多备份选项。
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.
在本文中,我们将在SQL Server Always On可用性组中快速了解备份首选项。 您可以参考“目录”部分的文章以获取更多详细信息。
备份首选项 (Backup Preference)
Prefer Secondary: Automated backup for the SQL availability group should occur on a secondary replica.
首选二级: SQL可用性组的自动备份应在二级副本上进行。
- 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.
仅辅助服务器: SQL可用性组的自动备份必须在辅助副本上进行。
- 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.
我们讨论了SQL Server Always On可用性组中的副本备份优先级。 让我们详细了解它。
Suppose we have three replicas in the existing setup and we have chosen backup preference as Prefer Secondary.
假设我们在现有设置中有三个副本,并且选择了备份首选项作为“ 首选二级”。
方案1:具有不同优先级和备份首选项的辅助副本优先选择辅助副本。 (Scenario 1: Secondary replica with different priority and backup preference Prefer Secondary.)
- Replica 1 (Primary Replica) Backup Priority: 20 副本1(主副本)备份优先级:20
- Replica 2 (Secondary Replica) Backup Priority: 40 副本2(辅助副本)备份优先级:40
- Replica 3 (Secondary Replica) Backup Priority: 10 副本3(辅助副本)备份优先级: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 如果我们执行自动日志备份,它将在辅助副本2上执行,因为它在两个辅助副本之间都具有较高的优先级
- If Replica 2 is down, the automated backup will happen on Secondary Replica 3 because it is the only available secondary replica 如果副本2关闭,则自动备份将在辅助副本3上进行,因为它是唯一可用的辅助副本
- If both Secondary Replica 2 and Replica 3 are down, the backup will happen on Primary Replica (Replica 1) 如果辅助副本2和副本3都关闭,则备份将在主副本(副本1)上进行
方案2:具有相似优先级和备份首选项的辅助副本优先选择辅助副本。 (Scenario 2: Secondary replica with similar priority and backup preference Prefer Secondary.)
- Replica 1 (Primary Replica) Backup Priority: 20 副本1(主副本)备份优先级:20
- Replica 2 (Secondary Replica) Backup Priority: 30 副本2(辅助副本)备份优先级:30
- Replica 3 (Secondary Replica) Backup Priority: 30 副本3(辅助副本)备份优先级: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 如果我们执行自动日志备份,它将在辅助副本2上执行。两个副本具有相似的优先级,但是副本2首先出现在列表中
- If Replica 2 is down, the automated backup will happen on Secondary Replica 3 because it is the only available secondary replica 如果副本2关闭,则自动备份将在辅助副本3上进行,因为它是唯一可用的辅助副本
- If both Secondary Replica 2 and Replica 3 are down, the backup will happen on Primary Replica (Replica 1) 如果辅助副本2和副本3都关闭,则备份将在主副本(副本1)上进行
If we switch the order of replica as follows.
如果我们按以下方式切换副本的顺序。
- Replica 1 (Primary Replica) Backup Priority: 20 副本1(主副本)备份优先级:20
- Replica 3 (Secondary Replica) Backup Priority: 30 副本3(辅助副本)备份优先级:30
- Replica 2 (Secondary Replica) Backup Priority: 30 副本2(辅助副本)备份优先级: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 如果我们执行自动日志备份,它将在辅助副本3上执行。两个副本具有相似的优先级,但是副本2首先出现在列表中
- If Replica 3 is down, an automated backup will happen on Secondary Replica 2 because it is the only available secondary replica 如果副本3关闭,则会在辅助副本2上进行自动备份,因为它是唯一可用的辅助副本
- If both Secondary Replica 2 and Replica 3 are down, the backup will happen on Primary Replica (Replica 1) 如果辅助副本2和副本3都关闭,则备份将在主副本(副本1)上进行
方案3:具有不同优先级和备份首选项的辅助副本仅辅助。 (Scenario 3: Secondary replica with different priority and backup preference Secondary Only.)
- Replica 1 (Primary Replica) Backup Priority: 20 副本1(主副本)备份优先级:20
- Replica 2 (Secondary Replica) Backup Priority: 40 副本2(辅助副本)备份优先级:40
- Replica 3 (Secondary Replica) Backup Priority: 10 副本3(辅助副本)备份优先级: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 如果我们执行自动日志备份,它将在辅助副本2上执行,因为它在两个辅助副本之间都具有较高的优先级
- If Replica 2 is down, the automated backup will happen on Secondary Replica 3 because it is the only available secondary replica 如果副本2关闭,则自动备份将在辅助副本3上进行,因为它是唯一可用的辅助副本
- Secondary Only 仅辅助服务器”而不会在主副本服务器上执行任何备份
方案4:优先级和备份首选项相似的辅助副本仅“辅助”。 (Scenario 4: Secondary replica with similar priority and backup preference Secondary Only.)
- Replica 1 (Primary Replica) Backup Priority: 20 副本1(主副本)备份优先级:20
- Replica 2 (Secondary Replica) Backup Priority: 30 副本2(辅助副本)备份优先级:30
- Replica 3 (Secondary Replica) Backup Priority: 30 副本3(辅助副本)备份优先级: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 如果我们执行自动日志备份,它将在辅助副本2上执行。两个副本具有相似的优先级,但是副本2首先出现在列表中
- If Replica 2 is down, the automated backup will happen on Secondary Replica 3 because it is the only available secondary replica 如果副本2关闭,则自动备份将在辅助副本3上进行,因为它是唯一可用的辅助副本
- Secondary Only 仅辅助服务器”而不会在主副本服务器上执行任何备份
方案5:具有不同或相似优先级和备份优先级的辅助副本。 (Scenario 5: Secondary replica with different or similar priority and backup preference Primary.)
- Replica 1 (Primary Replica) Backup Priority: 20 副本1(主副本)备份优先级:20
- Replica 2 (Secondary Replica) Backup Priority: 40 副本2(辅助副本)备份优先级:40
- Replica 3 (Secondary Replica) Backup Priority: 10 副本3(辅助副本)备份优先级:10
The backup sequence will be as follows.
备份顺序如下。
- Primary 小学
- If Replica 2 or 3 are down, it will not affect backups because backup will happen on Primary replica only 如果副本2或3关闭,则不会影响备份,因为备份只会在主副本上进行
- 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.
在本文中,我们探讨了SQL Server Always On可用性组中辅助副本上的日志备份。 我们还介绍了副本备份首选项和副本优先级。 我将在我的后续文章中介绍有关SQL Server Always On的模式。
翻译自: https://www.sqlshack.com/sql-server-always-on-availability-group-log-backup-on-secondary-replicas/