将事务日志应用于SQL Server Always On可用性组中的辅助副本

介绍 (Introduction)

SQL Server Always On Availability Groups provides HADR solutions for the SQL databases. Here HA refers to high availability and DR refers to disaster recovery. The priority of this feature is to keep the database highly available and then provide Disaster recovery. Due to this reason, if the secondary replica goes down in a synchronous data commit mode, SQL Server changes commit mode to Asynchronous so that users can continue run the transactions and a secondary replica can be in sync later once it bought up. SQL Listener also points to the primary replica and continues redirects connection to the primary replica.

SQL Server Always On可用性组为SQL数据库提供了HADR解决方案。 在这里，HA表示高可用性，DR表示灾难恢复。 此功能的优先级是保持数据库高可用性，然后提供灾难恢复。 由于这个原因，如果辅助副本在同步数据提交模式下关闭，则SQL Server会将提交模式更改为“异步”，以便用户可以继续运行事务，并且一旦购买后，辅助副本就可以同步。 SQL侦听器还指向主副本，并继续将连接重定向到主副本。

Suppose for a two-node synchronous data commit mode availability group, Secondary replica is down due to some hardware or power failure issues, and it might take longer for the respective team to fix it.

假设对于一个两节点同步数据提交模式可用性组，由于某些硬件或电源故障问题，辅助副本已关闭，各个团队可能需要更长的时间来修复它。

However, users will continue to use the primary replica database, but SQL Server holds the transaction log records on the primary replica. It cannot clear the transaction log despite the regular transaction log backups. It will cause transaction log growth, and we might face disk space-related issues if we do not have sufficient free space in the drive.

但是，用户将继续使用主副本数据库，但是SQL Server将事务日志记录保留在主副本上。 尽管有常规的事务日志备份，它仍无法清除事务日志。 这将导致事务日志增长，如果驱动器中没有足够的可用空间，我们可能会遇到与磁盘空间相关的问题。

In this case, it is good to remove the secondary replica (unhealthy) from the SQL Server Always On Availability Group. Once we remove the unhealthy replica, it is not part of the AG group, and SQL Server does not need to hold the transaction log for late usage.

在这种情况下，最好从SQL Server Always On可用性组中删除辅助副本（不正常）。 删除不正常的副本后，它就不再属于AG组，并且SQL Server无需保留事务日志以备后用。

删除一个SQL Server常开可用性组 (Remove a SQL Server Always On Availability Group)

To remove an availability group, connect to primary replica and go to Availability Replicas. Right-click on the replica we want to remove and click on Remove from Availability Group.

要删除可用性组，请连接到主副本并转到“ 可用性副本”。 右键单击我们要删除的副本，然后单击“ 从可用性组删除”。

Alternatively, you can execute the following query on the primary replica in SQL Server Always On Availability Group.

或者，您可以对SQL Server Always On可用性组中的主副本执行以下查询。

ALTER AVAILABILITY GROUP [TestAG] REMOVE REPLICA ON N'SQL2017Test';
GO

Once you remove a replica from the availability replicas, all database status changes to Not Synchronizing.

从可用性副本中删除副本后，所有数据库状态将更改为“不同步”。

Once the secondary database instance becomes available, connect to the secondary replica, you can drop an availability group.

一旦辅助数据库实例变得可用，连接到辅助副本，您可以删除可用性组。

DROP AVAILABILITY GROUP [TestAG];

It changes the database status to Restoring mode.

它将数据库状态更改为“还原”模式。

将数据库重新添加到“ SQL Server始终在线”可用性组中