Edit

Add Articles to and Drop Articles from Existing Publications

  • Article

Applies to: SQL Server Azure SQL Managed Instance

After a publication is created, it is possible to add and drop articles. Articles can be added at any time, but the actions required for dropping articles depend on the type of replication and when the article is dropped.

Adding Articles

Adding an article involves: adding the article to the publication; creating a new snapshot for the publication; synchronizing the subscription to apply the schema and data for the new article.

Note

If you add an article to a merge publication and an existing article depends on the new article, you must specify a processing order for both articles using the @processing_order parameter of sp_addmergearticle and sp_changemergearticle. Consider the following scenario: you publish a table but you do not publish a function that the table references. If you do not publish the function, the table cannot be created at the Subscriber. When you add the function to the publication: specify a value of 1 for the @processing_order parameter of sp_addmergearticle; and specify a value of 2 for the @processing_order parameter of sp_changemergearticle, specifying the table name for the parameter @article. This processing order ensures that you create the function at the Subscriber before the table that depends on it. You can use different numbers for each article, as long as the number for the function is lower than the number for the table.

  1. Add one or more articles through one of the following methods:

  2. After adding an article to a publication, you must create a new snapshot for the publication (and all partitions if it is a merge publication with parameterized filters). The Distribution Agent or Merge Agent then copies the schema and data for the new article to the Subscriber (it does not reinitialize the entire publication).

  3. After the snapshot is created, synchronize the subscription to copy the schema and data for the new article.

Dropping Articles

Articles can be dropped from a publication at any time, but you must take into account the following behaviors:

  • Dropping an article from a publication does not remove the object from the publication database or the corresponding object from the subscription database. Use DROP <Object> to remove these objects if necessary. When you drop an article that is related to other published articles through foreign key constraints, we recommend that you drop the table at the Subscriber manually or by using on-demand script execution: specify a script that includes the appropriate DROP <Object> statements. For more information, see Execute Scripts During Synchronization (Replication Transact-SQL Programming).

  • For merge publications with a compatibility level of 90RTM or higher, articles can be dropped at any time, but a new snapshot is required. Additionally:

    • If an article is a parent article in a join filter or logical record relationship, the relationships must be dropped first, which requires reinitialization.

    • If an article has the last parameterized filter in a publication, subscriptions must be reinitialized.

  • For merge publications with a compatibility level lower than 90RTM, articles can be dropped with no special considerations prior to the initial synchronization of subscriptions. If an article is dropped after one or more subscriptions is synchronized, the subscriptions must be dropped, re-created, and synchronized.

  • For snapshot or transactional publications, articles can be dropped with no special considerations prior to subscriptions being created. If an article is dropped after one or more subscriptions is created, the subscriptions must be dropped, recreated, and synchronized. For more information about dropping subscriptions, see Subscribe to Publications and sp_dropsubscription (Transact-SQL). sp_dropsubscription allows you to drop a single article from the subscription rather than the entire subscription.

  1. Dropping an article from a publication involves dropping the article and creating a new snapshot for the publication. Dropping an article invalidates the current snapshot; therefore a new snapshot must be created.

  2. After dropping an article from a publication, you must create a new snapshot for the publication (and all partitions if it is a merge publication with parameterized filters).

As noted above, in some cases dropping an article requires subscriptions to be dropped, recreated, and then synchronized. For more information, see Subscribe to Publications and Synchronize Data.

Note

SQL Server 2014 (12.x) Service Pack 2 or above and SQL Server 2016 (13.x) Service Pack 1 or above support dropping a table using DROP TABLE DDL command for articles participating in Transactional Replication. If a DROP TABLE DDL is supported by the publication(s), then the DROP TABLE operation will drop the table from the publication and the database. The log reader agent will post a cleanup command for the distribution database of the dropped table and do the cleanup of the publisher metadata. If the log reader hasn't processed all the log records that refer to the dropped table, then it will ignore new commands that are associated with the dropped table. Already processed records will be delivered to distribution database. They may be applied on Subscriber database if the Distribution Agent processes them before Log Reader cleans up the obsolete (dropped) article(s). The default setting for all transactional replication publications is to not support DROP TABLE DDL. KB 3170123 has more details about this improvement.

See Also

Publish Data and Database Objects
Reinitialize Subscriptions
Make Schema Changes on Publication Databases