如何用SQL脚本在SQL Server Replication中创建合并复制，以及怎么创建分区合并复制

假设我们要创建合并复制的发布端数据库是EFDemo其中有四张表，订阅端数据库是EFDemoSubscription，如下图所示：

首先创建发布端快照代理Sql agent job:"EFDemoMergePublicationJob":

USE [msdb]
GO

/****** Object:  Job [EFDemoMergePublicationJob]    Script Date: 11/29/2018 12:59:12 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [REPL-Snapshot]    Script Date: 11/29/2018 12:59:12 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'REPL-Snapshot' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'REPL-Snapshot'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'EFDemoMergePublicationJob', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'REPL-Snapshot', 
        @owner_login_name=N'domainscott', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Snapshot Agent startup message.]    Script Date: 11/29/2018 12:59:12 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Snapshot Agent startup message.', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=3, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'sp_MSadd_snapshot_history @perfmon_increment = 0,  @agent_id = 4, @runstatus = 1,  
                    @comments = N''Starting agent.''', 
        @server=N'DB_Server_001', 
        @database_name=N'distribution', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Run agent.]    Script Date: 11/29/2018 12:59:12 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run agent.', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=3, 
        @on_fail_step_id=0, 
        @retry_attempts=10, 
        @retry_interval=1, 
        @os_run_priority=0, @subsystem=N'Snapshot', 
        @command=N'-Publisher [DB_Server_001] -PublisherDB [EFDemo] -Distributor [DB_Server_001] -Publication [EFDemoMerge] -ReplicationType 2 -DistributorSecurityMode 1 ', 
        @server=N'DB_Server_001', 
        @database_name=N'distribution', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Detect nonlogged agent shutdown.]    Script Date: 11/29/2018 12:59:12 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Detect nonlogged agent shutdown.', 
        @step_id=3, 
        @cmdexec_success_code=0, 
        @on_success_action=2, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'sp_MSdetect_nonlogged_shutdown @subsystem = ''Snapshot'', @agent_id = 4', 
        @server=N'DB_Server_001', 
        @database_name=N'distribution', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Replication agent schedule.', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=14, 
        @freq_subday_type=1, 
        @freq_subday_interval=5, 
        @freq_relative_interval=1, 
        @freq_recurrence_factor=0, 
        @active_start_date=20181129, 
        @active_end_date=99991231, 
        @active_start_time=500, 
        @active_end_time=235959, 
        @schedule_uid=N'561c3b92-52c3-4661-b3c3-916807f2db2a'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

然后创建订阅端数据同步Sql agent job:"EFDemoMergeSubscriptionJob":

USE [msdb]
GO

/****** Object:  Job [EFDemoMergeSubscriptionJob]    Script Date: 11/29/2018 12:57:11 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [REPL-Merge]    Script Date: 11/29/2018 12:57:11 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'REPL-Merge' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'REPL-Merge'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'EFDemoMergeSubscriptionJob', 
        @enabled=1, 
        @notify_level_eventlog=2, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'REPL-Merge', 
        @owner_login_name=N'domainscott', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Merge Agent startup message.]    Script Date: 11/29/2018 12:57:11 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Merge Agent startup message.', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=3, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'sp_MSadd_merge_history @perfmon_increment = 0, @agent_id = 4, @runstatus = 1,  
                    @comments = N''Starting agent.''', 
        @server=N'DB_Server_001', 
        @database_name=N'distribution', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Run agent.]    Script Date: 11/29/2018 12:57:11 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run agent.', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=3, 
        @on_fail_step_id=0, 
        @retry_attempts=10, 
        @retry_interval=1, 
        @os_run_priority=0, @subsystem=N'Merge', 
        @command=N'-Publisher [DB_Server_001] -PublisherDB [EFDemo] -Publication [EFDemoMerge] -Subscriber [DB_Server_001] -SubscriberDB [EFDemoSubscription]   -Distributor [DB_Server_001] -DistributorSecurityMode 1 ', 
        @server=N'DB_Server_001', 
        @database_name=N'distribution', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Detect nonlogged agent shutdown.]    Script Date: 11/29/2018 12:57:11 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Detect nonlogged agent shutdown.', 
        @step_id=3, 
        @cmdexec_success_code=0, 
        @on_success_action=2, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'sp_MSdetect_nonlogged_shutdown @subsystem = ''Merge'', @agent_id = 4', 
        @server=N'DB_Server_001', 
        @database_name=N'distribution', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Replication agent schedule.', 
        @enabled=0, 
        @freq_type=1, 
        @freq_interval=0, 
        @freq_subday_type=0, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20181129, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959, 
        @schedule_uid=N'ad0c8cc4-c11e-4b9e-8f23-234f37e29925'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

然后就可以开始创建合并复制了：

-- 添加合并复制功能（合并复制自动创建的系统表）到发布端数据库EFDemo
use master
exec sp_replicationdboption @dbname = N'EFDemo', @optname = N'merge publish', @value = N'true'
GO

-- 添加合并复制EFDemoMerge，即发布端
use [EFDemo]
exec sp_addmergepublication @publication = N'EFDemoMerge', @description = N'Merge publication of database ''EFDemo'' from Publisher ''DB_Server_001''.', @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'true', @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
GO

-- 将前面创建的发布端快照代理Sql agent job:"EFDemoMergePublicationJob"分配给合并复制的发布端
-- 注意@publisher_login , @publisher_password 两个参数是发布端数据库的管理员用户名和密码
exec sp_addpublication_snapshot @snapshot_job_name=N'EFDemoMergePublicationJob', @publication = N'EFDemoMerge', @frequency_type = 4, @frequency_interval = 14, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 1, @frequency_subday_interval = 5, @active_start_time_of_day = 500, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 0, @publisher_login = N'Distribution', @publisher_password = N'1qaz!QAZ'
exec sp_grant_publication_access @publication = N'EFDemoMerge', @login = N'sa'
GO

-- 添加可以访问合并复制EFDemoMerge的SQL Server用户
exec sp_grant_publication_access @publication = N'EFDemoMerge', @login = N'domainscott'
GO
exec sp_grant_publication_access @publication = N'EFDemoMerge', @login = N'Distribution'
GO

-- 添加合并复制EFDemoMerge的Article（选取数据库EFDemo中要复制的表、存储过程等，当运行发布端快照代理Sql agent job:"EFDemoMergePublicationJob"后，合并复制会在下面选取的表中创建触发器、约束、RowGuid列等）
use [EFDemo]
exec sp_addmergearticle @publication = N'EFDemoMerge', @article = N'Book', @source_owner = N'dbo', @source_object = N'Book', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000010C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 10000, @identity_range = 1000, @threshold = 80, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'true', @partition_options = 0
GO
use [EFDemo]
exec sp_addmergearticle @publication = N'EFDemoMerge', @article = N'Cars', @source_owner = N'MD', @source_object = N'Cars', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000010C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 10000, @identity_range = 1000, @threshold = 80, @destination_owner = N'MD', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'true', @partition_options = 0
GO
use [EFDemo]
exec sp_addmergearticle @publication = N'EFDemoMerge', @article = N'Person', @source_owner = N'dbo', @source_object = N'Person', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000010C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 10000, @identity_range = 1000, @threshold = 80, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'true', @partition_options = 0
GO
use [EFDemo]
exec sp_addmergearticle @publication = N'EFDemoMerge', @article = N'Product', @source_owner = N'dbo', @source_object = N'Product', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000010C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'true', @partition_options = 0
GO

-- 添加合并复制的订阅端
use [EFDemo]
exec sp_addmergesubscription @publication = N'EFDemoMerge', @subscriber = N'DB_Server_001', @subscriber_db = N'EFDemoSubscription', @subscription_type = N'Push', @sync_type = N'Automatic', @subscriber_type = N'Global', @subscription_priority = 75, @description = N'', @use_interactive_resolver = N'False'
GO

-- 将前面创建的订阅端数据同步Sql agent job:"EFDemoMergeSubscriptionJob"分配给合并复制的订阅端
-- 注意@subscriber_login, @subscriber_password 两个参数是订阅端数据库的管理员用户名和密码
exec sp_addmergepushsubscription_agent @job_name=N'EFDemoMergeSubscriptionJob', @publication = N'EFDemoMerge', @subscriber = N'DB_Server_001', @subscriber_db = N'EFDemoSubscription', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'Distribution', @subscriber_password = N'1qaz!QAZ', @publisher_security_mode = 1, @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0
GO

这样我们就用SQL脚本创建了合并复制的发布端和订阅端。

也可以用下面的SQL脚本来删除合并复制的发布端和订阅端。

-- 用sp_dropmergesubscription，先删除合并复制的所有订阅端，注意这个存储过程并不会清除每个订阅端数据库中的合并复制对象（合并复制自动创建的系统表、触发器、约束、表中的RowGuid列等）
use [EFDemo]
exec sp_dropmergesubscription @publication = N'EFDemoMerge', @subscriber = N'DB_Server_001', @subscriber_db = N'EFDemoSubscription'
GO

-- 用sp_dropmergearticle，删除合并复制中发布端所有Article（发布端数据库中复制的所有表、存储过程等对象）
use [EFDemo]
exec sp_dropmergearticle @publication = N'EFDemoMerge', @article = N'Book', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO
use [EFDemo]
exec sp_dropmergearticle @publication = N'EFDemoMerge', @article = N'Cars', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO
use [EFDemo]
exec sp_dropmergearticle @publication = N'EFDemoMerge', @article = N'House', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO
use [EFDemo]
exec sp_dropmergearticle @publication = N'EFDemoMerge', @article = N'Person', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO
use [EFDemo]
exec sp_dropmergearticle @publication = N'EFDemoMerge', @article = N'Product', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO

-- 再用sp_dropmergepublication，删除合并复制的发布端，要先调用sp_dropmergesubscription将合并复制的订阅端删除完后，才能成功调用这个存储过程，否则会报错
use [EFDemo]
exec sp_dropmergepublication @publication = N'EFDemoMerge'
GO

-- 最后记得用sp_removedbreplication，来清除每个订阅端数据库中的合并复制对象（合并复制自动创建的系统表、触发器、约束、表中的RowGuid列等）
-- 很奇怪不知道为啥上面的sp_dropmergesubscription不会删除订阅端数据库中的合并复制对象
USE master
EXEC sp_removedbreplication @dbname='EFDemoSubscription'
GO

当然上面这些脚本也可以在SQL Server Management Studio中自动生成出来，不用自己去写。

使用SQL脚本创建带分区的合并复制

带分区的合并复制只能用存储过程来创建，首先通过如下存储过程，用发布端数据库EFDemo来创建合并复制EFDemoMerge：

-- 添加合并复制功能（合并复制自动创建的系统表）到发布端数据库EFDemo
use master
exec sp_replicationdboption @dbname = N'EFDemo', @optname = N'merge publish', @value = N'true'
GO

-- 添加合并复制EFDemoMerge，即发布端
use [EFDemo]
exec sp_addmergepublication 
@publication = N'EFDemoMerge', 
@description = N'Merge publication of database ''EFDemo'' from Publisher ''DB_Server_001''.', 
@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'true', 
@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'true', 
@conflict_retention = 14, 
@keep_partition_changes = N'true', 
@allow_synctoalternate = N'false', 
@max_concurrent_merge = 0, 
@max_concurrent_dynamic_snapshots = 0, 
@use_partition_groups = N'true', 
@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
GO

其中有四个参数黄色高亮标记了出来是开启分区合并复制的关键：

• @dynamic_filters = N'true'   分区合并复制必须使用动态函数（HOST_NAME()就是动态函数），所以要开启动态筛选
• @keep_partition_changes = N'true'   
• @use_partition_groups = N'true'   
• @allow_partition_realignment = N'true'

然后将发布端快照代理Sql agent job:"EFDemoMergePublicationJob"分配给合并复制的发布端，这个没什么特别的：

-- 将前面创建的发布端快照代理Sql agent job:"EFDemoMergePublicationJob"分配给合并复制的发布端
-- 注意@publisher_login , @publisher_password 两个参数是发布端数据库的管理员用户名和密码
exec sp_addpublication_snapshot @snapshot_job_name=N'EFDemoMergePublicationJob', @publication = N'EFDemoMerge', @frequency_type = 4, @frequency_interval = 14, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 1, @frequency_subday_interval = 5, @active_start_time_of_day = 500, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 0, @publisher_login = N'Distribution', @publisher_password = N'1qaz!QAZ'
exec sp_grant_publication_access @publication = N'EFDemoMerge', @login = N'sa'
GO

然后在添加合并复制的Article时，至少要有一个Article对象使用了带动态函数的Filter，下面的MD.Cars表上就使用了HOST_NAME()动态函数来作为Filter：

-- 添加合并复制EFDemoMerge的Article（选取数据库EFDemo中要复制的表、存储过程等，当运行发布端快照代理Sql agent job:"EFDemoMergePublicationJob"后，合并复制会在下面选取的表中创建触发器、约束、RowGuid列等）
use [EFDemo]
exec sp_addmergearticle 
@publication = N'EFDemoMerge', 
@article = N'Book', 
@source_owner = N'dbo', 
@source_object = N'Book', 
@type = N'table', 
@description = N'', 
@creation_script = N'', 
@pre_creation_cmd = N'drop', 
@schema_option = 0x000000010C034FD1, 
@identityrangemanagementoption = N'auto', 
@pub_identity_range = 10000, 
@identity_range = 1000, 
@threshold = 80, 
@destination_owner = N'dbo', 
@force_reinit_subscription = 1, 
@column_tracking = N'false', 
@subset_filterclause = N'', 
@vertical_partition = N'false', 
@verify_resolver_signature = 1, 
@allow_interactive_resolver = N'false', 
@fast_multicol_updateproc = N'true', 
@check_permissions = 0, 
@subscriber_upload_options = 0, 
@delete_tracking = N'true', 
@compensate_for_errors = N'false', 
@stream_blob_columns = N'true', 
@partition_options = 3
GO

use [EFDemo]
exec sp_addmergearticle 
@publication = N'EFDemoMerge', 
@article = N'Cars', 
@source_owner = N'MD', 
@source_object = N'Cars', 
@type = N'table', 
@description = N'', 
@creation_script = N'', 
@pre_creation_cmd = N'drop', 
@schema_option = 0x000000010C034FD1, 
@identityrangemanagementoption = N'auto', 
@pub_identity_range = 10000, 
@identity_range = 1000, 
@threshold = 80, 
@destination_owner = N'MD', 
@force_reinit_subscription = 1, 
@column_tracking = N'false', 
@subset_filterclause = N'[CarName] =HOST_NAME()', 
@vertical_partition = N'false', 
@verify_resolver_signature = 1, 
@allow_interactive_resolver = N'false', 
@fast_multicol_updateproc = N'true', 
@check_permissions = 0, 
@subscriber_upload_options = 0, 
@delete_tracking = N'true', 
@compensate_for_errors = N'false', 
@stream_blob_columns = N'true', 
@partition_options = 3
GO

可以看到上面我们添加了两张表dbo.Book和MD.Cars作为合并复制的Article，黄色高亮标记处显示，我们在MD.Cars表上使用了[CarName] =HOST_NAME()来作为Filter，其中使用了动态函数HOST_NAME()

最后运行发布端快照代理Sql agent job:"EFDemoMergePublicationJob"，然后我们可以看到在合并复制EFDemoMerge的属性中多了个选项叫"Data Partitions":

可以在这里添加分区，每个分区的HOST_NAME是分区的唯一标识符，也是动态函数HOST_NAME()的返回值：

当然分区还可以通过存储过程sp_addmergepartition来添加。

这里再附上几个关于分区合并复制的链接：

Snapshots for Merge Publications with Parameterized Filters

Parameterized Filters - Parameterized Row Filters

sp_addmergepartition