Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Configure an Azure SQL Server Integration Services Integration Runtime


By:   |   Updated: 2018-10-03   |   Comments (7)   |   Related: 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 Updated: 2018-10-03


get scripts

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.



    



Wednesday, June 26, 2019 - 6:22:50 PM - Phil Parkin Back To Top

Thanks, Koen

I was wondering how to make the Integration Services Catalog node appear, as I haven't seen the explanation elsewhere. You explained it clearly.

Phil


Monday, May 06, 2019 - 1:26:23 PM - Koen Verbeeck Back To Top

Hi Ravi,

using SSIS within the Azure-SSIS IR is a PAAS solution. You just have to configure your IR and the SSISDB and you're done.
You can re-use your existing packages. If you want to go full cloud, you can indeed store your Excel files in an Azure Storage account.

A good option is to use Azure File Services, as that service can use UNC paths as well, just as in your local environment. I have some tips about this in the pipeline, so keep an eye on this website!

Regards,
Koen


Saturday, May 04, 2019 - 2:53:33 PM - Ravi Rao Back To Top

Hey Koen,

Firstly, I really appreciate your efforts to bring these topics with such a nice n detailed explaination and implementation. It is really helping lot of ppl like me.

I have come to your blog while searching for a scenario where I need to migrate existing SSIS -OnPrem SQL DB based project to Azure cloud with PaaS service only.

Here I have few challenges like:

- Source file comes in the form of excel with multiple sheets, one sheet only to be used for copy/transformation.

- Destination is also an excel file.

- Current system is using SSIS package where source file (from email as attachment) placed into shared/local folder and then processed via SSIS DataFlow to copy into OnPrem SQL DB.

- After loading around 10+ files to DB a transformation package runs which generates the final table data which is again exported to excel as an output to users.

Can you please suggest the best PaaS alternative here, it will be great help, please!

I have thought of some approaches by checking the forums:

- Use Logic apps to download the email attachment and copy in blob storage.

- then use Custom Activity to read the excel file from blob storage and convert into csv and copy the data to PaasSQL DB.

- then export the data from Azure SQL DB to excel by creating a macro enabled excel template which will refresh the data whenever it opens.

Is there any way I can use the existing SSIS package (source will be blob instead of shared folder and target as PAAS SQL DB)? this article talks about the similar thing but not sure if this is a complete PAAS solution as we are creating a SQL Server (Logical) for IR.

Please help and suggest.

Thanks once again.


Wednesday, April 10, 2019 - 2:42:15 AM - Koen Verbeeck Back To Top

Hi M,

you can use Azure File Services or blob storage to store the file. Azure File Services has the advantage that it is the easiest to migrate your SSIS package to, since it also uses UNC paths.

For SFTP, you can use any 3rd party tool (like FileZilla or WinSCP) and use the Execute Process Command to call it. You do have to create a custom set-up for your Azure-SSIS IR to install those tools on the cluster.

Regards,
Koen


Tuesday, April 09, 2019 - 5:15:24 AM - M Back To Top

Hello Koen,

Our current solution has a package that creates a CSV file to a local disk, zips it and then uploads it to a SFTP environment.

How would this work on a SSIS-IR Dataflow since there is no local disk or SFTP destination task? 

Regards,
M


Friday, March 08, 2019 - 9:41:55 AM - Koen Verbeeck Back To Top

Hi Anne,

regarding the VNet, that's something you should take up with your network admin, I have no experience with this.

Regarding the Azure VMs, they are used behind the scenes as a managed compute environment for your Azure-SSIS IR. If you don't have any licenses, you can select the option to include licenses (first step I believe). This means your cost of the Azure-SSIS IR will go up. Keep in mind though that I'm not a licensing expert, so when in doubt contact your Microsoft representative.

Regards,
Koen


Thursday, March 07, 2019 - 6:50:45 PM - Anne Back To Top

Thanks for the article!

A question when I tried to do the integration run time setup, on the second setup page, under catalog database service tier, there is a line of statement:

Since your Azure SQL database connectivty is isolated with Vnets ercice endpoints, your Asure-SSIs integeration runtime must join the same Vnet and subnet in the next step.

There is no checkbox like yours does, so it is required, I stuck there.

Also does this SSIS integration runtime only works with Azure VM?

We only have license of Azure SQL databases and our on-premise SQL server licenses , we don't have Azure VM for SQL servers yet.

Can we still use this SSIS integration runtime?

Thanks,


Learn more about SQL Server tools