Need to replicate Azure SQL DB source tables (more than one) to an Azure Managed SQL Instance using ADF

Question

Need to replicate Azure SQL DB source tables (more than one) to an Azure Managed SQL Instance using ADF

Ashwani SRIVASTAVA 60

I am looking to actively replicate SQL tables from an Azure SQL database to Managed Instance SQL database. The replication needs to be ongoing for all the future changes. For this, I have enabled CDC on multiple SQL tables in Azure SQL db. Is there a way to setup replication using ADF pipelines?

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-01-12T07:10:59.98+00:00
@Ashwani SRIVASTAVA

Thanks for reaching MS Q&A

You can use the Azure Data Factory pipeline to copy data from Azure SQL Managed Instance DB to Azure SQL Server DB.

Create Azure data factoryresource.

Create Azure SQL DB linked service for source dataset connection.

Provide your Azure SQL database details in the connection.

Create__Azure SQL Managed Instance linked service__ for sink dataset connection.

Provide Azure subscription and SQL Managed instance details in linked service connection.

You can select different types of authentication methods in the connection.

Create a new pipeline and select Copy data activity from Activities. Create Source and sink datasets with database table details in Copy data activity and select source linked service and sink linked service respectively in Source and Sink settings.

You can go through these MS documents for more details on Source and Sink dataset.

Hope this helps. Do let us know if you any further queries.
Ashwani SRIVASTAVA 60 Reputation points

2024-01-12T07:31:27.2233333+00:00
thanks @phemanth
I am actually referring to https://learn.microsoft.com/en-us/answers/questions/542408/how-to-migrate-cdc-changes-of-multiple-tables-to-a blog and setup the pipeline. But this blog I think replicate cdc information data (e.g. lsn, data change etc) but does not replicate the data itself. Please correct me if I am wrong.

I am also setup a Data Flow with CDC option (screenshot below) to read data from source Azure SQL database and then using sink to replicate to Managed SQL Instance. I am calling this "Data Flow" from a pipeline by using below steps:

"Lookup" step to query from a table that has details about tables to replicate e.g. source tableName, target tableName

"For Each Loop" read data from step 1 and call above "Data Flow" for each table need to be replicated.

And this pipeline does only replicate data for the first table only even ForEachLoop runs for same number of times as records found in step 1. I have done some googling and found that it could due to checkpoint key however I could not find any reference as how it can be managed dynamically.

Checkpoint key:
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-01-17T08:31:48.5033333+00:00

@Ashwani SRIVASTAVA We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-01-18T07:00:13.5333333+00:00

@Ashwani SRIVASTAVA just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Accepted answer

0 additional answers

Your answer

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-01-12T07:10:59.98+00:00

@Ashwani SRIVASTAVA

Thanks for reaching MS Q&A

You can use the Azure Data Factory pipeline to copy data from Azure SQL Managed Instance DB to Azure SQL Server DB.

Create Azure data factoryresource.

Create Azure SQL DB linked service for source dataset connection.

Provide your Azure SQL database details in the connection.

Create__Azure SQL Managed Instance linked service__ for sink dataset connection.

Provide Azure subscription and SQL Managed instance details in linked service connection.

You can select different types of authentication methods in the connection.

Create a new pipeline and select Copy data activity from Activities. Create Source and sink datasets with database table details in Copy data activity and select source linked service and sink linked service respectively in Source and Sink settings.

You can go through these MS documents for more details on Source and Sink dataset.

Hope this helps. Do let us know if you any further queries.
Ashwani SRIVASTAVA 60 Reputation points

2024-01-12T07:31:27.2233333+00:00

thanks @phemanth
I am actually referring to https://learn.microsoft.com/en-us/answers/questions/542408/how-to-migrate-cdc-changes-of-multiple-tables-to-a blog and setup the pipeline. But this blog I think replicate cdc information data (e.g. lsn, data change etc) but does not replicate the data itself. Please correct me if I am wrong.

I am also setup a Data Flow with CDC option (screenshot below) to read data from source Azure SQL database and then using sink to replicate to Managed SQL Instance. I am calling this "Data Flow" from a pipeline by using below steps:

"Lookup" step to query from a table that has details about tables to replicate e.g. source tableName, target tableName

"For Each Loop" read data from step 1 and call above "Data Flow" for each table need to be replicated.

And this pipeline does only replicate data for the first table only even ForEachLoop runs for same number of times as records found in step 1. I have done some googling and found that it could due to checkpoint key however I could not find any reference as how it can be managed dynamically.

Checkpoint key:
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-01-17T08:31:48.5033333+00:00

@Ashwani SRIVASTAVA We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-01-18T07:00:13.5333333+00:00

@Ashwani SRIVASTAVA just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

@Ashwani SRIVASTAVA
You’re correct that the blog you’re referring to is about replicating CDC information data (e.g., lsn, data change, etc.) but not the data itself. The blog post describes how to migrate the changes made on CDC enabled tables to another Azure SQL DB using ADF pipelines.

Regarding your issue with the “ForEach Loop” only replicating data for the first table, it could indeed be due to the checkpoint key. The checkpoint key is used to set the checkpoint when a data flow is used for change data capture.

In Azure Data Factory, when you enable native change data capture or auto incremental extraction options in mapping data flow, ADF helps you manage the checkpoint to ensure each activity run will automatically only read the source data that has changed since the last time the pipeline ran.

The default checkpoint key is a hash of the data flow name and the pipeline name. If you’re using a dynamic pattern for your source tables or folders, you may wish to override this hash and set your own checkpoint key value.

For example, if you’re using a parameter in the data flow, you can set the Upsert_Key_Column parameter in the data flow to @item().UpsertKeyColumn. Then, in the Sink settings, select Custom Expression for Key columns and enter the following expression: split($upsert_key_column,',').

Ashwani SRIVASTAVA 60 Reputation points

2024-01-18T07:18:01.4633333+00:00

Hi @phemanth - Yes, I have setup the replication successfully now by introducing dynamic checkpoint key for each table required for replication.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-01-18T14:05:05.2166667+00:00

@Ashwani SRIVASTAVA Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

Need to replicate Azure SQL DB source tables (more than one) to an Azure Managed SQL Instance using ADF

0 additional answers

Your answer