Migrating SQL Server Datawarehouse to Azure

You have a Datawarehouse and a BI solution in place. You probably have some doubts about how it will scale or if it is aligned with modern trends. Then this workshop is for you!

A modern data warehouse lets you bring together all your data at any scale easily, and allows you to get insights through analytical dashboards, operational reports, or advanced analytics for all your users. The cloud, of course, provides all of that and more but with a complex mixture of services, that you need to carefully select. You need to plan your costs, your data movement, modeling engine, data storage and most importantly - how are you going to consume that data in an optimal way. Choosing the right service for the right task is crucial if you want scalable, performant and easy to maintain platform.

In this workshop you will get an architectural overview of the Azure BI Data Platform stack with focus on industry patterns, strategies to migrate existing DWH and BI solutions, including SSIS, SSAS, SSRS to an environment, where only the sky is the limit. You will understand what is behind the buzzwords like Data Lake, Azure Analysis Services, Azure DataBricks, Azure Data Factory and Azure Data Lake, and you will get familiar with them. You will dive into the modern DW concept and Synapse Analytics. You will get all the help you need and the knowledge to select the right data architecture or data pipeline for your scenario, and then select the Azure services and technologies that best fit your requirements.

Training overview

This migration training is focusing on describing the Azure services that participate in the modern Datawarehouse deployment first (such as Azure Data Lake, Azure Data Factory, Azure SQL DB, Azure Synapse Analytics, Azure Analysis Services, PBI). It also covers extension of the traditional DWH with big-data scenarios and potential services and how they integrate with the traditional data flow.

During the hands-on part of the workshop we take an on-premise deployment of SQL Server Integration Services, SQL Server Database and SQL Server Analysis Services, that is pre-prepared by Inspir-IT, and migrate it to Azure, discussing the different migration scenarios and deciding what is the best approach out of all available ones.

In the end of this service, participants will be able to:

• Describe and distinguish between different scenarios for implementing Modern Datawarehouse and Advanced Analytics solutions by using services, such as: Azure Blob Storage, Azure Data Lake, Azure Data Factory, Azure SQL DB, Azure SQL DWH, Azure Analysis Services, Azure DataBricks/Azure ML
• Be able to create Pipelines in ADF for automation of ETL, and migration AS-IS of the existing SSIS packages
• Describe and implement different migration strategies for on-premises SQL DWH databases to Azure (both to SQL DB and SQL DWH) – Lift and Shift and Modernize or Redesign. Understand the mechanics of migration and the available tooling.
• Get awareness on how to implement migration of existing models to Azure Analysis Services

Course overview

• Day 1: Educate session
• Day 2–4: Hands-on days
• Day 5: Design and planning session

The Design and planning stage is an extension of the service, where the knowledge from the first stages are applied on a fictious customer’s Datawarehouse. During the 5th day, an architecture example will be reviewed, and a complete migration scenario will be high-level designed and scoped. The Design day includes series of scoping and planning sessions. This day will give attendees extremely useful knowledge of how to approach a migration to the cloud, which they can apply in their existing Datawarehouse environments.

Out of scope for the standard delivery (Education and Hands-on stages)

This service is not covering the following:

• Customer’s databases, SSIS packages or any other artefacts existing in a customer environment

Audience

Datawarehouse specialists, data experts, BI developers and all others who deal with data and are responsible for creating, managing and maintaining your company’s Datawarehouse solution.

Prerequisites

• Experience with SQL Server Relational DW, star schema, SSIS, SSAS
• Azure Fundamentals or similar

Inspirit will provide pre-staged virtual machines and data to be used for the delivery.
Customer must have: Azure subscription

Workshop Agenda 

 

Day 1: Services Overview

• Module 1: Overview of Azure data platform related services – service description, pricing, typical tasks it solves and typical architectures it participates in.
• Module 2: Architectural overview of an Azure data platform solution
  • 1. Building blocks
  • 2. SLAs – per service and calculating the total solution SLA
  • 3. BCP/DR – per service
  • 4. Scalability
    • a. Typical solution and design patterns

Day 2: Approaching a migration, lift and shift to the Cloud

• Module 3: Migration Approaches and tools
  • 1. Common migration scenarios – lift and shift or modernize
  • 2. Mapp target architecture and services in Azure to existing solution and migration requirements
  • 3. Common migration steps and phases
  • 4. Tooling set for performing assessment and migration
  • Hands-On: Case Study and Migration assessment
• Module 4: The Lift and shift Approach
  • 1. Data Layer
  • 2. SSIS
  • 3. SSAS
  • 4. SSRS

Day 3 Tune and Modernize

• Module 4: Hands-ON: Lift and shift of existing solution to the cloud
• Module 5: Azure Synapse Analytics
  • 1. Diving into Synapse Analytics
  • 2. Data Store and ADL
  • 3. SQL Pool and important design concepts
  • 4. ETL options
  • 5. Data Discovery
  • 6. Data Visualization

Day 4 HandsOn

• Module 4: Hands-On: Modern DW with Azure Synapse Analytics
• Summary and Case study

Day 5 Design and Planning session

• Building a roadmap of DW Migration

 

Other relevant courses