Sometime due to the business requirement you might need to add tables to an existing transactional replication. This might be expensive activity if you add new tables specifically to an existing replication which is having a mix of large and small size  to generate a full snapshot.

In my  production environment I have 150 tables of  almost of 50 GB snapshot size.  If there is any change request to add new tables earlier we  had to plan a full outage communication where the data will not be available on target subscriber till the full snapshot is generated and applied.

We found out there is a very simple mechanism to limit the snapshot size , while adding new tables. The property ‘allow_anonymous’  and ‘immediate_sync’ , manage this.

— run on the publication Database to check the property

exec sp_helppublication ‘Your Publisher DBname’
GO

–Run on your publisher database
EXEC sp_changepublication
@publication = ‘your publication name’,
@property = ‘allow_anonymous’ ,
@value = ‘false’
GO
EXEC sp_changepublication
@publication = ‘your publication name’,
@property = ‘immediate_sync’ ,
@value = ‘false’

–check the subscription status

Exec sp_helpsubscription  —  Run  on your publisher database to verify the ‘subscription status’ of the subscribed article. If the Subscription status =1 that means the tables are already subscribed and only snapshot agent need to run.  Run the snapshot agent and generate the delta / limited snapshot.

Categories: Uncategorised

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

Uncategorised

TEMPDB drive full on PRODUCTION

How to shrink tempdb on active production: How am I going to shrink tempdb and release space to OS to suppress the disk space alerts on production without restarting SQL Service. As best practice tempdb Read more…

Uncategorised

Transcational Replication fails with -“Could not find stored procedure ‘sp_MSins_.’

We have SQLServer 2014,transactional  replication on Azure ARM VM. This started failing with the error Transactional Replication to Pull from the Publisher. I did not choose to update schema or do a Snapshot. And now Read more…

Uncategorised

SQL SERVER – The Patch Installer has Failed to Update the Shared Features

Applying a patch in SQL Server is a planned process which is followed across various companies. Most of the companies take time to apply the patch to make sure the stability of the patch can Read more…

www.000webhost.com