July 15, 2009 at 6:27 pm
DB Experts,
Need help on something. We have Transactional replication setup at my organization that basically replicates data from our Production OLTP database to 4 read-only servers. Our Production OLTP DB is 300 GB in size.
Last week we had a release where we updated 4 million records in a table that was being replicated and this blew up the replication. We had to then do a re-snapshot and do the re-initialization of the replication to get this replication back up and running. However this took us almost a day. Since the db size is 300GB i think this caused the replication re-snapshot to take a long time.
How do we handle such scenarios wherein we have to do mass updates to a replicated table? I assume we do the update in batches but is there any other setting at the replication level like increasing the timeouts etc.
I am also finding on a solution to re-initializing the subscribtion without having to do a snapshot of the entire table, this could mean doing a quick copy of the MDF, LDF files to the subscriber using some kind of OS mirroring. What are the pitfalls in this approach?
Any ideas, solutions will be greatly appreciated.
Thanks,
Amol
Amol Naik
July 21, 2009 at 12:26 pm
What actually "blew up" with respect to the replication? I would suspect you either had storage or network latency issues that resulted in an apparent failure (network) or actual failure (storage). As I'm sure you already know, if you update 4 million records, all those records get flagged for replication in the publisher transaction log and will not be removed (even with transaction log backups) until the records are applied at the subscribers. Could you be having transaction log-related disk space issues on the publisher? Another thing to consider would be the schedule used by the distribution agent for the 4 subscribers. If each subscriber is trying to pull those 4 million updates at the same time I would expect you to notice some network i/o latency. You may want to write a script that does the following:
1. Change the schedule to on-demand right before you do your large update
2. Update your 4 million records
3. Update each subscriber, one at a time
4. Change your schedule back to run continuously
I've assumed a few things about your environment in this response. Hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply