Search code examples
sql-serverazureazure-sql-database

How to configure "table replication" between 2 SQL Azure databases without any external component or service?

I have 2 SQL Azure databases (vCore). I want to configure table replication as follows:

  • Snapshot (Master tables) scheduled for once a day
  • Realtime replication (Transactional tables) real-time replication

https://learn.microsoft.com/en-us/azure/azure-sql/database/replication-to-sql-database?view=azuresql

This article I found says that SQL Azure can only be the subscriber and not the distributor.

Is it possible to do this in SQL Azure? Please help.

Thank you

Solution

  • As per this Azure Sql database works as subscriber for transactional and snapshot replication. So, it is not possible for Azure SQL database, but if you want to perform above replication you should use Azure SQL managed instance otherwise you should use third party components like Synapse SQL link. Link SQL database to the synapse workspace and do above replications. Synapse Link: On the set system assigned managed identity to on in sql server:

    enter image description here

    Login to SQL database with administrator authentication, add user to synapse workspace and add roles using below code:

    CREATE USER <workspaceName> FROM EXTERNAL PROVIDER;
ALTER ROLE [db_owner] ADD MEMBER <workspaceName>;

    Go to synapse workspace Go to Integrate click on + select Link Connection

    Create linked service of SQL database and select that as source select the required tables:

    enter image description here

    Select target pool

    enter image description here

    Provide a name for link connection and compute the settings

    enter image description here

    Click on Ok, you can set target table names and distributions

    enter image description here

    Start the link connection. you can use this to replications for more information you can refer this) .