ADF - How should I copy table data from source Azure SQL Database to 6 other Azure SQL Databases?

0
votes

We curate data in the "Dev" Azure SQL Database and then currently use RedGate's Data Compare tool to push up to 6 higher Azure SQL Databases. I am trying to migrate that manual process to ADFv2 and would like to avoid copy/pasting the 10+ copy data actives for each database (x6) to keep it more maintainable for future changes. The static tables have some customization in the copy data activity but the basic idea follows this post to perform an upsert.

How can the implementation described above be done in Azure Data Factory?

I was imagining something like the following:

  1. Using one parameterized link service that has the server name & database name configurable to generate a dynamic connection to Azure SQL Database.
  2. Creating a pipeline for each table's copy data activity.
  3. Creating a master pipeline to then nest each table's pipeline in.
  4. Using variables loop over the different connections an passing those to the sub-pipelines parameters.

Not sure if that is the most efficient plan or even works yet. Other ideas/suggestions?

2
azure-sql-databaseazure-data-factory-2
How many tables do you want to sync to other 6 Azure SQL tables? For example, you have many tables in Azure SQL database A and you want copy it to other 6 Azure SQL databases, am I right? Do you think about Data flow? You can create 6 flows to copy the data and sink has the option "upsert". – Leon Yue
Yup, we can use Dynamic Pipelines - Copy multiple tables in Bulk with 'Lookup' & 'ForEach'. we can perform dynamic copies of your data table lists in bulk within a single pipeline. Lookup returns either the lists of data or first row of data. ForEach - @activity('Azure SQL Table lists').output.value ; @concat(item().TABLE_SCHEMA,'.',item().TABLE_NAME,'.csv') + This is efficient and cost optimized since we are using less number of activities and datasets. – Arulmouzhi
@Arulmouzhi we can not tell you it that's the most efficient plan. But I think so. In usually, we also will choose dynamic parameter/pipeline, lookup + foreach active to achieve the scenario. Just make the pipeline make the pipeline has a strong logic, simple and efficient. – Leon Yue
If my answer is helpful for you, hope you can accept it as answer. This can be beneficial to other community members. Thank you. – Leon Yue

2 Answers

0
votes

we can not tell you if that's the most efficient plan. But I think so. Just make it works.

As you said in the comment:

  • we can use Dynamic Pipelines - Copy multiple tables in Bulk with 'Lookup' & 'ForEach'. we can perform dynamic copies of your data table lists in bulk within a single pipeline. Lookup returns either the lists of data or first row of data. ForEach - @activity('Azure SQL Table lists').output.value ; @concat(item().TABLE_SCHEMA,'.',item().TABLE_NAME,'.csv') + This is efficient and cost optimized since we are using less number of activities and datasets.

In usually, we also will choose same solution with you: dynamic parameter/pipeline, lookup + foreach active to achieve the scenario. In one word, make the pipeline has a strong logic, simple and efficient.

0
votes

Added the same info mentioned in the Comment as Answer.

Yup, we can use Dynamic Pipelines - Copy multiple tables in Bulk with 'Lookup' & 'ForEach'.

We can perform dynamic copies of your data table lists in bulk within a single pipeline. Lookup returns either the lists of data or first row of data.

ForEach - @activity('Azure SQL Table lists').output.value ; @concat(item().TABLE_SCHEMA,'.',item().TABLE_NAME,'.csv')

This is efficient and cost optimized since we are using less number of activities and datasets.

Attached pic as ref- enter image description here