sp_addmergepublication (Transact-SQL)
Creates a new merge publication. This stored procedure is executed at the Publisher on the database that is being published.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
sp_addmergepublication [ @publication = ] 'publication'
[ , [ @description = ] 'description'
[ , [ @retention = ] retention ]
[ , [ @sync_mode = ] 'sync_mode' ]
[ , [ @allow_push = ] 'allow_push' ]
[ , [ @allow_pull = ] 'allow_pull' ]
[ , [ @allow_anonymous = ] 'allow_anonymous' ]
[ , [ @enabled_for_internet = ] 'enabled_for_internet' ]
[ , [ @centralized_conflicts = ] 'centralized_conflicts' ]
[ , [ @dynamic_filters = ] 'dynamic_filters' ]
[ , [ @snapshot_in_defaultfolder = ] 'snapshot_in_default_folder' ]
[ , [ @alt_snapshot_folder = ] 'alternate_snapshot_folder' ]
[ , [ @pre_snapshot_script = ] 'pre_snapshot_script' ]
[ , [ @post_snapshot_script = ] 'post_snapshot_script' ]
[ , [ @compress_snapshot = ] 'compress_snapshot' ]
[ , [ @ftp_address = ] 'ftp_address' ]
[ , [ @ftp_port = ] ftp_port ]
[ , [ @ftp_subdirectory = ] 'ftp_subdirectory' ]
[ , [ @ftp_login = ] 'ftp_login' ]
[ , [ @ftp_password = ] 'ftp_password' ]
[ , [ @conflict_retention = ] conflict_retention ]
[ , [ @keep_partition_changes = ] 'keep_partition_changes' ]
[ , [ @allow_subscription_copy = ] 'allow_subscription_copy' ]
[ , [ @allow_synctoalternate = ] 'allow_synctoalternate' ]
[ , [ @validate_subscriber_info = ] 'validate_subscriber_info' ]
[ , [ @add_to_active_directory = ] 'add_to_active_directory' ]
[ , [ @max_concurrent_merge = ] maximum_concurrent_merge ]
[ , [ @max_concurrent_dynamic_snapshots = ] max_concurrent_dynamic_snapshots ]
[ , [ @use_partition_groups = ] 'use_partition_groups' ]
[ , [ @publication_compatibility_level = ] 'backward_comp_level' ]
[ , [ @replicate_ddl = ] replicate_ddl ]
[ , [ @allow_subscriber_initiated_snapshot = ] 'allow_subscriber_initiated_snapshot' ]
[ , [ @allow_web_synchronization = ] 'allow_web_synchronization' ]
[ , [ @web_synchronization_url = ] 'web_synchronization_url' ]
[ , [ @allow_partition_realignment = ] 'allow_partition_realignment' ]
[ , [ @retention_period_unit = ] 'retention_period_unit' ]
[ , [ @generation_leveling_threshold = ] generation_leveling_threshold ]
[ , [ @automatic_reinitialization_policy = ] automatic_reinitialization_policy ]
[ , [ @conflict_logging = ] 'conflict_logging' ]
Arguments
[ @publication = ] 'publication'
Is the name of the merge publication to create. publication is sysname, with no default, and must not be the keyword ALL. The name of the publication must be unique within the database.[ @description = ] 'description'
Is the publication description. description is nvarchar(255), with a default of NULL.[ @retention = ] retention
Is the retention period, in retention period units, for which to save changes for the given publication. retention is int, with a default of 14 units. Retention period units are defined by retention_period_unit. If the subscription is not synchronized within the retention period, and the pending changes it would have received have been removed by a clean-up operation at the Distributor, the subscription expires and must be reinitialized. The maximum allowable retention period is the number of days between Dec. 31, 9999 and the current date.Note
The retention period for merge publications has a 24 hour grace period to accommodate Subscribers in different time zones. If, for example, you set a retention period of one day, the actual retention period is 48 hours.
[ @sync_mode = ] 'sync_mode'
Is the mode of the initial synchronization of subscribers to the publication. sync_mode is nvarchar(10), and can be one of the following values.Value
Description
native (default)
Produces native-mode bulk copy program output of all tables.
character
Produces character-mode bulk copy program output of all tables. Required to support Microsoft SQL Server Compact and non-SQL Server Subscribers.
[ @allow_push = ] 'allow_push'
Specifies if push subscriptions can be created for the given publication. allow_push is nvarchar(5), with a default of TRUE, which allows push subscriptions on the publication.[ @allow_pull = ] 'allow_pull'
Specifies if pull subscriptions can be created for the given publication. allow_pull is nvarchar(5), with a default of TRUE, which allows pull subscriptions on the publication. You must specify true to support SQL Server Compact Subscribers.[ @allow_anonymous = ] 'allow_anonymous'
Specifies if anonymous subscriptions can be created for the given publication. allow_anonymous is nvarchar(5), with a default of TRUE, which allows anonymous subscriptions on the publication. To support SQL Server Compact Subscribers, you must specify true.[ @enabled_for_internet = ] 'enabled_for_internet'
Specifies if the publication is enabled for the Internet, and determines if file transfer protocol (FTP) can be used to transfer the snapshot files to a subscriber. enabled_for_internet is nvarchar(5), with a default of FALSE. If true, the synchronization files for the publication are put into the C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL.x\Repldata\Ftp directory. The user must create the Ftp directory. If false, the publication is not enabled for Internet access.[ @centralized_conflicts =] 'centralized_conflicts'
This parameter has been deprecated and is only supported for the backward compatibility of scripts. Use conflict_logging to specify the location where conflict records are stored.[ @dynamic_filters =] 'dynamic_filters'
Enables the merge publication to use parameterized row filters. dynamic_filters is nvarchar(5), with a default of FALSE.Note
You should not specify this parameter but instead allow SQL Server to automatically determine if parameterized row filters are being used. If you specify a value of true for dynamic_filters, you must define a parameterized row filter for the article. For more information, see Define and Modify a Parameterized Row Filter for a Merge Article.
[ @snapshot_in_defaultfolder = ] 'snapshot_in_default_folder'
Specifies if the snapshot files are stored in the default folder. snapshot_in_default_folder is nvarchar(5), with a default of TRUE. If true, snapshot files can be found in the default folder. If false, snapshot files will be stored in the alternate location specified by alternate_snapshot_folder. Alternate locations can be on another server, on a network drive, or on a removable media (such as CD-ROM or removable disks). You can also save the snapshot files to a File Transfer Protocol (FTP) site, for retrieval by the Subscriber at a later time. Note that this parameter can be true and still have a location specified by alt_snapshot_folder. This combination specifies that the snapshot files will be stored in both the default and alternate locations.[ @alt_snapshot_folder = ] 'alternate_snapshot_folder'
Specifies the location of the alternate folder for the snapshot. alternate_snapshot_folder is nvarchar(255), with a default of NULL.[ @pre_snapshot_script = ] 'pre_snapshot_script'
Specifies a pointer to an .sql file location. pre_snapshot_script is nvarchar(255), with a default of NULL. The Merge Agent will run the pre-snapshot script before any of the replicated object scripts when applying the snapshot at a Subscriber. The script is executed in the security context used by the Merge Agent when connecting to the subscription database. Pre-snapshot scripts are not run on SQL Server Compact Subscribers.[ @post_snapshot_script = ] 'post_snapshot_script'
Specifies a pointer to an .sql file location. post_snapshot_script is nvarchar(255), with a default of NULL. The Merge Agent will run the post-snapshot script after all the other replicated object scripts and data have been applied during an initial synchronization. The script is executed in the security context used by the Merge Agent when connecting to the subscription database. Post-snapshot scripts are not run on SQL Server Compact Subscribers.[ @compress_snapshot = ] 'compress_snapshot'
Specifies that the snapshot written to the @alt_snapshot_folder location is to be compressed into the Microsoft CAB format. compress_snapshot is nvarchar(5), with a default of FALSE. false specifies that the snapshot will not be compressed; true specifies that the snapshot is to be compressed. Snapshot files that are larger than 2GB cannot be compressed. Compressed snapshot files are uncompressed at the location where the Merge Agent runs; pull subscriptions are typically used with compressed snapshots so that files are uncompressed at the Subscriber. The snapshot in the default folder cannot be compressed. To support SQL Server Compact Subscribers, you must specify false.[ @ftp_address = ] 'ftp_address'
Is the network address of the FTP service for the Distributor. ftp_address is sysname, with a default of NULL. Specifies where publication snapshot files are located for the Merge Agent of a subscriber to pick up. Since this property is stored for each publication, each publication can have a different ftp_address. The publication must support propagating snapshots using FTP.[ @ftp_port= ] ftp_port
Is the port number of the FTP service for the Distributor. ftp_port is int, with a default of 21. Specifies where the publication snapshot files are located for the Merge Agent of a subscriber to pick up. Since this property is stored for each publication, each publication can have its own ftp_port.[ @ftp_subdirectory = ] 'ftp_subdirectory'
Specifies where the snapshot files will be available for the Merge Agent of the subscriber to pick up if the publication supports propagating snapshots using FTP. ftp_subdirectory is nvarchar(255), with a default of NULL. Since this property is stored for each publication, each publication can have its own ftp_subdirctory or choose to have no subdirectory, indicated with a NULL value.When pre-generating snapshots for publications with parameterized filters, the data snapshot for each Subscriber partition needs to be in its own folder. The directory structure for pre-generated snapshots using FTP must obey the following structure:
alternate_snapshot_folder\ftp\publisher_publicationDB_publication\partitionID.
Note
The values above in italics will depend on the specifics of the publication and Subscriber partition.
[ @ftp_login = ] 'ftp_login'
Is the username used to connect to the FTP service. ftp_login is sysname, with a default of 'anonymous'.[ @ftp_password = ] 'ftp_password'
Is the user password used to connect to the FTP service. ftp_password is sysname, with a default of NULL..gif "Security note")
Security NoteDo not use a blank password. Use a strong password.
[ @conflict_retention = ] conflict_retention
Specifies the retention period, in days, for which conflicts are retained. conflict_retention is int, with a default of 14 days before the conflict row is purged from the conflict table.[ @keep_partition_changes = ] 'keep_partition_changes'
Specifies whether to enable partition change optimizations when precomputed partitions cannot be used. keep_partition_changes is nvarchar(5), with a default of TRUE. false means that partition changes are not optimized, and when precomputed partitions are not used, the partitions sent to all Subscribers will be verified when data changes in a partition. true means that partition changes are optimized, and only Subscribers having rows in the changed partitions are affected. When using precomputed partitions, set use_partition_groups to true and set keep_partition_changes to false. For more information, see Optimize Parameterized Filter Performance with Precomputed Partitions.Note
If you specify a value of true for keep_partition_changes, specify a value of 1 for the Snapshot Agent parameter -MaxNetworkOptimization. For more information about this parameter, see Replication Snapshot Agent. For information about how to specify agent parameters, see Replication Agent Administration.
With SQL Server Compact subscribers, keep_partition_changes must be set to true to ensure that deletes are correctly propagated. When set to false, the subscriber might have more rows than expected.
[ @allow_subscription_copy= ] 'allow_subscription_copy'
Enables or disables the ability to copy the subscription databases that subscribe to this publication. allow_subscription_copy is nvarchar(5), with a default of FALSE. The size of the subscription database being copied must be less than 2 gigabytes (GB).[ @allow_synctoalternate = ] 'allow_synctoalternate'
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.[ @validate_subscriber_info = ] 'validate_subscriber_info'
Lists the functions that are used to define a Subscriber partition of the published data when parameterized row filters are used. validate_subscriber_info is nvarchar(500), with a default of NULL. This information is used by the Merge Agent to validate the Subscriber's partition. For example, if SUSER_SNAME is used in the parameterized row filter, the parameter should be @validate\_subscriber\_info=N'SUSER_SNAME()'.Note
You should not specify this parameter but instead allow SQL Server to automatically determine the filtering criterion.
[ @add_to_active_directory = ] 'add_to_active_directory'
This parameter has been deprecated and is only supported for the backward compatibility of scripts. You can no longer add publication information to the Microsoft Active Directory.[ @max_concurrent_merge = ] maximum_concurrent_merge
The maximum number of concurrent merge processes. maximum_concurrent_merge is int with a default of 0. A value of 0 for this property means that there is no limit to the number of concurrent merge processes running at any given time. This property sets a limit on the number of concurrent merge processes that can be run against a merge publication at one time. If there are more merge processes scheduled at the same time than the value allows to run, then the excess jobs will be put into a queue and wait until a currently-running merge process finishes.[ @max_concurrent_dynamic_snapshots =] max_concurrent_dynamic_snapshots
The maximum number of Snapshot Agent sessions that can be run concurrently to generate filtered data snapshots for Subscriber partitions. maximum_concurrent_dynamic_snapshots is int with a default of 0. If 0, there is no limit to the number snapshot sessions. If there are more snapshot processes scheduled at the same time than the value allows to run, then the excess jobs will be put into a queue and wait until a currently-running snapshot process finishes.[ @use_partition_groups = ] 'use_partition_groups'
Specifies that precomputed partitions should be used to optimize the synchronization process. use_partition_groups is nvarchar(5), and can be one of these values:Value
Description
true
Publication uses precomputed partitions.
false
Publication does not use precomputed partitions.
NULL(default)
System decides on the partitioning strategy.
Precomputed partitions are used by default. To avoid using precomputed partitions, use_partition_groups must be set to false. When NULL, the system will decide if precomputed partitions can be used. If precomputed partitions cannot be used, then this value will effectively become false without generating any errors. In such cases, keep_partition_changes can be set to true to provide some optimization. For more information, see Parameterized Row Filters and Optimize Parameterized Filter Performance with Precomputed Partitions.
[ @publication_compatibility_level = ] backward_comp_level
Indicates the backward compatibility of the publication. backward_comp_level is nvarchar(6), and can be one of these values:Value
Version
90RTM
SQL Server 2005
100RTM
SQL Server 2008
[ @replicate_ddl = ] replicate_ddl
Indicates if schema replication is supported for the publication. replicate_ddl is int, with a default of 1. 1 indicates that data definition language (DDL) statements executed at the publisher are replicated, and 0 indicates that DDL statements are not replicated. For more information, see Make Schema Changes on Publication Databases.The @replicate\_ddl parameter is honored when a DDL statement adds a column. The @replicate\_ddl parameter is ignored when a DDL statement alters or drops a column for the following reasons.
When a column is dropped, sysarticlecolumns must be updated to prevent new DML statements from including the dropped column which would cause the distribution agent to fail. The @replicate\_ddl parameter is ignored because replication must always replicate the schema change.
When a column is altered, the source data type or nullability might have changed, causing DML statements to contain a value that may not be compatible with the table at the subscriber. Such DML statements might cause distribution agent to fail. The @replicate\_ddl parameter is ignored because replication must always replicate the schema change.
When a DDL statement adds a new column, sysarticlecolumns does not include the new column. DML statements will not try to replicate data for the new column. The parameter is honored because either replicating or not replicating the DDL is acceptable.
[ @allow_subscriber_initiated_snapshot = ] 'allow_subscriber_initiated_snapshot'
Indicates if Subscribers to this publication can initiate the snapshot process to generate the filtered snapshot for their data partition. allow_subscriber_initiated_snapshot is nvarchar(5), with a default of FALSE. true indicates that Subscribers can initiate the snapshot process.[ @allow_web_synchronization = ] 'allow_web_synchronization'
Specifies if the publication is enabled for Web synchronization. allow_web_synchronization is nvarchar(5), with a default of FALSE. true specifies that subscriptions to this publication can be synchronized over HTTPS. For more information, see Web Synchronization for Merge Replication. To support SQL Server Compact Subscribers, you must specify true.[ @web_synchronization_url= ] 'web_synchronization_url'
Specifies the default value of the Internet URL used for Web synchronization. web_synchronization_url is nvarchar(500), with a default of NULL. Defines the default Internet URL if one is not explicitly set when sp_addmergepullsubscription_agent is executed.[ @allow_partition_realignment = ] 'allow_partition_realignment'
Determines whether deletes are sent to the subscriber when modification of the row on the publisher causes it to change its partition. allow_partition_realignment is nvarchar(5), with a default of TRUE. true sends deletes to the Subscriber to reflect the results of a partition change by removing data that is no longer part of the Subscriber's partition. false leaves the data from an old partition on the Subscriber, where changes made to this data on the Publisher will not replicate to this Subscriber, but changes made on the Subscriber will replicate to the Publisher. Setting allow_partition_realignment to false is used to retain data in a subscription from an old partition when the data needs to be accessible for historical purposes.Note
Data that remains at the Subscriber as a result of setting allow_partition_realignment to false should be treated as if it were read-only; however, this is not enforced by the replication system.
[ @retention_period_unit = ] 'retention_period_unit'
Specifies the units for the retention period set by retention. retention_period_unit is nvarchar(10), and can be one of the following values.Value
Version
day (default)
Retention period is specified in days.
week
Retention period is specified in weeks.
month
Retention period is specified in months.
year
Retention period is specified in years.
[ @generation_leveling_threshold= ] generation_leveling_threshold
Specifies the number of changes that are contained in a generation. A generation is a collection of changes that are delivered to a Publisher or Subscriber. generation_leveling_threshold is int, with a default value of 1000.[ @automatic_reinitialization_policy = ] automatic_reinitialization_policy
Specifies whether changes are uploaded from the Subscriber before an automatic reinitialization required by a change to the publication, where a value of 1 was specified for @force_reinit_subscription. automatic_reinitialization_policy is bit, with a default value of 0. 1 means that changes are uploaded from the Subscriber before an automatic reinitialization occurs.Important
If you add, drop, or change a parameterized filter, pending changes at the Subscriber cannot be uploaded to the Publisher during reinitialization. If you want to upload pending changes, synchronize all subscriptions before changing the filter.
[ @conflict_logging = ] 'conflict_logging'
Specifies where conflict records are stored. conflict_logging is nvarchar(15), and can be one of the following values:Value
Description
publisher
Conflict records are stored at the Publisher.
subscriber
Conflict records are stored at the Subscriber that caused the conflict. Not supported for SQL Server Compact Subscribers.
both
Conflict records are stored at both the Publisher and Subscriber.
NULL (default)
Replication automatically sets conflict_logging to both when the value backward_comp_level is 90RTM and to publisher in all other cases.
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_addmergepublication is used in merge replication.
To list publication objects to the Active Directory using the @add_to_active_directory parameter, the SQL Server object must already be created in the Active Directory.
If multiple publications exist that publish the same database object, only publications with a replicate_ddl value of 1 will replicate ALTER TABLE, ALTER VIEW, ALTER PROCEDURE, ALTER FUNCTION, and ALTER TRIGGER DDL statements. However, an ALTER TABLE DROP COLUMN DDL statement will be replicated by all publications that are publishing the dropped column.
For SQL Server Compact Subscribers, the value of alternate_snapshot_folder is only used when the value of snapshot_in_default_folder is false.
With DDL replication enabled (replicate_ddl = 1) for a publication, in order to make non-replicating DDL changes to the publication, sp_changemergepublication (Transact-SQL) must first be executed to set replicate_ddl to 0. After the non-replicating DDL statements have been issued, sp_changemergepublication can be run again to turn DDL replication back on.
Example
-- To avoid storing the login and password in the script file, the value
-- is passed into SQLCMD as a scripting variable. For information about
-- how to use scripting variables on the command line and in SQL Server
-- Management Studio, see the "Executing Replication Scripts" section in
-- the topic "Programming Replication Using System Stored Procedures".
--Declarations for adding a merge publication
DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'AdventureWorks2012';
SET @publication = N'AdvWorksSalesOrdersMerge';
SET @login = $(Login);
SET @password = $(Password);
-- Enable merge replication on the publication database, using defaults.
USE master
EXEC sp_replicationdboption
@dbname=@publicationDB,
@optname=N'merge publish',
@value = N'true'
-- Create a new merge publication, explicitly setting the defaults.
USE [AdventureWorks2012]
EXEC sp_addmergepublication
-- These parameters are optional.
@publication = @publication,
-- optional parameters
@description = N'Merge publication of AdventureWorks2012.',
@publication_compatibility_level = N'120RTM';
-- Create a new snapshot job for the publication.
EXEC sp_addpublication_snapshot
@publication = @publication,
@job_login = @login,
@job_password = @password;
GO
Permissions
Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_addmergepublication.
See Also
Reference
sp_changemergepublication (Transact-SQL)
sp_dropmergepublication (Transact-SQL)
sp_helpmergepublication (Transact-SQL)
Replication Stored Procedures (Transact-SQL)