Primary Key Violation in Bidirectional Replication between SQL Managed Instance and SQL server on VM

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Bidirectional transnational replication is a specific transnational replication topology that allows two servers to exchange changes with each other: each server publishes data and then subscribes to a publication with the same data from the other server.

 

The steps to create a Bi-directional replication is simple, and similar to the steps for configuring transnational replication with extra step to enable the @loopback_detection parameter of sp_addsubscription to ensure that changes are only sent to the Subscriber and do not result in the change being sent back to the Publisher.

 

The most common issue for the Bi-directional replication is when the loop back detection is not working as expected; which results in data conflicts and Primary Key Violations.

 

Error011.png

 

In order to ensure that loop back detection will work probably, we need to configure both publishers and subscribers using the exact local server names, you can use the below query to find the server name:

 

SELECT @@SERVERNAME AS 'Server Name'  

 

In SQL Managed Instance, the @@SERVERNAME will return the private FQDN by default, but in SQL Servers installed in Azure Virtual Machines or on-premises, it will return the local server name without the domain name.

 

As an example:

 

From SQL Managed instance to SQL Server on VM:

EXEC sp_adddistributor @distributor = @@servername

EXEC sp_adddistpublisher @publisher = @@servername

.

.

.

exec sp_addpushsubscription_agent @publication = N'Publication_Tran',

       @subscriber = ‘servername.xxxx.com’

 

The domain name after machine name need to be added to mach the DNS name, otherwise replication will fail stating that « could not connect to subscriber ».

 

From the SQL Server on VM to SQL Managed Instance:

Select @@servername returns : servername without  xxxx.com

 

 

The fix for this issue was to change the SQL server name (on Azure VM) from servername to servername.xxxx.com following the steps in this article: https://blog.sqlauthority.com/2015/07/13/sql-server-how-to-change-server-name/

 

Thank You!

 

References:

https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/bidirectional-transactional-replication?view=sql-server-ver15

https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-two-instances-and-sql-server-configure-tutorial

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.