Replication: add a new table with out a new snapshot

Question

Replication: add a new table with out a new snapshot

andreas.kreuzberg

SSCertifiable

Points: 6191
More actions
August 8, 2013 at 4:40 am

#297391

Hello,
i've got a database with 500 GB DATA and 200 GB INDEX. I had to add an article (table) to the replication. I want do avoid, that the replication creates a new snapshot. We need about 7 hours to create the new index in the database.
Is it possible, to add article without a new snapshot. I read something, that only the difference ( in my example: 1 table) is stored in the snapshot.
Publisher: SQL 2008 R2 SP2 / OS Windows Enterprise 2008 48 GB RAM
Subscriber: SQL 2008 R2 SP2 / OS Windows Standards 2008 48 GB RAM
Distribution-Server: SQL 2012 / OS Windows Enterprise 24 GB RAM
create the snapshot needed about 30 Minutes
deliver the snapshot needed about 6,5 Hours
create index neede about 7 Hours
Thanks for you help,
Andreas

Viewing 9 posts - 1 through 8 (of 8 total)

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

sturner SSC-Insane Points: 22411 More actions · Answer 1

Yes, you can set up the replication of the new article without doing a snapshot. Create the table first then populate it yourself, or do it later when you have some maintenance time. You can also back-fill it on the fly in small batches until it is synched up.

The probability of survival is inversely proportional to the angle of arrival.

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 2

Hello,

thanks for your reply, but i don' understand everything.

How can i populate the new table?

And what do you mean with back-fill)

Is it possible, to create the table at the subscriber database manually, and the replication starts itself replication the data from the new table?

I read a lot of the parameter "@sync_method = N'concurrent". Is it possible, to change the replication vom synchron to asynchron modus. And, when i change this parameter, is my new table published without a new snapshot?

(sorry for my englisch, i am not a native englisch speaker)

sqlhuntingdog SSC Veteran Points: 279 More actions · Answer 3

Hi Andreas,

I think adding new Table to existing publication will trigger snapshot.

What about creating another Replication with just a single table? Then snapshot will contain only that table and it would be much faster?

Regards

please use free productivity tool for SSMS - SQL Hunting Dog [/url]

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 4

Hello,

after we had to life without our replication for about 20 hours, we have also considered setting up a second replication for the new table.

But, i think, we need every week 1-2 new tables. It is then somewhat confusing, if we had about 20 replications for one database.

So I am desperately a solution without a new snapshot

😉

sqlhuntingdog SSC Veteran Points: 279 More actions · Answer 5

OK have you looked at this article here:

it says only new table will be added to snapshot

please use free productivity tool for SSMS - SQL Hunting Dog [/url]

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 6

Yep,

i read this page yesterday, when my trouble starts. But is there a difference between putting the new article via Microsoft SQL Server Management Studio or put the new article via script to the publication.

And, is it possible, to create the new table at the subscriber database manually?

sturner SSC-Insane Points: 22411 More actions · Answer 7

Yes it is possible to create the table ahead of time. If your create the subscription you should be offered the option of not creating the snapshot.

The probability of survival is inversely proportional to the angle of arrival.

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 8

Hello,

i found my solution on this page, i hope it is okay to post it in this forum:

http://www.mssqltips.com/sqlservertip/2502/limit-snapshot-size-when-adding-new-article-to-sql-server-replication/

I could test this in my QA and it works.

thanks for you help, please feel free to close this topic.

Kind regards,

Andreas