带直接种子SQL Server 2016 Always On可用性组

SQL Server Always On Availability Groups are an enterprise-level high-availability and disaster-recovery feature introduced the first time in SQL Server 2012as an alternative to database mirroring. A set of user databases that fail over together forms the availability group. These availability databases are hosted by the availability replicas and can be readable- writable at the primary replica and up to eight sets of secondary replica databases that can be configured to be read-only databases. The availability groups fail over due to the availability replica’s level issues and not the ones caused due to database level issues such as data loss or database corruption.

SQL Server Always On可用性组是一项企业级的高可用性和灾难恢复功能，是SQL Server 2012年首次引入的，它是数据库镜像的替代方法。 一起故障转移的一组用户数据库构成可用性组。 这些可用性数据库由可用性副本托管，并且在主副本和最多八组辅助副本数据库上可读写，这些辅助副本数据库可以配置为只读数据库。 可用性组的故障转移是由于可用性副本的级别问题引起的，而不是由于数据库级别的问题（例如数据丢失或数据库损坏）引起的。

Always On Availability Group replicas can work in two availability modes;Asynchronous-commit mode in which the primary replica will not wait for the secondary replica’s acknowledgement that the log is hardened completely, opposite to Synchronous-commit mode in which the primary replica waits for the secondary replica to acknowledge the log hardening completion, increasing the transaction latency.

始终可用的可用性组副本可以在两种可用性模式下工作： 异步提交模式，在该模式下主副本将不等待辅助副本的日志已完全硬化，这与同步提交模式在同步模式下，主副本将等待辅助副本以确认日志加固完成，从而增加了事务潜伏。

An Always On Availability Group is built over Windows Server Failover Clustering (WSFC) including all replicas of the same availability group under the same cluster. An Always On Availability Group differs from database mirroring feature in that there is no witness role in Always On Availability Groups. On the other hand, you can bring the secondary replica databases online for read-only access in Always On Availability Groups in contrast to database mirroring, in which the secondary database is not accessible in restoring mode.

Always On可用性组是通过Windows Server故障转移群集（WSFC）构建的，包括同一群集下同一可用性组的所有副本。 Always On可用性组与数据库镜像功能的不同之处在于Always On可用性组中没有见证人角色。 另一方面，与数据库镜像（在还原模式下不能访问辅助数据库）相反，您可以使辅助副本数据库联机以在Always On可用性组中进行只读访问。

In SQL Server 2012 and 2014, initializing the secondary replica requires taking full and transaction log backups of the primary database to a network share and restore it to the secondary replicas. This operation can be performed manually or automatically via the availability group wizard after configuring the network share and the required permissions on that network share to the account that the SQL Server service runs with. Once the log backup file is restored to the secondary replicas, the data synchronization process, in which all changes applied on the primary replica will be applied to the secondary replica, will start until all replicas become synchronized.

在SQL Server 2012和2014中，初始化辅助副本需要将主数据库的完整日志和事务日志备份备份到网络共享，然后将其还原到辅助副本。 配置网络共享以及该网络共享对运行SQL Server服务的帐户的必需权限之后，可以手动或通过可用性组向导自动执行此操作。 一旦将日志备份文件还原到辅助副本，则数据同步过程将开始，直到对所有副本进行同步为止，在该数据同步过程中，对主副本应用的所有更改都将应用于辅助副本。

Starting with SQL Server 2016, a new simple initialization method introduced, in which the databases in the secondary replica are initialized automatically by enabling Direct Seeding, reducing the amount of work required by the database administrator to add databases to the secondary replicas. Using direct seeding, you need to add the database to the availability group only, without the need to configure the network share or performing backup or restore operations, reducing the time required to initialize the secondary replica dramatically. Internally, a Microsoft SQL Server Virtual Device Interface (VDI) backup is performed to the secondary replica over the network. Using the direct seeding method, there is no need to create a dummy database before creating the availability group to proceed with the creation process.

从SQL Server 2016开始，引入了一种新的简单初始化方法，其中通过启用直接播种自动初始化辅助副本中的数据库，从而减少了数据库管理员将数据库添加到辅助副本所需的工作量。 使用直接播种，您仅需要将数据库添加到可用性组，而无需配置网络共享或执行备份或还原操作，从而大大减少了初始化辅助副本所需的时间。 在内部，通过网络对辅助副本执行Microsoft SQL Server虚拟设备接口（VDI）备份。 使用直接播种方法，在创建可用性组以继续创建过程之前，无需创建虚拟数据库。

A new parameter, SEEDING_MODE, is added to the CREATE and ALTER AVAILABILITY GROUP T-SQL statements to configure and enable the direct seeding per each replica. SEEDING_MODE takes two values; Manual seeding indicating that the old backup and restore secondary initialization method will be used, which is the default seeding method, and Automatic seeding which enables using the new automated secondary initialization method, that is the direct seeding method across the replication network. The SEEDING_MODE option is a replica level configuration, if you have multiple databases in your availability group, this setting will be applied to all databases in the availability group. It is also applicable to use mixed seeding within the availability group, by using automatic seeding for some replicas and use the legacy seeding with backup and restore for the other replicas. Until this moment, configuring the seeding mode to initialize the secondary replica is available using the T-SQL command only and not available via the wizard.

将新参数SEEDING_MODE添加到CREATE和ALTER AVAILABILITY GROUP T-SQL语句中，以配置和启用每个副本的直接播种。 SEEDING_MODE采用两个值； 手动播种表示将使用旧的备份和还原辅助初始化方法（这是默认的播种方法）， 自动播种指示使用新的自动辅助初始化方法（即跨复制网络的直接播种方法）。 SEEDING_MODE选项是副本级别的配置，如果可用性组中有多个数据库，则此设置将应用于可用性组中的所有数据库。 通过对某些副本使用自动种子设置，并在其他副本中使用带有备份和还原功能的旧种子，也可以在可用性组内使用混合种子设置。 在此之前，仅可使用T-SQL命令配置播种模式以初始化辅助副本，而不能通过向导使用。

The Direct seeding method is not and technique for initializing the secondary replica if the database is very large, as it doesn’t use compression as a default setting, in contrast to the availability group wizard in which the backup operation can be performed using compression. Fortunately, compression can be enabled in direct seeding to reduce the network traffic while seeding by enabling the Trace Flag 9567 using DBCC TRACEON or as a startup parameter. The side effect for adding a large database using the direct seeding with compression enabled is the high CPU utilization that you will notice during the seeding.

如果数据库很大，则直接播种方法不是用于初始化辅助副本的方法，也不是用于初始化辅助副本的技术，因为它不使用压缩作为默认设置，而可用性组向导使用压缩来执行备份操作。 幸运的是，通过使用DBCC TRACEON或作为启动参数启用跟踪标志9567，可以在直接播种中启用压缩以减少播种时的网络流量。 使用启用了压缩功能的直接播种来添加大型数据库的副作用是播种期间会注意到较高的CPU使用率。

Another thing that we should take into consideration when using the direct seeding method to initialize the secondary replica is that the transaction log of the added databases can’t be truncated during the direct seeding process, which will cause significant transaction log growth in high transactional databases. So, be aware of the workload amount, database size, the log growth rate and the replication distance that affect the seeding time.

使用直接播种方法初始化辅助副本时，我们还应考虑的另一件事是，在直接播种过程中不能截断所添加数据库的事务日志，这将导致高事务数据库中的事务日志显着增长。 因此，请注意影响播种时间的工作量，数据库大小，日志增长率和复制距离。

A set of extended events introduced in SQL Server 2016 to monitor the direct seeding process both at the primary and secondary replicas such as hadr_physical_seeding_backup_state_change, hadr_physical_seeding_restore_state_change, hadr_physical_seeding_forwarder_state_change, hadr_physical_seeding_failure, hadr_physical_seeding_progress, hadr_automatic_seeding_start, hadr_automatic_seeding_success and hadr_automatic_seeding_timeout valuable events. You can collect the extended events that monitor the backup process from the primary replica and the ones related to the restore process from the secondary replica. As per Microsoft’s advice, you should be careful when collecting the extended events, as some events may impact SQL Server performance.

SQL Server 2016中引入的一组扩展事件，以监视主要副本和辅助副本上的直接种子过程，例如hadr_physical_seeding_backup_state_change，hadr_physical_seeding_restore_state_change，hadr_physical_seeding_forwarder_state_change，hadr_physical_seeding_failure，hadr_autocal_seed_matic_see_matic_see_time_start_cess_auto_gress_proc__ 您可以收集扩展事件，以监视主副本中的备份过程以及与辅助副本中的还原过程相关的事件。 根据Microsoft的建议，收集扩展事件时应格外小心，因为某些事件可能会影响SQL Server性能。

Direct seeding can be also monitored by querying the two new DMVs that are introduced in SQL Server 2016 both at the primary and secondary replicas to troubleshoot any failure occurred. The first DMV sys.dm_hadr_automatic_seeding is used to view successful or failed database seeding information and the error messages displaying the seeding failure reason. The second DMV sys.dm_hadr_physical_seeding_stats displays statistical information about the currently running and completed seeding.

还可以通过在主副本和辅助副本上查询SQL Server 2016中引入的两个新DMV来监视直接种子，以对发生的任何故障进行故障排除。 第一个DMV sys.dm_hadr_automatic_seeding用于查看成功或失败的数据库种子信息以及显示种子失败原因的错误消息。 第二个DMV sys.dm_hadr_physical_seeding_stats显示有关当前正在运行和已完成播种的统计信息。

Let’s start our demo to understand direct seeding practically. As mentioned previously, direct seeding is available only using T-SQL commands. We will create a new availability group with two replicas, without creating a dummy database during the creation process, using CREATE AVAILABILITY GROUP T-SQL command. What is new here is the additional SEEDING_MODE option which we will set it to AUTOMATIC in order to enable the direct seeding. The below script will be executed at the primary replica:

让我们开始演示，以实际了解直接播种。 如前所述，仅可以使用T-SQL命令进行直接播种。 我们将使用CREATE AVAILABILITY GROUP T-SQL命令创建一个具有两个副本的新可用性组，而无需在创建过程中创建虚拟数据库。 这里的新功能是附加的SEEDING_MODE选项，我们将其设置为AUTOMATIC以便启用直接播种。 以下脚本将在主副本上执行：

 
USE master
GO
CREATE AVAILABILITY GROUP AG40VS  
FOR  
REPLICA ON
N'DB41VS'
WITH (ENDPOINT_URL = N'TCP:// DB41VS.TestDomain.COM:5022',  
    FAILOVER_MODE = AUTOMATIC,  
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,   
    BACKUP_PRIORITY = 50,   
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),   
    SEEDING_MODE = AUTOMATIC)
,   
N'DB42VS'
WITH (ENDPOINT_URL = N'TCP:// DB42VS.TestDomain.COM:5022',   
    FAILOVER_MODE = AUTOMATIC,   
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,   
    BACKUP_PRIORITY = 50,   
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),   
    SEEDING_MODE = AUTOMATIC);   
GO
 

The below snapshot from SQL Server Management Studio shows the created availability group with the two replicas; the primary and the secondary:

下面SQL Server Management Studio快照显示了创建的具有两个副本的可用性组。 主要和次要：

A new field is added to the sys.availability_replicas system table to list the seeding mode for all replicas, that can be joined with sys.availability_groups to include the availability groups information as in the below script:

向sys.availability_replicas系统表中添加了一个新字段，以列出所有副本的种子模式，可以将其与sys.availability_groups结合以包括可用性组信息，如以下脚本所示：

 
SELECT
    AVGrp.name as group_name,
    AVGRep.replica_server_name as replica_name,
    AVGRep.endpoint_url,
    AVGRep.availability_mode_desc,
    AVGRep.failover_mode_desc,
    AVGRep.seeding_mode_desc as seeding_mode
FROM sys.availability_replicas as AVGRep
JOIN sys.availability_groups as AVGrp
    ON AVGRep.group_id = AVGrp.group_id;
 

The following result shows that the seeding mode used in our case is AUTOMATIC seeding mode, which means that we are using the direct seeding method in our demo as configured previously:

以下结果表明，在我们的案例中使用的播种模式是AUTOMATIC播种模式，这意味着我们将在演示中使用直接播种方法，如先前配置的那样：

Now we will join the secondary replica to the availability group using ALTER AVAILABILITY GROUP statement and grant the availability group access to create database, so it will create the database at the secondary replica directly and seed it automatically once created:

现在，我们将使用ALTER AVAILABILITY GROUP语句将辅助副本加入可用性组，并授予可用性组访问权限以创建数据库，因此它将直接在辅助副本上创建数据库，并在创建后自动将其作为种子：

 
ALTER AVAILABILITY GROUP AG40VS JOIN  
ER AVAILABILITY GROUP AG40VS GRANT CREATE ANY DATABASE;
GO 
 

We will create a testing database at the primary replica to add it later to the availability group:

我们将在主副本上创建一个测试数据库，以稍后将其添加到可用性组：

 
CREATE DATABASE [DirectSeedTest]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DirectSeedTest', FILENAME = N'F:\Microsoft SQL 
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DirectSeedTest.mdf' , SIZE = 4096KB , FILEGROWTH = 
1024KB )
 LOG ON 
( NAME = N'DirectSeedTest_log', FILENAME = N'F:\Microsoft SQL 
Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DirectSeedTest_log.ldf' , SIZE = 2048KB , 
FILEGROWTH = 10%)
GO 
 

And take a dummy full backup for that database to meet the Always On Availability Group pre-requisites:

并对该数据库进行虚拟完全备份，以满足“始终在线可用性组”的先决条件：

 
BACKUP DATABASE [DirectSeedTest] TO  DISK = N'F:\Microsoft SQL 
Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\DirectSeedTest.bak' WITH NOFORMAT, NOINIT,  NAME 
= N'DirectSeedTest-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO  
 

At the primary side, the testing database will be displayed without any addition like any normal user database:

在主要方面，将显示测试数据库，而无需像任何普通用户数据库那样添加任何内容：

Let’s add the database to the availability group using ALTER AVAILABILITY GROUP T-SQL command below:

让我们使用下面的ALTER AVAILABILITY GROUP T-SQL命令将数据库添加到可用性组：

 
ALTER AVAILABILITY GROUP AG40VS ADD DATABASE [DirectSeedTest];
GO
 

That’s it!

而已！

Now the database will be added to the availability group at the primary replica, the database will be created at the secondary replica and will be seeded directly and automatically without any effort from the database administrator to take full or transactional log backup to synchronize the two replicas. If we check the primary replica, the database will be synchronized as in the following image:

现在，数据库将被添加到主副本上的可用性组中，该数据库将在辅助副本上创建，并且将直接直接自动播种，而无需数据库管理员进行全部或事务日志备份来同步两个副本。 如果我们检查主副本，数据库将如下图所示同步：

The one-line command to add the database to the availability group will synchronize it at the secondary replica too as below:

将数据库添加到可用性组的单行命令也将在辅助副本上对其进行同步，如下所示：

As mentioned previously, two new DMVs were introduced in SQL Server 2016 to monitor the direct seeding method. Querying the sys.dm_hadr_automatic_seeding DMV:

如前所述，SQL Server 2016中引入了两个新的DMV来监视直接播种方法。 查询sys.dm_hadr_automatic_seeding DMV：

 
select start_time ,completion_time , current_state ,performed_seeding , 
failure_state_desc,error_code , number_of_attempts   from sys.dm_hadr_automatic_seeding  
 

the result will show us all successful and failed database seeding information with clear error messages displaying the seeding failure reason:

结果将向我们显示所有成功和失败的数据库种子信息，并显示清除种子失败原因的清晰错误消息：

Also we can use the sys.dm_hadr_physical_seeding_stats DMV:

我们也可以使用sys.dm_hadr_physical_seeding_stats DMV：

 
select local_database_name , remote_machine_name,role_desc ,internal_state_desc 
,transfer_rate_bytes_per_second ,transferred_size_bytes 
,database_size_bytes,estimate_time_complete_utc ,failure_message ,failure_time_utc, 
is_compression_enabled     from sys.dm_hadr_physical_seeding_stats
 

To show the statistical information about the running seeding processes in addition to the completed ones as below:

除了显示已完成的播种过程外，还显示有关正在运行的播种过程的统计信息：

The SQL Server error logs can be used also to trace and troubleshoot the direct seeding process. You need to check the backup process logs at the primary replica:

SQL Server错误日志还可以用于跟踪直接播种过程并进行故障排除。 您需要检查主副本上的备份过程日志：

And the restore process logs at the secondary replica:

还原过程将记录在辅助副本上：

It is clear from the previous logs that the direct seeding process is completed successfully; started at 12:34:53 and finished at 12:34:57, taking 4 seconds to seed the secondary replica completely.

从先前的日志中可以明显看出，直接播种过程已成功完成； 开始时间为12:34:53，结束时间为12:34:57，这需要4秒才能完全植入辅助副本。

结论 (Conclusion )

SQL Server 2016 introduced many new features and enhancements that make it the fastest SQL Server version to date. One of these enhancements is the Always On Availability Groups direct seeding option, that eases the secondary initialization and seeding process saving time and effort. Rather than taking full and transaction log backups to initialize and synchronize the secondary replica, just set the seeding mode to automatic and add the database to the availability group at the primary replica, it will be created and synchronized at the secondary replica directly without any extra effort. Direct seeding does not work well with huge databases due to the fact that the compression option is disabled by default in the direct seeding. If you can tolerate the high CPU utilization, you can enable it. Direct seeding also blocks the log truncation during the seeding process, that may fill the log file of the database with huge number of transactions. Like any new feature, test it in your development environment before using it at the production environment.

SQL Server 2016引入了许多新功能和增强功能，使其成为迄今为止最快SQL Server版本。 这些增强功能之一是Always On Availability Groups直接播种选项，它简化了辅助初始化和播种过程，从而节省了时间和精力。 与其进行完整和事务日志备份来初始化和同步辅助副本，不如将播种模式设置为自动并将数据库添加到主副本的可用性组中，它将直接在辅助副本上创建和同步，而无需任何额外操作努力。 直接播种不适用于大型数据库，因为在直接播种中默认情况下禁用了压缩选项。 如果可以忍受高CPU使用率，则可以启用它。 直接播种还会在播种过程中阻止日志截断，这可能会使数据库的日志文件充满大量事务。 与任何新功能一样，在生产环境中使用它之前，请在开发环境中对其进行测试。

翻译自: https://www.sqlshack.com/sql-server-2016-always-availability-group-direct-seeding/