October 24, 2014 at 9:59 am
I have a fairly large database (over 400GBs) that is being replicated. There is a new table that has 90m records that I want to add to replication. Can I do a backup and restore of that table only? I tried adding the one new article and taking a snapshot of that article only but it slowed my replication to a halt due to the massive size of the table. I had to break replication and do a backup and restore of the entire database.
Going forward, can I just copy the table to the Subscriber then add the table to the article without taking a snapshot? This way, it will only replicate new transactions going forward?
Is there a better method?
October 24, 2014 at 11:54 am
It's easier to create a new publication and subscription.
But of course you are limited if it is referenced by a foreign key constraint.
October 27, 2014 at 8:00 am
Would the first option work? Just copy the entire table to the subscriber, add the article to the current publication, but do not start the snapshot agent?
October 27, 2014 at 9:42 pm
alhajirr (10/27/2014)
Would the first option work? Just copy the entire table to the subscriber, add the article to the current publication, but do not start the snapshot agent?
When you add a subscription, you have a few options about what to do on the subscriber such as do nothing ("replication support only" is the actual value specified). Have a look at the values you can specify for the @sync_type parameter for sproc sp_addsubscription (http://msdn.microsoft.com/en-us/library/ms181702(v=sql.100).aspx has all the details on this sproc)
If you have loaded the data into the subscriber for the table you are concerned about (through whatever means) and you are sure that all data that you need had been loaded (i.e. no new transactions are happening on the publisher whilst you are loading the subscriber), then choose the "replication support only" option when adding the new subscription.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply