Need help on parameterization of Integration Runtime and Azure Key vault Secrets on link service in Azure Data Factory pipeline

HemantSudehely-0557 246 Reputation points
2020-12-29T17:41:43.283+00:00

Hi,

I have to migrate 90 clients database from Azure Managed Instances databases to Azure VM Data Servers.
For the above requirement, I have created the below resources
* 90 Azure Data Factory Pipelines
* 90 self-hosted integration runtime as each target data servers database(SHIR only support max 4 nodes)
* 90 stored procedure to transform data
* 180 Keyvoalt secrets for (90 source connection string, 90 target connection string)

Now all this seems to be so much setup for managing this huge migration.

Can you please suggest if there is a way to parameterize, self-hosted integration Runtime(SHIR) and key vault Secrets(Connection Strings) while triggering pipelines? this way we just need to manage one pipeline for all migration?

Any suggestion or reference will be a great help?

52037-image.png

These are activities, we are performing in each pipeline!
51967-image.png

Azure Key Vault
Azure Key Vault

An Azure service that is used to manage and protect cryptographic keys and other secrets used by cloud apps and services.

Azure Database Migration Service
Azure Data Factory
Azure Data Factory

An Azure service for ingesting, preparing, and transforming data at scale.

SQL Server Integration Services
0 comments

4 answers

Sort by: Newest
Most helpful Newest Oldest
  1. HemantSudehely-0557 246 Reputation points
    2020-12-31T14:52:23.753+00:00

    Thanks, Nandan...

    To answer your question, yes, all target database servers are in the same domain but each database is hosted on a different data server VM's, and SHIR has a limitation of max 4 nodes or connection can be used by the same SSIR whereas only 2 nodes of the same SHIR working fine, with adding 3 or 4 nodes causing the performance issue.

    hence decide to use 1 SHIR connection for two data servers and create 45 SSIR for 90 data servers.

    Thanks for the reference JSON and below confirmation...

    As of now, it is not possible to parameterize the Integration run time :
    https://github.com/MicrosoftDocs/azure-docs/issues/40699

    Was this answer helpful?

  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. HemantSudehely-0557 246 Reputation points
    2020-12-30T16:39:24.943+00:00

    Hi Mona,

    This looks like a partial answer.
    My problem is, I have 90 Self-hosted integration runtime(SHIR) and similar key vaults and causing a manageability issue.
    if there is a way to parameterized SHIR and Keyvault, then it will reduce huge efforts and we can easily manage 90 migration by one pipeline only. so suggest

    52318-image.png

    Was this answer helpful?

    0 comments
  4. Monalv-MSFT 5,926 Reputation points
    2020-12-30T02:46:54.297+00:00

    Hi @HemantSudehely-0557 ,

    You can now parameterize a linked service and pass dynamic values at run time. For example, if you want to connect to different databases on the same logical SQL server, you can now parameterize the database name in the linked service definition. This prevents you from having to create a linked service for each database on the logical SQL server. You can parameterize other properties in the linked service definition as well - for example, User name.

    You can use the Data Factory UI in the Azure portal or a programming interface to parameterize linked services.

    Please refer to Parameterize linked services in Azure Data Factory.

    Best Regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues in November--What can I do if my transaction log is full?
    Hot issues in November--How to convert Profiler trace into a SQL Server table?

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.