- 4.7
- 5.0
Official Learning Partner
Course Highlights
SQL DBA Training with 100% Practical, Step by Step course. Includes Basic to Advanced Database Administration activities : Capacity Planning, Query Tuning, Management, Maintenance, HADR, Repairs, Jobs, Alerts, Security, Azure Cloud Migrations, Data Sync, Failover, Always On Availability Groups [AOAG] with Real-time Project for your SQL DBA Resume. This course include #DP203 Exam Guidance also !
Training Highlights:
- Basic to Advanced SQL DBA
- Routine DBA Activities
- Emergency DBA Activities
- Maintenance DBA Activities
- Always-On, Azure Migrations
- Repairs, Tuning, Security
- Data Recovery Strategies
- DP300 Exam Guidance
Trainer: Mr. Sai Phanindra Tholeti
Profile: https://www.linkedin.com/in/saiphanindra/
Training Modes:
LIVE OnlineSelf Paced Videos
SQL DBA Training (with Server,
Azure DBA) Course Contents:
Course 1: SQL DBA Training Content [For Plans A, B, C]
Module I: SQL Server & T-SQL
Installation, Architecture, DB Basics
Ch 1: DATABASE INTRO & SQL DBA
- Data, Databases and RDBMS Software
- Database Types : OLTP, DWH, OLAP
- Microsoft SQL Server Advantages, Use
- Versions and Editions of SQL Server
- SQL : Purpose, Real-time Usage Options
- SQL Versus Microsoft T-SQL [MSSQL]
- Microsoft SQL DBA: Job Roles
- Routine SQL DBA Activities in Realtime
- Emergency SQL DBA Activities, Job Work
- Maintenance SQL DBA Activities
- SQL Server Components and Usage
- Database Engine Component and OLTP
- 24 x 7 Online Lab for Remote DB Access
- Software Installation Pre-Requisites
Ch 2: SQL SERVER INSTALLATIONS
- System Configuration Checker Tool
- Versions and Editions of SQL Server
- SQL Server and SSMS Installation Plan
- SQL Server Pre-requisites : S/W, H/W
- SQL Server 2022 / 2019 Installation
- SQL Server 2017 Installation
- Instance Name and Server Features
- Instances : Types and Properties
- Default Instance, Named Instances
- Port Numbers, Instance Differences
- Service and Service Account Use
- Authentication Modes and Logins
- Windows Logins and SQL Logins
- FileStream and Collation Properties
Ch 3: SSMS Tool, SQL BASICS – 1
- SQL Server Management Studio
- Local and Remote Connections
- System Databases: Master and Model
- MSDB, TempDB, Resource Databases
- Creating Databases : Files [MDF, LDF]
- Creating Tables in User Interface
- Data Insertion & Storage. Limitations
- SQL : Purpose and Real-time Usage
- SQL Versus T-SQL : Basic Differences
- DDL, DML, SELECT, DCL and TCL
- Creating Tables using SQL Scripts
- Data Storage, Inserts – Basic Level
- Table Data Verifications with Select
- SELECT Statement for Table Retrieval
Ch 4: SQL BASICS – 2
- Creating Databases & Tables in SSMS
- Single Row Inserts, Multi Row Inserts
- Rules for Data Insertion Statements
- SELECT Statement @ Data Retrieval
- SELECT with WHERE Conditions
- Batch Concept and Go Statement
- AND and OR Operators Usage
- 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
- ADD, ALTER and DROP Columns
Ch 5: SQL Basics – 3, T-SQL INTRO
- Database Objects : Tables and Schemas
- 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 Retreival and 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
Ch 6 : CONSTRAINTS, INDEXES
- 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 Sorting and Search Advantages
- Clustered and NonClustered Indexes
- Primary Key and Unique Key Indexes
- Need for Indexes – working with Keys
Ch 7: JOINS & LINKED SERVERS
- 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
- CROSS JOIN and CROSS APPLY
- One-way, Two way Data Comparisons
- Linked Servers Configurations
- Linked Servers: RPC Settings & Tests
- Linked Servers, Remote Joins in TSQL
- Multi Server Connections, DB Access
- 2 Part, 3 Part, 4 Part Name Conventions
Ch 8: VIEW, SPs, FUNCTIONS BASICS
- Database Objects: Overview & Usage
- Views: Types, Usage in Real-time
- System Predefined Views and Audits
- Listing Databases, Tables, Schemas
- Functions: Types, Usage in Real-time
- System Predefined Functions, Audits
- DBId, DBName, ObjectID, ObjectName
- Using Parameters in SQL Server
- Dynamic Joins for Database Audits
- 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
Ch 9: 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
- 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
Ch 10: DB Architecture & Group By
- Database Architecture: Data Files
- Database Architecture: Log Files
- Primary and Secondary Data Files
- Log Files Creation and Realtime Use
- Creating and Using Filegroups
- Linking Tables with Filegroups
- Scripting Database and Objects
- GROUP BY: Importance, Realtime Use
- GROUP BY Queries and Aggregations
- Group By Queries with Having Clause
- Group By Queries with Where Clause
- Using WHERE and HAVING in T-SQL
- Using Group By in Data Audits
- Using Group By with Joins, Audits
Module II: Basic SQL DBA
Backup-Restores, Jobs, Performance Tuning, Security
Ch 11: BACKUPS & MANAGEMENT
- Database Backups, Filegroup Backups
- Log File Backups and Log Truncations
- COPY_ONLY Backups and Real-time Use
- Mirror Backups and Split Backups
- Partial Backups – ReadOnly Filegroups
- Format, Compression and Checksum
- Backup Verification, RetainDays, Stats
- ContinueOnError and Backup Scripts
- GUI and Script Backups: Differences
- Backup History Tables in MSDB – Joins
- Backup Audits. HOT and COLD Backups
- Backup Devices – Creation and Usage
- Using Backup Devices – Advantages
- Common Backups Errors, Solutions
Ch 12: 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 Backups Errors,Solutions
Ch 13: 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
- Backup Cleanup & History Cleanup Jobs
- Backup Strategies For Minimal Data Loss
- Backup Options: Block & Transfer Size
- DB Mail Configurations and Alert System
- DB Mail Profiles, SMTP Email Accounts
- Operators : Creation and Real-time Use
- Linking Jobs with Operators, Notifications
Ch 14: RECOVERY, DB SNAPSHOTS
- Database Recovery Models, Logging
- Database Log File Architecture
- Full Recovery Mode, Real-time Usage
- Simple Recovery Mode, Real-time Use
- Bulk Logged Recovery Model, Imports
- Recovery Models & Selectivity
- Limitations with Simple Recovery Model
- Database Snapshots : Creation, Use
- Creating & Testing DB Snapshots
- Read and Write Operations
- Querying Database Snapshots
- DB Snapshots: Page Versioning
- Database Snapshots: Realtime Use
- Comparing DB Backups & Snapshots
Ch 15: SECURITY MANAGEMENT
- Authentication Types & Modifications
- Windows Logins : Creation and Usage
- SQL Server Logins : Creation, Usage
- Password Policies; User Creations
- Logins – Users Mapping, DB Access
- Server Roles & Database Roles – Usage
- Password Resets & Security Policies
- Server Logs – Security Management
- Log Archives: Login Failure Errors
- Object Security and Column Security
- Schema Security and Built-In Schemas
- GRANT, WITH GRANT, DENY, REVOKE
- CONTROL, OWNERSHIP, Authorization
- Execute, References, View Definition
Ch 16: DATABASE MIGRATIONS
- Creating Credentials @ Server Security
- Creating Proxies & Agent Security
- Linking Credentials and Proxies
- Scritping Credentials, Proxies
- Realtime Usage of Proxies
- Using Proxies for DB Migrations
- CDW: Copy Database Wizard @ SSMS
- Database Detach and Attach Options
- SMO Method and Database Scripting
- CDW SSIS Packages, SSIS Proxies Use
- Scheduling Database Migration Jobs
- Detecting and Resolving Orphan Users
- Containment Databases Authentication
- SSMS Connections & Containment
Ch 17: SERVER ARCHITECTURE, AUDITS
- SQL Server Architecture : Protocols
- Named Pipes, TCP IP; Storage Engine
- Query Processor: Parser, Optimizer
- File Manager and Database Files
- Transaction Services, Buffer Manager
- CLR, WAL, Lazy Writer, Checkpoint
- Lock Manager, IO Manager, MDAC
- 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
Ch 18: PAGE ARCHITECTURE, INDEXES
- Database Data Files and Pages
- Data Pages, Mixed / Uniform Extents
- Database Log Files and Pages
- Virtual Log Files (VLF) and LSN
- MINI LSN and Log File Usage Report
- Database Files : Size and Location
- 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
- Database Properties & Query Store
Ch 19: TUNING: INDEX MANAGEMENT
- PARTITIONS : Advantages, Performance
- Table Partitions : Creation & Advantages
- Partition Functions & Partition Schemes
- Partitioning Un-partitioned Tables: GUI
- Verifying / Auditting Partitioned Tables
- Partition Compression: ROW and PAGE
- Auditing Table Partitioned Structures
- Statistics: Real-time Use, Performance
- Statistics: Manual & Auto Creations
- Internal and External Fragmentation
- Index Rebuilding Process and Audits
- Database Maintenance Plans Jobs
- Last Used, Page Count, Fragmentation
- Index Page Count, Degree Of Parallelism
Ch 20: TUNING TOOLS, EXEC PLANS
- 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
Module III: Advanced SQL DBA
HA-DR, Errors & Solutions, Always-On, SLA
Ch 21: REPLICATION For HA – Level 1
- Replication Architecture and Topology
- Publication Types – Purpose, Importance
- DB Articles, Publications, Subscriptions
- Distributor and Distribution Database
- Replication Topologies and HA DR Plan
- Replication Jobs: Types and Usage
- One Way and Two Way Replication
- PUSH and PULL Subscriptions
- Snapshot Publication: Creation, Filters
- Snapshot Replication and Repl Agents
- Log Reader Agent – Configuration, Keys
- Transactional Replication Configuration
- Security Options with Log Reader Agent
- Replication Monitor – Tracer Tokens
- Replication Monitor – Warnings, Alerts
Ch 22: REPLICATION For HA – Level 2
- Merge Replication and Merge Agent Job
- Replication Conflicts and ROWGUIDCOL
- Subscription Reinitialization & Expiry
- Server Subscription & Client Subscription
- Scripting Snapshot Replication
- Scripting Transactional Replication
- Limitations with Traditional Replication
- Peer – Peer Replication : Realtime Usage
- Configure Peer-Peer Replication
- Multi Master Replications & Node IDs
- Peer-Peer Replication Connections
- Replication Conflicts and Priority Settings
- Disabling, Cleaning Replication Topology
- Replication: Load Balancing Topologies
- Common Replication Errors, Solutions
Ch 23: 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
- 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
- Comparing Replication Vs Log Shipping
- Limitations of Log Shipping For HA DR
- Log Shipping Errors and Solutions
Ch 24: 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
- Database Mirroring Advantages
- Common Mirroring Errors, Solutions
Ch 25: HEALTH CHECKS & ERRORS
- 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, 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
Ch 26: 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
Ch 27: 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
- Verifications, Smoke Test and Rollbacks
- Multi Instance Updates & Port Changes
- SERVER Upgrades & VERSION Changes
- Silent Installation & Installation Repairs
- Verifications, Smoke Test and Rollbacks
- System Database REBUILDs using CMD
- Silent Installation & Installation Repairs
- SQLCMD Tool and Instance Connections
- DAC : Dedicated Administration Console
Ch 28: SLA, OLA PROCESS FOR DBA
- SLA and OLA Process, Ticketing Tools
- Realtime Job Environment: SLA
- Ticketing and Process; KB Articles
- Immediate, High, Normal Priorities
- Impact, Urgency and SLA Metrics
- Licensing and Pricing Options
- Core Based Licenses. Device CALs
- User CALs and Multiplexing Concept
- Versions, Editions Comparisons
- Edition Upgrades & Precuations
- Command Line Installations
- Version Checks & Edition Checks
- Build Numbers, Server Configurations
- Client Protocols, Ports & Server Aliases
Ch 29: 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
Ch 30: 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
Course 2: Azure DBA Training Content [For Plans B, C]
Ch 31: CLOUD INTRO, AZURE CONFIG
- Introduction to Cloud & Advantages
- Cloud Architecture: IaaS, PasS and SaaS
- Microsoft Cloud Advantages, Azure
- Azure Products and SQL Services
- Database Implementations in Azure
- Comparing PaaS & IaaS Implmentations
- Paas Implementations For SQL Server
- IaaS Implementations For SQL Server
- Logical, Virtual Machine, Azure MI
- Installing SSMS and Azure Data Studio
- Azure Account and Free Subscription
- Azure Resources: Resource Groups
- Azure Active Directory: User & Groups
- Azure Active Directory Authentication
Ch 32: AZURE SQL SERVER CONFIG
- Installing SSMS and Azure Data Studio
- Azure Account and Free Subscriptions
- Azure SQL Server (Logical Server)
- Azure SQL Server Logical Server Firewall
- Firewall Rules – IP for Remote Access
- Server Properties and Status Format
- Password Resets in Azure SQL Server
- Azure SQL Databases & Pricing Tiers
- Azure SQL Database Access from SSMS
- Elastic Scaling with Azure Databases
- General Purpose and Business Critical
- Basic, Standard and Premium Plans
- vCore Based Purchasing : Gen 4, Gen 5
- Resource Groups For Azure SQL Server
Ch 33: ELASTIC DTUs, MIGRATION
- Azure Storage: Creation & Containers
- LRS, GRS, RA-GRS Storage Accounts
- Storage Containers, Explorer Tool
- Data and Database Migration in Azure
- Data Migration Assistant (DMA) Tool
- DB Migrations To Azure SQL Databases
- Database Exports and Imports in Azure
- Migration Scopes: Schema and Data
- Schema Generation, Data Migration
- Data Migration Verification, Row Count
- DTU: Data Transaction Unit
- Bounding Box Model, Elastic Pool
- eDTUs, Elastic Pool. Per DB Settings
- Performance Recommendations
Ch 34: AZURE SQL DATABASE TUNING
- Azure SQL Server Level Tuning Options
- Azure SQL Database Tuning Options
- Automated Tuning and Peak-Loads
- Force Plan, Create Index and Drop Index
- Query Insight and Recommendations
- IO Metrics, CPU & Query Statistics
- Data File IO, Log File IO, Custom Reports
- Query Audits with Query IDs, Dashboards
- DTU Usage Reports and Elastic Queries
- Query Recommendations, Query Costs
- Azure Search Service and Pricing Tiers
- Suggester and Analyzer Modes for Tuning
- Retrievable, Facetable, Facetable Indexes
- Change Tracking & Watermark Columns
Ch 35: ALERTS, BACKUPS / RESTORES
- Azure SQL Server Activity Log and Options
- Events, Operations and Event Categories
- Quick Insights from Events and Filters
- Events Exports to Event Hub, Log Analytics
- Log Analytics Workspace, Alert Rules
- Email / SMS Notifications and Schedules
- Log Analytics Settings and Azure Storage
- Activity Logs and Audit Reports, Insights
- Azure SaaS Notifications System Options
- Automated Backups in Azure SQL DBs
- Long Term, Automated Backup Retentions
- Backups – Retentions, Audits. LTR Policy
- Restore Points and Database Recovery
- Geo-Redundant Backups and Restores
DP 300 – Exam Samples, Guidance
Ch 36: HA-DR : GE REPLICATION, SYNC
- Failover Groups, Active Geo-Replication
- Read-Write Policy & Automated Failover
- Geo Replication Configuration in Azure
- Primary-Secondary and Read / Write
- GeoReplication Monitor, Forced Failover
- Azure Server Failover Process, Strategies
- Automated, Manual and Forced Failover
- Backup Errors, Geo Replication Errors
- SQL Data Sync Service and Sync Groups
- HUB, MEMBER and SYNC DATABASE
- Sync Direction, Sync Schema & Interval
- bi-directional Load Balancing and Failover
- Conflict Resolutions with HUB, MEMBER
- Cloud to Cloud, OnPremise Synchronizatio
Ch 37: SECURITY MANAGEMENT, RLS
- Firewall & Virtual Network Configurations
- Server Firewall, Database Firewall Rules
- System Procedures for Firewall Settings
- Logins, Users and Roles Permissions
- GRANT, DENY, REVOKE, WITH GRANT
- Role Level and Schema Level Security
- Threat Detection,Transparent Encryption
- Vulnerability Assessment, Data Masking
- Implementing RLS – Row Level Security
- SECURITY Schema for RLS Functions
- Creating Security Predicates and Filters
- Security Policies for Row Level Security
- EXECUTE AS option & STATE for RLS
- Dynamic Masking with Azure SQL DB
Ch 38: POWERSHELL, AZURE VMs
- Azure Virtual Machines Deploy, Configure
- Configure Windows Server, Redhat Linux
- Azure Storage Blades and Pricing Options
- Azure VM Settings, Access & DB Creation
- On-Premise & Azure VMs: Similarities
- SQL Server Instance, Agent in Azure VM
- Azure SQL Database in VM to SQL Cloud
- Azure SQL Database in VM to on-Prem
- Deployment Automation Scripts in Azure
- Azure Cloud Shell – PowerShellGet
- Power Shell Scripts for Configurations
- Power Shell Scripts for Job Schedules
- Power Shell Scripts for Firewall, ARM
- Azure CLI – Purpose, Real-time Use
Ch 39: WINDOWS CLUSTERS SETUP
- Windows Clusters For HA and DR
- Virtual Networks, Subnets and Ips
- Availability Sets and Availability Zones
- Fault Domains and Update Domains
- DC Deployment & Configurations
- Active Directory Domain Services
- SAN [Storage Area Network] and LUN
- DNS Server, DSRM and Private IP
- Windows Level Clusters, MSCS Service
- Active-Active, Active-Passive Clusters
- DNS Tools, Actions & Adding AA Hosts
- SQL Clustering Service & RAID Levels
- SQL Cluster Groups, Domain Accounts
- Quorum and MSDTC Disks. LUN Grows
Ch 40: ALWAYS-ON WITH SQL Server
- SQL Server VMs and File Share Witness
- Joining Machines to Domain Network
- Install Accounts to Active Directory
- Install Failover Cluster (FCI) Features
- Firewall Configurations, Port Exceptions
- FSW File Shares and Usability Options
- Need for Always – On Availability (AAG)
- Always On Availability Group [AOAG]
- Multi-Database Replication Options
- Backup Preferences, Automated Seeding
- Synchronous and Asynchronous Modes
- Data Synchronization for AOAG
- AOAG Dashboards & Automated Failover
- Need for Azure Managed Instances
Resume, Course Participation Certificate, Mock Interview
Course 3: ETL Admin Content [For Plans C]
Part 1: Azure Data Factory, Synapse Analytics
Chapter 1: Cloud Basics, Azure SQL
- Cloud Introduction and Azure Basics
- Azure Implementation: IaaS, PaaS, SaaS
- 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
- Azure Account, Subscription (Free)
- Azure SQL Server [Logical Server]
- Firewall Rules and Azure Services
- Azure SQL Database Deployment
- Azure SQL Pool Deployment
- Compute: DTU Versus DWU
- Test Connections from SSMS
Chapter 2: Synapse SQL Pools (DWH)
- Dedicated SQL Pools in Azure
- Data Warehouse with Synapse
- Massively Parallel Processing (MPP)
- Control Nodes and Compute Nodes
- DMS: Data Movement Service
- Start/Resume/Pause & Scaling
- SQL Pool Config @ TSQL Scripts
- Start/Resume/Pause, Scaling Options
- Table Creations @ TSQL Scripts
- Table Partitions: Left & Right
- Distributions: Round Robin, Hash
- Distributions: Replicate and Usage
- Auto Indexing & Column Store
- Planning for Big Data Loads
- Need for ADF: Azure Data Factory
Chapter 3: Azure Data Factory, Pipelines
- Azure Data Factory (ADF) Concepts
- ADF Pipelines : Architecture
- Integration Runtime (IR) & Use
- Linked Services and Datasets
- Pipeline Activities: Copy Data Tool
- DIU : Data Integration Units
- DTU Vs DWUs Vs DIU
- ADF Pipeline with Copy Data Tool
- Azure SQL DB to Synapse Data Loads
- Multi Tables Data Loads with ADF
- Bulk Insert, Data Copy Methods
- ETL Staging: Storage Account
- Staging Container Connections
- DIU Allocations & Publish
- ETL Pipeline Monitoring, Runs
Chapter 4: OnPremise Data Loads, Upsert
- Copy Data Tool : Incremental Loads
- On-Premise Data Sources with Azure
- Self Hosted Integration Runtime (IR)
- Access Keys, Remote Linked Service
- Synapse SQL Pool (DW), OnPremise
- ETL Staging with Storage Account
- Copy Method: Polybase – Tuning
- Polybase : Big Data Loads
- ETL Pipelines for Incremental Loads
- Business Keys For Table Upsert
- Pipeline Schedules with ADF
- ETL Logging with Storage Account
- Copy Method: UPSERT
- DIU, DOCP & Publish
- Manual Pipeline Executions in ADF
Chapter 5: File Incremental Loads in ADF
- Incremental Loads with Files (BLOB)
- ETL Schedules: Tumbling Window
- Execution Retry and Delay Options
- Binary Copy, Structural Data Loads
- Incremental Loads Verification Tests
- Incompatible Rows & Fault Tolerance
- Pipeline Compression & Tuning
- Pipeline Publish, Monitor Options
- Azure Monitor Resource : Metrics
- ADF Metrics and Pipeline Runs
- ADF: Pipeline Monitoring and Alerts
- Synapse: Storage Monitoring, Alerts
- Conditions, Signal Rules and Metrics
- Alerts & Action Groups: Emails
- Email Notifications with Azure
Chapter 6: ADF Data Flow – 1
- Data Flow Task, Data Flow Activity
- Transformations with Data Flow
- Spark Cluster For Debugging
- Cluster Node Configurations
- Spark Cluster Types & Sizing
- Transaction Optimized – Capacity
- Memory Optimized – Capacity
- Data Cleansing with ADF
- Data Orchestration with Data Flow
- SELECT Transformation & Options
- Conditional Split Transformation
- UNION, SELECT Transformation
- Spark Cluster For Pipeline Executions
- Pipeline Monitoring & Run IDs
- Adding Data Flow into Pipelines
Chapter 7: ADF Data Flow – 2
- ADF Pipelines For ETL Operations
- Data Flow Tasks, Activities in Synapse
- JOIN & EXISTS Transformations
- Aggregate & Group By Transformations
- Window Functions, Rank in Data Flow
- Rank / DenseRank / Row Number
- Derived Column Transformation
- Lookup, Surrogate Key, Parse
- Type Convert, Cast Transformations
- Reusing Data Flow Tasks in Synapse
- Pipeline Validations & Executions
- Inline Datasets, Schema Drift
- Data Deduplication with ADF
- DFT Optimization Techniques
- Data Flow Task – Staging, Logging
Chapter 8: Azure Synapse Analytics
- Azure Synapse Analytics Resource
- Azure Synapse Analytics Workspace
- Managed Resource Group, SQL Account
- Synapse Workspace & Synapse Studio
- Operations with Synapse Workspace
- ADLS Gen 2 Storage Account, Container
- Synapse Studio: Scripts & Pipelines
- Dedicated SQL Pools : Creation, Use
- Synapse Tables, Data Loads with TSQL
- COPY INTO Statements with T-SQL
- Row Terminator and Compressions
- T-SQL Queries and Aggregations
- Aggregation Data Loads in Synapse
- Creating Synapse Pipelines with TSQL
- Stored Procedure Activity & Triggers
Chapter 9: Synapse Analytics with Spark
- Synapse Pipelines: Performance Advantage
- Pivot Transformation For Normalization
- Generate Pivot Column, Aggregations
- Pivot Transformation & Pivot Setting
- Pivot Key Selection, Value and Nulls
- Pivoted Columns and Column Pattern
- Column Prefix, Help Graphic, Metadata
- Denormalized Data and Aggregations
- Apache Spark Pool in Azure Synapse
- Spark Cluster Nodes: Vcores, Memory
- Notebooks : Purpose, Usage Options
- Python Notebooks For Remote Access
- Creating Databases in Apache Spark Pool
- Data Loads from Dedicated SQL Pools
- PySpark Code for Data Operations, Writes
Chapter 10: Synapse Security & Parameters
- Azure Active Directory (AAD) Users, Groups
- IAM: Identity & Access Management
- Synapse Workspace Security with RBAC
- ADF Security: RBAC, Owner, Contributor
- Azure Synapse SQL Pool Security: Logins
- Creating SQL Logins & Users : master
- SQL Users in Azure SQL DB and SQL Pool
- Grant, Control, Revoke: Security Roles
- Parameters – Creation and Use in Pipelines
- Dynamic Connections with Credentials
- User Name and Password Connectivity
- Dynamic Dataset Configurations
- Pipeline Expressions with Parameters
- Resource Classes and Usage with SQL Pool
Chapter 11: Change Data Capture (CDC)
- Change Data Capture (CDC) Data Loads
- Incremental Loads with CDC Types
- SQL Server CDC : ETL Load Dates
- Pipeline Expression, Data Window
- JSON Parameters, Pipeline Scheduling
- ETL Optimization Techniques
- Serverless Pool in Azure Synapse
- Connections, Use with Serverless Pool
- Using Azure OpenDatasets in Synapse
- OPENROWSET and BULK Data Loads
- Working with Parquet Files in Synapse
- Python Notebooks (Pyspark) in Synapse
Part 2: Data Lake Storage, Stream Analytics
Chapter 1: Azure Fundamentals – Storage
- Azure Resources: Storage Components
- Storage Resources and Properties
- Resource Groups & Subscriptions
- Azure Storage : Files, Tables and ETL
- Azure Storage Account & Use
- Data Lake Storage Account (ADLS)
- Advanced Options: HNS Property
- Resource Location, Resource Group
- Azure Portal: Deployment Verifications
- Azure Portal: Deployment Verification
- Storage Account : Basic Properties
- Overview Page: Status, HNS State
- Azure Storage : Access Options
- Azure Storage Explorer Tool
- Explorer Tool : Configuration
Chapter 2: Azure Storage Operations
- BLOB: Binary Large Objects
- Storage Browser and Service Pages
- Storage Browser: Container Creation
- Storage Browser: Folder, File Uploads
- Service Page: Container Creation
- Service Page: Folder, File Uploads
- Container, Folder, File Properties
- Limitations with Storage Portal
- Azure Data Explorer Tool : Usage
- Contrainer: Creation, Properties
- File Uploads, Edits and Access URLs
- Azure Storage Explorer Tool Usage
- Azure Account Options in Explorer
- Directory Creation, File Operations
- Limitations with Explorer Tool
Chapter 3: Azure Storage Security, ACLs
- Azure Data Lake Storage Security Options
- Shared Access Keys: Primary, Secondary
- SAS Key Generation: Container, Tables
- SAS Key Permissions, Validation Options
- Access Keys: Account Level Permissions
- Azure Active Directory: Users, Groups
- Azure AD Security: RBAC, IAM, ACLs
- Owner Role, Contributor, Reader Role
- Azure Data Lake Storage Security
- ACL : Access Control Lists & Security
- Azure BLOB Storage Containers & ACLs
- Folder Level and File Level Security
- ACL Permissions: Read, Write, Execute
- Access Policy: Creation, Realtime Use
- rwacdl; Azure Principals, CORS
Chapter 4: SQL Database Migrations
- OnPremise SQL DB to Azure Migration
- SSMS Tool, SQL Database Installation
- Source Database Scripts & Validations
- BACPAC File Generation: SSMS Tool
- Table Selection & Advanced Options
- Azure Data Lake Storage, SSMS Access
- Azure Storage Container, BACPAC Files
- IAM and Account Key Authentication
- Azure SQL Server Creation From Portal
- Azure SQL Database Deployment
- DTU : Data Transaction Units, Pricing
- Azure Firewall Configuration, Security
- Azure SQL Database Imports (bacpac)
- Azure SQL Server with ADLS Containers
- Azure SQL DB Migrations, Verification
Chapter 5: Azure Tables & Replication
- Azure Tables – SchemaLess Design
- Azure Tables: Creation, Data Inserts
- Tables, Entities, Properties Concepts
- Structured, Relational Data Storage
- Azure Tables: GUI, Data Types
- Azure Tables: Big Data Imports
- Data Edits, Queries, Delete Operations
- Odata Options (REST API), End Points
- Azure Storage: Replications, DR Options
- LRS: Locally Redundant Storage
- GRS: Globally Redundant Storage
- ZRS: Zone Redundant Storage
- Replication Options and Advantages
- Replication Verification, Modifications
- Storage Endpoints, Failover Partner
Chapter 6: Azure Stream Analytics, IoT
- Azure Stream Analytics Real-time Use
- Real-time Data Processing, Events
- Ingest, Deliver & Analysis Operations
- Azure Stream Analytics Jobs Concept
- Understanding Input, Output Options
- SAQL Queries: Stream Analytics Jobs
- IoT: Internet Of Things, Real-time Data
- Need for IoT Hubs and Event Hubs
- Conditional Split Transformation
- Creating IoT Device for Data Inputs
- Creating Azure Stream Analytics Job
- Stream Analytics for Historical Data
- Azure SQL Database for ASA Jobs
- SAQL: Query Formatting, Validation
- Historical Data Upload, ASA Jobs
Chapter 7: Azure 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
- Output Aliases with ADLS Gen 2
- SAQL Query, 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 : Executions & LIVE Data
- On-Premise App Integration, ASA Jobs
Chapter 8: Storage Architecture, Queues
- Azure Storage Account : Architecture
- Etag: Replication & Encryption Use
- BLOB Types: Block, Append & Page
- Access Tiers: Hot, Cool, Cold Types
- Archive Access Tier & Retention
- Legal Hold & Time Bound Access
- Pricing : HNS, Security, Encryption
- EndPoint URL & Read-Only Use
- Azure File Share Service (Files)
- Mounting Files From On-Premise
- SMB File Share : Hot, Optimized
- Azure Queue Service & Messages
- Message Queues : Operations
- Storage Explorer Tool with Shares
- Azure Storage Services: ETL Needs
Chapter 9: Monitoring & Key Vaults
- Azure Monitor, Metrics & Activity 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
- Azure Alerts & Conditions, Notifications
- Signal Logic Conditions and Emails
- Key Vaults Types: Standard & Premium
- Secret Page, Key Backups, Key Restores
- Azure Key Vaults – Name and Vault URI
- Inbuilt Managed Key and Azure Key Vault
- Key Vaults Types: Standard & Premium
- Secret Page, Key Backups, Key Restores
- Managed Identity with ETL Process
Real-time Project (Azure Data Engineer)
- 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)
- Handling Unstructured Data in ADF
- End to End Workflows, Automations
- Azure Logic Apps: Automated Workflows
- Visual Designer & Prebuild Templates
- Server Less Integrations in Azure
- Workflow, Triggers and Actions
- Managed Connectors, Integrations
- ARM Template : Deployments
- ARM Templates : ADF, ADLS
Part 3: Databricks, Spark, Python
Chapter 1: Azure Intro, Azure Databricks
- Azure Cloud : SaaS, PaaS, PaaS & IaaS
- Azure Cloud : Storage, ETL Resources
- Azure Databricks : Compute Resources
- Need for Azure Databricks (ADB)
- Azure Databricks : Purpose & Config
- Azure Databricks Service Creation
- Azure Databricks Components
- Azure Databricks Workspace, Usage
- Spark Cluster Configurations, Capacity
- Driver Nodes, Worker Nodes in Spark
- Cluster Types : Personal, Unrestricted
- CPU, Memory & IO Resources
- Virtual Machines (VM) for Clusters
- Databricks : Runtime & DBFS Storage
- DBFS : Files, Tables with Spark DB
Chapter 2: SparkDatabase, SQL Notebooks
- DBFS : File Uploads from ON-Premise
- Creating Spark Tables; Spark DB
- Data Explorer: HIVE Metastore
- Data Explorer: Spark Database, Tables
- Notebooks: SQL, Python and Scala
- Creating SQL Notebooks in Databricks
- Creating User Defined Spark Databases
- Connecting / Using Spark Databases
- Spark SQL : Big Data Loads
- Spark SQL : Database & Table List
- Spark SQL : Data Aggregations, Jobs
- Spark SQL : Data Analytics, Reports
- Analytics: X, Y Axis, Group By
- Notebooks : Export, Import, Clone
- Notebooks : Storage & Versions
Chapter 3: Python Intro, Data Loads
- Python : Introduction, Real-time Use
- Python For ETL and DWH
- Python For Azure: Data Engineer
- Python Data Frames & Purpose
- Python Dataframes – Pandas
- Python with Spark Integrations
- PySpark for DDL and ETL
- PySpark Versus SQL Notebooks
- Reading DBFS Data into Spark
- Creating Dataframes for ETL
- Temporary Views & Dataframes
- Spark Temp Views: Aggregations
- Spark Table Loads, HIVE Data
- write.format() & overwrite
- Parquet Tables with Spark DB
Chapter 4: PySpark with ADLS
- Azure Storage Account : Creation
- Azure Data Lake Storage : HNS
- Creating Containers in ADLS
- BLOB File Uploads / Generation
- Account Key : Access Key, SAS Key
- BLOB Access URL for Databricks
- WASBS URL for PySpark Notebook
- Generating PySpark Script
- PySpark Connection Variables
- Databricks : Data Import Scripts
- Config Options with ADLS, Spark
- config (), Session Context
- DataFrames with Temp Tables
- Escape Sequence with SparkSQL
- Data Explorer: HIVE & Spark DB
Chatper 5: PySpark Widgets
- Widgets : Notebook Parameters
- widget module : Text, Combo
- Dropdown, Multi Select Parameters
- dbutils help(), get() & remove()
- Dataframes, Spark SQL @ Variables
- Python Data Frames, Spark SQL
- Reading Parameters Values
- Parameters Versus Variables
- Using Parameters For Temp Tables
- Using Parameters for Spark Tables
- Data Storage and HIVE Metastore
- Reading Parameterized Data
- Format Strings with PySpark
- Dynamic Queries with Spark SQL
- Aggregations and f Strings
Chapter 6: Architecture, Workflows
- Driver Nodes, Worker Nodes, DBUs
- RDD : Resilent Data Distribution
- DAG : Directed Acyclic Graph
- Hadoop HDES and Spot Instance
- Cluster Manager, Master Node
- RDDS, Worker, Excecutor & Slave
- Hadoop HDES & Databricks Runtime
- Databricks Optimization Techniques
- Spot Instance, Photon Acceleration
- All Purpose Cluster, Job Cluster
- Databricks Jobs: Creation & Tasks
- Jobs with Parameters, Executions
- Task Dependency & Notifications
- Continuous & Manual Schedules
- Active Jobs, Recent Run Jobs, Monitor
Chapter 7: Databricks Security, Scala
- Azure Databricks Security Operations
- Azure Active Directory (Azure AD)
- AD Users and RBAC with IAM
- Owner, Contributor & Reader Roles
- Workspace Admin Permissions
- Notebook Permissions & Share
- Workflow Security, HTTP Path
- User Tokens & ServerName
- Scala : Differences with PySpark
- Scala : Variables Declaration, Usage
- SparkSQL with Scala Notebooks
- Temp Views with Scala Notebooks
- Aggregations with Scala Notebooks
- Visual Data Analytics with Scala
- PySpark to Scala Conversions
Chapter 8: Scala with ADLS, Azure SQL
- Data Imports with Azure SQL DB
- Using Scala for Big Data Loads
- Spark SQL Queries @ Temp Views
- Variables, display(), read()
- Scala Transformations, display()
- JSON, AVRO and DBFS Mounts
- azure.sas.container @ ADLS
- write.jdbc() & JVM
- JDBC Connection, DataframeWriter
- Data Extraction, SQLContext
- Spark Context and Spark Session
- SQLServerDriver with Scala
- ADLS with Scala Notebooks
- Parameters (Widgets) with Scala
- Compare Python with Scala
Chapter 9: DeltaLake Incr Loads, DWH
- Azure DeltaLake Implementation
- ACID Properties, Upsert Advantages
- Delta Engine Optimizations & Uses
- Pipeline Creation: JSON Files in DBFS
- Delta Tables Creation, Data Loads
- Spark Cluster Settings: Auto Optimize
- Auto Compact, Delta Table Optimize
- JSON Files, Delta Streaming Location
- Joins and Merge with Delta Tables
- Incremental Loads, Delta Tables
- Create & Use DWH with Databricks
- Upsert (Merge) with Spark Tables
- Big Data & Jupyter Notebooks
- Databricks with Data Factory (ADF)
- End to End Implementations
Real-time Project (Azure Data Engineer)
- ADLS with Spark Databases
- Aggregations with Big Data Loads
- Parameterized ETL Sources
- Parameterization & Workflows
- Python Notebooks to Scala
- Azure SQL DB Connections
- ARM Templates & JSON
- Project Requirement
- Project Solution, FAQs
- Concept wise FAQs
- Resume Guidance
- Mock Interviews (1 to 1)
- DP 203 Certification Guidance
- DP 203 Sample Papers (Latest)
SQL DBA (with Server, T-SQL, Azure SQL DBA) Training Course Pricing
Plan ACore DBA | Plan BCore DBA + | Plan CCore DBA + | |
|---|---|---|---|
| Course Duration | 6 Weeks | 9 Weeks | 16 Weeks |
| Course Content | Ch 1 to Ch 30 | Ch 1 to 45 | Ch 1 to 86 |
| Completely Practical, Real-time | ✔ | ✔ | ✔ |
| Routine SQL DBA Activities | ✔ | ✔ | ✔ |
| Emergency SQL DBA Activities | ✔ | ✔ | ✔ |
| SQL Server, DB Maintenance | ✔ | ✔ | ✔ |
| Repairs, Security, Migrations | ✔ | ✔ | ✔ |
| Patches, Upgrades, Tuning | ✔ | ✔ | ✔ |
| HA-DR : Replication, Log Shipping | ✔ | ✔ | ✔ |
| HA-DR : Mirroring, Common Errors | ✔ | ✔ | ✔ |
| Clustering, Always-On Availability | ✔ | ✔ | ✔ |
| SLA-OLA, Incident Management | ✔ | ✔ | ✔ |
| Azure DB Migrations, Tuning | ✖ | ✔ | ✔ |
| Azure SQL DB Security, HA-DR | ✖ | ✔ | ✔ |
| Azure Always-On, PowerShell | ✖ | ✔ | ✔ |
| Azure Virtual Machines (VM) | ✖ | ✔ | ✔ |
| DP 300 Exam : Guidance | ✖ | ✔ | ✔ |
| Azure DWH & ETL Admin | ✖ | ✖ | ✔ |
| Azure ADF, Synapse | ✖ | ✖ | ✔ |
| ADLS Config, Admin | ✖ | ✖ | ✔ |
| Azure Databricks, Spark Admin | ✖ | ✖ | ✔ |
| DP 300 Exam Guidance | ✖ | ✖ | ✔ |
| Total Course Fee* | INR 20000USD 250 | INR 28000USD 350 | INR 50000USD 600 |
SQL DBA Training Schedules
Azure SQL DBA Training Schedules ( Chapter 31 – 45 )
SQL SCHOOL
24x7 LIVE Online Server (Lab) with Real-time Databases.
Course includes ONE Real-time Project.
Why Choose SQL School
- 100% Real-Time and Practical
- ISO 9001:2008 Certified
- Concept wise FAQs
- TWO Real-time Case Studies, One Project
- Weekly Mock Interviews
- 24/7 LIVE Server Access
- Realtime Project FAQs
- Course Completion Certificate
- Placement Assistance
- Job Support
- Realtime Project Solution
- MS Certification Guidance
