SQL Server始终可用的可用性组数据重新同步

In my previous article Data Synchronization in SQL Server Always On Availability Group, we described a scenario where if a secondary replica goes down in synchronous data commit mode, SQL Server Always on Availability group changes to asynchronous data commit mode. It ensures that users can get their transaction commit irrespective of waiting for a secondary replica to come online.

在我之前的文章《 SQL Server Always On可用性组中的数据同步》中 ，我们描述了一种方案，如果辅助副本在同步数据提交模式下关闭，则SQL Server Always On可用性组将变为异步数据提交模式。 它确保用户可以获取事务提交，而无需等待辅助副本联机。

In this article, we will explore the following scenario related with SQL Server Always on Availability group.

在本文中，我们将探讨与SQL Server始终可用性组有关的以下方案。

• Synchronous Secondary Replica resynchronization process SQL Server Always on Availability Group
  同步辅助副本重新同步过程SQL Server始终存在可用性组
• Automatic failover in case of Primary Replica goes down
  万一主副本出现故障，自动故障转移
• Manual planned Failover
  手动计划的故障转移
• Force Manual failover with data loss
  强制进行手动故障转移并丢失数据

同步辅助副本重新同步过程SQL Server始终存在可用性组 (Synchronous Secondary Replica resynchronization process SQL Server Always on Availability Group)

Suppose we have three SQL Always On replicas as follows.

假设我们有三个SQL Always On副本，如下所示。

• Two replicas in DC having synchronous data commit
  DC中具有同步数据提交的两个副本
• One replica is DR having asynchronous data commit
  一个副本是具有异步数据提交的DR

In the following screenshot, we can see two nodes are down. I have stopped SQL services on both secondary replicas (RDP to replica server and Open SQL Server Configuration Manager and stop SQL Service)

在以下屏幕截图中，我们可以看到两个节点都关闭了。 我已经停止了两个辅助副本上SQL服务（RDP到副本服务器和Open SQL Server Configuration Manager并停止了SQL Service）

• Down, It switches to Asynchronous data commit Down ，它切换到异步数据提交
• Down 向下

Once the secondary synchronous data replica is offline, it switches database status to Not Synchronizing. In this mode, Primary replica does not wait for the acknowledgement from the secondary replica.

一旦辅助同步数据副本脱机，它将数据库状态切换为“ 不同步”。 在此模式下，主副本不等待来自辅助副本的确认。

In the following screenshot, we can see that the status of both secondary replicas is Not Synchronizing.

在以下屏幕截图中，我们可以看到两个辅助副本的状态均为“ 未同步”。

SELECT CASE r.replica_server_name
           WHEN 'ABC'
           THEN 'Primary Replica'
           WHEN 'XYZ'
           THEN 'Secondary DC Replica'
           ELSE 'Secondary DR Replica'
       END AS Replica, 
       adc.database_name, 
       drs.is_local, 
       drs.is_primary_replica, 
       drs.synchronization_state_desc, 
       drs.is_commit_participant, 
       drs.synchronization_health_desc, 
       drs.recovery_lsn
FROM sys.dm_hadr_database_replica_states AS drs
     INNER JOIN sys.availability_databases_cluster AS adc ON drs.group_id = adc.group_id
     AND drs.group_database_id = adc.group_database_id
     INNER JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id
     INNER JOIN sys.availability_replicas AS ar ON drs.group_id = ar.group_id
     AND drs.replica_id = ar.replica_id
ORDER BY Replica;

In the following query, we use DMV sys.dm_hadr_database_replica_states to get details about LSN on primary and secondary replica.

在以下查询中，我们使用DMV sys.dm_hadr_database_replica_states获取有关主副本和辅助副本上LSN的详细信息。

SELECT CASE r.replica_server_name
           WHEN 'ABC'
           THEN 'Primary Replica'
           WHEN 'XYZ'
           THEN 'Secondary DC Replica'
           ELSE 'Secondary DR Replica'
       END AS Replica, 
       rs.is_primary_replica IsPrimary, 
       rs.last_received_lsn, 
       rs.last_hardened_lsn, 
       rs.last_redone_lsn, 
       rs.end_of_log_lsn, 
       rs.last_commit_lsn
FROM sys.availability_replicas r
     INNER JOIN sys.dm_hadr_database_replica_states rs ON r.replica_id = rs.replica_id
ORDER BY replica;

In SQL Server Always on Availability Group, we can use last_commit_lsn to check the commit LSN on all the available nodes. We can see that end_of_log_lsn is similar when the always on the group were in a synchronized state.

在“ SQL Server始终可用性组”中，我们可以使用last_commit_lsn来检查所有可用节点上的提交LSN。 我们可以看到，当组中的Always处于同步状态时，end_of_log_lsn相似。

Now, connect to primary replica and do some DML transactions. It generates transaction log records, but those records could not be sent to secondary replica because of their unavailability.

现在，连接到主副本并执行一些DML事务。 它生成事务日志记录，但是由于这些记录不可用而无法发送到辅助副本。

Again execute the query to check last_commit_lsn. We can see that the primary replica is ahead of the secondary replica. You can also check end_of_log_lsn column, and it indicates the last log LSN on respective replicas.

再次执行查询以检查last_commit_lsn 。 我们可以看到主副本位于辅助副本之前。 您还可以检查end_of_log_lsn列，它指示各个副本上的最后一个日志LSN。

Bring the services online (take RDP to corresponding replica server and Open SQL Server Configuration Manager and Start SQL Service). Once the SQL Services are up on secondary replica, it establishes a connection with the primary replica. Secondary replica sends end_of_log_lsn to the primary replica. Previously we noticed that SQL Server Always on Availability Group changes to the asynchronous mode and commit the records in primary replica only. It commits the transactions but does not truncate the logs until a secondary replica is in sync again. Primary replica sends all transaction blocks starting from end_of_log_lsn to secondary replica.

使服务联机（将RDP带到相应的副本服务器，然后打开SQL Server配置管理器并启动SQL服务）。 一旦SQL服务在辅助副本上启动，它将与主副本建立连接。 辅助副本将end_of_log_lsn发送到主副本。 以前，我们注意到SQL Server Always On可用性组更改为异步模式，并且仅在主副本中提交记录。 它会提交事务，但不会截断日志，直到辅助副本再次同步。 主副本将所有从end_of_log_lsn开始的事务块发送到辅助副本。

Secondary replica receives these transaction blocks and hardens those transactions. Data Synchronization mode is still asynchronous. It also changes synchronization state from Not Synchronizing to Synchronizing.

辅助副本接收这些事务块并强化这些事务。 数据同步模式仍然是异步的。 还将同步状态从“ 不同步”更改为“正在同步”。

The secondary replica sends an acknowledgement for transaction blocks and keeps doing this process until last_hardened_lsn of both primary and secondary replica is the same.

辅助副本发送对事务块的确认，并继续执行此过程，直到主副本和辅助副本的last_hardened_lsn相同为止。

At this point, SQL Server Always on Availability group data synchronization changes to synchronous data commit from Asynchronous data commit.

此时，SQL Server始终可用性组数据同步将从异步数据提交更改为同步数据提交。

Note: Asynchronous data commit mode remains the same. It does not change to synchronous data commit automatically. SQL Server again starts waiting for the acknowledgement from secondary replica for all transactions.

注意：异步数据提交模式保持不变。 它不会更改为自动同步提交数据。 SQL Server再次开始等待来自辅助副本的所有事务的确认。

We have one secondary DR replica as well. In this case, the first connection gets established between primary and secondary replica and Primary replica send transaction log after end_of_log_lsn of the secondary replica. It changes the status to synchronizing from not synchronized.

我们也有一个辅助DR副本。 在这种情况下，将在主副本和辅助副本之间建立第一个连接，并且主副本在辅助副本的end_of_log_lsn之后发送事务日志。 它改变的状态从同步 不同步 。

万一主副本出现故障，自动故障转移 (Automatic failover in case of Primary Replica goes down)

We can achieve automatic failure in case of loss of primary replica. Automatic failover could occur in case of synchronous data commit only.

如果丢失主副本，我们可以实现自动故障。 仅在同步数据提交的情况下，才可能发生自动故障转移。

In SQL Server 2012 and 2014, if the primary instance is available and healthy, it does not perform automatic failover. It does not check the individual database in an availability group. In SQL Server 2016, we can have availability group health monitoring as well. We can configure that if a database in an availability group becomes unavailable, it can also trigger an automatic failover.

在SQL Server 2012和2014中，如果主实例可用且运行状况良好，则它不会执行自动故障转移。 它不会检查可用性组中的单个数据库。 在SQL Server 2016中，我们也可以进行可用性组运行状况监视。 我们可以配置为，如果可用性组中的数据库不可用，它也可以触发自动故障转移。

自动故障转移步骤 (Automatic failover Steps)

• SQL Server Always On Availability group status for primary replica changes to Disconnected from Synchronized
  主副本的“ SQL Server永远在线”可用性组状态更改为“已从同步断开”
• Secondary replica starts taking Primary role in Availability group. It rolls forward any pending transactions in the recovery queue and hardens them
  辅助副本开始在“可用性”组中担任“主要”角色。 它将恢复队列中的所有未决事务前滚并强化它们
• The secondary replica works as a new primary replica. It rolls back any uncommitted transactions and database become available for the users. If we are using listener configuration in Always On, it automatically points all connections to the new primary replica. It also starts asynchronous data commit and commit transactions on Primary replica only
  辅助副本用作新的主副本。 它回滚所有未提交的事务，并且数据库对用户可用。 如果我们在Always On中使用侦听器配置，它将自动将所有连接指向新的主副本。 它还仅在主副本上启动异步数据提交和提交事务。
• Later, once the secondary replica becomes available and connects with Primary replica, it follows the steps we explored in the previous section and start data synchronizing process. Once the databases are in sync, new primary replica starts synchronization data commit with the new secondary replica node. It does not do automatic failover again to change the status of an old primary replica (current secondary replica) to the current primary replica
  以后，一旦辅助副本可用并与主副本连接，它就会遵循上一节中探讨的步骤并开始数据同步过程。 数据库同步后，新的主副本开始与新的辅助副本节点进行同步数据提交。 它不会再次执行自动故障转移以将旧的主副本（当前的辅助副本）的状态更改为当前的主副本。

手动计划的故障转移 (Manual planned Failover )

We can perform planned manual failure to secondary transition replica to the primary replica. We can perform planned failover using SSMS or t-SQL.

我们可以执行计划中的手动故障，以将辅助过渡副本复制到主副本。 我们可以使用SSMS或t-SQL执行计划的故障转移。

• Both the primary and secondary replica should be running in synchronous data commit mode
  主副本和辅助副本都应在同步数据提交模式下运行
• Synchronized 同步

We can check whether the database is ready for manual failover using the is_failover_ready column of sys.dm_hadr_database_replica_cluster_states DMV.

我们可以使用sys.dm_hadr_database_replica_cluster_states DMV的is_failover_ready列检查数据库是否已准备好进行手动故障转移。

select replica_id ,database_name ,is_failover_ready from sys.dm_hadr_database_replica_cluster_states

In the following screenshot, we can see the bottom two rows are showing failover ready. It is because we have two replicas in synchronous data commit mode. We have another DR secondary replica in asynchronous data commit mode; therefore, it does not show is_failover_ready value as one for this replica. We should initiate a planned manual failover from the secondary replica.

在下面的屏幕截图中，我们可以看到下面两行显示故障转移已准备就绪。 这是因为我们在同步数据提交模式下有两个副本。 我们还有另一个处于异步数据提交模式的DR辅助副本； 因此，此副本不会将is_failover_ready值显示为1。 我们应该从辅助副本启动计划的手动故障转移。

手动计划的故障转移操作 (Manual planned failover actions)

• Once a user initiates manual planned failover secondary replica database roll forward pending logs and bring it online
  用户启动手动计划的故障转移辅助副本数据库后，前滚未决日志并将其联机
• It also rolls back any uncommitted transactions to keep the database in a consistent state
  它还回滚所有未提交的事务，以使数据库保持一致状态
• Secondary replica takes the role of a new Primary replica and starts to synchronize with the current secondary replica (old primary replica)
  辅助副本充当新的主副本的角色，并开始与当前的辅助副本（旧的主副本）同步
• The database status remains NOT SYNCHRONIZING until synchronization happens between primary and secondary replica
• 在主副本和辅助副本之间发生同步之前，数据库状态保持为NOT SYNCHRONIZING
• Synchronized 同步

强制进行手动故障转移并丢失数据 (Force Manual failover with data loss)

We can do a manual failover to any secondary replica including asynchronous data commit replica. Usually, we should use forced failover for disaster recovery purpose only. Once we initiate a forced failover, the secondary replica takes the role of the new primary replica. In this case, data synchronization does not start automatically. It remains in the suspended state. We need to resume it manually. You might have data loss in case of forced manual failover.

我们可以手动故障转移到任何辅助副本，包括异步数据提交副本。 通常，我们应仅将强制故障转移用于灾难恢复目的。 一旦我们启动了强制故障转移，辅助副本将充当新的主副本的角色。 在这种情况下，数据同步不会自动开始。 它保持挂起状态。 我们需要手动恢复它。 如果强制进行手动故障转移，则可能会丢失数据。

In the following image, you can notice the following

在下图中，您可以注意到以下内容

• Secondary replica for synchronous data commit is down
  用于同步数据提交的辅助副本已关闭
• Due to some issues, the primary replica also goes down. We have now only DR replica available is for configured for asynchronous data commit
  由于某些问题，主副本也会关闭。 现在，只有可用的DR副本用于配置异步数据提交
• Due to asynchronous data commit, Last_hardended_LSN value is also different on both replicas
  由于异步数据提交，两个副本上的Last_hardended_LSN值也不同

Once we initiate forced failover, the secondary replica takes over the role as Primary replica. In this new primary replica, we have Last_hardended_LSN 90. Once we bring old primary replica online, both primary and secondary replica communicates with each other. Since the Last_hardended_LSN value

一旦我们启动了强制故障转移，辅助副本就将充当主副本。 在这个新的主副本中，我们具有Last_hardended_LSN90 。一旦使旧的主副本联机，主副本和辅助副本便会相互通信。 由于Last_hardended_LSN值

Once the old primary replica is brought online, it shows its synchronization as suspended is 90 on the primary replica, secondary replica (Last_hardended_LSN – 150) rolls back its transaction to LSN 90 and start the synchronization process. It caused data loss for forced manual failover.

一旦旧的主副本上线，它将显示其同步在主副本上处于暂停状态90，辅助副本（ Last_hardended_LSN – 150）将其事务回滚到LSN 90并开始同步过程。 强制手动故障转移导致数据丢失。

结论 (Conclusion)

In this article, we explored scenario related to data resynchronization in SQL Server Always on Availability Groups. I will cover more materia related to SQL availability group in the upcoming articles. If you had comments or questions, feel free to leave them in the comments below

在本文中，我们探讨了与SQL Server Always on可用性组中的数据重新同步有关的方案。 在接下来的文章中，我将介绍与SQL可用性组有关的更多材料。 如果您有任何意见或问题，请随时将其留在下面的评论中

翻译自: https://www.sqlshack.com/sql-server-always-on-availability-group-data-resynchronization/