Why my p2p+transaction can't only replicate sp exec?

Category: sql server replication


waterfa on Mon, 05 Jan 2015 16:38:32

I have P2P with 3 nodes(A,B,C) , and hope to update lots of data at A,B,C, so I setup one transaction replication (one way) on A, and choose only replicate proc execution (not choose those based tables here) . When I run "exec proc" at A I can see same "exec proc" running at B and C, but after running done, the A node undelivered cmds show all cmds are waitting to deliver... (I use browsereplcmd to get the cmds are exactly contents of the stored proc! )  and then I have to do much clean up jobs.

Can I avoid such loop back replication? Thanks in advance!    version: sql 2012 


Hilary Cotter on Mon, 05 Jan 2015 21:44:31

I tried this out on SQL 2014 and I did not see the behavior you are talking about. I ran a trace on all nodes in my topology (there were 3) and saw the proc execute one time on all three nodes.

I suspect there is something else you are seeing here.

waterfa on Tue, 06 Jan 2015 15:06:03

Thanks, Hilary, one of nodes edition is 2215 and others are SP2, maybe it's low version issue. So your transaction replication  is only setup at one node and only with sp without based table, right?

Hilary Cotter on Tue, 06 Jan 2015 15:24:49

I did it on a SQL 2014 topology. I doubt it is a version issue but will try to cehck.

I setup p2p on one node and then added 2 more nodes to my p2p topology, so data origianting on a would move to b and c. Data originating a c would move to b and a.

I had one table and one sp. I replicated the table and the sp.

What does your configuration look like?

waterfa on Thu, 08 Jan 2015 06:32:49

Hey Hilary,

My configuration is similar to you, about "I had one table and one sp. I replicated the table and the sp." part, I have 2 replication, the table is only in P2p, and the sp is only in the transaction repl without updatable subscriber. Is that same for you too?


Hilary Cotter on Fri, 09 Jan 2015 13:33:58

Now you are using a different term "updateable subscriber". This normally refers to updateable subscribers which is not peer to peer replication.  Can you confirm your replication type?

If you are using p2p end to end - then we both have the same configuration only I tested on 3 nodes running SQL Server 2014.

waterfa on Sat, 07 Feb 2015 15:05:07

Yes, Hilary, I'm using P2P, thanks for your help, and I believe it's my sql different version's issue, I'll consider other methods.