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

By:   |   Comments (4)   |   Related: > Azure


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.


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:

bi components chart

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.

migration path for bi components

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.

virtual machine options

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:



Multiple partitions

DirectQuery storage mode


DAX calculations

Row-level security

In-mem storage

Backup and restore

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

Multi-Dimensional Tabular







Custom Assemblies



Custom Rollups



Default Member



Named sets






Role-Playing Dimensions



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.

analysis services costs

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.

sql data warehouse costs

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.


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Dinesh Asanka 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

Comments For This Article

Tuesday, February 28, 2023 - 2:12:59 AM - Lalu Back To Top (90959)
Good One Dinesh

Thursday, February 4, 2021 - 10:58:49 AM - Matt Birchall Back To Top (88174)
I think the road map picture has the wrong label on OLAP in Phase III. Presumably this should be Azure Tabular rather than Azure DF?

Thursday, June 18, 2020 - 8:46:21 AM - pranita Back To Top (86019)

Thank you for the effective tip. Can we also migrate SSAS 2014 as is to Azure and what are the steps, pre-requisites. Azure VM should be same as on prem server with DB engine, SSIS, SSAS and SSRS components

Tuesday, September 17, 2019 - 12:29:44 PM - mobes Back To Top (82485)

Can you clarify why Phase III or the roadmap graphic shows the OLAP column migrating from Tabular to Azure Data Factory? I don't see that mentioned in the text. Thank you for clarifying. This high level over-view is interesting.

get free sql tips
agree to terms