Video 2: SSMS Tool, SQL BASICS - 1

• SSMS Tool Installation, Connections
• SQL Server Management Studio
• Single Server, Multi Server Connections
• System Databases: Master and Model
• MSDB, TempDB, Resource Databases
• SQL and T-SQL : Basic Differences
• DDL, DML, SELECT Statements

• Using GUI and SQL Scripts in SSMS
• Creating Databases : Files [MDF, LDF]
• Creating Tables in SQL Server
• Data Storage in Tables : Inserts
• SELECT : Data Retrieval Statement
• Table Scans. LIVE QUERY STATISTICS
• Limitations with SSMS GUI Options

Video 11: RESTORES & DB RECOVERY

• Restore Phases - COPY, REDO, UNDO
• RECOVERY, NORECOVERY Options
• STANDBY and REPLACE in Restores
• File, File Group & Metadata Restores
• Backup Verifications using GUI, Scripts
• VERIFYONLY : Backup Verification
• STATS, UNLOAD, STOPAT and INIT

• PARTIAL / PIECEMEAL Restores - Use
• Tail Log Backup Usage in Real-time
• Restores using GUI and T-SQL Scripts
• MOVE Options for File Level Restores
• Point-In-Time Restore, Checkpoint LSN
• Standby Restores and Read-Only State
• Common Errors and Solutions

Video 20: REPLICATION For HA - Level 2

• Merge Replication and Merge Agent Job
• Replication Conflicts and ROWGUIDCOL
• Subscription Reinitialization, Expiry Setting
• Server Subscription & Client Subscription
• Peer-Peer Replication Connections, Nodes
• NodeID and Conflict Detection Options
• Replication Conflicts and sp_MSRepl

• sp_changedbowner, backup initialization
• Replication Conflicts and Priority Settings
• Replication Verify - Rowcount, Checksum
• Disabling, Cleaning Replication Topology
• Replication Strategies for HA and DR Plan
• Replication for Load Balancing Topologies
• Common Errors and Solutions

Video 3: SQL BASICS For DBAs - 2

• Creating Databases in SQL Server
• Database Connections and Usage
• Creating Tables and Data Storage
• Data Inserts and SELECT Statement
• WHERE Conditions and Keywords
• IN Operator and NOT IN Operator
• Between, Not Between Operators
• LIKE and NOT LIKE Operators

• UPDATE Statement & Conditions
• DELETE & TRUNCATE Statements
• Logged and Non-Logged Operations
• Table Structure Modifications
• ADD, ALTER and DROP Columns
• Aliases and Batch Statements

Video 12: JOBS, MAINTENENCE PLANS

• SQL Server Agent Service & Agent XPs
• SQL Agent Jobs - GUI, Script Creations
• Job Steps - Creation, Edits and Parse
• Job Executions, Disable/Enable Options
• Job History Purge. Job Activity Monitor
• Database Maintenance - Backup Jobs
• Scheduling Database Maintenance Plans
• Automated Job Creations using DMPs

• Backup Cleanup & History Cleanup Jobs
• Backup Strategies For Minimal Data Loss
• Backup Options: Block Size, Transfer Size
• DB Mail Configurations and Alert System
• DB Mail Profiles, SMTP Email Accounts
• Operators : Creation, Job Notifications

Video 21: LOG SHIPPING (HA - DR)

• Log Shipping Topology for HA and DR
• Primary and Secondary: Recovery Plan
• Log Shipping Monitor, Jobs and Alerts
• NORECOVERY Mode - Configuration
• STANDBY Mode Configuration & Jobs
• Log Shipping Jobs and Manual Failover
• Disconnections and Delay in Restores

• Log Shipping Mode Changes - cautions
• Re-Restoring Log Backups for Recovery
• LSBackup, LSCopy & LSRestore Jobs
• LS Job Audits, Dashboards (Reports)
• TUF Files and Standby Options in LS
• Broken Log Shipping Chains & Issues
• Common Errors and Solutions

Video 4: SCHEMAS, TEMP TABLES

• Schemas : Group Tables in Database
• Schemas : Security Management Object
• Creating Schemas & Batch Concept
• Using Schemas for Table Creation
• Data Storage in Tables with Schemas
• Data Retrieval, Usage with Schemas
• Table Migrations across Schemas

• Import and Export Wizard in SSMS
• Data Imports with Excel File Data
• Performing Bulk Operations in SSMS
• Temporary Tables : Real-time Use
• Local and Global Temporary Tables
• # and ## Prefix, Scope of Usage
• Session Level, Connection Level Use

Video 13: SECURITY MANAGEMENT - 1

• Authentication Types & Modifications
• Windows Logins & SQL Server Logins
• Logins - Users Mapping, DB Access
• Server Roles & Database Roles - Usage
• Object, Column and Schema Security
• GRANT, WITH GRANT, DENY, REVOKE
• CONTROL, OWNERSHIP, Authorization

• Data Encryption: Keys and Certificates
• Data Encryption with Stored Procedures
• Job Security : Credentials and Proxies
• Using Proxies for SSIS Jobs, Repl Jobs
• Security Scripts and Documentation
• DMVs for Security Audits, Orphan Users
• Login, User, Server Principal Audits

Video 22: DB MIRRORING (HA - DR)

• DB Mirroring Architecture For HA & DR
• Log Shipping Versus Database Mirroring
• TCP Endpoints, TCP Network Security
• Heartbeat and Polling Concepts in DM
• Automatic Fail-Over Procedures, Tests
• PARTNER OFFLINE Conditions, Options
• DB Mirroring Monitors and Commit Loads

• SYNCHRONOUS & ASYNCHRONOUS
• DB Mirroring and Port Configurations
• Mirroring Monitor, Stop/Resume Options
• Need for Always-On & Higher Availability
• DB Recovery without Witness. Failover
• Mirroring Monitor Jobs - Real-time Usage
• Common Errors and Solutions

Video 5: CONSTRAINTS, INDEX Basics

• Constraints and Keys - Data Integrity
• NULL, NOT NULL Property on Tables
• UNIQUE KEY Constraints: Importance
• PRIMARY KEY Constraint: Importance
• FOREIGN KEY Constraint: Importance
• REFERENCES, CHECK and DEFAULT
• Candidate Keys and Identity Property

• Database Diagrams and ER Models
• Relationships Verification and Links
• Indexes : Basic Types and Creation
• Index Sort Options, Search Advantages
• Clustered and NonClustered Indexes
• Primary Key and Unique Key Indexes
• Need for Indexes - working with Keys

Video 14: MIGRATIONS & SECURITY - 2

• Database Migration Precautions
• Migration Methods & Comparisons
• CDW: Copy Database Wizard @ SSMS
• Database Detach and Attach Options
• SMO Method and Database Scripting
• Creating and Using Credentials
• Creating and Using Agent Proxies

• SSIS Job Subsystem with Proxies
• Copy Database Wizard: SSIS Packages
• Scheduling Database Migration Jobs
• Transfer of Logins & Server Objects
• Detecting and Resolving Orphan Users
• Containment Databases Authentication
• Login Failure Audits & Server Logs

Video 23: Health Checks, Issues, Solutions

• Alerts : Creation and Notifications
• DB Suspect Event Alerts (023)
• Important Perfmon Counters, Alerts
• Log Space, Memory, Tempdb Alerts
• DBCC CHECKDB : DB Health Checks
• Allocation Errors, Consistency Errors
• DBCC ShowContig, Extent Fragmentation

• Trace Flags and EstimateOnly
• DBCC Page: GAM, SGAM and PFS
• Consistency Errors : Cause & Solutions
• Allocation Errors : Cause and Solutions
• Log Space Issues and Log Rebuilds
• Memory & TempDB Issues, Solutions
• DBCC ShrinkDB and Page Restores

Video 6: JOINS & AUDITS

• JOINS - Table Comparisons Queries
• INNER JOINS For Matching Data
• OUTER JOINS For (non) Match Data
• Left Outer Joins with Example Queries
• Right Outer Joins with Example Queries
• FULL Outer Joins - Realtime Scenarios
• Join Queries with "ON" Conditions

• Join Unrelated Tables in SQL Server
• NULL, IS NULL Operators in Joins
• CROSS JOIN and CROSS APPLY
• CROSS JOIN Versus CROSS APPLY
• One-way, Two Way Data Comparisons
• Important Join Queries in T-SQL
• Join Options: Merge, Loop, Hash

Video 15: Tuning 1 - Audits, Indexes

• Audit Long Running Queries : DMV, DMF
• Activity Monitor Tool, Server Dashboards
• Logical I/O, Physical I/O, Database I/O
• Recent Expensive Queries, Wait Time
• Active Expensive Queries, Statistics
• Plan Handle, Execution Time - Audits
• CPU, IO, Memory Consumption Reports

• Indexes: Architecture and Index Types
• B Tree Structure, IAM Page [Root]
• Clustered & NonClustered Indexes
• Included, Columnstore, Online
• Filtered, Covering, Indexed Views
• Fill Factor and Pad Index Options
• Query Store - Settings and Advantages

Video 24: PATCHES, UPGRADES, CUs

• Establishing Downtime For Maintenance
• Precautions for Maintenance Activities
• DB Backups, Scripting and Services
• Service Packs and Patch/Hotfix Activities
• Cumulative Updates (CU), Hotfix Process
• Instance Selectivity for Updates, Cautions
• Verifications, Smoke Test and Rollbacks

• Multi Instance Updates & Port Changes
• SERVER Upgrades & VERSION Changes
• System Database REBUILDs using CMD
• Silent Installation & Installation Repairs
• SQLCMD Tool and Instance Connections
• DAC : Dedicated Administration Console
• Service Startup Issues and Solutions

Video 7: View, Procedures, UDF Basics

• Views : Types, Usage in Real-time
• System Predefined Views and Audits
• Listing Databases, Tables, Schemas
• Functions : Types, Usage in Real-time
• Scalar, Inline and Multi-Line Functions
• System Predefined Functions, Audits
• DBId, DBName, ObjectID, ObjectName
• Date and Time Functions : Getdate()

• Variables & Parameters in SQL Server
• Procedures : Types, Usage in Real-time
• User & System Predefined Procedures
• Parameters and Dynamic SQL Queries
• Sp_help, Sp_helpdb and sp_helptext
• sp_pkeys, sp_rename and sp_help
• sp_recompile, Perrformance Benefits
• System Objects for Metadata Access

Video 16: Tuning 2 - INDEX MANAGEMENT

• PARTITIONS : Advantages, Performance
• Partition Functions & Partition Schemes
• Partitioning Un-partitioned Tables: GUI
• Partition Compression : ROW and PAGE
• Auditing Table Partitioned Structures
• Statistics : Purpose, Auto Creation
• Statistics : Audits and Updates
• Working with Indexes and Partitions

• Internal and External Fragmentation
• Index Rebuilding Process and Audits
• Database Maintenance Plans Jobs
• Last Used, Page Count, Fragmentation
• Index Page Count and Index Condition
• Degree Of Parallelism [DOP] Settings
• Resumable Indexes: ONLINE, RESUME
• PAUSE & RESUME in Index Rebuilds

Video 25: ALWAYS ON AVAILABILITY

• Always-On: Non-Clustered Environment
• Multi Database Replication with AOAG
• Synchronous and Asynchronous Modes
• Policy Based Management for AOAG
• Facets and Conditions for Policies
• Backup Preferences, Location Options
• Synchronization, Automated Seeding
• Data Synchronization for AOAG

• Port Settings, Backup Strategies: AAG
• AOAG Verifications and Dashboards
• Adding Availability Replica, Database
• Adding Availability Listeners and DNS
• Automated Failovers, Manual Failovers
• Always-On Availability Groups Health
• NonClustered Environment Limitations
• Clustering with Always-On in Ch 37,38

Video 8: Triggers & Transactions

• Triggers - Purpose, Real-world Usage
• FOR/AFTER Triggers - Real time Use
• INSTEAD OF Triggers - Real time Use
• INSERTED, DELETED Memory Tables
• Using Triggers for Data Replication
• Enable Triggers and Disable Triggers
• Database Level, Server Level Triggers
• Auditing Triggers and Real World Use

• Transactions : Types, ACID Properties
• Transaction Types and AutoCommit
• EXPLICIT & IMPLICIT Transactions
• COMMIT and ROLLBACK Statements
• Open Transaction Scenarios & Cause
• Query Blocking Scenarios @ Real-time
• NOLOCK and READPAST Lock Hints

Video 17: Tuning 3 - Tuning Tools

• Tuning Tools: Workload Files, .trc Files
• Profiler Tuning Template, SP Events
• DTA, Profiler Trace : Recommendations
• PDS: Physical Design Structures
• Index, Stats, Partition Recommendations
• DTA with Query Execution Cache
• Perfmon Tool : Usage, Permon Counters
• Real-time Tracking: CPU, Memory, IO

• Execution Plan Analysis and Internals
• Query Costs: IO Cost and CPU Cost
• Query Costs: SubTree & Operator Cost
• NUMA Nodes, Processor, IO Affinity
• Thread Count, Degree of Parallelism
• Stored Procedure Recompilations
• Table Scan, Index Scan, Index Seek

Video 26: SQL DBA PROJECT - Level 1

• Audit Login Failures : Server Logs
• Monitoring Connectivity Issues
• Database Refresh and MSDTC
• Adhoc Memory Dump Files
• PLE (Page Life Expectancy) Issues
• Object Refresh and Recompilations
• Server Registrations and Operations

• Lock Monitoring Operations
• Index Management Options
• Open Transactions, Blocking
• Metadata Sync-up Issues
• Stored Procedure Recompilations
• Backup and HA-DR Strategies
• Db Restores and DB Repairs
• Health Checks, Issues, Solutions

Video 9: Server, DB Architecture

• Server Architecture and Protocols
• Database Engine and Query Processor
• Parser, Optimizer, SQL & DB Manager
• Storage Engine Components, SQL OS
• File Manager and Database Files
• Transaction Services, Buffer Manager
• Lock Manager, IO Manager, MDAC
• CLR, WAL, Lazy Writer, Checkpoint

• Database Architecture - Data Files
• Primary (mdf), Secondary Files (ndf)
• Filegroups; File Size, Location
• Pages, Extents. Uniform, Mixed Extents
• Transaction Log File [LDF], LSN, VLF
• Linked Servers and Remote DB Access
• RPC, RPC Out and Remote Data Access

Video 18: Tuning 4 - Lock Management

• LOCKS : Types and Isolation Levels
• S, X, IX,U, MD, Sch-M and Sch-S
• Lock Audits : SP_WHO2 & SP_LOCK
• sysprocesses and Lock Waits : Audits
• Open Transactions, Query Blocking
• Lock Hints and Isolation Levels
• Read Committed, Read Uncommitted
• Serializable and Repeatable Read

• Snapshot Isolation, Page Versioning
• Read Committed Snapshot Row Version
• Choosing Correct Isolation Level
• Profiler Tool and Lock Templates
• Profiler Filters, Column Selections
• Deadlock Audits and Deadlock Graphs
• XDL Files and Deadlocks Prevention

Video 27: SQL DBA PROJECT - Level 2

• Server Down Issues and Solutions
• Database Down Issues and Solutions
• Data Missing Issues and Solutions
• Hot CPU and Resource Allocations
• Port Level Issues and Solutions
• Online & Offline Backups, Certificates

• Ticketing Tools : SLA & OLA Concepts
• Incident Management and Ticketing
• Immediate, High and Medium Priority
• Levels of Support for Production DBA
• 3rd Party Tools and Real-time Use
• Automated Backups, Log File Readers
• Licensing and Pricing Options, CALs
• Device CALs and User CALs
• Multiplexing with Server Licenses

Chapter 1: Cloud Basics, Azure SQL DB

• Cloud Introduction and Azure Basics
• Azure Implementation: IaaS, PaaS, SaaS
• Benefits of Azure Cloud Environment
• Azure Data Engineer: Job Roles
• Azure Storage Components
• Azure ETL & Streaming Components
• Need for Azure Data Factory (ADF)
• Need for Azure Synapse Analytics

• Azure Resources and Resource Types
• Resource Groups in Azure Portal
• Azure SQL Server [Logical Server]
• Firewall Rules and Azure Services
• Connections with SSMS & ADS Tools
• Working with Azure Portal
• Resource Group Navigations, Options
• DP 203 Certification Guidance

Chapter 1: Azure Storage & Containers

• Storage Components in Microsoft Azure
• Azure Storage Services and Types - Uses
• High Availability, Durability & Scalability
• Blob: Binary Large Object Storage
• General Purpose: Gen 1 & Gen 2 Versions
• Blobs, File Share, Queues and Tables
• Data Lake Gen 2 Operations with Azure
• Azure Storage Account Creation

• Azure Storage Container: Usage
• Azure Data Explorer: Operations
• File Uploads, Edits and Access URLs
• Azure Storage Explorer Tool Usage
• Azure Account Options in Explorer
• Directory Creation, File Operations
• End User Access Options With Files
• Data Explorer Vs Storage Explorer Tool

Chapter 1: Azure Intro, Azure Databricks

• Azure Databricks : Purpose & Config
• Need for Azure Databricks (ADB)
• Azure Databricks Service Creation
• Azure Databricks Workspace & Usage
• Spark Cluster Configurations & Capacity
• Driver Nodes and Worker Nodes in Spark
• Master Node & Cluster Creation Process
• Cluster Types and Capacity Options

• Standard, High Concurrency Clusters
• Databricks Runtime Service & DBUs
• Databricks File System (DBFS) and Usage
• Azure Databricks Workspace Operations
• ETL and Data Storage Components
• Spark Concepts and Spark SQL
• Spark Context and Spark Session
• DataFrame, Dataset and Real-time Use

Chapter 2: Synapse SQL Pools (DWH)

• Dedicated SQL Pools in Azure
• Enterprise Data Warehouse with Synapse
• DWU: Data Warehouse Units, Resources
• Massively Parallel Processing (MPP)
• Control Nodes and Compute Nodes
• SQL Pool Access from SSMS Tool
• T-SQL Queries @ SQL Pools
• Start/Resume/Pause, Scaling Options

• Creating Tables in Azure SQL Pool
• Compression, MAX DOP & Indexes
• Distributions: Round Robin, Hash
• Distributions: Replicate and Usage
• Data Imports with COPY Table
• Dynamic Views (DMV) with PDW
• Data Loads Monitoring, Resource Class

Chapter 2: Azure Migration, BLOB Imports

• SQL Server (On-Premise) to Azure Migration
• Source Database Scripts & Validations
• BACPAC File Generation From SSMS Tool
• Azure Data Lake Storage and SSMS Access
• Azure Storage Container, BACPAC Files
• Azure SQL Server Creation From Portal
• Azure SQL DB Imports, Storage SAS Keys
• Azure SQL Database Migrations, Verification

• BLOB Data Access from On-Premise
• Data Imports From Excel and CSV Files
• BLOB Data Imports using T-SQL Queries
• SAS - Shared Access Signature Generation
• CSV File - Uploads, Downloads, Edits, Keys
• Master Keys, Credentials, External Sources
• BULK INSERT Statement and Data Imports
• T-SQL Imports : Practical Limitations

Chapter 2: SQL Notebooks & Python

• Notebooks: Concept, Usage Options
• Creating SQL Notebooks in Databricks
• Using DBFS Tables in SQL Notebooks
• Data Access and Analytics Options
• SparkSQL Queries: SELECT, GROUP BY
• SparkSQL Queries: Aggregates, Conditions
• Notebook Operations: Download, Clone
• Notebook Operations: Upload, Reuse

• SQL Notebooks with Python Code
• Using DBFS Sample Data Sources (CSV)
• Dataframes: Creation and Real-time Use
• Pandas Dataframe, Virtual Table Creation
• Dataframe Data Access, Caching Options
• Take() and Display() Functions in PySpark
• Temporary View Creation and Access
• SparkSQL Queries, Analytics, Chart Reports

Chapter 3: Azure Data Factory Concepts

• Azure Data Factory (ADF) Concepts
• Hybrid Data Integration at Scale
• ADF Pipeline Components & Usage
• Configure ADF Resource in Azure
• Understanding ADF Portal and IR
• Linked Services and Connections
• Datasets and Tables / Files for ETL
• ADF Pipelines: Design, Publish & Trigger

• ADF Pipeline with Copy Data Tool
• Creating Azure Storage Account
• Storage Container, BLOB File Uploads
• Data Loads with Azure BLOB Files
• DIU Allocations and Concurrency
• Creating Linked Services, Datasets
• Pipeline Trigger, Author and Monitor

Chapter 3: Azure Tables, Shares

• Azure Tables - Real-time Usage
• Schema-less Design and Access Options
• Structured and Relational Data Storage
• Tables, Entities and Properties Concepts
• Azure Tables: Creation and Data Inserts
• Azure Tables in Portal - GUI and Data Types
• Azure Tables: Data Imports in Explorer
• Data Edits, Queries & Delete Operations

• Azure Files - SMB Protocol, Creation, Usage
• Shared Access, Fully Managed & Resiliency
• Performance, Size Requirements for Shares
• Azure Storage Explorer Tool for File Shares
• Azure Queues: Message Queues, Limitations
• Adding Messages, Queuing and De-Queuing
• Data Access & Clear Queue from Explorer
• End Points for Azure Message Queues

Chapter 3: Python Notebooks

• Azure SQL Server Configurations
• Azure SQL Database Creation
• Azure Firewall Rules and IP Address
• Allow Azure Services, Remote Access
• Connection Tests with SSMS Tool
• Python Notebooks with Azure Databricks
• Data Imports and Table Creations (Code)
• Parquet Files and Usage in Databricks

• Using Dataframes for Data Operations
• SparkSQL Queries with SELECT, TOP
• Establishing Connections to Azure SQL DB
• JDBC Connection Strings, DataframeWriter
• JDBC Properties, Port Settings & Options
• Data Extraction, SQLContext & Dataframes
• Pandas Data Frame for Big Data Analytics
• JDBC URL Options & PySparkSQL Modules

Chapter 4: ADF Pipelines, Polybase

• Copy Data Tool For ETL Operations
• Azure SQL DB to Synapse Data Loads
• Working with Multi Tables Data Loads
• Query Options for Source Datasets
• Transformations with Copy Data Tool
• Rename, Rearrange & Remove Options
• Pipeline Execution: DTU & DOCP
• ADF Pipeline Monitoring Options

• ADF Pipelines: Execution Settings
• ADF Logging Options, Consistency Check
• Compression Option, DOP and DOCP
• ETL Staging Advantages & Performance
• Staging with Storage Account, Container
• ADF Pipeline Triggers and Monitoring
• Polybase For Azure Synapse, Advantages

Chapter 4: Azure Storage Security, Admin

• Azure Data Lake Storage Security Options
• Shared Access Keys - Primary, Secondary Keys
• SAS Key Generation: Container, Tables, Files
• SAS Key Permissions, Validation Options
• Access Keys: Account Level Permissions
• Azure Active Directory (AAD): Users, Groups
• Azure AD Security: RBAC with IAM, ACLs
• Owner Role, Contributor and Reader Role

• Azure Data Lake Storage Security Options
• ACL : Access Control Lists & Security
• Azure BLOB Storage Containers & ACLs
• Folder Level and File Level Security
• ACL Permissions: Read, Write & Execute
• Access Policy: Creation and Realtime Use
• Permissions: rwacdl; Azure Principals, CORS
• Comparing IAM and ACLs in Data Lake Store

Chapter 4: Open Data Sources, DeltaLakes

• Creating Python Notebooks with Databricks
• Spark Dataframes with Azure OpenDatasets
• Windows Azure Storage Blob [wasb] Sources
• Creating Dataframes & Temporary Views
• Using Print and Display Functions with ADB
• Big Data Analysis with BLOB Data & Charts
• Keys, Values, Aggregations, Display Type
• Databricks Notebooks, Jobs and Stages

• Azure DeltaLake Implementation
• ACID Properties and Upsert Advantages
• Delta Engine Optimizations & Uses
• Pipeline Creation with JSON Files in DBFS
• Delta Tables Creation, Data Loads
• Spark Cluster Settings: Auto Optimize
• Auto Compact and Delta Table Optimize
• Delta Locations; Data Retrieval, Versions

Chapter 5: OnPremise Data with ADF

• On-Premise Data Sources with Azure
• Self Hosted Integration Runtime (IR)
• Access Keys, Remote Linked Services
• Synapse SQL Pool (DW) with OnPremise
• Staged Data Copy and Performance
• Pipeline Executions and Monitoring
• Pipeline RunIDs and Audits / Tracing
• Incompatible Rows Skips, Fault Tolerance

• Incremental Loads with Files (BLOB)
• Pipeline Executions and Schedules
• Regular Schedules and Tumbling Window
• Execution Retry and Delay Options
• Binary Copy, Last Modified Date in Blob
• Automated Loops and Trigger Schedules
• Incremental Loads Verification Tests

Chapter 5: Azure Monitoring, Power BI

• Azure Monitor, Metrics & Logs
• Monitoring Azure Storage Namespaces
• Add KQL Metrics; Account, Blob and File
• Total Ingress and Egress Metrics: Charts
• Average Latency, Transaction Count
• Request Breakdowns, Signal Logic Options
• Azure Alerts and Conditions, Notifications
• Signal Logic Conditions and Emails

• Power BI Desktop Tool Installation
• Binary Data and Record Data Access
• Azure Data Lake Storage: Access Keys
• Azure Data Lake Storage with Power BI
• BLOB File Access with Power BI
• Azure Tables Creation and File Imports
• Azure Table Access with Power BI

Chapter 5: Databricks Security & Jobs

• Azure Databricks Security Operations
• Azure Active Directory (Azure AD)
• AD Users and RBAC with IAM
• Owner, Contributor & Reader Roles
• Workspace Admin Permissions
• Notebook Permissions and Share Options
• Shared Notebooks, User Access Options
• Notebook Operations: Clone & Export

• Databricks Jobs: Creation Options, Usage
• Job Limits, Workspace, Concurrency Limits
• Notebooks with and without Parameters
• Jobs with Default Parameters, Executions
• Interactive, Automated Clusters for Jobs
• Job Schedules and Manual Executions
• Active Jobs, Recently Run Jobs, Monitoring
• ADB Jobs with Azure OData Sources, BLOB

Chapter 6: ADF Data Flow - 1

• Limitations with Copy Data Tool
• Data Flow Task, Data Flow Activity
• Transformations with Data Flow
• Spark Cluster For Debugging
• Cluster Node Configurations
• Data Preview Options with DFT
• SELECT Transformation & Options
• JOIN Transformation and Usage

• Conditional Split Transformation
• Aggregate & Group By Transformations
• Synapse Sink Options with DFT
• DFT Optimization Techniques
• Pipeline Debug Runs and ETL Testing
• Spark Cluster For Pipeline Executions
• Pipeline Monitoring & Run IDs

Chapter 6: Azure Stream Analytics, IoT

• Azure Stream Analytics: Real-time Usage
• Real-time Data Processing, Event Tracking\
• Ingest, Deliver and Analysis Operations
• Azure Stream Analytics Jobs Concept
• Understanding Input & Output Options
• SAQL Queries for Stream Analytics Jobs
• IoT: Internet Of Things For Real-time Data
• Need for IoT Hubs and Event Hubs

• Creating IoT Device for Data Inputs
• Creating Azure Strean Analytics Resource
• Stream Analytics Jobs for Historical Data
• Azure SQL Database Options for ASA Jobs
• SAQL: Query Formatting and Validation
• Historical Data Uploads, ASA Job Execution
• Stream Analytics Job Monitoring Options

Chapter 6: Databricks @ BLOB, Power BI

• BLOB Data Access with Databricks
• Accessing Storage Account, Container
• Gerate, Use SAS: Shared Access Signature
• dbutils.fs.mount() with DBFS Store
• fs.azure.sas.container.strorageaccount
• spark.read() and DBFS Mounts
• Scala Transformations, Create Temp View
• Spark SQL Queries with Temp Views

• dataframe.write.jdbc() & JVM Properties
• spark.read.jdbc() with Azure SQL DB
• Power BI Integration with Databricks
• Server Host Name, Port and Http Path
• Cluster Configurations and JDBC
• User Access Token Generation, Usage
• Spark ClusterAccess, Power BI Analytics

Chapter 7: ADF Data Flow - 2

• ADF Pipelines For ETL Operations
• Data Flow Tasks and Activities in Synapse
• Pivot Transformation For Normalization
• Generating Pivot Column, Aggregations
• Pivot Transformation and Pivot Settings
• Pivot Key Selection, Value and Nulls
• Pivoted Columns and Column Pattern
• Column Prefix, Help Graphic & Metadata

• Window Functions & Usage in Data Flow
• Rank / DenseRank / Row Number
• Over Clause and Input Options
• Derived Column Transformations
• Exists & Lookup Transformations
• Reusing Data Flow Tasks in Synapse
• Pipeline Validations & Executions

Chapter 7: IoT Hubs & Event Hubs

• Azure Stream Analytics For API Data
• IoT Hubs & IoT Devices, Connection Strings
• Rasberry APP Connections with IoT Hub
• Azure Storage Account and Container
• Creating Azure Stream Analytics Job
• Configuring Input Aliases with IoT Hub
• Configuring Output Alias with ADLS Gen 2
• SAQL Query and Job Executions; Monitoring

• Azure Event Hubs and Event Instances
• Event Hub Namespaces, Partition Counts
• Access Policies, Permissions & Defaults
• RootManageSharedAccessKey & Options
• Connection Strings & Event Service Bus
• Telco App Installation, Executions. LIVE Data
• On-Premise App Integration with ASA Jobs

Chapter 7: Databricks Integrations

• Azure Databricks with Data Lake Storage
• Handling Unstructured Data in Azure
• Data Preparation and Staging Operations
• Azure App (Service Principal) Registration
• Azure Key Vault Creation & Key Usage
• Service Principal Permissions @ Data Loads
• Tenants and Authorization Settings
• Client Credentials, Token Provider Options

• Spark Notebooks For Dynamic Connections
• Parameterized Options & Blob Access
• Data Preparation & Big Data Ingestion
• Data Extraction and ADLS Storage
• show(), transformations, wasbs Options
• Azure SQL Server & Synapse Creations
• Data Loads with Incremental Changes

Chapter 8: Azure Synapse Analytics

• Azure Synapse Analytics Resource
• Azure Synapse Analytics Workspace
• Managed Resource Group, SQL Account
• SQL Admin Account and its Purpose
• Operations with Synapse Workspace
• ADLS Gen 2 Storage Account, Container
• Synapse Studio (Synapse Portal)
• Dedicated SQL Pools & Spark Pools

• Creating Dedicated SQL Pools
• Synapse Tables, Data Loads with T-SQL
• COPY INTO Statements with T-SQL
• Clustered Column Store Indexes
• Row Terminator and Compressions
• T-SQL Queries and Aggregations
• Aggregation Data Loads in Synapse

Chapter 8: Azure Stream Analytics Security

• Azure Key Vaults & ADLS [Data Lake] Security
• Azure Passwords, Keys and Certificates
• Azure Key Vaults - Name and Vault URI
• Inbuilt Managed Key and Azure Key Vault
• Standard Type, Premium Type Azure Key Vaults
• Secret Page, Key Backups and Key Restores
• Adding Keys to Azure Vaults. Key Type, Size
• Using Azure Key Vaults to secure Resources

• Azure Storage: Replications and DR Options
• LRS: Locally Redundant Storage
• GRS: Globally Redundant Storage
• ZRS: Zone Redundant Storage
• Replication Options and Advantages
• Replication Verification and Modifications
• Azure Storage Endpoints, Failover Partner

Real-Time Project

• ADF Integration, Real-time Project
• Azure Databricks Integrations with ADF
• Defining Scala Notebooks in ADB
• Using Notebooks in Azure Data Factory
• spark.conf.set & fs.azure.account.key
• spark.read.format, Option() and Head()
• Online Retail Database Data Source
• Azure Migrations and ETL Concepts
• Azure SQL Pool (Synapse DWH) Tables

• Apache Spark Pool : Databases, Tables
• Azure Data Lake Storage (ADLS Gen 2)
• Azure Stream Analytics Jobs with IoT
• Azure Data Bricks and DBFS, Notebooks
• Concept wise FAQs, Resume Guidance
• Project Requirement, Solution, FAQs
• DP 203 Certification Guidance

Chapter 9: Synapse Analytics with Spark

• Apache Spark Pool in Azure Synapse
• Spark Cluster Nodes: Vcores, Memory
• Creating Spark Clusters @ Synapse Studio
• Python Notebooks For Remote Access
• Creating Databases in Apache Spark Pool
• Data Loads from Dedicated SQL Pools
• Table Creations, Aggregation Operations
• PySpark Code for Data Operations, Writes

• Serverless Pool in Azure Synapse
• Connections, Usage with Serverless Pool
• Using Azure OpenDatasets in Synapse
• OPENROWSET and BULK Data Loads
• Azure Storage Account : Data Analysis
• Working with Parquet Files in Synapse
• Python Notebooks (Pyspark) in Synapse

Chapter 10: Incremental Loads @ Synapse

• Incremental Loads with Synapse Studio
• Multi Table Merge Operations
• On-Premise Data Sources & Timestamps
• Azure SQL DB Destinations, Watermarks
• Watermark Table Usage & Audits
• Stored Procedures for Timestamp Updates
• Table Data Type and Dynamic MERGE
• SQL Queries for Datasets and Fetch

• Lookup Activity and its Usage un Synapse
• Expressions in ADF Portal for Lookup
• Expressions in ADF Portal for Source
• Output Pipeline Expression, Data Window
• Concat Function, Run IDs Expressions
• JSON Parameters, Pipeline Scheduling
• Pipeline Validation, Trigger and Monitoring

Chapter 11: Optimizations, Power Query

• ADF ETL with GUI : Power Query
• Power Query Resoruce Creation, Use
• Source Data Configurations & Settings
• Rename, Remove, Pivot, Group By, Order
• Index, Filter, Remove Error Rows
• Using Power Query Activity, ADF Pipelines
• Spark Cluster Configurations for Pipelines
• Concurrency, Big Data Recommendations

• Storage Optimization Techniques
• ETL Optimization Techniques
• SQL Pool (Synapse) Optimizations
• Indexes, Partitions, Distributions, DOP
• Pipeline Optimization Techniques
• Partitions, DOCP, Compressions, DIU
• Staging, Polybase and Core Counts

Chapter 12: Pipeline Monitoring, Security

• Azure Monitor Resource and Usage
• Pipeline Monitoring Techniques
• ADF: Pipeline Monitoring and Alerts
• Synapse: Pipeline Monitoring and Alerts
• Synapse: Storage Monitoring and Alerts
• Conditions, Signal Rules and Metrics
• Email Notifications with Azure
• Concurrency, Big Data Recommendations

• Azure Active Directory (AAD) Users, Groups
• IAM: Identity & Access Management
• Synapse Workspace Security with RBAC
• ADF Security with RBAC: Owner, Contributor
• Azure Synapse SQL Pool Security: Logins
• Users, Roles and Resource Classes (RC)
• ADF V1 to V2 Migrations, Considerations