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

Replication using Remote distributor on ALWAYS ON AVAILABILITY GROUP

In SQL Server, Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. It is highly recommended to Read more…

Uncategorised

Configuring transnational replication on Always on availability group SQL Server 2014

SQL Server replication supports the automatic failover of the publisher, the automatic failover of transactional subscribers, and the manual failover of merge subscribers. The failover of a distributor on an availability database is not supported. In Read more…

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…

000webhost logo