Ch 1: SQL Database Job Roles

• Introduction to Databases
• DBA Job Roles
• Routine DBA Activities
• Emergency DBA Activities
• ETL Admin Job Scope, Job Growth

Ch 2: Database Intro & Installations

• SQL Server Installations
• Instance & Collations
• SSMS Tool Installation
• Connections, Authentications

Ch 3: SQL Basics V1 (Commands)

• SQL Basics (DDL, DML, etc..)
• Creating Databases, Tables
• Data Inserts (GUI, SQL)
• Basic SELECT Queries

Ch 4: SQL Basics V2 (Commands, Operators)

• DDL: Create, Alter, Drop
• DML: Insert, Update, Delete
• DQL: Select, Fetch
• Add, Truncate Statements
• SQL Operators

Ch 5: Data Types

• Integer Data Types
• Character, MAX Data Types
• Decimal & Boolean Data Types
• Date and Time Data Types
• SQL_Variant Type

Ch 6: Data Imports

• Data Imports with Excel
• Data Imports with CSV
• Auto Detection of Data Types
• OLE-DB Connections
• Order By, TOP, OFFSET

Ch 7: Schemas & Batches

• Schemas & Table Grouping
• Real-world Banking Database
• 2 Part, 3 Part & 4 Part Naming
• Batch Concept & “Go” Command

Ch 8: Constraints, Keys & RDBMS

• Null, Not Null Constraints
• Unique Key & Check
• Primary Key Constraint
• Foreign Keys, Default
• DB Diagrams & ER Models

Ch 9: Joins & Queries

• Joins: Table Comparisons
• Inner Joins & Matching Data
• Outer Joins: LEFT, RIGHT
• Full Outer Joins & Aliases
• Self Joins & Aliases

Ch 10: Views & RLS

• Views: Realtime Usage
• DML, SELECT with Views
• WITH CHECK OPTION
• Row Level Security (RLS)
• Important System Views

Ch 11: Stored Procedures (Practical Overview)

• Stored Procedures: Realtime Use
• Parameters Concept with SPs
• Procedures with SELECT
• System Stored Procedures
• Stored Procedures, Tuning

Ch 12: User Defined Functions (Practical Overview)

• Functions in Real-world
• Scalar & Table Value Functions
• Parameterized Queries
• Variables & Parameters
• Function Executions

Ch 13: Triggers & Automations

• Need for Triggers in Real-world
• DDL & DML Triggers
• For / After Triggers
• Instead Of Triggers
• Disabling DMLs Triggers

Ch 14: Transactions & ACID

• Auto Commit Transaction
• Explicit Transactions
• COMMIT, ROLLBACK
• Checkpoint & Query Blocking
• READPAST, LOCKHINT

Ch 15: CTEs & Tuning

• Common Table Expression
• CTEs for Data Retrieval
• CTEs for DML Operations
• CTEs for Data Cleansing
• CTEs with Row Number

Ch 16: Linked Servers

• SQL Server Instances
• Linked Server: Creation
• Linked Server: Testing
• Scripting Linked Servers
• Realtime Usage
• Remote DB Access

Ch 17: Server Architecture

• Database Engine Components
• Parser, Compiler & Optimizer
• Protocols and Query Processing
• MDAC and CLR Components
• Parsing and Compilation
• Memory Manager & IO Managers
• SQL OS Components, MDAC

Ch 18: DB Architecture (VLDB)

• Planning Large Databases
• Primary, Secondary Data Files
• Filegroups, Spacing and Sizing
• Log File: Usage and Precautions
• Creating Tables with Filegroups
• Pages and Extents for Storage
• VLF, MiniLSN & Checkpoint

Ch 19: Backups

• Database Backups & Types
• DB, Filegroup, File Backups
• Copy Only Backups, Usage
• Partial Backups, Split Backups
• Mirror Backups, Verifications
• Checksum, ContinueOnError

Ch 20: Restores

• DB, Filegroups, File Restores
• GUI Restores: Limitations
• Restoring & Online States
• FILELISTONLY
• MOVE Options with Restores
• Standby Mode Restores
• Point-In-Time (PITR) Restores

Ch 21: Jobs, Maintenance & DB Mail

• SQL Server Agent Service
• Job Steps & Schedules
• DB Maintenance Plans
• Backup Maintenance
• Common Errors, Solutions
• SMTP, Email Profile
• Alert System : Agent Settings
• Operators, Job Notifications j

Ch 22: Security Management

• Security Objects, Logins
• Users, Roles & Principals
• Schema Level Security
• Object, Column Level Security
• Security Audit, Logon Failures
• Keys & Certificates
• Encryptions & Data Security

Ch 23: DB Migrations

• Creating Credentials
• Creating SSIS Proxies
• Creating CDW Packages
• Database Detach – Attach
• SMO Migration Method
• DB Migration Schedules
• Detect, Fix Orphan Users

Ch 24: Tuning: Audits, AM Tool

• Activity Monitor Tool
• Perfmon Tool & Counters
• Query Audits: DMVs, DMFs
• Plan Handle, Execution Time
• Long Running Queries
• Query Store & Data Flush

Ch 25: Tuning: Indexes

• Indexes: Sort Locations
• Clustered & Online Indexes
• Non Clustered, Column Store
• Included Indexes in Realtime
• Covering Index & Selectivity
• Indexed Views (Materialized)

Ch 26: Tuning: Partitions

• Partitions: Performance Tuning
• Partition Functions & Schemes
• Partition Un-partitioned Tables
• Compressions: ROW, PAGE
• Partitions Limitations in OLTP
• Partitions with DWH

Ch 27: Statistics & Tuning

• Statistics: Realtime Usage
• Index & Column Statistics
• Statistics & Key Purpose
• Statistics Versus Indexes
• Stats Updates on Tables
• Stats Updates on Views

Ch 28: Index Management

• Index Management Options
• Index Rebuilds, Re-Organize
• Database Maintenance Plans
• Page Count and DOP
• Resumable & Online Indexes
• PAUSE, RESUME in Rebuilds

Ch 29: Tuning Tools

• Tuning Tools: Workload Files
• Profiler Tuning, Events
• DTA, Profiler Options
• Physical Design Structures
• PDS Recommendations
• Query Execution Cache
• Tuning Tools: Precautions

Ch 30: Execution Plans

• Execution Plan Analysis
• IO Cost and CPU Cost
• SubTree & Operator Cost
• NUMA Nodes, Processor Affinity
• Thread Count, DOP
• CTEs & Tuning

Ch 31: Lock Management

• Open Transactions in Realtime
• Open Transaction, Blocking
• LOCKS: Types & Audits
• S, X, IX, U and MD Locks
• Sch-M and Sch-S Locks
• SP_WHO2, SP_LOCK
• sysprocesses & Lock Waits

Ch 32: Isolation Levels

• Lock Hints and Isolation Levels
• Read Committed, Uncommitted
• Serializable, Repeatable Read
• Snapshot Isolation, Versioning
• Read Committed Snapshot
• sysprocesses & sp_who2
• Choosing Correct Isolation Level

Ch 33: Deadlocks, LIVE Locks

• Deadlocks in Real-world
• Profiler Tool & Deadlocks
• Lock Management: Deadlocks
• Deadlock Graphs & Events
• Deadlock Avoidance, Prevention
• Deadlock Prevention
• sysprocesses & sp_who2

Ch 34: Recovery Models

• Recovery Models
• Database Properties
• Full Recovery Model
• Simple & Bulk Logged
• Realtime Uses
• Backups Versus Recovery Models

Ch 35: HA DR @ Replication – 1

• Replication : Realtime Usage
• Distributor Configurations
• Publisher and Subscriber
• Replication Types: Snapshot
• Transactional Replication
• Log Reader Agent & Usage
• Replication Monitor, Alerts

Ch 36: HA DR @ Replication – 2

• Merge Replication & Usage
• Peer – Peer Replication
• Push & Pull Subscriptions
• Conflict Detection, Avoidance
• Scripting Replication Agents
• Limitations with Replication
• Common Errors, Solutions

Ch 37: HA DR @ Log Shipping

• Log Shipping Configurations
• Primary, Secondary Servers
• Working with Network Shares
• Jobs: Backup, Copy, Restore
• NORECOVERY, STANDBY
• Manual Failover Process
• Common Errors, Solutions

Ch 38: HA DR @ DB Mirroring

• Database Mirroring Concepts
• Configuring Principal, Mirror
• Configure Witness, EndPoint
• Synchronous, Asynchronous
• Automated Failover Process
• Manual Failover, Monitoring
• Common Errors, Solutions

Ch 39: DB Health Checks, Repairs

• DBCC Commands in Realtime
• DBCC for Audits, Repairs
• MSDB Suspect Pages
• User Database Repairs
• Single User Mode
• Emergency Mode
• Recovery & Restoring Modes

Ch 40: Issues & Solutions

• Log Space Issues, Solutions
• TempDB Issues, Solutions
• Memory Issues, Solutions
• Health Check Strategies
• Healthy Backups
• Maintenance Plans
• Database States

Ch 41: Updates & Upgrades

• Updates Process
• RTM, SP, CU (Cumulative Updates)
• Upgrades Process (In-Place)
• Cautions & Maintenance
• Rollbacks

Ch 42: System DB Rebuilds, DAC

• Command Line Installations
• System DB Rebuilds
• Server Down Issues
• Service Startup Issues
• DAC: Dedicated Admin Console
• Login Failure Issues
• SQL CMD : Secondary Logins

Ch 43: AI for SQL DBA, SLA & OLA Process

• AI Extensions
• AI Optimizations
• SLA – OLA Process
• Ticketing Tools
• 3rd Party Tools
• Common Errors & Solutions

Ch 44: SQL DBA Project – Phase 1 (Enterprise SQL Server Database Administration)

• Routine DBA Activities
•  Emergency DBA Activities
•  Maintenance DBA Activities
• Auditing Techniques
• RCA (Root Cause Analysis)

Ch 45: SQL DBA Project – Phase 2 (Enterprise SQL Server Database Administration)

• Login Failure Errors
• Slow Database Issues
• Network Errors
• Security Strategies
• HA DR Strategies
• Common Error Codes
• Need for Azure Cloud

Part 1: Azure Fundamentals, ADLS & IAM
Ch 1: Azure Fundamentals

• Cloud Introduction, Azure
• Cloud Implementations
• Azure Account, Subscription
• Azure ETL & DWH Resources
• Azure Storage, IoT Resources

Ch 2: Azure Storage & ADLS

• Azure Storage Account
• Azure Data Lake Storage
• Azure BLOB Containers
• Blob File Uploads
• Azure Tables

Ch 3: Azure Storage Security

• Microsoft Entra ID
• RBAC, IAM
• Access Keys, SAS Keys
• ACL (Access Control List)
• Connection Strings

Ch 4: Azure Stream Analytics

• Azure IoT Hubs
• Azure Stream Analytics
• LIVE Data Ingestions
• SAQL Queries
• Stream Analytics Jobs
• Handling JSON Data

Ch 5: Azure Key Vaults

• Azure Key Vaults
• Azure Keys & Secrets
• Access Policies
• Secret Management
• Managed Identity

Ch 6: Azure Logic Apps

• Azure Logic Apps
• Logic Apps & Automations
• Azure Events
• Azure Triggers & Signals
• ADLS with Logic Apps

Part 2: Azure Data Factory & Synapse
Ch 1: Azure Fundamentals

• Cloud Introduction, Azure
• Cloud Implementations
• Azure Account, Subscription
• Azure ETL & DWH Resources
• Azure Storage, IoT Resources

Ch 2: Azure Deployments, Azure SQL

• Azure SQL Server Deployment
• Azure SQL DB Deployment
• Azure SQL Database (OLTP)
• Azure Firewall Rules
• Connections from SSMS Tool

Ch 3: Azure SQL DB Migrations

• Azure Storage Account
• On-Premise SQL DB, bacpac
• Azure SQL DB Migration
• SQL Server Imports from BLOB
•  Migration Verifications

Ch 4: Azure Synapse (DWH)

• Synapse Pool Architecture
• Control Node, Compute Node
• DMS (Data Movement Service)
• Azure DWH Tables: Partitions
• Distributions & MAXDOP

Ch 5: Azure Data Factory (ADF)

• Need for ADF & Pipelines
• Data Orchestration with IR
• Integration Runtime Engine
• Linked Services, Datasets
• Pipelines: Copy Data Activity
• Data Flow Activity with IR

Ch 6: Azure SQL DB Loads

• ADF: Author, Azure SQL DB Reads
• Azure SQL Pool Writes
• Synapse Analytics with IR
• Pipeline Design, Validation
• Pipeline Runs, Monitoring

Ch 7: Azure Blob Data Loads

• ADF Author & Pipeline Runs
• Azure BLOB Storage Access
• Storage Containers in ADF
• Pipeline Validation, Schedules

Ch 8: File Incremental Loads

• File Incremental Loads
• Storage Account, Data Lake
• Binary Copy, Schema Drift
• Staging Concept in ADF
• Initial, Incremental Loads
• Schema & Data Changes

Ch 9: Table Incremental Loads

• Implement SCD with ADF
• Self Hosted IR: Realtime Use
• On-premise Data: Incr Loads
• Copy Method: Upsert, Keys
• Staging & ADF Optimizations
• Pipeline Logging

Ch 10: ADF Data Flow – 1

• Creating Data Flow Items
• Using Multiple Sinks
• Conditional Split Transformation
• Select, Sort, Union, Loops
• ADF Debug Options

Ch 11: ADF Data Flow – 2

• Working with Multiple Tables
• Join Transform, Broadcast
• Surrogate Keys, Derived Cols
• ETL Loads Dates, Sink Options
• Aggregated Data Loads

Ch 12: ADF Data Flow – 3

• Pivot Transformation
• Group By & Pivot Keys
• Column Pattern, Deduplicate
• Lookup, Cached Lookup
• Tuning Transformations
• Tuning Data Flow, Spark

Ch 13: ADF Data Flow – 4

• Get Metadata
• IF & Lookup Transformation
• Cache Lookup
• Data Validations
• Lookup Versus Joins

Ch 14: ADF Optimizations

• Synapse SQL Pool Partitions
• ADF Partitions
• Broadcast Options
• Staging, Logging
• DIU, DOCP

Ch 15: ADF Parameters, Security

• Linked Service Parameters
• Creating Logins & Users
• Schemas, ETL Permissions
• Logins Parameters in ADF
• Dynamic Linked Services

Ch 16: SCD & ETL with Control Tables

• Incremental Loads (SCD)
• Control Tables, Watermarks
• Lookup Activity, Delta Queries
• SP Activity & Parameters
• Pipeline Parameters, SPs

Ch 17: ADF Metrics, Alerts

• Azure Insights
• Azure Metrics for ADF
• Azure Metrics for Synapse
• Alerts and Notifications
• Action Groups, Tuning Options

Ch 18: Synapse Analytics

• Azure Synapse Analytics
• Synapse Deployments
• Synapse Configurations
• ADLS Containers
• Workspace Server Setup
• Synapse Studio (GUI)

Ch 19: Synapse: Dedicated SQL Pools

• Creating Dedicated SQL Pools
• BLOB Data Imports
• TSQL Queries, Data Imports
• Big Data Analytics

Ch 20: Synapse: Serverless Pools

• Serverless Pools, TSQL
• Serverless Architecture
• OPENROWSET Operations
• BULK Copy Scripts
• Big Data Analytics

Ch 21: ADF with DevOps & CI/CD

• Git repositories and branching
• ADF Git integration
• Development / Test / Production environments
• Parameterized deployments
• Production release workflow

Ch 22: Azure Databricks

• Azure Databricks Deployments
• Azure Databricks Regions
• Classic Deployments
• Access connector for Databricks

Ch 23: Azure Databricks with ADLS

• Creating IAM Users
• ADLS Security for Databricks
• Creating Service Principals
• Enterprise Applications
• Client ID, Secret ID
• External Tables (Delta)

Ch 24: Azure Databricks with ADF

• Azure Databricks Connections in ADF
• Calling Notebooks in ADF
• ADF Versus Databricks
• ADF With Databricks
• End to End ETL Strategies

Ch 25: Fabric Concepts

• Fabric Architecture
• OneLake
• Lakehouse
• Warehouse
• Data Factory
• Fabric Pipelines
• Dataflow Gen2

Ch 26: Fabric Migrations

• Azure → Fabric migration scenarios
• ADF → Fabric Data Factory
• Synapse → Fabric
• ADLS → OneLake

Part 3: Azure ETL & DWH Admin
Ch 1: Azure DWH Backups

• Azure DWH Backups
• Backup Policies
• Backup Retention
• Backup Configurations
• Azure OLTP Versus DWH Backups

Ch 2: Azure DWH Restores

• Azure DWH Restores
• Point In Time Restores (PITR)
• Azure OLTP Versus DWH Restores

Ch 3: Azure DWH Replication

• Azure DWH Replication Options
• Replication Settings & Process
• Testing Replication in Azure DWH

Ch 4: Azure DWH Security – 1

• Azure SQL Server Logins
• Azure SQL Pool Users
• Logins, Permissions
• Users, Permissions
• Roles & Schemas
• GRANT, WITH GRANT

Ch 5: Azure DWH Security – 2

• Azure AD (Microsoft Entra)
• IAM and RBAC
• Azure Defender
• TDE & Data Encryptions

Ch 6: Azure DWH Management

• Azure Synapse Partitions
• Distributions
• Performance Tuning
• Big Data Issues, Solutions

Ch 7: Data Governance

• Azure Data Governance Concepts
• Data Governance Rules
• Data Governance Scenarios
• Data Security & Management

Environment: Online Travel Data Engineering Platform
Project Objective:

Build an end-to-end Azure ETL Solution to process, transform, and analyze Online Travel business data
from multiple sources.

Skills Gained:

• Data Ingestion & ETL Development
• Azure Data Factory Pipelines
• Data Lake Architecture, RBAC
• Azure IoT, Stream Analytics
• Azure ETL Admin
• Azure ETL Backups, Restores
• Azure ETL Security

Components For Project

• ADF Pipelines
• Pipeline Logging
• Pipeline Management
• Synapse Analytics
• Apache Spark Schemas
• Monitoring & Alerts
• End to End Integrations
• IAM & Managed Identity
• Key Vaults & Security

Resume Highlights
After completing this module, you can confidently showcase experience in:

• Azure OLTP Management
• Azure DWH Management
• Azure ETL Management
• Azure to Fabric Migrations
• Data Quality & Validation
• Performance Optimizations