Menu
Menu
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.
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
Disadvantages
Proposed Architecture when not using the Azure-SSIS IR component.
Figure 2: Proposed Architecture without Azure-SSIS IR
Architecture
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:
Figure 3:Elastic Pool tier
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.
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
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!
