Tuesday, March 6, 2012

Add new article to transactional replication

This one is driving me crazy...
We are using Transactional Replication with the subscribers initialized from
a backup (no snapshot). It's working great, but now we need to add several
new articles to the publication and have them picked up in the subscribers.
The only way I can get this to work is to drop the subscriber, backup the
published database, restore the previously made backup over the destination
databases and then re-create the subscriptions. There has to be a better
way?
I've tried using the sp_reinitializesubscription, but that doesn't work
since we aren't using a snapshot. I've tried skipping the restore, and just
creating the tables in both the source and destination databases. Doing
this, the subscription is re-created sucessfully, but data in the new
articles is not actually being replicated.
Replication isn't my forte, and the documentation on initializing with
backups is pretty sparse, so any advice is appreciated.
Use sp_addarticle followed by sp_addsubscription with @.sync_type =
automatic.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

No comments:

Post a Comment