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

Question

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?

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

Answer 1

Hey @HemantSudehely-0557

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

On a side note, are all the 90 databases being migrated to the same domain and Vnet IaaS VMs?

If yes, you can leverage the same Self hosted IR for connecting to every database rather than having seperate IRs fir each database.

And w.r.t key vault parameterization, it can be done by Advanced section as seen below:

Normal :

Advanced :

{  
    "properties": {  
        "type": "Sftp",  
        "annotations": [],  
        "parameters": {  
            "Host": {  
                "type": "string",  
                "defaultValue": ""  
            },  
            "Port": {  
                "type": "string",  
                "defaultValue": ""  
            }  
        },  
        "typeProperties": {  
            "host": "@linkedService().Host",  
            "port": "@linkedService().Port",  
            "skipHostKeyValidation": true,  
            "authenticationType": "Basic",  
            "userName": "test",  
            "password": {  
                "type": "AzureKeyVaultSecret",  
                "store": {  
                    "referenceName": "LS_AzureKeyvault_AEAP_Core",  
                    "type": "LinkedServiceReference"  
                },  
                "secretName": "SFTPPassword"  
            }  
        },  
        "connectVia": {  
            "referenceName": "IR-",  
            "type": "IntegrationRuntimeReference"  
        }  
    }  
}  

Note: This is what we had done in past year. You can try experimenting whether you can parameterize the IR as well via this method :)

Answer 2

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?

Answer 3

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

Answer 4

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