sql server 复制_SQL Server复制（合并）–复制架构更改中的性能问题

sql server 复制

This article will review performance issues in replicating schema changes on tables involved in SQL Server Replication (Merge).

本文将回顾在SQL Server复制（合并）中涉及的表上复制架构更改时的性能问题。

We had SQL Server merge replication configured on one of our databases to replicate data from publisher to subscriber and vice versa. Replicate schema changes was enabled on the publication to send the DDL changes made on replicated tables at publishers to subscribers.

我们在一个数据库上配置了SQL Server合并复制，以将数据从发布者复制到订阅者，反之亦然。 在发布上启用了复制架构更改，以将在发布服务器上对复制表所做的DDL更改发送给订阅服务器。

When we applied the deployment scripts which modify the existing replicating tables by adding new columns or dropping existing columns at publisher, these schema changes took a lot of time to replicate to subscribers. Let us see what happens internally when there is a schema change on the table involved in SQL Server merge replication and why it is taking time to replicate schema changes.

当我们应用通过在发布服务器上添加新列或删除现有列来修改现有复制表的部署脚本时，这些架构更改需要大量时间才能复制到订阅服务器。 让我们看看当SQL Server合并复制所涉及的表上发生架构更改时内部会发生什么以及为什么要花时间来复制架构更改。

To illustrate this let us create a merge publication with replicate schema changes option enabled.

为了说明这一点，让我们创建一个启用了复制模式更改选项的合并发布。

Below are the server and database details in my environment. Please change the T-SQL scripts by replacing your publisher server, database and subscriber server, database names.

以下是我的环境中的服务器和数据库详细信息。 请通过替换发布服务器，数据库和订阅服务器，数据库名称来更改T-SQL脚本。

• Publisher Server: PUBSERV01
  发布服务器：PUBSERV01
• Publication Database: test_pub
  发布数据库：test_pub
• Subscriber Server: SUBSERV01
  订阅服务器：SUBSERV01
• Subscriber Database: test_sub1
  订户数据库：test_sub1

Please follow below steps to create table, publication, adding table to publication and creating subscribers using T-SQL scripts.

请按照以下步骤创建表，发布，将表添加到发布以及使用T-SQL脚本创建订阅服务器。

配置SQL Server合并复制 (Configuring SQL Server Merge Replication)

Create table “schema_test” in publisher database and enable database for SQL Server Replication (Merge).

在发布者数据库中创建表“ schema_test”，并为数据库启用SQL Server复制（合并）。

use [test_pub]
CREATE TABLE schema_test (ID INT )
 
use master
exec sp_replicationdboption @dbname = N'test_pub', @optname = N'merge publish', @value = N'true'
GO

Now let us configure merge publication using T-SQL script. Execute sp_addmergepublication at publisher on database that is being published. Replace the snapshot folder as per your need.

现在，让我们使用T-SQL脚本配置合并发布。 在正在发布的数据库上的发布服务器上执行sp_addmergepublication。 根据需要更换快照文件夹。

USE [test_pub]
 
EXEC sp_addmergepublication @publication = N'test'
	,@description = N'Merge publication of database ''test_pub''.'
	,@sync_mode = N'native'
	,@retention = 14
	,@allow_push = N'true'
	,@allow_pull = N'true'
	,@allow_anonymous = N'true'
	,@enabled_for_internet = N'false'
	,@snapshot_in_defaultfolder = N'false'
	,@alt_snapshot_folder = N'D:\Replication\ReplData'
	,@compress_snapshot = N'false'
	,@ftp_port = 21
	,@ftp_subdirectory = N'ftp'
	,@ftp_login = N'anonymous'
	,@allow_subscription_copy = N'false'
	,@add_to_active_directory = N'false'
	,@dynamic_filters = N'false'
	,@conflict_retention = 14
	,@keep_partition_changes = N'false'
	,@allow_synctoalternate = N'false'
	,@max_concurrent_merge = 0
	,@max_concurrent_dynamic_snapshots = 0
	,@use_partition_groups = N'false'
	,@publication_compatibility_level = N'100RTM'
	,@replicate_ddl = 1
	,@allow_subscriber_initiated_snapshot = N'false'
	,@allow_web_synchronization = N'false'
	,@allow_partition_realignment = N'true'
	,@retention_period_unit = N'days'
	,@conflict_logging = N'both'
	,@automatic_reinitialization_policy = 0

Once the publication is created, Navigate to the Local Publications under the replication folder in SQL Server Management Studio.