This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(102.4, 57.00000000000001%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESD%3C/text%3E%3C/svg%3E)
Can you suggest possible solution for bidirectional real time sync between on prem SQL (2016) and Azure SQL with no primary key exist
@Shantanu Dekapurwar Thanks for posting your question on Microsoft Q&A
Azure SQL Database supports one-way transactional or snapshot replication as a push subscriber, but it does not support bidirectional, immediate, updatable, or peer-to-peer replication.
https://learn.microsoft.com/en-us/azure/azure-sql/database/replication-to-sql-database?view=azuresql
For bidirectional transactional replication, a sample script for creating a Bidirectional Transactional Replication between two Managed Instances is available
Please check the blog here https://techcommunity.microsoft.com/t5/azure-database-support-blog/bidirectional-transactional-replication-in-sql-managed-instance/ba-p/3584998
Regards
Geetha
This third-party tool named SymmetricDS may help you in this task.
Another option is to use Change Data Capture feature on SQL Server. This SQL Server feature tracks insert, update, and delete operations on a table and can be used to replicate changes to Azure SQL Database.
Does this third-party tool support replication without primary key?
Can CDC be implemented in Azure sql to On-prem without Primary key?
SymmetricDS does not require a primary key but you need to specify Sync Key Names. With Sync Key Names you specify a comma-delimited list of columns that should be used as the key for synchronization operations. By default, if not specified, then the primary key of the table will be used.
CDC can work without a PK. If there is no primary key, CDC requires a unique index to function properly because it needs a way to uniquely identify rows. CDC can be used on both Azure SQL and SQL Server.
@Shantanu Dekapurwar Adding to @Alberto Morillo You can also try other options, I would suggest trying to get it working with Azure Data Factory Mapping Data Flow. Mapping data flows - Azure Data Factory | Microsoft Learn
It also works for On-Prem, but extra steps are required: Access on-premises SQL Server from Data Factory Managed VNet using Private Endpoint - Azure Data Factory | Microsoft Learn
Another option which would offer better customization (but more effort) would be leveraging Azure Functions.
Regards
Geetha