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:
- Adding/dropping article(s) with a snapshot
- Adding/dropping article(s) without generating a snapshot using T-SQL
- Adding an article to a subscription initialized through the backup using SSMS
- And more…
- Requires understanding of replication terminologies such as articles, publisher, subscriber, and snapshot agent, log reader agent, and distribution agent
- Good understanding of transactional replication topology
- Replication monitor
- Need to have an account with syadmin and db_owner role
- 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
- Adding the article to the publication
- Creating a new snapshot for the publication
- 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.
First, change the allow_anonymous property of the publication to FALSE12345EXEC sp_changepublication@publication = N'Adventureworks2016-Pub',@property = N'allow_anonymous',@value = 'FALSE'GO
Next, disable Change immediate_sync12345EXEC sp_changepublication@publication = N'Adventureworks2016-Pub',@property = N'immediate_sync',@value = 'FALSE'GO
Invalidate the snapshot12345EXEC sp_addarticle@publication = N'Adventureworks2016-Pub',@article =N'Cities',@source_object =N'Cities',@force_invalidate_snapshot=1
Refresh subscriptions1EXEC sp_refreshsubscriptions @publication = N'Adventureworks2016-Pub'
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,
- Next, start log reader agent
Re-enable the disabled properties, first, immediate_sync and then Allow_anonymous options123456789EXEC 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'
- 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:
- To drop the article from the subscriber, run sp_dropsubscription
- To drop the article from the publisher, run sp_droparticle
- 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
- Create a new publication for just that article and follow the same steps that we followed to add an article by generating the snapshot
- 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
- Copy the newly created tables from Publisher to Subscriber using any known methods
First, stop the log reader agent. It is the recommended practice to stop the log-reader agent before doing any operations with replication articles
- Now, quiesce the table. In this way you can ensure the data consistency between publisher and subscriber
Add the article to the publication
- Browse Replication folder and select the AdventureWorks-Pub under Local Publications
- Right-click and select Properties
- In the Select a page, Choose Articles
- Now, uncheck Show only checked articles in the list
Select the Articles under Object to Publish and then Click Ok. In this case, Cities article is added the AdventureWorks -Pub
- The configuration is complete
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
- 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
Launch Replication monitor to monitor the transactions.
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 environment123456789:CONNECT <Source>GOselect @@servernameselect * from AdventureWorks2016.dbo.citiesGO:CONNECT <target>GOselect @@servernameselect * from AdventureWorks2016_Report.dbo.cities
Now, you can see the rows are in sync in the newly created tables
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
- Azure SQL Database vs SQL Server on Azure VMs - October 20, 2020
- Data Virtualization with MongoDB using PolyBase in SQL Server 2019 - October 9, 2020
- Four different methods to copy your Azure SQL database - September 30, 2020