Thursday, March 8, 2012

bi directional transactional replication and subscriber name

Hi,
I am trying to setup bi-directional transactional replication between 2 SQL Servers,
I add the subscription on both the servers using the code below.

EXEC sp_addsubscription @.publication = N'test',
@.article = N'all', @.subscriber = 'AnyServer',
@.destination_db = N'test', @.sync_type = N'none',
@.status = N'active', @.update_mode = N'read only',
@.loopback_detection = 'true'
GO

I have defined 'AnyServer' on both the servers using cliconfg
(Server1's AnyServer pointed towards Server2 and Server2's AnyServer pointed towards Server1, I need to do that because there is a restriction to run the same code on both the servers),

After inserting a record on server1 in the 'test' database, the changes successfully transfers to the server2, then server2 sends it back to server1 and server1 generates the error of

"Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'Table1'."

It seems as if loop detection is failing if I keep the same subscriber name on both sides.

It runs fine when I change the subscriber name in the subscription (@.subscriber = 'Server2' for server1 and @.subscriber = 'Server1' for server2).

Can anybody explains this behavior to me?not really , but common sense says that if you try & create a duplicate primary key on the first server, it MUST fail to preserve PK integrity...|||Let me rephrase my question.....

Why bi-directional replication is failing with the same subscriber name (managed by cliconfg) on both sides.... when it is running fine with the real server names?

No comments:

Post a Comment