Introduction to Data Warehousing

Introduction to Data Warehousing  

∙ Introduction/Overview of SQL Database. 

∙ Comparing Single Database vs Managed Instance ∙ Creating and Using SQL Server and SQL Database Services. ∙ Azure SQL Database Tools. 

∙ Migrating on premise database to SQL Azure. 

∙ Purchasing Models 

o DTU service tiers 

o vCore based Model 

o Serverless compute tier 

∙ Service Tiers 

o General purpose / Standard 

o Business Critical / Premium  

o Hyperscale 

∙ Planning the Deployment of an Azure SQL Database ∙ Elastic Pools. 

o What is SQL elastic pools 

o When to use 

o Choosing the correct pool size 

o Creating a New Pool 

o Manage Pools 

∙ Monitoring and Tuning Azure SQL Database 

∙ Configure SQL Database Auditing 

∙ Export and Import of Database 

∙ Manage Business Continuity 

o Automated Backup 

o Point in Time Restore 

o Restore deleted databases 

o Long-term backup retention 

o Active Geo Replication 

o Auto Failover Group 

∙ Security Features 

o Server and Database Level IP firewall rules.

1 

Deccansoft Software Services Azure Data Analytics 

o SQL Authentication  

o Azure AD Authentication 

o Advanced data Security 

o Dynamic Data Masking 

o Transparent Data Encryption 

∙ Azure SQL Database Features 

o Multi-model capabilities 

o In-memory 

o Transactional replication 

o SQL Data Sync 

o Temporal tables 

o Job Automation 

o Linked Servers 

o Service Broker 

o Database mail 

o Server-side CRL / .NET Integration 

∙ Backup and Recovery options in SQL Database. ∙ Implement PolyBase 

∙ Implement SQL Database monitoring 

Azure Storage Service 

∙ About Storage Service and Account 

∙ Creating a Storage Account 

∙ Standard and Premium Performance 

∙ Understanding Replication  

∙ Hot, Cold and Archive Access Tiers 

∙ Working with Containers and Blobs 

∙ Types of Blobs (Block, Append, Page) 

∙ Blob Metadata 

∙ Soft Delete 

∙ Azure Storage Explorer 

∙ Transfer Data with AzCopy  

∙ Async Blob Copy

2 

Deccansoft Software Services Azure Data Analytics 

∙ Access blobs securely 

o Access Key 

o Account Shared Access Token 

o Service Shared Access Token 

o Shared Access Policy 

∙ Storage Service Encryption 

o Azure Key Vault 

o Customer Managed Keys 

∙ Configure Custom Domain with SSL 

∙ Scale Blob Storage 

∙ BLOB storage monitoring 

Azure Data Lake 

Overview of Azure Data Lake 

∙ What is Data Lake? 

∙ What is Azure Data Lake? 

∙ Data Lake Architecture? 

Working with Azure Data Lake  

∙ Provisioning Azure Data Lake. 

∙ Explore Data Lake Analytics 

∙ Explore Data Lake Store 

∙ Uploading Sample File 

∙ Using Azure Portal 

∙ Using Storage Explorer 

∙ Using Azure CLI 

Azure Data Lake and U-SQL 

∙ First U-SQL Script 

∙ First Data Lake Analytics Job 

∙ Overview of Job Graph 

Working With U-SQL 

∙ Introduction to U-SQL

Deccansoft Software Services Azure Data Analytics 

∙ Extractors and Outputters 

∙ Azure Data Lake Schema on Read 

∙ Rowset 

∙ U-SQL Datatypes 

∙ Process data From Multiple Files and Filter it 

Using Visual Studio for Development 

∙ Tools Required 

∙ U-SQL Project 

∙ Local Execution 

∙ Cloud Execution 

∙ FileSets and Virtual Column 

Working With U-SQL Catalog 

∙ U-SQL Catalog 

∙ Create / Drop Database, Schema and Table ∙ Create / Drop Schema 

∙ Create / Drop Table 

∙ Create / Drop Index 

∙ Working with Table Valued Function 

∙ Insert Data 

∙ Query Table 

∙ Working with View 

Using C# to extend U-SQL Capabilities 

∙ Using Inline Function and operators 

∙ Using Code-Behind Class 

∙ Using Custom Assembly 

∙ Using Visual Studio to create and register assembly 

Understanding U-SQL JOB 

∙ U-SQL JOB Architecture 

∙ Understanding Job Graph 

∙ Understanding Job Heat Map

4 

Deccansoft Software Services Azure Data Analytics 

∙ Monitoring and Optimizing U-SQL Jobs 

Azure Data Factory 

Introduction to Data Factory 

∙ What is Data Factory? 

∙ Data Factory Key Components 

o Pipeline and Activity 

o Linked Service 

o Data Set 

o Integration Runtime 

Provision Required Azure Resources 

∙ Create Resource Group 

∙ Create Storage Account 

∙ Provision SQL Server and Create Database 

∙ Provision Data Factory 

Working with Copy Activity 

∙ Understanding Data Factory UI 

∙ Copy Data from Blob Storage to SQL Database (Use Copy Data Wizard) ∙ Copy data from storage account to storage account 

o Create Linked service 

o Create Dataset 

o Create Pipeline 

∙ Integration Service 

∙ Copy Data from on-premise SQL Server to Blob Storage 

Working with Activities 

∙ Understanding Lookup Activity 

∙ Understanding for Each Activity 

∙ Filter Activity 

∙ Get Metadata Activity 

Azure Lift and Shift

Deccansoft Software Services Azure Data Analytics 

∙ Provisioning Azure – SSIS Integration Runtime ∙ Execute SSIS Packages from Azure 

∙ Execute SSIS Packages from SSISDB 

Triggers, Monitoring Pipeline 

∙ Debug Pipeline 

∙ Trigger pipeline manually 

∙ Monitor pipeline 

∙ Trigger pipeline on schedule 

SQL Data Warehouse 

Introduction to SQL Data Warehouse 

∙ SQL DW Introduction 

∙ Provisioning and Configuring SQL DW 

Designing and Querying Data Warehouse 

∙ Table Design and Implementation 

∙ Partitioning Tables 

∙ Indexes and Statistics 

∙ Monitoring Queries 

Integrating and Ingesting Data 

∙ Loading Data 

∙ Integrating with other Azure and third-party tools ∙ Migrating to SQL DW 

Managing Data Warehouse 

∙ SQL DW Performance 

∙ SQL DW Security 

∙ Managing Compute 

∙ SQL DW Backup 

∙ Monitoring SQL DW