CODEWITHSUNDEEP
sqlsql-server
Travis Brand
Travis Brand

Reputation: 53

Adding table(s) to replicated DB

I recently added two new tables to a db that is currently being transactionally replicated. Short of dropping and recreating the entire publication is there a way to quickly add these two new tables to the existing publication? Will I have to take an entirely new snapshot? I only ask because this is a production db and cant be stopped until nighttime, lockups will cause major issues. Thanks - Travis

Upvotes: 0

Views: 739

Answers (1)

sfabrey
sfabrey

Reputation: 26

DISCALIMER: All replication has the potential to lock entire databases as it reads the entire log. Changes should be thoroughly tested outside of Production and implemented off hours.

For basic transactional replication, you can use sp_addarticle and sp_addsubscription for each table without affecting the existing subscriptions. If you initialized the current subscription with sp_addsubscription @article = 'all'; (default), it may not let you add additional articles in which case you will have to drop existing subscriptions or create a new publication.

You won't necessarily have to take a snapshot for the existing subscriptions even if you do have to drop them, but you take responsibility for keeping the data in sync. You should use triggers or other methods to lock down changes before dropping subscriptions, and recreate them using sp_addsubscription @sync_type='replication support only'; If all subscriptions are created this way, a snapshot will not be generated. If only the new articles are subscribed with @sync_type='automatic' then only those articles will be present in the new snapshot. Afterwards, you should verify data integrity between publisher and subscriber.

Upvotes: 1

Related Questions