How to Create Merge Replication in SQL Server - SQL Server DBA Tutorial

In this video you will learn how to create Merge replication in SQL Server using SQL Server management studio as well as using T-SQL script. You will also learn how to configure the publication for Merge replication, best practices of configuring publications for Merge replication, how to add database and it's articles in Merge replication, configuring Publication agent security and best practices of security configuration of Publisher as well as Subscriber.
It also demonstrates consideration while creating Merge Replication, walk through article, publisher and distributor properties.

Script used in the video to Create Merge Replication in SQL Server

-- Enabling the replication database
USE master

EXEC Sp_replicationdboption
  @dbname = N'ReportServer',
  @optname = N'merge publish',
  @value = N'true'

GO

-- Adding the merge publication
USE [ReportServer]

EXEC Sp_addmergepublication
  @publication = N'ReportMerge',
  @description = N'Merge publication of database ''ReportServer'' from Publisher ''TBSSQL\SQLPROD''.',
  @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

EXEC Sp_addpublication_snapshot
  @publication = N'ReportMerge',
  @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 = 500,
  @active_end_time_of_day = 235959,
  @active_start_date = 0,
  @active_end_date = 0,
  @job_login = N'Techbrothers\clusteradmin',
  @job_password = NULL,
  @publisher_security_mode = 1

EXEC Sp_grant_publication_access
  @publication = N'ReportMerge',
  @login = N'sa'

GO

EXEC Sp_grant_publication_access
  @publication = N'ReportMerge',
  @login = N'TECHBROTHERS\sqlsvc'

GO

EXEC Sp_grant_publication_access
  @publication = N'ReportMerge',
  @login = N'TECHBROTHERS\clusteradmin'

GO

EXEC Sp_grant_publication_access
  @publication = N'ReportMerge',
  @login = N'NT SERVICE\Winmgmt'

GO

EXEC Sp_grant_publication_access
  @publication = N'ReportMerge',
  @login = N'NT SERVICE\SQLWriter'

GO

EXEC Sp_grant_publication_access
  @publication = N'ReportMerge',
  @login = N'NT SERVICE\MSSQL$SQLPROD'

GO

EXEC Sp_grant_publication_access
  @publication = N'ReportMerge',
  @login = N'NT SERVICE\SQLAgent$SQLPROD'

GO

EXEC Sp_grant_publication_access
  @publication = N'ReportMerge',
  @login = N'distributor_admin'

GO

-- Adding the merge articles
USE [ReportServer]

EXEC Sp_addmergearticle
  @publication = N'ReportMerge',
  @article = N'ConfigurationInfo',
  @source_owner = N'dbo',
  @source_object = N'ConfigurationInfo',
  @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 [ReportServer]

EXEC Sp_addmergearticle
  @publication = N'ReportMerge',
  @article = N'DataSets',
  @source_owner = N'dbo',
  @source_object = N'DataSets',
  @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 [ReportServer]

EXEC Sp_addmergearticle
  @publication = N'ReportMerge',
  @article = N'DataSource',
  @source_owner = N'dbo',
  @source_object = N'DataSource',
  @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

-- Adding the merge subscriptions
USE [ReportServer]

EXEC Sp_addmergesubscription
  @publication = N'ReportMerge',
  @subscriber = N'TBSClient\SQLMirror',
  @subscriber_db = N'Reportserver',
  @subscription_type = N'Push',
  @sync_type = N'Automatic',
  @subscriber_type = N'Global',
  @subscription_priority = 75,
  @description = N'',
  @use_interactive_resolver = N'False'

EXEC Sp_addmergepushsubscription_agent
  @publication = N'ReportMerge',
  @subscriber = N'TBSClient\SQLMirror',
  @subscriber_db = N'Reportserver',
  @job_login = N'Techbrothers\clusteradmin',
  @job_password = NULL,
  @subscriber_security_mode = 1,
  @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 

How to Create Merge Replication in SQL Server