Skip to main content
  • 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 !

Trainer: Mr. Sai Phanindra Tholeti
Profile: https://www.linkedin.com/in/saiphanindra/

Training Modes:

LIVE OnlineInhouse Classroom (Limitted Intake)Self Paced Videos

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

Course Content

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 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 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 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 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 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

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

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

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 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

SQL DBA (with Server, T-SQL, Azure SQL DBA) Training Course Pricing

Plan A

Core DBA

Plan B

Core DBA +
Azure DBA

Plan C

Core DBA +
Azure DBA +
ETL Admin
Course Duration6 Weeks9 Weeks16 Weeks
Course ContentCh 1 to Ch 30Ch 1 to 40Ch 1 to 86
Completely Practical, Real-time
Routine SQL DBA Activities
Emergency SQL DBA Activities
SQL Server, DB Maintenance
DB Repairs, Security Management
Migrations, Patches, Upgrades
HA-DR : Replication, Log Shipping
HA-DR : Mirroring, Common Errors
Clustering, Always-On Availability
Tuning Tools, Execution Plans
Ticketing Tools, 3rd Party Tools
SLA-OLA, Incident Management
Azure SQL Database Migration
Azure SQL DB Security, HA-DR
Azure Always-On, PowerShell
Azure Virtual Machines (VM)
DP 300 Exam : Guidance
ETL Admin
ADE: Azure Synapse
ADE: Azure Storage
ADE: Stream Analytics
ADE: Azure Databricks
Total Course Fee*INR 20000USD 300INR 28000USD 400INR 50000USD 630

CERTIFICATE OF COMPLETION

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
×