- Replication Stored Procedures
- Modifying Published Table Schemas
- Adding and Removing Articles from Publications
- Subscription Expiration and Deactivation
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Adding and Removing Articles from Publications
You can add articles to the publication at any time using the articles page of the Publication Properties dialog box. Keep in mind that newly added articles won’t be delivered to the subscriber until you run the Snapshot Agent. Even so, you can make changes to the data in the replicated table without waiting on the snapshot-replicated commands, which will simply accumulate in the distribution database until the table is created on the subscriber.
You can remove a table from the publication at any time by using the same page of the Publication Properties dialog box. However removing an article will require reinitializing subscriptions from a new snapshot. SSMS will warn you that all subscriptions to the current publication will have to be reinitialized when you attempt to remove an article. If you didn’t initialize subscriptions during replication setup, a new snapshot is not generated.
Recall that with previous releases of SQL Server, you had to drop subscriptions prior to removing any articles from the publication.
If you prefer to add and drop articles using scripts, you can use the system stored procedures sp_addarticle and sp_droparticle, respectively. For example, the following script adds the article DimAccount to the publication:
EXEC sp_addarticle @publication = N’PublicationName’, @article = N’DimAccount’, @source_owner = N’dbo’, @source_object = N’DimAccount’, @type = N’logbased’, @description = N’’, @creation_script = N’’, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000803519F, @identityrangemanagementoption = N’manual’, @destination_table = N’DimAccount’, @destination_owner = N’dbo’, @status = 16, @vertical_partition = N’false’, @ins_cmd = N’SQL’, @del_cmd = N’CALL [sp_MSdel_dboDimAccount]’, @upd_cmd = N’MCALL [sp_MSupd_dboDimAccount]’
The following command removes the article dimCurrency from the publication:
EXEC sp_droparticle @publication= ’PublicationName’, @article= ’dimCurrency’, @force_invalidate_snapshot= 0
Note that sp_droparticle does not allow removing an article from a publication to which subscriptions exist. If you use SSMS to drop the article from the publication, SQL Server briefly removes the subscription(s), removes the article, and then adds the subscription(s) back to the publication.