Question about replicating stored procedure execution - 02-09-2006 , 06:55 PM
I am doing some research in advance of setting up replication. I've
read about replicating stored procedure execution and I need
clarification on what it is exactly.
My understanding is that, for example, if you have a stored procedure
on the publisher that modifies 10,000 rows then replication will not
replicate the 10,000 modifications to the subscriber but will rather
pass along the execute command which will then be executed locally at
the subscriber. Do I have that correct?
Is this a common practice in a replication scenario?
Re: Question about replicating stored procedure execution - 02-09-2006 , 07:32 PM
Exactly. Its not commonly used because most people don't know about it, and
secondly sometimes the tables referenced by the stored procedures are
different on the publisher and subscriber and therefore you get different
dml on either side.
Other people want the proc run within a serializable transaction for
consistency, which is another option. Is the proc is not run within a
serializable transaction, the DML is replicated instead of the proc.
Looking for a SQL Server replication book?
Looking for a FAQ on Indexing Services/SQL FTS
<pshroads (AT) gmail (DOT) com> wrote
RE: Question about replicating stored procedure execution - 02-10-2006 , 02:22 PM
I have tried this feature and worked OK. I have a nightly purge job that
delete lots of data and the processes can be separately on each server (it is
based on creation timestamp of the rows, which was replicated). My
situation, this worked well. I think the serialization consistency will only
gnerate more locks.
However, I ran into problem when try to verify the replication. It treated
the SP as a table and verification failed. Try it, see if you got the same
error. Thk !!