Change Publication and Article Properties

 

Applies To: SQL Server 2016

After a publication has been created, most publication and article properties can be changed, but some require that the snapshot be regenerated and/or subscriptions be reinitialized. This topic provides information about all properties that require one or both of these actions if they are changed.

DescriptionStored procedurePropertiesRequirements
Change snapshot format.sp_changepublicationsync_methodNew snapshot.
Change snapshot location.sp_changepublicationalt_snapshot_folder

 snapshot_in_defaultfolder
New snapshot.
Change snapshot location.sp_changedistpublisherworking_directoryNew snapshot.
Change snapshot compression.sp_changepublicationcompress_snapshotNew snapshot.
Change any File Transfer Protocol (FTP) snapshot options.sp_changepublicationenabled_for_internet

 ftp_address

 ftp_login

 ftp_password

 ftp_port

 ftp_subdirectory
New snapshot.
Change pre- or post-snapshot script location.sp_changepublicationpre_snapshot_script

 post_snapshot_script
New snapshot (also required if you change the script contents).

Reinitialization is required to apply the new script to the Subscriber.
Enable or disable support for non-Microsoft SQL Server Subscribers.sp_changepublicationis_enabled_for_het_subNew snapshot.
Change conflict reporting for queued updating subscriptionssp_changepublicationcentralized_conflictsCan only be changed if there are no active subscriptions.
Change conflict resolution policy for queued updating subscriptions.sp_changepublicationconflict_policyCan only be changed if there are no active subscriptions.
DescriptionStored procedurePropertiesRequirements
Drop an articlesp_droparticleAll parameters.Articles can be dropped prior to subscriptions being created. Using stored procedures, it is possible to drop a subscription to an article; using SQL Server Management Studio, the entire subscription must be dropped, recreated, and synchronized. For more information, see Add Articles to and Drop Articles from Existing Publications.
Change a column filter.sp_articlecolumn@column

 @operation
New snapshot.

Reinitialize subscriptions.
Add a row filter.sp_articlefilterAll parameters.New snapshot.

Reinitialize subscriptions.
Drop a row filter.sp_articlefilter@articleNew snapshot.

Reinitialize subscriptions.
Change a row filter.sp_articlefilter@filter_clauseNew snapshot.

Reinitialize subscriptions.
Change a row filter.sp_changearticlefilterNew snapshot.

Reinitialize subscriptions.
Change schema options.sp_changearticleschema_optionNew snapshot.
Change how tables at the Subscriber are handled prior to applying the snapshot.sp_changearticlepre_creation_cmdNew snapshot.
Change article statussp_changearticlestatusNew snapshot.
Change INSERT, UPDATE or DELETE commands.sp_changearticleins_cmd

 upd_cmd

 del_cmd
New snapshot.

Reinitialize subscriptions.
Change destination table namesp_changearticledest_tableNew snapshot.

Reinitialize subscriptions.
Change destination table owner (schema).sp_changearticledestination_ownerNew snapshot.

Reinitialize subscriptions.
Change data type mappings (applies to Oracle publishing only).sp_changearticlecolumndatatype@type

 @length

 @precision

 @scale
New snapshot.

Reinitialize subscriptions.
DescriptionStored procedurePropertiesRequirements
Change snapshot formatsp_changemergepublicationsync_modeNew snapshot.
Change snapshot location.sp_changemergepublicationalt_snapshot_folder

 snapshot_in_defaultfolder
New snapshot.
Change snapshot location.sp_changedistpublisherworking_directoryNew snapshot.
Change snapshot compressionsp_changemergepublicationcompress_snapshotNew snapshot.
Change any FTP snapshot optionssp_changemergepublicationenabled_for_internet

 ftp_address

 ftp_login

 ftp_password

 ftp_port

 ftp_subdirectory
New snapshot.
Change pre- or post-snapshot scripts.sp_changemergepublicationpre_snapshot_script

 post_snapshot_script
New snapshot (also required if you change the script contents).

Reinitialization is required to apply the new script to the Subscriber.
Add a join filter or logical record.sp_addmergefilterAll parameters.New snapshot.

Reinitialize subscriptions.
Drop a join filter or logical record.sp_dropmergefilterAll parameters.New snapshot.

Reinitialize subscriptions.
Change a join filter or logical record.sp_changemergefilter@property

 @value
New snapshot

Reinitialize subscriptions.
Disable the use of parameterized filters (enabling parameterized filters does not require any special actions).sp_changemergepublicationA value of false for dynamic_filtersNew snapshot.

Reinitialize subscriptions.
Enable or disable the use of precomputed partitions.sp_changemergepublicationuse_partition_groupsNew snapshot.
Enable or disable Microsoft SQL Server 2000 partition optimization.sp_changemergepublicationkeep_partition_changesReinitialize subscriptions.
Enable or disable Subscriber partition validation.sp_changemergepublicationvalidate_subscriber_infoReinitialize subscriptions.
Change the publication compatibility level to 80sp3 or lower.sp_changemergepublicationpublication_compatibility_levelNew snapshot.
DescriptionStored ProcedurePropertiesRequirements
Drop an article, where the article has the last parameterized filter in the publication.sp_dropmergearticleAll parametersNew snapshot.

Reinitialize subscriptions.
Drop an article, where the article is a parent in a join filter or logical record (this has the side effect of dropping the join).sp_dropmergearticleAll parametersNew snapshot.

Reinitialize subscriptions.
Drop an article, all other circumstances.sp_dropmergearticleAll parametersNew snapshot.
Include a column filter that was previously unpublished.sp_mergearticlecolumn@column

 @operation
New snapshot.

Reinitialize subscriptions.
Add, drop, or change a row filter.sp_changemergearticlesubset_filterclauseNew snapshot.

Reinitialize subscriptions.

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.

If an article is not involved in any join filters, you can drop the article and add it again with a different row filter, which does not require the entire subscription to be reinitialized. For more information about adding and dropping articles, see Add Articles to and Drop Articles from Existing Publications.
Change schema options.sp_changemergearticleschema_optionNew snapshot.
Change tracking from column-level to row-level (changing from row-level tracking to column-level tracking does not require any special actions).sp_changemergearticleA value of false for column_trackingNew snapshot.

Reinitialize subscriptions.
Change whether permissions are checked before statements made at the Subscriber are applied at the Publisher.sp_changemergearticlecheck_permissionsNew snapshot.

Reinitialize subscriptions.
Enable or disable download-only subscriptions (changing to or from other upload options does not require any special actions).sp_changemergearticleChange to or from a value of 2 for subscriber_upload_optionsReinitialize subscriptions.
Change destination table owner.sp_changemergearticledestination_ownerNew snapshot.

Reinitialize subscriptions.

Administration (Replication)
Create and Apply the Snapshot
Reinitialize Subscriptions
sp_addmergefilter (Transact-SQL)
sp_articlecolumn (Transact-SQL)
sp_articlefilter (Transact-SQL)
sp_changearticle (Transact-SQL)
sp_changearticlecolumndatatype (Transact-SQL)
sp_changedistpublisher (Transact-SQL)
sp_changemergearticle (Transact-SQL)
sp_changemergefilter (Transact-SQL)
sp_changemergepublication (Transact-SQL)
sp_changepublication (Transact-SQL)
sp_droparticle (Transact-SQL)
sp_dropmergearticle (Transact-SQL)
sp_dropmergefilter (Transact-SQL)
sp_mergearticlecolumn (Transact-SQL)

Community Additions

ADD
Show: