Microsoft SQL Server - Add Articles to Existing Replication Without Reinitializing Snapshot

Replication can be defined as the process via which Data and database objects are copied and distributed between databases, and then synchronized to ensure consistency. Transactional replication is typically used in scenarios requiring high server throughput, Reporting and data warehouses; improving scalability and availability; integrating data from disparate sources. Batch processing can be offloaded, heterogeneous data can be integrated, and multiple sites can be integrated.

Now a days, a major issue for every client is related to database performance. This blog explains how to tune memory in database which result in high performance in databases. The following are the various kinds of memory tuning methods that are available: OS level process tuning, CPU tuning, RAM tuning, database tuning etc. In this blog I will be discussing about the memory tuning in databases.

What we are trying to achieve:

Adding a fresh article to a replication is always a tedious task and specially when publisher is big in size or we have customizations in the subscription level. We don’t want to spend hours on reinitializing entire subscription or lose all customization happening on the subscriber for business needs. For such cases we have a option to add article in exiting publication without generating the whole snapshot. Let’s see how we can accomplish this.

Existing Replication details:-

• Publisher Instance:- LG4WX8Y2
• Publisher Database:- [AdventureWorks2019]
• Publisher Name :- AdvWorks-Publication
• Article Count :- 13

• Subscriber Instance:- LG4WX8Y2
• Subscriber Database:- [AdventureWorksReporting]
• Article Count :- 13

****************** **Steps to add articles in to existing replication ** **************************

1. Ensure exiting replication is healthy.

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

Note: Anonymous subscriptions can be created for the given publication, and immediate_sync must also be true. This cannot be changed for peer-to-peer publications.

3. Next, disable change immediate_sync

Note: Immediate_sync feature instructs replication to maintain Snapshot BCP files and distributed transactions in the Distribution database. New Subscribers (or reinitialize) would not need to have a “fresh” snapshot generated.

4. Add article and invalidate the snapshot (New articles :- [Production].[Product] and [Person].[Person])

5. Refresh Subscription so that we can generate new snapshot

Note: /*When using pull subscription use below command
*/–EXEC sp_refreshsubscriptions @publication = N’Adventureworks2016-Pub'

6. Now, start Snapshot Agent using Replication monitor for publisher “AdvWorks-Publication”

NOTE:- You should notice that bulk-insert statements are created only for 2 article instead of all articles.

7. Check if log reader agent is running and replicating transactions.

8. Ensure articles are added to publisher – Now you can see 15 articles in the list.

9. First, change the immediate_sync property of the publication to true

10. Next, enable change allow_anonymous

11. Monitor the replication for some time.

Scripts Used for Demo:

Learn about Rackspace Managed SQL Databases →