On Premises SQL Server Business Intelligence Stack Migration Process to Azure Synapse Cloud Data Platform

  • Home
  • Business & Strategy
  • On Premises SQL Server Business Intelligence Stack Migration Process to Azure Synapse Cloud Data Platform


I am very excited about the opportunity to create this eBook and how Synapse’s role will be pivotal in providing a data platform which facilitates answering key questions which require data analytics such as climate change, pandemics, water and poverty, etc.

Azure Synapse is a limitless analytics service that brings together enterprise data warehousing and Big Data analytics. It gives you the freedom to query data on your terms, using either serverless or dedicated resources—at scale.

Purpose of this Blog Series

1) Captivate Interest of a typical on-premises BI Developer, BI Team Leader, BI Architect. Presenting a clear concise and overwhelmingly positive migration business case for on premises BI developers with TSQL and Power BI skills. That these developers can leverage many business benefits of Synapse with only modest reskilling.

2) Tour of features with extensive links. Based on AdventureWorks on-premise environment which is migrated to Azure Synapse.

3) Light and open, not text heavy, more focus on diagrams. Such as architectural diagrams, decision trees and screen shots.

4) Address technical detail where this is essential to justify the core guidance of this ebook. For example SQL Pools partitioned materialised views as a data source to direct query Power BI data sets. Using TSQL dmvs to evidence that this is a performant alternative to Azure Analysis Services.

5) Present a concise vision of leveraging further Synapse and other Azure Cloud features in further architectural implementations e.g. Spark and Cosmos DB.

Structure Of this eBook

Please navigate the following Visio diagram including clicking on the hyperlinks.

General Usage

Since Synapse is a web based application there is typically a wait time of approximately a couple of seconds where you would expect an immediate response in an on-premises environment. To let this distract you from how quick with Synapse you can …

  • Query millions of rows within a flat file within a data lake using T-SQL.
  • Query billions of rows within a SQL Pools database.
  • Set up a service within literally a few minutes, e.g. SQL Pools, Spark, etc.
  • Run a batch job in minutes which would previously take days or longer.

Leverage all the above using your existing T-SQL skills.

Further Reading

Azure Synapse Analytics

What is Azure Synapse Analytics?

Enterprise business intelligence – Azure Reference Architectures | Microsoft Docs

Learn more CTAs

Get started with 12 months of free services.

Join one of our local Analytics in a Day workshops. Find a workshop near you.

Get hands-on with this step-by-step guide on creating a data warehouse and running multiple queries with Azure Synapse Analytics.

Connect with an Azure sales specialist on pricing, analytics best practices, setting up a proof of concept, and more.

Learn why customers are choosing Azure for their analytics.