Bidirectional replication between on prem SQL server and Azure sql with no primary key exist

Shantanu Dekapurwar 20 Reputation points
2024-05-30T21:51:18.5233333+00:00

Can you suggest possible solution for bidirectional real time sync between on prem SQL (2016) and Azure SQL with no primary key exist

Azure SQL Database
{count} votes

2 answers

Sort by: Most helpful
  1. Alberto Morillo 33,251 Reputation points MVP
    2024-05-30T23:52:37.2666667+00:00

    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.


  2. GeethaThatipatri-MSFT 28,537 Reputation points Microsoft Employee
    2024-05-31T15:53:03.3966667+00:00

    @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

    0 comments No comments