Lift and shift SSIS packages to Azure

This article is about moving packages deployed in On-premises SQL Server to Azure SQL database.

Once packages deployed to cloud, managing and monitoring them is similar how we do from Integration Services Catalog.

Azure SSIS Integration Runtime engine, introduced as part of Azure Data Factory version 2 will be used to execute SSIS packages on cloud.

To provision the SSISDB in Azure and Azure SSIS Integration runtime:

We can do this by using Powershell commands.

Before this, we need to create Azure SQL database & server with firewall rules enabled to connect from SSMS 17 and we need to create Azure data factory with version V2.

It is better to keep the database server in the same Azure region as the integration runtime.

Login to Azure account:

Login-AzureRmAccount

Specify values to variables:

# Azure Data Factory version 2 information 
$ResourceGroupName = "Azure resource group name"
# Data factory name. Must be globally unique
$DataFactoryName = "Data factory name" 


# Azure-SSIS integration runtime information. This is a Data Factory compute resource for running SSIS packages
$AzureSSISName = "Specify a name for your Azure-SSIS IR"
$AzureSSISDescription = "Specify description for your Azure-SSIS IR"
$AzureSSISLocation = "Location" 
# In public preview, only Standard_A4_v2, Standard_A8_v2, Standard_D1_v2, Standard_D2_v2, Standard_D3_v2, Standard_D4_v2 are supported
$AzureSSISNodeSize = "Standard_A4_v2"
# In public preview, only 1-10 nodes are supported.
$AzureSSISNodeNumber = 2 
# In public preview, only 1-8 parallel executions per node are supported.
$AzureSSISMaxParallelExecutionsPerNode = 2 

# SSISDB info
$SSISDBServerEndpoint = "Azure SQL server name.database.windows.net"
$SSISDBServerAdminUserName = "Azure SQL server - user name"
$SSISDBServerAdminPassword = "Azure SQL server - user password"
# Remove the SSISDBPricingTier variable if you are using Azure SQL Managed Instance (private preview)
# This parameter applies only to Azure SQL Database. For the basic pricing tier, specify "Basic", not "B". For standard tiers, specify "S0", "S1", "S2", 'S3", etc.
$SSISDBPricingTier = "pricing tier of your Azure SQL server. Examples: Basic, S0, S1, S2, S3, etc."

Create an integration runtime:

$secpasswd = ConvertTo-SecureString $SSISDBServerAdminPassword -AsPlainText -Force
$serverCreds = New-Object System.Management.Automation.PSCredential($SSISDBServerAdminUserName, $secpasswd)
Set-AzureRmDataFactoryV2IntegrationRuntime  -ResourceGroupName $ResourceGroupName `
                                            -DataFactoryName $DataFactoryName `
                                            -Name $AzureSSISName `
                                            -Type Managed `
                                            -CatalogServerEndpoint $SSISDBServerEndpoint `
                                            -CatalogAdminCredential $serverCreds `
                                            -CatalogPricingTier $SSISDBPricingTier `
                                            -Description $AzureSSISDescription `
                                            -Location $AzureSSISLocation `
                                            -NodeSize $AzureSSISNodeSize `
                                            -NodeCount $AzureSSISNodeNumber `
                                            -MaxParallelExecutionsPerNode $AzureSSISMaxParallelExecutionsPerNode

Start integration runtime:

write-host("##### Starting your Azure-SSIS integration runtime. This command takes 20 to 30 minutes to complete. #####")
Start-AzureRmDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
                                             -DataFactoryName $DataFactoryName `
                                             -Name $AzureSSISName `
                                             -Force

write-host("##### Completed #####")

Deploying and managing SSIS packages in Azure:

Before connecting to Azure SQL database server from SSMS17, click on Options and enter the database name as SSISDB

To deploy the packages stored in On-premise to Azure SQL database Integration services catalog

To execute package and see the execution report, right-click on package from Integration Services Catalogs

Scheduling packages in Azure:

We can schedule the execution of packages stored in the SSISDB Catalog database on an Azure SQL Database server by choosing one of the following scheduling options: 

SQL Server Agent

SQL Database elastic jobs

The Azure Data Factory SQL Server Stored Procedure activity

To manage & manage Azure SSIS Integration runtime:

# To get status of Azure - SSIS IR
Get-AzureRmDataFactoryV2IntegrationRuntime -DataFactoryName MyDataFactory -ResourceGroupName MyResourceGroup -Name MyAzureIR -Status
# To stop Azure - SSIS IR, we cannot delete Azure data factory without stopping Azure - SSIS IR 
Stop-AzureRmDataFactoryV2IntegrationRuntime -DataFactoryName $DataFactoryName -Name $AzureSSISName -ResourceGroupName $ResourceGroupName
# To reconfigure Azure - SSIS IR
Set-AzureRmDataFactoryV2IntegrationRuntime -DataFactoryName $DataFactoryName -Name $AzureSSISName -ResourceGroupName $ResourceGroupName -NodeCount 5

Below are the benefits of moving on-premises SSIS workloads to Azure as mentioned in Microsoft BOL:

Reduce operational costs and reduce the burden of managing infrastructure that you have when you run SSIS on-premises or on Azure virtual machines.

      Increase high availability with the ability to specify multiple nodes per cluster, as well as the high availability features of Azure and of Azure SQL Database.

      Increase scalability with the ability to specify multiple cores per node (scale up) and multiple nodes per cluster (scale out).

References:  

      Lift and shift SQL Server Integration Services workloads to the cloud
Deploy SQL Server Integration Services packages to Azure
Schedule the execution of an SSIS package on Azure
Video - Lift and shift SSIS to the cloud by using Azure Data Factory

 See Also:

 My posts on Microsoft Azure

Compare Azure Data Factory V1 and V2