Managed Instance (in combination with SSIS Integration Runtime) a first POC story

First of all, this article is based on a personal experience and is a snapshot in time. All comments made are based on the experience and what was available at time of writing.

We have a client who wants to get rid of its ‘On Prem’ infrastructure and wants to push everything into the Azure Cloud environment, not having to think about maintenance/patching/high availability and so on. You may have noticed that On Prem is written between quotes? The answer why is that the server(s) are already in Azure, but in a IaaS model and is a intermediary step to go into a full blow (Azure) Cloud environment.

Managed Instance (MI) is currently being marketed as a solution to perform a migration towards the Azure environment by the ‘lift and shift’ method. Meaning that what you have on your trusted SQL Server machine is what you get on a spanking new machine on the latest technology’s without having the hassle of maintaining and patching the machine on a regular basis, without saying you have to fire your infra person right this instance. That is where Kohera was contacted for a 5 day Proof of Concept to find out if a lift and shift is possible for their current environment, including a minor cleanup of ‘lone’ servers also containing SQL Server, and if not what are the other options to.

Why managed Instance?
Our customer Europ Assistance says it best:

Read the full reference case here

The current environment exists out of a SQL Server Always On Availability Groups in two VM’s located on Azure containing a full SQL Stack (SQL Databases, SSIS, SSRS), another VM also containing a SQL install for reporting of Third party database. The SSIS and SSRS retrieves and sends data from several other data sources like Oracle and MySQL.

The scope of our POC was to find out if the ‘lift and shift’ was possible in this environment taking them into a maintenance free environment with minimal impact on current processes and applications. The following schema visualizes what the ideal scenario for our client as an intermediary step towards a full Azure environment:

The installation of the MI is pretty straight forward and fairly quick to set up thanks to the documentation and how-to guides provided by Microsoft on how to do this. For this part of the POC we were 100% convinced this was going to work and would help the client in his quest to go into a fully managed infrastructure.

The next step and most challenging part of the POC was to help the SSIS packages go to heaven, actually the intermediary step to heaven is via the cloud J. To be able to do this we need to set up Data Factory with Integration Runtime (IR) as the ‘SSIS in the cloud’ is translated towards Data Factory at runtime, also no big deal thanks to the Microsoft documentation.

The actual difficulty exists into getting 3th party or custom components installed onto the Integration Runtime, as this is actually a separate machine that is spinned up, takes about an hour, for you each time you enable Integration Runtime and broken down each time you stop. Also Microsoft is generous enough to provide everyone with documentation on how to do this.

Only there is where we have met our nemesis the installation of Oracle Enterprise only installs the client by following the steps described in the documentation. What we needed was the OLEDB and OLEDB connector to have the SSIS packages to connect on Oracle. After investigating the procedures of the standard installation file for Oracle on IR we found out that the available options are provided but you’ll have to script them within this file. After several tests and modifications we came to the conclusion that it isn’t working as promised although the logging provided us with a install success status we couldn’t connect onto any of the Oracle databases in 64bit nor in 32bit execution with the SSIS catalog providing the error message below, a ticket is raised to Microsoft about this message, at time of writing Microsoft is continuing with its investigation. Whenever we get feedback an update will be provided or the answer will be provided in a separated blogpost about how to make your own custom IR component installation.

On the other side we also managed to have a plus, we created our own install file for MySQL ODBC since this is/was not made available by Microsoft when we did the POC and was tested with success. How to do this will be included in a blogpost committed to custom IR components.

Our suggestions based on the POC experience

Scenario1: with Managed Instances, but without Integration Runtime

We can use Managed instance as SQL server replacement for Primary and Secondary.

In order to be able to have SSIS / SSRS running we still need an IaaS alternative.

We still need 1 SQL server running for the third party database which can only work within a IaaS environment. This server can be used for a combination of third party database, SSIS, and SSRS.

Together with this, we can already start further investigating using Azure Data factory. New developments for ETL will be done in Azure Data factory.

Current SSIS packages will be migrated to Azure data factory step-by-step.
The current integration runtime component for SSIS is hard to configure.

We are able to install ORACLE and MYSQL, but packages are failing and giving errors for Oracle

Advantages

• Less maintenance needed.
• High Availability is guaranteed by Managed Instance
• Backups and retention is defined by Managed Instance
• License for the Active SQL server is standard edition. We don’t need enterprise edition for this.
• Data factory is in place for the ETL jobs.

Disadvantages

• SSIS and SSRS are still IaaS
• Currently SSRS is not yet under PaaS solution.

Proposed Architecture when not using the Azure-SSIS IR component.

Figure 2: Proposed Architecture without Azure-SSIS IR

Architecture

• Managed Instance running all SQL Databases
• 1 Active SQL Server with Standard edition running
  • SSRS // SSAS // third party databases

Scenario 2: No SQL Managed Instances but SQL Azure DB

We don’t migrate to Azure SQL managed instance currently. We start refactor the database to be an Azure SQL Database. (Fully managed PaaS).

The first test are already done and this is feasible if we redesign certain parts to be compliant with the Azure DB constraints.

We could use the following Tier in clients use case:

• Elastic pool

Figure 3:Elastic Pool tier

• Single Database

The majority of the load (according to first load tests) can run on the following tier.

Figure 4: Standard Tier – Azure SQL database S4

A small part of the load will require more DTU to run smoothly.

This is the following

Figure 5: Standard Tier – Azure SQL database S6

This scenario is in combination with refactoring SSIS packages to run on Azure Data factory.

Client conclusion

After discussion with the client we decided to go for scenario 1 due to the current architecture and a potentially big refactoring project on existing applications if scenario 2 was chosen.

This scenario consists of

• Creation of the managed Instance
  • Creation and setup
  • Integration with Azure AD – AD
  • Backup – Restore Databases
  • Changing connection strings
  • Refactoring SSIS packages to point to Managed Instance.

• Reconfiguring 1 SQL IaaS Node to have
  • SSRS
  • SSIS
  • Third party database
  • This can be a small machine running SQL server standard edition.

If you want to know if your environment can benefit from moving towards Managed Instance and potentially decrease your annual invoice and service costs, Contact us!