辅助副本上SQL Server始终打开可用性组日志备份

最新推荐文章于 2021-08-12 14:13:52 发布

culuo4781

最新推荐文章于 2021-08-12 14:13:52 发布

阅读量489

点赞数

文章标签：数据库 java mysql python 分布式

原文链接：https://www.sqlshack.com/sql-server-always-on-availability-group-log-backup-on-secondary-replicas/

版权

本文深入探讨了SQL Server Always On可用性组中辅助副本的日志备份，包括支持的备份类型、备份过程和配置。重点讨论了在不同优先级和备份首选项下的日志备份策略，确保在高OLTP环境下维护一致的日志备份链。

摘要由CSDN通过智能技术生成

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

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.

您可以在下图中从辅助副本了解整个日志备份过程。

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.

我们在上一节中介绍了整个日志备份过程。让我们尝试使用一个示例对此进行探索。

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.

让我们以图形表示形式表示这些日志备份，以获得更好的视图。

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.

在上图中，我们可以看到从哪个副本执行日志备份都没关系。日志备份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)

SQL Always On Availability Group 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.

假设我们在现有设置中有三个副本，并且选择了备份首选项作为“ 首选二级”。