Share via

How to continuous replicate data from Azure Synapse Dedicated SQL pool (Source) to Azure SQL DB(Target). Both are hosted on the same SQL server.

Nitin Goyal 20 Reputation points
2023-01-17T05:55:27.5366667+00:00

Current Setup:

Within Synapse Analytics - ADF pipelines are used for data ingestion, dedicated SQL pool is used for processing (Stored procedures), storing and serving as reporting layer to external service PowerBI.

Problem: Overall Performance degrade when external service like PowerBI refresh occur in parallel to ADF pipelines run for Data integration on Synapse.

Solution Looking for: Off-load write-intensive (On Synapse Dedicated SQL pool) workload to another replica/Secondary DB (May be on Azure SQL DB) hosted on the same server where Dedicated SQL pool is hostedv. Doing so replica/secondary DB on Azure SQL will serve as new Reporting layer and external services can connect to Azure SQL DB instead of Dedicated SQL pool. What is the best way to continuous data replication/Data Sync from Dedicated SQL pool to Azure SQL DB.

Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,696 questions
Accepted answer
  1. HimanshuSinha-msft 19,386 Reputation points Microsoft Employee
    2023-01-25T21:25:16.7133333+00:00

    Hello @Nitin Goyal ,
    Thanks for the question and using MS Q&A platform.

    I think the initial ask is how to make sure that when the data load is in progress other process ( e.g PowerBI refresh) does not get impacted. I know you are inclined to have READ ONLY replica as we had with in-premise SQL Server and as correctly pointed we do not have that in the current dedicated SQL.
    I wanted to know your thoughts if you want to scale-up the compute of dedicated sql just before the load starts and scale down once the ingestion is over. Since you are already using a ADF you can call a procedure and add the logic called out here https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/quickstart-scale-compute-tsql and scale down once the load is done with the current values.

    If this approach works, it will be cost effective then the below approach.

    Since you are already using the ADF to pump data in dedicated SQL maybe you can use ADF to pump data to a new SQL Azure also.

    Thanks
    Himanshu

    Please accept as "Yes" if the answer provided is useful , so that you can help others in the community looking for remediation for similar issues. 

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Pratik Somaiya 4,206 Reputation points
    2023-01-17T07:31:48.77+00:00

    Hello @Nitin Goyal

    I understand the issue and it is good to use a different db for reporting

    To keep Azure SQL DB in-sync with Synapse Dedicated Pool I will recommend to use ADF as Azure Sync currently does not support Synapse as a source, it supports on-premises SQL Server

    There is a third party application as well but I haven't used it yet: https://www.cdata.com/kb/tech/azuresynapse-sync-azure.rst

    0 comments