Running SSIS on Azure VM (IaaS) – Do more with less money


Hi all,

In the SQL PASS Summit of 2016, I presented a session about "Running SSIS on Azure VM - Do more with less money" and it was well received based on the session feedback. Therefore, I am extracting some key content from my SQL PASS presentation and sharing it on this blog so that more SSIS users can benefit from it.

Why running SSIS on Azure VM?

  • Pay per use with support of BYOL
  • Elasticity with cost options (1 Core 2GB 1TB -> 32 Core 512GB 64TB)
  • Extremely Secure at physical/infrastructure/SQL level
  • Reliable storage with sync local copy and async remote copy
  • Fast storage that uses Solid-State Drives (SSD)
  • High Availability SLA 99.95%, 99.99% with always-on
  • Easy deployment with simply configuration
  • Easy way to “Lift and Shift” your SSIS execution to the cloud
  • Save IT costs by taking advantage of Automated Backup and Patching
  • Lab / office being restructured or demolished

How to create new Azure VM to run SSIS?

There are 3 options you can consider to run SSIS on Azure VM as IaaS

Option 1: Create SQL VM with per-minute licensing

  • Flexible option for non-EA customers or workload running for short time.
  • Supports SQL 2016 (Enterprise, Standard, Dev) SQL 2014 SP1(Enterprise, Standard), SQL 2012 SP3 (Enterprise, Standard)

Option 2: Create SQL VM with an existing license (BYOL)

  • For Enterprise Agreement (EA) customers
  • Images don’t charge SQL Server license, just the cost for running Windows VM
  • One free passive secondary replica license per Availability Group deployment
  • Supports SQL 2016 (Enterprise, Standard) SQL 2014 SP1(Enterprise, Standard), SQL 2012 SP3 (Enterprise, Standard)

Option 3: Manual installation on Azure VM

  • No automated SQL Server configuration experience (e.g. Automated Backup, Patching)
  • No AlwaysOn Availability Group Portal template (for HA deployment on BYOL images)

 

How to migrate your SSIS packages / catalog to Azure VM?

There are 3 options you can consider to migrate your SSIS packages / catalog to Azure VM

Option 1: Copy over the package files via copy and paste and run deployment again. Use copy and paste functionality on remote desktop, or use tools like CloudBerry

  • Simplest if you have only few SSIS projects to migrate (recommended)
  • Remote Desktop example details
    • 1)      Open a RDP session to your Azure VM instance hosting your SQL server instance.

      2)      Install SSDT tools from https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt on the remote instance.

      3)      Create SSIS catalog (if not already done) using “Integration Services Catalog” menu under Databases.

      4)      On your client machine navigate to the folder where you have  your SSIS package (for example: c:\ssispackages\) and right click to copy the package you want to deploy.

      5)      On the remote desktop window, in windows explorer, navigate to the folder where you want to save these packages (for example c:\packagesonvm)  and then do a paste.

      6)      Launch SQL Server data tools (SSDT) on the remote desktop window, add the package you copied in (Step 5) to a SSIS project and then deploy to the local SQL instance.

Option 2: Use the Microsoft-provided power shell script to do a data level migration

  • Best If you have large amount of projects to migrate

Option 3: Leverage Always-On deployment on-premises, use the Add Azure Replica Wizard to create replica in Azure, then add SSISDB to an AlwaysOn group using the Create AlwaysOn group” Wizard and launch “Enable SSIS to support alwaysOn Wizard” . Let it failover and have the new packages run against the catalog on the VM database instance

  • Most complicated approach but this allows you to minimize your downtime

 

How to access on-premises data sources from Azure VM?

There are 3 options you can consider to access on-premises data sources

Option 1: VNET

  • Free of charge up to 50 Virtual Networks across all regions for each subscription
  • Public IP and Reserved IP Addresses used on services inside a Virtual Network is charged.
  • Network appliances such as VPN gateway and Application gateway that are run inside a Virtual Network are also charged
  • COST LEVEL: Medium

Option 2: Express Route

  • Fast, reliable and private connection between Azure and on your premises
  • Suitable for scenarios like periodic data migration, replication for business continuity, disaster recovery, and other high-availability strategies
  • Extend your datacenter securely with more compute and storage
  • COST LEVEL: High

Option 3: Your own company VPN

  • COST LEVEL: Varies

 

Tips and Tricks

Script to install SSIS components on VM

What will the script do

  • Get environment information.
  • Make temp directory in script location.
  • Download tools and connectors and install
  • Remove temp directory and files. Log files will be reserved

Where to find

How to use it

  • Install.ps1
  • install.ps1 -feature <toolName1>, <toolName2>…

What it installs

Type Full Name Tool Name

(for parameter)

Available? *Only for Enterprise /Developer?
2012 2014 2016
Tools SSDT SSDT yes yes yes no
Microsoft Access 2016 Runtime AccessRuntime yes no
2007 Office System Driver AccessDriver yes no
OracleCDCDesigner OraCDCDesigner yes yes yes yes
OracleCDCService OraCDCService yes yes yes yes
Connectors Microsoft Connectors for Oracle OraAdapter yes yes yes yes
Microsoft Connectors for Teradata TeraAdapter yes yes yes yes
Microsoft Connector for SAP BI SAPBI yes yes yes yes
OData Source Component ODataSource yes yes in-box no
Balanced Data Distributor Component BDD yes yes in-box no
Data Feed Publishing Components ComplexFeed yes no in-box no

Script to migrate SSIS projects to VM

What will the script do

  • Export catalog folders and projects to file system (.ispac).
  • Import folders and .ispac files to catalog (catalog is recreated).

Where to find

How to use it

  • CatalogExport.ps1 (default exporting to C:\SSIS)
  • CatalogImport.ps1 (default importing from C:\SSIS. Catalog is recreated with a default secret written in the script.)

 

 

Scripts to schedule tasks to start / stop VMs

Create scheduled tasks to start a single VM or a set of VMs

Create scheduled tasks to stop a single VM or a set of VMs

 

Customer Examples for running SSIS on Azure VM (IaaS)

Example #1 - Cost Driven

  • UK Customer in medical industry
  • 1 Azure VM for SQL Server and 1 Azure VM for SSIS
  • The SQL Server VM runs 24/7, the SSIS VM runs as on-demand
  • Cost to run the SQL VM 24/7 and SSIS VM on-demand is about 4K pounds per year, 12K  pounds for 3 years.
  • Cost to set up a physical lab for 1 beefy SQL Server machine and 1 SSIS machine with a 3 years deprecation lifespan is about 15K pounds
  • 3K pounds saving for using SQL/SSIS on Azure VM
  • Tips: use power script to start/shut down the SSIS VM for on-demand use in order to avoid charges when VM is idle.

Example #2 - Lift and Shift

  • Canadian partner in e-Commerce business
  • All databases reside on Azure – need to run SSIS on Azure too!
  • 1 Azure VM running 24/7 for both SQL Server and SSIS per client
  • Use Azure blob storage to store SSIS package files (.DTSX) and use PowerShell Script to trigger file copying and SSIS package executions
  • Running with DS 11 SQL VM with P20(1TB) disk
  • Tips: Everything on Azure, no worry about on-prem data access

Example #3 - Hosted Solution

  • A Puerto Rico consulting firm provide healthcare/medicare BI solution for either on-premises or as a hosted deployment in Auzre.
  • Their BI solution helps calculate the insurance quality, different KPIs and Measure for their client. Some calculation can takes up to 72 hrs to do
  • 1 dedicated Azure VM for SSIS running 24/7 for all clients
  • Dev / test environment all on Azure VM as needed (A6 or A7)
  • Cheaper to run in Azure and more flexible to setup new server
  • Tips: Considering to combine SQL Server and SSIS on same machine
Example #4 - Demo Purpose
  • Belgium partner in ERP business
  • 1 Azure VM for both SQL Server and SSIS, run as needed basis (DS13, 8 core 56 GB RAM, 1 TB storage)
  • Cost saving for:
    • No more USB drive to distribute the solution
    • No need to purchase beefy laptops for everyone
    • Use to do demo/training for an ISV solution
    • Tips: Shut down VM after demo / training
Hope this helps
Comments (0)

Skip to main content