Migrating On-Premises Microsoft SQL Server Business Intelligence Solution to Azure

By: Dinesh Asanka   |   Updated: 2019-09-17   |   Comments (4)   |   Related: > Azure

Problem

Some organizations are looking at the possibility of migrating their Microsoft Business Intelligence solutions to the cloud due to cost-effectiveness, easier maintenance, scalability, etc. However, they are running current solutions on-premises which have been servicing their stakeholders for years. Because of this, with the cost factor and the time factor, it might not be feasible to start a new solution from scratch. Although, they would like to lift and shift the existing solution to the cloud as is, there are lot of issues with that migration. Mainly, some of the existing features may not be compatible with the cloud. This article looks at how on-premises Microsoft Business Intelligence solutions can be migrated to an Azure environment.

Solution

What is a typical Microsoft Business Intelligence environment?

A typical Microsoft Business Intelligence (BI) environment consists of multiple components such as:

• Data Sources
• ETL (Extract-Transform-Load) layer
• Data warehouse
• OLAP server
• Reporting and/or dashboard layer

Check out the image below for a general workflow:

ETL (Extract-Transform-Load) is used to extract data from heterogeneous data sources.  These sources can be varied from relational databases, No-SQL databases, text files, images, videos, audios, etc. The extracted data will be stored in the data warehouse which is tailor-made for analytical queries. However, the data warehouse which is a database instance, may not be feasible for reporting as analytical reporting is performed for a large volume of data. Hence, OLAP cubes are used to deliver end-user requests. End users will connect to the cubes by means of Reports, Dashboards, or spreadsheets to satisfy their business needs.

The above image shows the basic structure of the Business Intelligence environment. Different technology partners offer different solutions to the different phases in a BI environment. Let's look at how Microsoft provides their solutions for the traditional BI environment.

Microsoft Business Intelligence Environment

Microsoft has solutions for the various Business Intelligence components.

ETL: Microsoft provides SQL Server Integration Services (SSIS) as their flagship tool for ETL. However, many organizations use SQL Server stored procedures for transformation phases when it requires large and complex transformations. When there are heterogeneous data sources available, organizations tend to use SSIS as an extraction tool.

Data Warehouse: Typically, a data warehouse is a database instance which is designed for analytical purposes. Most databases are designed for transactional purposes, but in the case of business intelligence, the database is designed for analytical purposes. This means that SQL Server Database Engine will be used as data warehouse components in the BI environment.

Cubes: In the world of Microsoft, there are two types of cubes. SQL Server Analysis Service Multi Dimensional Cubes and SQL Server Analysis Services Tabular, tabular being the latest addition to the Microsoft OLAP world, still many organizations are trying to adapt to tabular.

Reporting: SQL Server Reporting Services (SSRS) is the main tool for reporting in the Microsoft BI environment. However, many organizations use Power BI to connect to Multi Dimensional or Tabular cubes. Another popular tool is Excel which users connect to the cubes. In Excel, many users incorporate pivot tables and pivot graphs to build their reports. Though it is not as popular, SQL Server Mobile Report Publisher can be used as a dashboard tool.

Road Map

Moving an on-premises Business Intelligence solution to the Azure cloud is not a one-step process. Instead, it needs to be methodical, with pre-planned phases as shown in the below image.

In Azure, there are different service models in the cloud which need to be part of the Azure road map, they are:

• Infrastructure as a service (IaaS)
• Platform as a Service (PaaS)
• and Software as a Service (SaaS)

Starting Point

As discussed previously, most of the on-premises Microsoft BI solutions comprise the Database Engine, SSIS, SSAS and SSRS.

Phase I

Phase 1 is the start of the journey to Azure from on-premises. This phase is moving the on-premises databases to IaaS. This will be a lift and shift to the Azure IaaS.  This requires the minimum operations, however when the movement is done you need consider the next phases as well. For example, in the on-premises mode, multiple services may be running on the same instance. Typically, SSIS and SSAS are running on the same instance. However, in the case of Azure, SSIS and SSAS will be two separate services. Therefore, during the IaaS migration, it is essential to separate these services. The next important thing is to migrate data from on-premises to IaaS.  Backup options include: Full, Differential, and Transactional Log backups which can be used to migrate data to the IaaS. Apart from the Backup and Restore options, there is Migration Assistant to transfer the data to IaaS. Please note that pricing of Azure is an important consideration.

There are a lot of pricing options in VM as shown below.

You can choose the Virtual Machine with SQL Server with Enterprise or Standard editions. As you can see 8 GB RAM, 50 GB storage will cost US $1.709 per hour.  Please note that these prices and configuration options will change. Since configurations are for an hourly basis, you have the option of shutting down the server when not in use. For example, since the SSIS instance is used only during the ETL time, which will be a typically once a day, SSIS is a candidate for a shutdown when not in use which can save some money for your organization.

Phase II

As shown in the previous image, Phase II will have three steps. SSIS will be converted to Azure Data Factory, SSAS will be converted to Azure Tabular Service and the SSRS reports will be converted to Power BI.

Converting SSIS to Azure Data Factory means, rewriting the ETL from scratch which is the recommended method. However, this may not be feasible based on time and expertise. For that there is an option of running your existing SSIS packages on Azure Data Factory.

Next is to migrate the SSAS MDM cubes which is a key component in the BI environment. However, Azure does not have a PaaS service for Multi Dimensional. So the option would be to rewrite to the Azure Tabular Service. Tabular service has InMemory and DirectQuery options which can be used to suit your environment. If you don't want to rewrite, then the option would be leaving the existing Multi Dimensional on IaaS.  There are three options for Tabular such as Developer, Basic and Standard which have the following features:

If you are choosing to migrate to Tabular, there are few features which are not available in Tabular.

There is also different pricing for Analysis Service.

Following is the Azure Analysis Service configuration which also has an option of adding a scale-out instance.

Converting reports to Power BI is also a challenge. Since SSRS does not have a PaaS service, Power BI should be used. However, again rewriting is required. In case you are unable to convert SSRS, the next option would be leaving SSRS in an IaaS.

Phase III

Phase III is converting the existing database to the Data warehouse service. Though this is the only task, this will be a herculean task and also the data warehouse is a costly service as shown in the table below:

Your on-premises database cannot use a lift and shift process to move to the Data Warehouse service. It is again a "sort of" rewrite. For example, some T-SQL is not compatible with the Data Warehouse service. For example, the MERGE statement is used by many users to adopt type 2 slowly changing dimensions. However, the Merge statement is not compatible with the Data Warehouse services.

Another challenge is multiple databases are not possible with DWH. Typically, in an on-premises BI solution there will be multiple databases such as Staging, Data Warehouse, Reporting, etc. This has to be mapped with schemas in the Data Warehouse service. This means that essentially you will need to rewrite existing code.  Following is the configuration for the Data Warehouse service.

Since migrating to Data Warehouse service requires a lot of changes, there is an option of migrating to Managed Databases. However, please note that managed databases does not offer the Massive Parallel Processing (MPP) option.

Recommendations

It is essential that you need to plan for this migration journey since BI has a lot of components. For Azure, these components will be moved to different Azure infrastructure and sometimes you need to have combination of SaaS, PaaS and IaaS for the different components.

Next Steps

• Check out these resources from Microsoft:
  • Azure Analysis Service
  • Azure Data Warehouse
  • Azure Pricing
• MSSQLTips.com Resources
  • Azure Tips
  • Business Intelligence Resources
  • Business Intelligence Tutorials
  • Power BI Tips

About the author

Dinesh Asanka is a 10 time Data Platform MVP and frequent speaker at local and international conferences with more than 12 years of database experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips