Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

Configure an Azure SQL Server Integration Services Integration Runtime


By:   |   Read Comments   |   Related Tips: More > Azure

Problem

We have an existing business intelligence environment on-premises. We wish to migrate everything to the Azure cloud. Is it possible to deploy our existing Integration Services projects and packages to Azure? How do we set-up a managed environment to run our packages?

Solution

With the release of Azure Data Factory v2, the Integration Runtime (IR) has been introduced. The IR is the compute infrastructure for Azure Data Factory and it allows for several data integration capabilities:

  • Data movement between different network environments
  • Activity Dispatch
  • SSIS package execution

There are three types of Integration Runtime: Azure, Self-Hosted and Azure-SSIS. In this tip we’re interested in the last type. More information about the IR can be found in the documentation.

With the Azure-SSIS IR, we can lift & shift existing SSIS projects and packages to the Azure cloud. There you can natively run your SSIS packages in a managed environment. The Azure-SSIS IR is a managed cluster of Azure VMs where you can execute SSIS packages. With the creation of the IR, you can specify the compute power of a node (scale up) and also how many nodes your cluster will have (scale out).

In the Azure-SSIS, you have access to an Integration Services catalog, just like you have on-premises. You have two options for storing your SSIS catalog:

  • An Azure SQL database
  • A SQL Server Managed Instance

In this tip, we’re going to configure an Azure-SSIS IR using an Azure SQL database. The advantages of using Azure SQL DB is that it is lightweight and easy to set-up. The disadvantage is that it doesn’t have SQL Server Agent, but Managed Instance does.

Setting Up Azure – SQL Server Integration Services Integration Runtime

Prerequisites

Let’s start with configuring some prerequisites. The first one is creating a new SQL Server instance in Azure. You can use an existing one as well, but it is important there is no existing SSISDB (the database behind the SSIS catalog)! In the Azure Portal, add a new resource and search for SQL Server.

search for sql server in Azure Portal

In the results, select SQL Server (logical server).

select logical server

In the next blade, click Create.

create logical server

Specify a name for your server (keep in mind that Azure has strict naming guidelines for most objects), an admin account and a resource group. You can either create a new resource group or use an existing one. Choose a location for the server that is relevant to your environment. Important is that the checkbox for “Allow Azure services to access server” is selected, as the Azure Data Factory will need to communicate with the SQL Server instance.

specify details for SQL Server

The next prerequisites are certain PowerShell cmdlets:

Follow both links to the documentation on how to install both modules on your machine (make sure the PowerShell interface is launched with administrator privileges).

Configure Azure Data Factory

Now we’re adding an instance of Azure Data Factory to the environment. Create a new resource and in the Analytics section click on Data Factory.

create Data Factory

In the New data factory blade, enter a name, choose a subscription and a resource group (the same one as you used for the SQL Server logical server). Make sure to choose version 2 of data factory, as this is needed for the Azure-SSIS IR. Finally, choose the same location as your SQL Server.

configure data factory

After the data factory has been created, click on Author & Monitor in the Overview section. This will launch the Data Factory portal in a separate tab or window in your browser.

launch data factory

In the Azure Data Factory, click on Configure SSIS Integration Runtime.

configure ssis ir link

SQL Server Integration Services Integration Runtime Setup

Specify a name for your IR and a description. Choose the same location as the data factory and SQL Server.

SSIS IR config

There are several options available for the node size:

available options for node size

In this set-up, we choose the smallest node size for our cluster (to save money) and two as the number of nodes. As edition, Standard is chosen. Only chose Enterprise if you need advanced SSIS features. You can find an overview of those features here. If you already have a valid SQL Server license with software assurance, you can save extra money by using the Azure Hybrid Benefit. Click on Next to go to the next part of the set-up.

Here you configure the database for hosting the SSIS catalog. Choose a subscription and a location (again, the same location we used before). For the Catalog Database Server Endpoint, choose the logical SQL Server we configured (this should be auto-populated). You can either choose to use the admin account with SQL Server authentication, or Windows Authentication.

If you want to use Windows Authentication, addition steps are necessary. Open up the PowerShell IDE and create a new group:

create new AD group in PowerShell

The new group is stored in the &Group variable as we need this later on. First we need to find the service identity ID of the Azure Data Factory. In the Azure Portal, go to the Data Factory and then to the properties section. There you can find the ID. Copy it to the clipboard.

find service identity ID of the DF

Back in PowerShell, add a new user with the service identity ID to the group we just created.

add new user to group in PS

We can verify the user has been added in Azure AD:

Azure AD group info

The same information can be checked in PowerShell, where it is clearer we added the Data Factory application to the group:

verify group in PS

Now we have to add this group to SQL Server. If you haven’t already, set yourself up as an admin to the SQL Server instance. You can do this by going to the SQL Server in the Azure Portal and then by clicking Set Admin in the Active Directory Admin section.

set admin

From the list of available users, select a user that will be made admin of the SQL Server logical server:

select admin user

Don’t forget to click on Save in the previous screen!

save changes

With the admin account, log into the SQL Server instance. Choose Active Directory – Password to log in. If you don’t see those options, make sure you upgrade SSMS to the latest version.

log into master database

If it’s the first time logging in, it’s possible you have to add your IP address to the firewall:

add IP to firewall

If you’ve logged in successfully, you should see the master database:

master db

Open up a new query window in the master database and run the following script to add the group to the server:

CREATE USER [SSISIrGroup] FROM EXTERNAL PROVIDER;			

Then assign this group to the dbmanager role:

ALTER ROLE dbmanager ADD MEMBER [SSISIrGroup];			

This will allow the Data Factory to create the SSISDB database in the Azure SQL Server. If you followed all the steps, you can now choose to use Windows Authentication in the Azure-SSIS IR set-up:

use windows authentication

Make sure to test the connection! Click on Next to go to the next part of the configuration. In the advanced settings, you can set-up the number of maximum parallel executions per node. With this setting, you can choose to run a single package with multiple cores (if the number of parallel executions is low), or to run multiple packages within a single core.

advanced settings

For now, we’re going to leave the other options blank. Click on Create to finish the set-up. Keep in mind that this can take quite some time (up to 20-30 minutes)!

creating the IR. Waiting time!

During this process, the Azure Data Factory will create the SSISDB, the SSIS catalog and the cluster that will run your SSIS packages. Once the creation is done, you can see the SSISDB when you refresh your connection in SSMS:

SSISDB created

To see the Integration Catalog itself, you need to log into the SSISDB with SSMS. In the connection screen, go to Options.

connection screen

There enter the SSISDB database name in the connection properties:

set connection properties

When you now connect to the Azure SQL database, the SSIS catalog will be shown in the tree:

the SSIS catalog in Azure SQL DB

Keep in mind you need at least version 17.2 of SSMS. When you’re done, don’t forget to stop the Azure-SSIS runtime, since running a scale out SSIS cluster in the cloud can get quite expensive. In the next tips, we’ll explain how you can deploy and execute your SSIS packages in the Azure-SSIS IR.

Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools