• 如何用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

  • 相关阅读:
    近期Android学习II
    近期Android学习
    Java中AQS基本实现原理
    Java中CAS 基本实现原理
    SpringBoot 消息国际化配置
    SpringBoot2.0 配置多数据源
    浅谈Java 线程池原理及使用方式
    Java并发编程之闭锁与栅栏
    Java 8 Stream API实例
    第二阶段考试
  • 原文地址:https://www.cnblogs.com/OpenCoder/p/10037780.html
Copyright © 2020-2023  润新知