Transactional replication: how to replicate new objects without re-creating replication?

Question

Post reply

Transactional replication: how to replicate new objects without re-creating replication?

SQL Guy 1

SSCoach

Points: 15979
More actions
July 23, 2014 at 9:36 am

#296733

We have a transactional replication for a database with size 220 GB. It was created with backup/restore method.
Sometimes developers make new DDL changes. For ALTER (object), replication perfectly reproduces them on subscriber. However it does not work for CREATE (object).
I tried to add article by EXEC sp_addarticle, it really created an article for a new table, I can check for it in sysarticles, but that was all, it's not reproduced on the subscriber. What else should I do to bring it to subscriber?
Dropping and recreating replication is not an option, backup/restore takes more than 1 hour, users won't accept it.
Any suggestions?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 737868 More actions · Answer 1

I believe that adding a new article requires a new snapshot of the publication.

http://msdn.microsoft.com/en-us/library/ms152493.aspx

Alternatively, you can create a new publication for the new object. There's nothing wrong with this, though it can be administratively a hassle.

I did see a note on StackExchange that said you can add an object to a publication and ignore the reinitialization (new shapshot) if you don't have data. Not sure if that works for you, but I suspect it's possible.

Here's a better view of the process: http://saveadba.blogspot.com/2011/08/adding-new-article-without-generating.html

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

You can add a new article and get it sync'd without a full snapshot.

Here is an article on how to do it.

http://ansqldba.blogspot.com/2012/02/adding-new-article-to-existing.html

The key settings are

allow_anonymous

immediate_sync

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 737868 More actions · Answer 3

A few people have mentioned that a new article should only snapshot that article. Looking for references.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

That link I posted goes through the settings to change to make that happen. The default behavior is a snapshot of the entire publication.

Changes to those settings snaps only the newly added articles.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 5

Thanks Jason, that's good article. I was able to add a table to replication even though my subscription was initially synchronized from a backup, not snapshot.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Good to hear.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Transactional replication: how to replicate new objects without re-creating replication?

Cookies on SQLServerCentral