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