August 16, 2010 at 3:23 pm
Hi All,
I have a scenario were i'm trying to find the most efficient way to implement a solution.
I have 1 publication with 3 large tables, 1 table requires a raft of updates many millions of rows and new fields added, on my test system it blew up the subscriptions posting all those cmds brings the disks to its knees.
My next thought was to drop the table(article) requiring the updates from the publication, run the updates then add it back, reinitialize with a new snapshot, problem being it would initialize all the articles taking a long time.
Is there a way to only reinitialize 1 article within a publication or would i need to create a new publication and add it to that so i don't effect the others?
Idea's appreciated
August 16, 2010 at 10:01 pm
You could drop the subscription and article on the article that is being used for the table with that you are updating, then apply the same updates and column changes to each database and then create the article for that table. You can then add the subscriptions but use the option that says that the subscriber already has the schema and data for the article. You do not need to apply a new snapshot since you applied the same changes to every database.
Another option is to use the features of replication to do the work for you. When creating the publication, allow schema changes to be replicated. This will automatically replicate ALTER TABLE commands (check BoL for the limitations). If you do the updates using stored procedures, you can get replication to replicate the EXECUTE of the stored procedure instead of the results of the stored procedure (i.e. the EXECUTE instead of the individual insert/update/delete statements).
If you can, I would go for the second option - you don't need to stop replication so the risk of missing a transaction is eliminated. This may require some changes to how you do things but it is a heck of a lot easier to manage.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply