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.
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: