假设我们要创建合并复制的发布端数据库是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'domain\scott', @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'domain\scott', @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'domain\scott'
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