LIFT OFF: LIFT &

SHIFT AZURE OPTIONS

FOR THE SQL SERVER
© 2017 DMI CONFIDENTIAL & PROPRIETARY

BI STACK
SQL Saturday Indianapolis
August 11, 2018
David Mellinger
 ABOUT ME

› David Mellinger
› Data Platform & Analytics Practice Lead
at DMI: Digital Transformation Solutions
› Twitter: @HoosierDataDev
© 2017 DMI CONFIDENTIAL & PROPRIETARY

› LinkedIn: linkedin.com/in/dmellinger
› MCSE: BI\Data Management & Analytics
› Live in Columbus, Indiana
› Active member of Terrace Lake Church
› Sports fan HS, College, and Pros various
sports
2
 AGENDA

› Why Lift & Shift or move to the cloud?

› Discuss different Azure options for lift and shift across the data
analytics platform stack
› Sample Lift and Shift
© 2017 DMI CONFIDENTIAL & PROPRIETARY
 WHY MOVE OR LIFT & SHIFT TO CLOUD

Pros Cons

Cost efficiency? (Solution) Cost efficiency? (All in)

Scalability (ease of) Lack of Corporate Cloud Readiness

© 2017 DMI CONFIDENTIAL & PROPRIETARY

(Culture, Knowledge, Processes)

Flexibility, Experimentation, Ability Loss of capital investments
to innovate
Stay current Potential loss of stability

Performance? Performance?

4
 LIFT & SHIFT OPTION SUMMARY
SQL Server Azure\Cloud

Data Warehouse SQL Server RDBMS Azure SQL DB

Azure SQL Data Warehouse
Data Integrations SSIS Azure Data Factory
V2 (SSIS Integration Runtime)
Semantic Model SSAS (Tabular and MD) Azure Analysis Services (Tabular)
Reporting SSRS (Paginated, Mobile, Power BI (Service and Reports,
Power BI Reports) soon to host SSRS reports)
© 2017 DMI CONFIDENTIAL & PROPRIETARY

Advanced Analytics SQL Server Machine Azure Machine Learning Studio

Learning Services Azure Machine Learning Services
SSAS Data Mining Spark for Azure HDInsight
Azure Databricks
Master Data Management Master Data Services N\A
Big Data Support Interaction through Polybase Azure HDInsight
(Hadoop\Azure Blob Storage) Azure Data Lake
Azure Databricks
 AZURE LIFT & SHIFT DATA WAREHOUSE

Azure SQL DB & Azure SQL Data Warehouse

© 2017 DMI CONFIDENTIAL & PROPRIETARY

6
 AZURE DATA WAREHOUSE OPTIONS
Azure SQL DB - Azure SQL DB – Azure SQL Data
Singleton Managed Instance Warehouse
Architecture Symmetric Multi- Symmetric Multi- Massively Parallel
Processing (SMP) Processing (SMP) Processor (MPP)
Scale 4 TB 8 TB “Unlimited” - $

Cost Generally lowest, pay Higher than singleton, Highest, can pause
by service tier, DTU or vCore model compute, DWU model
vCore models
© 2017 DMI CONFIDENTIAL & PROPRIETARY

Max 8,000 8,000? 128

workers\requests
SQL Server Supports most on- Close to 100% More T-SQL and other
Compatibility premise capabilities limitations\differences
Polybase Support No No Azure Storage and
Azure Data Lake Store

7
 AZURE SQL DATABASE SINGLETON CONSIDERATIONS
› Data Migration Assistant
to help move
› No SQL Agent
› Current Max Size is 4 TB
› DTU or vCore Pricing
Models
› Cannot natively be in
© 2017 DMI CONFIDENTIAL & PROPRIETARY

Azure vNET
› Generally Cheapest option

8
 AZURE SQL DATABASE MANAGED INSTANCE CONSIDERATIONS
› Not currently compatible with Data Migration Assistant
› Can restore native backups or use Azure DB Migration Assistant
› More expensive than singleton
› Has SQL Agent
› Current Max Size is 8 TB
› vCore Pricing Model
› Native vNet deployment
© 2017 DMI CONFIDENTIAL & PROPRIETARY

9
 AZURE SQL DATA WAREHOUSE
Control Node

› Can store massively large DW

Compute Node Compute Node Compute Node Compute Node Compute Node

› Implementations are usually

difficult
Storage Storage Storage Storage Storage Storage Storage Storage Storage Storage
Container Container Container Container Container Container Container Container Container Container

› Table Distributions: Hash,

Storage Storage Storage Storage Storage Storage Storage Storage Storage Storage
Container Container Container Container Container Container Container Container Container Container

Round-robin, Replicated
Storage Storage Storage Storage Storage Storage Storage Storage Storage Storage
Container Container Container Container Container Container Container Container Container Container

› Finding right combinations for

Storage Storage Storage Storage Storage Storage Storage Storage Storage Storage
Container Container Container Container Container Container Container Container Container Container

workloads can be difficult and

Storage Storage Storage Storage Storage Storage Storage Storage Storage Storage
Container Container Container Container Container Container Container Container Container Container
© 2017 DMI CONFIDENTIAL & PROPRIETARY

need tuned\updated
Storage Storage Storage Storage Storage Storage Storage Storage Storage Storage
Container Container Container Container Container Container Container Container Container Container

› Described as very hard T-SQL

› Usual usage is hub and spoke with › Generally the most expensive
Azure SQL DW as hub and Azure option
Analysis Services, SSAS, Power BI › Can pause Compute and pay
as spokes only for storage
10
 AZURE CLOUD DATA WAREHOUSE COMPETITOR - SNOWFLAKE
› Competitor Snowflake on Azure (in preview)
is an attractive option
› Snowflake ANSII-SQL Compliant
› No Stored Procedures
› Use Python Scripts
› No table distribution setup, Snowflake
handles
© 2017 DMI CONFIDENTIAL & PROPRIETARY

› Great performance without continual

tuning

11
 AZURE LIFT & SHIFT DATA INTEGRATIONS

Azure Data Factory

© 2017 DMI CONFIDENTIAL & PROPRIETARY

12
 SSIS & AZURE DATA FACTORY CONCEPTS AND TERMINOLOGY

Concept SSIS Azure Data Factory

Project Project Data Factory

Container
Unit of work Package Pipeline
Container
© 2017 DMI CONFIDENTIAL & PROPRIETARY

Data Movement Data Flow Copy Activity

Control Logic Control Flow Control Activity

External Task Execute SQL, Script Task etc Transform Activity

Schedule SQL Agent Trigger

Connections Connection Managers Linked Services

13
 AZURE DATA FACTORY HIGHLIGHTS
› ELT as opposed to ETL
› Copy Activity just moves
data, data will be
manipulated at the
sink\target
› This allows taking
advantage of compute
© 2017 DMI CONFIDENTIAL & PROPRIETARY

power of database or
distributed system
› Examples: Stored
Procedures, HDInsight
Spark, Databricks
Python, Data Lake
14 Analytics U-SQL
 AZURE DATA FACTORY HIGHLIGHTS
› Azure Cloud based, very good
Azure connectivity
› SSIS Integration Runtime
› Deploy SSIS Projects to
Azure SQL DB
› Call SSIS Packages from
ADF Pipelines
© 2017 DMI CONFIDENTIAL & PROPRIETARY

› VSTS and Git integration

support

15
 AZURE DATA FACTORY WATCH OUTS
› Not a direct SSIS or ELT\ETL Tool replacement
› Most appropriate when many of your sinks\targets are Azure
based
› While it provides for many sources, the sinks\targets it
supports outside Azure is limited
› On-premise data source requirements
› Self-hosted Runtime needs for on-premise data sources
© 2017 DMI CONFIDENTIAL & PROPRIETARY

› SSIS Integrated Runtime must be joined to vNet

connected to on-premise network
› Still not very ultra-reliably stable
› No Analysis Services native activities
› Workarounds involving API calls for Azure Analysis
Services
16
 AZURE LIFT & SHIFT SEMATIC LAYER

Azure Analysis Services

© 2017 DMI CONFIDENTIAL & PROPRIETARY

17
 AZURE ANALYSIS SERVICES HIGHLIGHTS
› Development\
deployment same as
SQL Server
(Compatibility Level
1200 and above)
› Scale up and down
service levels for peak
© 2017 DMI CONFIDENTIAL & PROPRIETARY

processing\query
times, or even pause
when not in use to
optimize costs
› Scale out with query › On-premise Data Gateway needed
replicas and queries for on-premise data sources
18 will be load balanced
 AZURE ANALYSIS SERVICES WEB DESIGNER
› Can create models from Azure SQL DB, Azure SQL Data Warehouse, or a Power BI
Desktop File
› Power BI Desktop importing via SSDT to SSAS Tabular project is on the roadmap
© 2017 DMI CONFIDENTIAL & PROPRIETARY
 AZURE ANALYSIS SERVICES WEB DESIGNER
› Intended for quick creations of basic models or creating simple changes
› Can check basic querying as well
› SSDT is still primary authoring tool
© 2017 DMI CONFIDENTIAL & PROPRIETARY
 AZURE LIFT & SHIFT REPORTING

Power BI
© 2017 DMI CONFIDENTIAL & PROPRIETARY

21
 POWER BI HIGHLIGHTS
› Power BI Desktop
› Data gathering\manipulation,
OLAP Modeling, and Reporting
tool
› Analytical engine same
technology as Analysis Services
› Power BI Service
© 2017 DMI CONFIDENTIAL & PROPRIETARY

› Cloud service:
Report\dashboard rendering,
alerting\subscriptions, Mobile
Apps › Can use for just reporting by
› Monthly Updates connecting directly to enterprise
Analysis Services model
22
 POWER BI AND POWER BI REPORT SERVER
› Power BI Report Server is a superset of SSRS, with the ability to
surface Power BI Reports
Power BI Service Power BI Report Server
Location Cloud On-premise (or IaaS)

“Dashboarding” Power BI Dashboards (Q&A) SSRS KPI’s

© 2017 DMI CONFIDENTIAL & PROPRIETARY

Reports it surfaces Power BI, Excel Power BI, Excel, SSRS Paginated
(SSRS Paginated Reports, SSRS Mobile Reports
Reports\RDL surfacing soon
coming to Public Preview)
Subscriptions Yes Yes

Alerts Yes (limited) No

23
 POWER BI DASHBOARD VS SSRS KPI’S
› Can click through to reports on both, but no flexibility with SSRS KPI’s
© 2017 DMI CONFIDENTIAL & PROPRIETARY

24
 AZURE LIFT & SHIFT PHASED APPROACH
Data Integrations Relational Databases Semantic Models Reporting
Phase X\Last Phase X\1 Phase X\2 Phase X\Anytime
© 2017 DMI CONFIDENTIAL & PROPRIETARY

Azure Data Factory Azure SQL DB Power BI

 REPORTING PHASE
› This phase could happen at anytime
› The technical concerns are minimal
› If done during or before data source
phases (DW, Analysis Services, possibly
both) will need On-premise Data
Gateway setup
› Main concern here is proper roll out,
© 2017 DMI CONFIDENTIAL & PROPRIETARY

training, and buy in from the user

community
› Should be pretty easy as users generally
love Power BI
› Often will result in hybrid ending where
certain detail or more operationally focused
26 reports persist in SSRS
 RELATIONAL DATABASES\DW PHASE
› If going to a form of Azure SQL DB will
be pretty simple, could be first phase
› Use Data Migration Assistant (or
backups if Managed Instance) to move
on-premise staging, DW\Data Mart
databases
› Just need to switch connection strings
© 2017 DMI CONFIDENTIAL & PROPRIETARY

from any connecting systems

(ELT\ETL, Analysis Services, other
subscribing systems)

27
 SEMANTIC LAYER PHASE
› Could be first or second phase or
concurrently along with database
migrations
› Very easy upgrade
› If on 1200 or above compatibility
SSAS Tabular, just provision AAS
Server and deploy
© 2017 DMI CONFIDENTIAL & PROPRIETARY

› If sources are still on-premise will

need On-premise Data Gateway
› If sources are already moved to
Azure no Gateway necessary

28
 DATA INTEGRATIONS PHASE
› Most complex phase, best to save for
last
› More other services that are in Azure
the easier
› Will likely run into networking, account
access, scheduling complexities
› Switching to native ADF copy\transform
© 2017 DMI CONFIDENTIAL & PROPRIETARY

activity pipelines requires new dev

› Still complexities if move to SSIS
Integration Runtimes (even on Managed
Instances)
› Must join SSIS IR to vNet
› Managing uptime of SSIS IR to control
29 costs
 RESOURCES LINKS

› Data Migration Assistant › Azure Analysis Services

› Azure SQL DB Options › AAS Web Designer
› Azure SQL Data Warehouse › Power BI
›
© 2017 DMI CONFIDENTIAL & PROPRIETARY

› Snowflake on Azure Power BI Report Server

› Azure Data Factory › Blogs, Google and explore!
› Integration Runtimes
Questions?

31
© 2017 DMI CONFIDENTIAL & PROPRIETARY