April 10, 2008 at 8:31 am
A question for all you replication gurus...
I have a transactional replication publication running on a 2k5 SQL Server. If I issue an Update statement on my publisher like
UPDATE A
SET A.column = X.column
FROM replicatedtable A
JOIN ##temptable X
ON A.Id = X.Id
How do these updates get applied at the subscriber? I have some large updates to do and I'm wondering if I should A) Generate the SQL Updates and Inserts and issue them directly against the publisher (100,000 individual update statements - currently takes about 20 minutes) or B) Insert into a temporary table and perform a set based update on the publisher. It takes far less time, but if the distributor is going to push these out as individual updates anyway then there isn't much point?
Any thoughts gratefully recieved
Kindest Regards,
Frank Bazan
April 10, 2008 at 10:07 am
By default, transactional replication creates a bunch of stored procedures on the subscriber that handle your inserts, updates, and deletes. Feel free to go look at them.
The default behavior is to replicate one row at a time. So, if you run an update that modifies 1000 records on your publisher, replication will run the update procedure 1000 times on each subscriber (handling transactions properly of course).
This behavior can be changed in a couple of ways. For instance, you can specify that stored procedure execution be replicated by running the same procedure with the same parameters on the subscribers.
If you intend to diverge from the default behavior - be careful and do a bunch of research first. By default, replication is pretty reliable. It may not be super-efficient, but it is stable, error reporting works, everything stays in sync, etc. Moving off of the defaults opens you up to lots of potential issues if you are not careful. It can be done without causing problems, but watch your step and don't assume anything.
April 11, 2008 at 2:32 am
Thanks Michael,
I did some tests and the performance of the update on the subscriber doesn't appear to be significantly different regardless of how I perform the update on the publisher, so I will stick with generating individual inserts and updates.
Cheers
Kindest Regards,
Frank Bazan
April 14, 2008 at 9:12 am
the replication Distribution Agent (DA) has to faithfully "replay" at the sub whatever happened on the pub. This means it has to do a BEGIN TRAN ... stuff ... COMMIT and will bulk out the tranlog at the sub just like on the pub. If the whole transaction fails (eg the 999th I/U/D out of 1000 of "stuff") it will rollback and try again later [hopefully DBA has upp'ed the AutoGrow limits to allow growth to fit it all in by then!].
there are some considerations on the pub [particularly] related to triggers ..
[AFTER] triggers will fire once per statement, so you put the 100K rows into ##temptable and do ONE UPDATE then the trigger will fire ONCE
most developers are celebrally challenged to write fully functional triggers, so caveat DBA !
- and the debate [elsewhere please!] about [evil] cursors is yet another related weak point
HTH
Dick
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply