Prashanth Jayaram

How to Add/Drop articles from existing publications in SQL Server

August 24, 2018 by

This article discusses the challenges of meeting the availability, and performance requirements of high ended transactional replication environments. In addition, you’ll learn a new innovative approach that can be used to add/drop articles from an existing replication environment while maintaining replication system up and running.

After reading this article, you should be able to perform the following tasks:

  1. Adding/dropping article(s) with a snapshot
  2. Adding/dropping article(s) without generating a snapshot using T-SQL
  3. Adding an article to a subscription initialized through the backup using SSMS
  4. And more…

Pre-requisites

  1. Requires understanding of replication terminologies such as articles, publisher, subscriber, and snapshot agent, log reader agent, and distribution agent
  2. Good understanding of transactional replication topology
  3. Replication monitor
  4. Need to have an account with syadmin and db_owner role
  5. AdventureWorks2016 database, download here

Let us deep-dive into the concepts of adding article(s) to the existing transactional replication.

Adding or dropping articles from existing publication by regenerating a snapshot

This is simple, straight-forward and Microsoft recommended practice. The walk-through of the above steps is common where the environment is small and relatively has less workload on the replication database and getting outage windows is relatively much easier.

Adding an article involves the following tasks

  1. Adding the article to the publication
  2. Creating a new snapshot for the publication
  3. Using the distribution agent, synchronize the subscription database by applying the schema and data for the new article

Adding/dropping article(s) from an existing publication by invalidating the snapshot agent

As I mentioned above, adding articles to and dropping articles from an existing publication, you must create a new snapshot for the publication. This is the recommended practice. To avoid generating a snapshot for all articles when adding a new article, publication property immediate_sync must be set to 0 and then call sp_addarticle, followed by sp_addsubscription. If it is pull subscription, you must call sp_refreshsubscriptions. Then generate a snapshot and this process will yield to generate snapshot only for the newly added articles.

As invalidating the snapshot is not a recommended option, but still, this is more important when we deal with large replication database and we don’t want to reinitialize snapshot agent because of the database size and other considerations in mind. In order to do this; we need to set the publisher properties allow_anonymous and Immediate_sync to False. Let us do this using the following T-SQL.

  1. First, change the allow_anonymous property of the publication to FALSE

  2. Next, disable Change immediate_sync

  3. Invalidate the snapshot

  4. Refresh subscriptions

  5. Now, start Snapshot Agent using Replication monitor

    You should notice that bulk-insert statements are created only for the specific article instead of all articles,

  6. Next, start log reader agent
  7. Re-enable the disabled properties, first, immediate_sync and then Allow_anonymous options

  8. Now you can verify the article on all your subscribers

Let us talk about the other scenario as well. In the real world, there are situations where articles are out of sync. In a real-time system, getting an outage window is a daunting task. And effectively using that window is another challenge. By default, transactional replication, locks will be applied on the related transactional articles only during the initial phase of snapshot generation. If the publisher database is huge in size, then there is an impact on the user actions as locks prevents the users from insert/update operations. However, if the table is HUGE and out of sync and it is required to re-sync and you don’t have any other option—drop the article in LIFO (Last-In-First-Out) order and re-add the article or Use data export utilities to sync the data or use BCP or SSIS to sync the subscription.

Here are the steps to follow:

  1. To drop the article from the subscriber, run sp_dropsubscription
  2. To drop the article from the publisher, run sp_droparticle
  3. To re-add the article to publication with the Immediate_sync option set to false

Adding an article to a subscription initialized through the backup

Let us talk about another scenario where we set up transactional replication using a database backup. Typically, transactional replication is initialized with a snapshot. In this case, the subscription is initialized from a backup. When you setup a subscription with sync_type=’replication support only’, it does not allow to add any more articles.

Let us see the steps on how to add an article to an existing Transactional Subscription initialized through backup

Adding new articles to a publisher and a subscriber can be addressed in two ways

  1. Create a new publication for just that article and follow the same steps that we followed to add an article by generating the snapshot
  2. Add the article to an existing publication and manually synchronizing the data between publisher and subscriber

The setup instruction is not discussed in this article and it is out of scope for the discussion. You’ll see the demo of adding article to existing publisher and subscriber that has been configured with “replication support only“ feature. Unlike initializing with a backup, you or your application must ensure the data and schema are properly synchronized at the time you add the subscription. If, for example, there is activity on the Publisher between the time data and schema are copied to the Subscriber and the time at which the subscription is added, changes resulting from this activity might not be replicated to the Subscriber.

Let us follow the steps to add an article to publisher

  1. Copy the newly created tables from Publisher to Subscriber using any known methods
  2. First, stop the log reader agent. It is the recommended practice to stop the log-reader agent before doing any operations with replication articles

  3. Now, quiesce the table. In this way you can ensure the data consistency between publisher and subscriber
  4. Add the article to the publication
    1. Browse Replication folder and select the AdventureWorks-Pub under Local Publications
    2. Right-click and select Properties
    3. In the Select a page, Choose Articles
    4. Now, uncheck Show only checked articles in the list
    5. Select the Articles under Object to Publish and then Click Ok. In this case, Cities article is added the AdventureWorks -Pub


  5. The configuration is complete
  6. Add few more rows to table in the Publisher database. In this case, 10 rows are added. The output has two data sets. The first referencing publisher article and the second is subscriber

  7. Next, start log reader agent. In this way, the article is ready to accept any change and the log reader will be able to start tracking the changes from this point
  8. Launch Replication monitor to monitor the transactions.


  9. Let’s do a QAD data Comparison to measure difference. Use the following T-SQL to query the articles. In this case, T-SQL is executed under SQLCMD mode. You can change the source and target server instance names as per your environment

  10. Now, you can see the rows are in sync in the newly created tables

Wrap Up

In this article, we looked at core replication features about how to add articles to transactional replication that are configured with a snapshot and also a scenario where replication is configured using a backup. After reading this article, you might have realized the how difficult the process is and many DBAs might not even be aware of how to set up replication using backup.

In the next article, How to set up a DDL and DML SQL Server database transactional replication solution, we’ll take another approach to this use case using different technologies

Table of contents

SQL Server replication: Overview of components and topography
SQL Replication: Basic setup and configuration
How to Add/Drop articles from existing publications in SQL Server
How to do a quick estimated compare of data in two large SQL Server databases to see if they are equal
SQL Server transactional replication: How to reinitialize a subscription using a SQL Server database backup
How to setup a custom SQL Server transaction replication model with a Central Subscriber and Multiple Publisher databases
How to setup custom SQL Server transactional replication with a central publisher and multiple subscriber databases
How to set up a DDL and DML SQL Server database transactional replication solution
How to setup cross-platform transactional SQL Server replication for database reporting on Linux
SQL Server database migrations with zero data loss and zero downtime
Using transactional data replication to replay and test production loads on a staging server
How to setup SQL Server database replication for a reporting server
SQL Server transactional replication: How to reinitialize a subscription using a “Replication support only” –TBA
SQL Server Replication Monitoring and setting alerts using PowerShell –TBA
Prashanth Jayaram
Replication, Transaction log

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

168 Views