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

   1 2 3 4 5

   EXEC sp_changepublication @publication = N'Adventureworks2016-Pub', @property = N'allow_anonymous', @value = 'FALSE' GO

2. Next, disable Change immediate_sync

   1 2 3 4 5

   EXEC sp_changepublication @publication = N'Adventureworks2016-Pub', @property = N'immediate_sync', @value = 'FALSE' GO

3. Invalidate the snapshot

   1 2 3 4 5

   EXEC sp_addarticle @publication = N'Adventureworks2016-Pub', @article =N'Cities', @source_object =N'Cities', @force_invalidate_snapshot=1

4. Refresh subscriptions

   1	EXEC sp_refreshsubscriptions @publication = N'Adventureworks2016-Pub'

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

   1 2 3 4 5 6 7 8 9

   EXEC sp_changepublication @publication = N'Adventureworks2016-Pub', @property = N'immediate_sync', @value = 'TRUE'   EXEC sp_changepublication @publication = N'Adventureworks2016-Pub', @property = N'allow_anonymous', @value = 'TRUE'

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

   1 2 3 4 5 6 7 8 9

   :CONNECT <Source> GO select @@servername select * from AdventureWorks2016.dbo.cities GO :CONNECT <target> GO select @@servername select * from AdventureWorks2016_Report.dbo.cities

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

• Author
• Recent Posts

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

Latest posts by Prashanth Jayaram (see all)

• Stairway to SQL essentials - April 7, 2021
• A quick overview of database audit in SQL - January 28, 2021
• How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021