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

By:   |   Updated: 2019-09-17   |   Comments (1)   |   Related: More > 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:

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:

FEATURE DEVELOPER BASIC STANDARD

Perspectives

Multiple partitions

DirectQuery storage mode

Translations

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

Actions

Yes

No

Aggregations

Yes

No

Custom Assemblies

Yes

No

Custom Rollups

Yes

No

Default Member

Yes

No

Named sets

Yes

No

Writeback

Yes

No

Role-Playing Dimensions

Yes

No

There is also different pricing for Analysis Service.

INSTANCE QPUs MEMORY (GB) PRICE / Month (USD)

S0

40

10

591.300

S1

100

25

1,481.900

S2

200

50

2,963.800

S4

400

100

5,920.300

S8

320

200

7,577.400

S9

640

400

15,154.800

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:

SERVICE LEVEL DWU PAY AS YOU GO (Month)

DW100c

100

~$876/month

DW200c

200

~$1,752/month

DW300c

300

~$2,628/month

DW400c

400

~$3,504/month

DW500c

500

~$4,380/month

DW1000c

1000

~$8,760/month

DW10000c

10000

~$87,600/month

DW15000c

15000

~$131,400/month

DW30000c

30000

~$262,800/month

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.

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


Last Updated: 2019-09-17


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools