August 21, 2006 at 12:51 am
Hi All,
I am in the process of setting up a replication solution from our operations DB into a Warehouse DB. So far i have configured about 15 tables for replication, with the largest table being 12.5 Million rows. This table took quite a while o replicate, and becasue we run a 24/7 business, there were issues with users experiencing slow peformance.
I need to replicate a table that has 49 million rows. Is there another way to get the table over to the Warehouse server using a backup, and then start the replication to carry on from there?
August 21, 2006 at 4:01 am
Gavin
Yes, there is. Script out the articles you have already created, drop the subscription, restore a backup of the publication database to the subscriber, recreate the subscription using sp_addsubscription with @sync_type = 'none', and recreate the articles from your scripts. Finally, create a new article for the large table.
I would recommend you try this on a test server first with a cut-down version of your data if you can, and back up everything before you start in the live environment.
John
August 21, 2006 at 4:09 am
Thanks for the Info John, i will give that a try.
After testing this scenario on the test box, I am going to create a separate publication for this large table to ease administration.
Thanks again!
August 21, 2006 at 12:55 pm
Hey John
why do you even need to script out the articles when you are restoring the PUB db on the SUB? You can just restore the db on subscriber and drop the constraints and proceed with setting up the publication.
There are some tricky issues with IDENTITY columns though.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
August 22, 2006 at 1:38 am
Dinakar
The way I understood it, Gavin already has a publication set up and wishes to add another article by restoring the database. Therefore, I think the safest way is to script out what he already has, restore the database to the subscriber, and start again with a new subscription.
John
August 22, 2006 at 1:43 am
Guys,
What i actually did was drop all my articles and publication because there was data inconsistency across the board. I recreated the entire replication process, with separate publications containing articles that were similar in name (POP tables, IV, RM etc..)
I firstly wanted to keep the existing publication and add the large tables, but after seeing the data issues, i decided to start from 'scratch'
I am happy to say that replication is now working 100%
Thanks for all of you help and comments!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply