SQL DBA Classroom Training (LIVE, Instructor-Led)

This SQL DBA Training from Real-time, Certified Trainers includes Basic to Advanced Database Administration activities : Capacity Planning, Query Tuning, Index Management, Database Maintenance, High Availability (HA), Disaster Recovery (DR), Query Store, In-Memory Tables, Database Repairs, Page Repairs, Jobs, Alerts, Resource Management, Security Management including Azure SQL DBA Activities like Azure Sync, Azure Server Failover, Azure Virtual Machines and Azure DB Migrations.

This SQL DBA Training also includes practical implementation of Always-On Availability Groups [AOAG] in Windows Clusters , LINUX Clusters and Real-time Azure SQL DBA activities. Additionally, this SQL DBA Training Course includes complete guidance for your Job Profile, Study Material, MCSA SQL DBA Certification (70-764 & 70-765) and Real-time Projects for your Resume. Versions: SQL Server 2014, 2016, 2017 and 2019. Download SQL DBA Curriculum Here

SQL DBA (with Server, T-SQL, Azure SQL DBA)

  PLAN A PLAN B
SQL DBA Job Profile Core DBA Core DBA + Azure DBA
SQL DBA Course Duration 6.5 Weeks 8.5 Weeks
Real-time Issues, Solutions Check-Symbol-for-Yes Check-Symbol-for-Yes
SQL Server, DB Architecture Check-Symbol-for-Yes Check-Symbol-for-Yes
Query Tuning and Plan Analysis Check-Symbol-for-Yes Check-Symbol-for-Yes
HA - DR, Issues, Troubleshooting Check-Symbol-for-Yes Check-Symbol-for-Yes
DB Repairs, Security Management Check-Symbol-for-Yes Check-Symbol-for-Yes
Migrations, Updates (CU), Upgrades Check-Symbol-for-Yes Check-Symbol-for-Yes
Errors & Troubleshooting Check-Symbol-for-Yes Check-Symbol-for-Yes
Events, Traces, Encryptions Check-Symbol-for-Yes Check-Symbol-for-Yes
SQL Server on Windows Clusters Check-Symbol-for-Yes Check-Symbol-for-Yes
SQL Server on LINUX Clusters Check-Symbol-for-Yes Check-Symbol-for-Yes
Always-On Availability Groups Check-Symbol-for-Yes Check-Symbol-for-Yes
SLA, OLA, Licensing Plans Check-Symbol-for-Yes Check-Symbol-for-Yes
SQL DB Migration to Azure Croos-symbol-for-No Croos-symbol-for-Yes
Azure SQL Database Admin Croos-symbol-for-No Croos-symbol-for-Yes
Azure SQL DB: HA-DR Croos-symbol-for-No Croos-symbol-for-Yes
Common Errors, Solutions Croos-symbol-for-No Croos-symbol-for-Yes
Azure Virtual Machines, XEL Croos-symbol-for-No Croos-symbol-for-Yes
Azure PowerShell, Azure CLI Croos-symbol-for-No Croos-symbol-for-Yes
MCSA 70-764 Certification Croos-symbol-for-No Croos-symbol-for-Yes
MCSA 70-765 Certification Croos-symbol-for-No Croos-symbol-for-Yes
Total Course Fee INR 10000/- INR 15000/-

** Course Fee payable in Installments.

SQL DBA Training Schedules - Trainer : Mr. Sai Phanindra T

S. No Timings (IST) Free Demo Start Date  
Schedules for SQL DBA Training Plan A ( Chapters 1 to 33 )
1 6:30 AM to 7:30 AM Apr 23rd Apr 25th Register
2 10:15 AM to 11:15 AM Mar 25th Mar 25th Register
3 7:30 PM to 8:30 PM Apr 1st Apr 3rd Register
Schedules for SQL DBA Training Plan B ( Chapters 1 to 44 )
  Schedules for Chapters 1 to 33 = Core DBA  
1 6:30 AM to 7:30 AM Apr 23rd Apr 25th Register
2 10:15 AM to 11:15 AM Mar 25th Mar 25th Register
3 7:30 PM to 8:30 PM Apr 1st Apr 3rd Register
  Schedules for Chapters 34 to 44 = Azure DBA  
1 6:30 AM to 8:30 AM (W) Apr 6th Apr 7th Register
2 8:30 AM to 10:30 AM (W) Apr 20th Apr 21st Register

If the above schedules does not work, Then please opt for SQL DBA Video Training

SQL DBA TRAINING HIGHLIGHTS :

✔ In-depth Tuning ✔ HA - DR Issues ✔ Azure Migrations
✔ XEL Graphs ✔ DAC, PBM ✔ Stretch Databases
✔ DB Migrations ✔ Licensing Plans ✔ Azure HA - DR
✔ In-Memory Tables ✔ DMA Tool ✔ POCs, RCA
✔ Ticketting Tool ✔ Tuning Tools ✔ Blob Storage
✔ Always-On ✔ SLA / OLA ✔ Power Shell
✔ Lite Speed ✔ Windows Clusters ✔ Managed Instance
✔ Realtime Errors ✔ Linux (RHEL) ✔ Virtual Machines
✔ DBA Checklists ✔ Updates, Upgrades ✔ MCSA Certification

All Trainings are completely Practical and Real-Time. Curriculum Download

Part 1 : SQL DBA with SQL Server, T-SQL [Applicable for SQL DBA Plan A & B]

Module I: SQL Server & T-SQL

Installation, DB Architecture, Indexes, Tuning

Module II: Basic SQL DBA

Backup-Restores, Jobs, Tuning, Security, Migrations, Upgrades

Module III: Advanced SQL DBA

HA-DR, Errors & Solutions, Always-On, RHEL

Day 1: SQL DBA - JOB ROLES, INTRO

  • Need for Databases & RDBMS Software
  • Need for Database Administrators (DBA)
  • SQL DBA : Job Roles and Responsibilities
  • Routine, Emergency Maintenance DBA Roles
  • Database Types: OLTP, OLAP, DWH, HTAP
  • MS SQL Server - Technical Advantages
  • SQL Server @ OnPremise: Windows, Linux
  • SQL Server @ Cloud: Azure, AWS, G Cloud
  • Versions and Editions of SQL Server
  • SQL Server and SSMS Installation Plan
  • SQL Server 2019 : Installation [Overview]
  • System Configuration Checker (SCC) Tool

Day 12: BACKUPS - DB, File, Filegroup

  • Database Backups, Filegroup Backups
  • Log File Backups, COPY_ONLY Backups
  • Mirror Backups, Split Backups, FORMAT
  • Partial Backups and Backup Media Files
  • UNLOAD, INIT, FORMAT, VERIFY, SKIP
  • Compression,Checksum,RetainDays,Stats
  • ContinueOnError and Backup Validations
  • Backup History Tables in MSDB - Joins
  • Backup Audits. HOT and COLD Backups
  • Backup Devices - Creation and Usage
  • Database Recovery Models - DB Logging
  • Common Backups Errors and Solutions

Day 23: REPLICATION For HA - LEVEL 1

  • Replication Architecture and Topology
  • Publication Types - Purpose, Importance
  • DB Articles, Publications, Subscriptions
  • Distribution DB Configuration, Snapshots
  • Snapshot Replication and Repl Agents
  • Adding Articles to Existing (LIVE) Replica
  • PUSH, PULL Subscriptions. N/W Shares
  • Transactional Replication Configuration
  • Log Reader Agent - Configuration, Keys
  • Tracer Tokens Latency, Ranking Options
  • Replication Monitor - Usage and Options
  • Replication Warnings and Agent Alerts

Day 2: INSTALLATIONS [DETAILED]

  • SQL Server Pre-requisites : WMI, MSXML
  • SQL Server 2016 Installation, Guidance
  • SQL Server 2017 Installation, Guidance
  • Instance Types: Default & Named Instances
  • Instance Names, Server Names & Ports
  • SQL Server Services : Database Engine
  • SQL Agent, Full Text Search, Browser
  • Authentication Modes and Login Types
  • System Databases: Master, Model, MSDB
  • Resource Database. TempDB Configurations
  • File Stream and Collation in SQL Instances
  • SQL Server Management Studio. SSMS Tool
  • Local Server & LIVE Online Lab Connections
  • Common Installation Errors, Solutions

Day 13: RESTORES & DB RECOVERY

  • Restore Phases - COPY, REDO, UNDO
  • RECOVERY and NORECOVERY Options
  • STANDBY, REPLACE Options in Restores
  • File, File Group and Meta Data Restores
  • Backup Verifications using GUI, Scripts
  • FILELISTONLY and VERIFYONLY Options
  • HEADERONLY, STATS, UNLOAD, STOPAT
  • PARTIAL / PIECEMEAL Restores - Use
  • Tail Log Backups. UNDO, REDO Phases
  • 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
  • Restores from SQL Server 2017 to 2019

Day 24: REPLICATION For HA - LEVEL 2

  • Merge Replication and Merge Agent Job
  • Replication Conflicts and ROWGUIDCOL
  • Subscription Reinitialization, Expiry Setting
  • Server Subscription & Client Subscription
  • Peer-Peer Replication Connections, Nodes
  • NodeID and Conflict Detection Options
  • Replication Options on Conflicts: sp_MSRepl
  • MSSQL_ENGXXXXX Errors & Solutions
  • sp_changedbowner, backup initialization
  • Replication Conflicts and Priority Settings
  • Replication Verify - Rowcount, Checksum
  • Disabling, Cleaning Replication Topology
  • Replication Strategies for HA and DR Plan
  • Replication for Load Balancing Topologies

Day 3: FIREWALL, QUERY ARCHITECTURE

  • Firewall Configurations for SQL Server
  • SQLServr.exe and SQLBrowser.exe Files
  • SQL : Purpose, Usage for SQL DBAs [Audits]
  • DDL, DML, SELECT, DCL and TCL
  • Creating SQL Databases and Tables
  • INTEGER, CHAR, FLOAT Data Types
  • INSERT and INSERT INTO Statements
  • Single Row Inserts, Multi Row Inserts
  • SELECT Statement for Table Retrieval
  • LIVE Query Statistics & TABLE SCAN
  • Client Statistics and Execution Time
  • Generating TRAILS and Comparisons

Day 14: JOBS, MAINTENENCE PLANS

  • SQL Server Agent Service and Agent XPs
  • SQL Agent Jobs - GUI, Script Creations
  • Job Steps - Creation, Edits and Parse
  • Job Schedules and Email Notifications
  • 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
  • DB Mail Configurations and Alert System
  • DB Mail Profiles, SMTP Agent Operators

Day 25: 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
  • TUF Files and Standby Options in LS
  • Broken Log Shipping Chains & Issues
  • Log Shipping Jobs - Errors & Solutions

Day 4: BASIC SQL QUERIES (Contd.)

  • CHAR Versus VARCHAR Data Types
  • Single Row Inserts, MultiRow Inserts
  • GO Statement, SQL BATCH Concept
  • Table Scan and LIVE Execution Plans
  • IS NULL, NOT NULL. LIKE, NOT LIKE
  • ORDER BY with ASC, DESC Options
  • DISTINCT, TOP and COUNT() Options
  • FETCH, OFFSET, NEXT ROW Options
  • UNION, UNION ALL and Sub Queries
  • Single Quotes, Double Quotes, Aliases
  • UPDATE : Conditional & Unconditional
  • DELETE and TRUNCATE Commands
  • ALTER Command   DROP Command

Day 15: Tuning 2 - AUDITS, PARTITIONS

  • Audit Long Running Queries : DMVs, DMFs
  • Activity Monitor Tool, Query Statistics
  • Logical & Physical I/O, DB IO, Waits
  • Recent and Active Expensive Queries
  • Plan Handle Executions, Performance
  • Resumable Indexes: ONLINE, RESUME
  • PAUSE, MAX_DURATION. Query Store
  • Partition Mechanism : Database Filegroups
  • Partition Functions & Partition Schemes
  • Aligned / Indexed Partitions - Importance
  • Partition SPLIT and MERGE, NextUsed
  • Partition Compression : ROW, PAGE
  • Data Archival & SWITCH. Partitioned Views

Day 26: 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
  • Mirroring Monitor, Stop/Resume Options
  • Need for Always-On & Higher Availability
  • DB Recovery without Witness. Failover
  • Mirroring Monitor Jobs - Real-time Usage
  • Database Mirroring - Errors & Solutions

Day 5: DATABASE ARCHITECTURE

  • SQL Server Database Architecture - Detailed
  • Database Data Files, Database Log Files
  • Primary Data File [MDF] : Real-time Use
  • Secondary Data File [NDF] : Real-time Use
  • Log File / Transaction Log File [LDF]
  • Filegroups : Creation and Table Linking
  • Default Filegroup [PRIMARY] for Tables
  • File Stream Files and Real-time Use, BLOB
  • Pages, Extents Types [Uniform and Mixed]
  • Logs, Checkpoints, Virtual Log File (VLF)
  • Log Sequence Number (LSN), Mini LSN
  • File Properties : Initial Size and Max Size
  • File Growth [Auto Growth] and Location
  • Database Metadata. T-SQL Scripting Options
  • DBCC CheckTable - Page Count Verification

Day 16: Tuning 3 - FTS, STATISTICS

  • Full Text Search (FTS) - Architecture
  • StopWords, Stemmer, Thesaurus For FTS
  • Indexer, Query Processor & Compilation
  • Full Text Catalogs and Filter Daemon
  • FDHost.Exe and Daemon Threads for FTI
  • Full Text Indexes (FTI), Crawler Threads
  • Change Tracking (CT) and Data Population
  • CONTAINS(), FREETEXT() with SELECT
  • In-Memory Tables : Creation, Tuning
  • Memory Snapshots at DB and Table Level
  • FileStream Files and MOT Filegroups
  • MEMORY_OPTIMIZED_ELEVATE Snapshot
  • Stats Updates : FullScan, NoRecompute
  • Temporal Tables Usage & History Tracking
  • Statistics : Index and Column Statistics

Day 27: CLUSTER CONFIGURATION

  • Windows Clusters For HA and DR
  • Domain Controller (DC) Configuration
  • Active Directory (AD) Accounts, Use
  • SAN [Storage Area Network] and LUN
  • Public IP Address, Private IP Address
  • Windows Level Clusters, MSCS Service
  • Need for DNS Hosts - Server Manager
  • DNS Tools, Actions & Adding AA Hosts
  • SQL Clustering Service & RAID Levels
  • RAID Levels for Data Files & Log Files
  • SQL Cluster Groups, Domain Accounts
  • Active-Active, Active-Passive Clusters
  • Quorum and MSDTC Disks. LUN Grows
  • MSCS Service Startup Options, Drains
  • Need for Always - On Availability (AAG)

Day 6 : SQL SERVER ARCHITECTURE

  • Client Server Architecture, TDS Packets
  • SQL Native Client, OLE DB Command @ TDS
  • Query Optimizer (QO) and SQL Manager
  • Storage Engine, File and DB Manager
  • Transaction Manager and Lock Manager
  • Buffer Manager, Memory and IO Buffer
  • Write Ahead Log (WAL) and Lazy Writer
  • SQL OS Components, MDAC & CLR
  • Utilities : Excel Imports and Exports
  • Temporary Tables : Types, Real-time Use
  • Local and Global Temporary Tables
  • Schemas : Grouping Tables and Options
  • Transferring Tables across Schemas
  • Synonyms. 2Part, 3Part, 4Part Naming

Day 17: Tuning 4 - INDEX MANAGEMENT

  • Index Management : Performance Tuning
  • Internal and External Fragmentation
  • Fragmentation Audits : DMFs, Thresholds
  • Index Reorganization, Index Rebuilds
  • Database Maintenance Plans (DMP) Jobs
  • Index Page Count and Index Condition
  • Fast, Detailed Scans. Statistics Updates
  • LOCKS : Types, Concurrency Control
  • X, S, IS, IX, U, MD, Sch-M and Sch-S
  • Lock Audits : SP_WHO2, SP_LOCK
  • Deadlock Simulation and Prevention
  • Deadlock Audits & Events in Profiler
  • Isolation Levels and Query Blocking
  • Choosing Isolation Levels in Real-time

Day 28: ALWAYS ON AVAILABILITY (AAG)

  • Always On Availability Group [AOAG]
  • Synchronous and Asynchronous Modes
  • Replica Recommendations for Always-On
  • Backup Preferences & Location Options
  • Synchronization & Automated Seeding
  • Data Synchronization Settings for AOAG
  • Port Settings, Backup Strategies in AAG
  • AOAG Verifications and Dashboards
  • Adding Availability Replica, Database
  • Adding Availability Listeners and DNS
  • Automated Failovers, Manual Failovers
  • Always-On Availability Groups Health
  • New Hosts and AAA Hosts in AOAG
  • Forward Lookup Zones - AOAG Domain

Day 7: CONSTRAINTS & JOINS

  • Constraints, Keys - Table Data Integrity
  • NULL, NOT NULL Property on Tables
  • UNIQUE KEY Constraints: Importance
  • PRIMARY KEY Constraint: Importance
  • FOREIGN KEY Constraint: Importance
  • REFERENCES, OLTP Relational Tables
  • CHECK, DEFAULT & Identity Property
  • JOINS - Types and Real-time Usage
  • JOIN Types and JOIN Options in T-SQL
  • INNER JOIN - Examples, WHERE, ON
  • OUTER JOIN - LEFT, RIGHT & FULL
  • CROSS JOIN, "," and CROSS APPLY
  • MERGE JOIN, LOOP JOIN, HASH JOIN
  • Query Tuning with JOIN OPTIONS

Day 18: Tuning 5 - TUNING TOOLS

  • Tuning Tools : Workload and Trace Files
  • SQL Profiler Tool - Tuning Template, Events
  • DTA Tool with Profiler : Recommendations
  • DTA with Procedure Cache & .SQL Files
  • Execution Plans - Internals. Plan Types
  • Query Costs : IO Cost and CPU Cost
  • Query Costs : SubTree Cost and Operator Cost
  • Numa Nodes, Boost Priority & Thread Count
  • Perfmon Counters and Real-time Tracking
  • Processor, Disk, Memory, Database Counters
  • Perfmon for Big Query Audits. Memory
  • Longest Running Transactions and TPS
  • Log File, Tempdb, Memory Issues. Solutions
  • Performance Tuning - Final Tuning Checklist

Day 29: SQL SERVER @ LINUX (RHEL)

  • Understanding LINUX OS Environment
  • LINUX OS Components - Kernel, Daemons
  • BootLoader, Shell and Desktop Env
  • SQL Server Options on LINUX Platform
  • Installing SQL Server on RHEL Cluster
  • su and sudo Commands in LINUX Cluster
  • Creating Linux Packages, REPO Files
  • Installing mssql-server Packages
  • Service Enable and Activation Options
  • Firewall Configurations, sa Passwords
  • Installing SQL Server Tools in LINUX
  • ms-sqltools Installation: SQLCMD, BCP
  • UNIXODBC Packages Installation, Verify
  • Running Test Connections and Queries

Day 8: INDEXES & TUNING - 1

  • Indexes: Architecture and Types
  • Clustered and Non Clustered Indexes
  • Included and ColumnStore Indexes
  • FILTERED and COVERING Indexes
  • UNIQUE Indexes, Query Optimizer
  • LIVE Online Indexes in Real-time
  • B Tree Structures and IAM Pages
  • Tuning Joined Queries, Conditions
  • Views on Tables - SCHEMABINDING
  • ENCRYPTION and CHECK OPTION
  • Orphan Views - Real-world Solutions
  • Cascaded Views, Encrypted Views
  • System Views for Metadata Access
  • Indexed Views / Materialized Views

Day 19: SECURITY MANAGEMENT

  • Authentication Types - Windows, SQL Server
  • Windows Logins and REGEDIT with T-SQL
  • SQL Server Logins : POLICIES and EXPIRY
  • Logins, Users, Roles and Schemas Creation
  • Server Roles and Database Roles - Usage
  • Password Resets and Security Scripting
  • Object Level Security & Column Security
  • Logon Failures & Server Management Logs
  • Grant, With Grant, Deny, Revoke Settings
  • CONTROL, OWNERSHIP, Authorization
  • Keys & Certifications for Data Encryption
  • Credentials and Proxies for Job Security
  • DMVs for Security Audits, Orphan Users
  • Containment Databases - Users, Roles

Day 30: SLA/OLA, RAID, PROCESS

  • Routine SQL Server DBA Activities
  • Maintenance SQL DBA Activities
  • Emergency SQL DBA Activities
  • End to End Process for SQL DBAs
  • Tiers & Levels of Production Support
  • Real-time Project Plan, Support Terms
  • Capacity Planning with SQL Server
  • SLA and OLA Process - Production
  • RAID Levels for SQL Databases
  • RAID for SQL Server Data Files
  • RAID for SQL Server Log Files
  • Bit Level and Block Level Parities
  • Speed and Safety Options with RAID
  • Database Size, RAM Capacity Options

Day 9: FUNCTIONS, PROCEDURES

  • User Defined Functions in SQL Server
  • Scalar Functions, Inline Table Functions
  • Multi - Line Table Valued Functions
  • Table Variables and Table Data Type
  • Variables & Parameters in SQL Server
  • Dynamic Joins @ Functions, Performance
  • OBJECTID, OBJECTNAME Functions
  • System Functions & Metadata Access
  • Stored Procedures - Purpose, Usage
  • IF .. ELSE and ELSE IF Conditions
  • Using Stored Procs with Parameters
  • Recompilation of Stored Procedures
  • Sp_help, Sp_helpdb and sp_helptext
  • Sp_recompile, sp_pkeys System SPs

Day 20: DB Migrations, Orphan Users

  • Logins and User Mapping Concepts
  • Detecting / Auditing Orphan Users
  • Resolving Orphan Users in Databases
  • sp_resolve_login and Logins RPT File
  • Containment Database Authentication
  • Creating Users without Logins. Roles
  • Containment Datbase Users Permissions
  • Database Migration Options with SSIS
  • CDW : Copy Database Wizard @ SSMS
  • Database Detach and Attach Options
  • FORATTACH, sp_single_file_attach_db
  • SMO Method and Database Scripting
  • CDW SSIS Packages, SSIS Proxies Use
  • Scheduling Database Migration Jobs

Day 31: Ticketing Tools, Licenses

  • Ticketing Tools - REMEDY (Cloud)
  • IR, CR and SR (Incident Management)
  • SLA - OLA Reports and Tickets
  • Tickets : Properties, Ownerships
  • Impact, Urgency and Ticket Priority
  • Status, Events, Customers, Details
  • Incident Tickets and Resolutions
  • SQL Server Licensing Plans, Pricing
  • Device Type and Core Type Licenses
  • Device CAL and User CAL : Selections
  • CORE Licensing : Processor, Core
  • Multiplexing and Software Assurance
  • Pricing Options with SQL Server License
  • Version and Edition Comparison Sheets

Day 10: TRIGGERS, TRANSACTIONS

  • Triggers - Purpose, Real-world Usage
  • FOR/AFTER Triggers - Real time Use
  • INSTEAD OF Triggers - Real time Use
  • INSERTED, DELETED Memory Tables
  • Enable Triggers and Disable Triggers
  • Database Level DDL Triggers - Usage
  • Server Level DDL Triggers - Usage
  • COMMIT and ROLLBACK Statements
  • EXPLICIT & IMPLICIT Transactions
  • Autocommit Transaction, ACID Options
  • Open Transactions and Query Impact
  • Query Blocking Scenarios @ Real-time
  • NOLOCK and READPAST Lock Hints
  • Transactional Integrity in Database

Day 21: HEALTH CHECK, REPAIRS

  • Database Health Check : DBCC Commands
  • Allocation Errors, Consistency Errors
  • DBCC ShowContig, Extent Fragmentation
  • EstimateOnly, NoInfoMsgs with Tempdb
  • DDBC Page- GAM, SGAM, PFS, ML, Bitmap
  • DBCC Shrink, Suspect Pages and Repairs
  • Repair_Rebuild, DB States, Page Restores
  • Resource Governor : Real-time Usage
  • Resource Pools and Workload Groups
  • Query Priority, DOP & Memory Grants
  • Classifier Function and RECONFIGURE
  • Policy Based Management (PBM)
  • Facets and Conditions for Policies
  • Database Recovery Model Policies

Day 32: SQL DBA Project - Level 1 [30 m]

  • Audit Login Failures : Server Logs
  • Monitoring Connectivity Issues
  • Auditing Long Running Queries
  • Memory Issues and Solutions
  • PLE (Page Life Expectancy) Issues
  • MSDTC and Remote Connections
  • Job Monitoring and Precautions
  • Monitoring HA & DR Mechanisms
  • Database Refresh, Schema Refresh
  • Creating Dump Files and Audits
  • Idle Sessions and Precautions
  • Index Management and Integrity
  • Deadlock : Monitoring, Prevention
  • Monitor Server Health, Dashboards

Day 11: LINKED SERVERS, CTEs

  • Linked Servers - Creation and Tests
  • sp_addlinkedserver System Procedures
  • Linked Server Security, Remote Logins
  • RPC and RPC Out. Data Access Modes
  • Four Part Naming Conventions - Options
  • Using Linked Servers for Remote Joins
  • Tuning Remote Join Queries, Options
  • Cursors : Real-time Usage, Memory
  • Cursor Types and KEYSET Indexes
  • Local, Global, Static, Dynamic Cursors
  • Forward_Only and Scroll Cursors
  • CTE : Common Table Expressions
  • SQL Browser Service - Remote Access
  • SSCM : SQL Server Configuration Manager

Day 22: PATCHES, UPGRADES, CUs

  • Establishing Downtime For Maintenance
  • Precautions for Maintenance Activities
  • DB Backups, Scripting and Services
  • Service Packs and Patch/Hotfix Activities
  • Cumulative Updates (CU), Hotfix Process
  • Instance Selectivity for Updates, Cautions
  • Verifications, Smoke Test and Rollbacks
  • SERVER Upgrades, VERSION Changes
  • Planning for DB Maintenance Activities
  • System Database REBUILDs using CMD
  • Silent Installation & Installation Repairs
  • SQLCMD Tool and Instance Connections
  • DAC : Dedicated Administration Console
  • Verifications, Smoke Test and Rollbacks

Day 33: SQL DBA Project - Level 2 [30 m]

  • Server Down : Issues, Solutions
  • Database Down : Issues, Solutions
  • Data Synchronization : Issues, Solutions
  • Slow Running Queries : Issues, Solutions
  • Database Response : Issues, Solutions
  • Slow Replication : Issues, Solutions
  • Replication @ HA-DR : Issues, Solutions
  • Log Shipping @ HA-DR : Issues, Solutions
  • Important Events and Errors, Solutions
  • Hot CPU and Processor Node, Priority
  • CPU Thread Counts and Windows Fibers
  • Contained Databases and Orphan Users
  • Memory Dump Files and Solutions
  • Always-On (AAG): Issues and Solutions

REAL-TIME PROJECT (With Solution : For your Resume)

Our SQL DBA Video Training Course includes one Real-time Project on E-Commerce Database. This project involves Real-time DBA Issues and Solutions including the usage of Ticketing Tools, 3rd Party Tools. We deal with Routine SQL DBA Activities, Emergency DBA Activities and Maintenance Activities with Solutions. Also include 200+ Errors and Solutions , Database Migrations, Upgrades, Always-On Availability Groups Implementation in a Clustered Environment. This Real-time Project gives greater in-depth insight on Real-world SQL DBA Job Work and can also be used in your Resumes. This SQL DBA Project is included in 32nd and 33rd videos above. Click Here to download SQL DBA Curriculum

Part 2: Azure SQL DBA [Applicable for Plan B]

Module I: AZURE SQL DATABASE

Module II: AZURE SQL DBA

Day 34: AZURE CLOUD & AZURE SQL DATABASE

  • Introduction to Cloud. Need for Cloud, Advantages
  • Cloud Architecture Basics - Iaas, PasS and SaaS
  • Advantages of Microsoft Cloud - Azure Platform
  • Azure Products and Azure Services - MarketPlace
  • Comparing Azure with Google Cloud for SQL Server
  • Comparing Azure with AWS Cloud for SQL Server
  • Azure Sources - Types, Microsoft Market Place
  • SQL Database Implementations in Azure Platform
  • Logical Servers, Virtual Machines, Managed Instance
  • Azure SQL Database Architecture Components
  • Creating Azure SQL Server (Logical Server)
  • Creating Azure SQL Databases and Pricing Tiers
  • Price Tiers: Basic, Standard, Premium, PremiumRS
  • DTUs Allocation for Database Size, Cost Models
  • Firewall Settings for Azure SQL Server (Logical Server)
  • Adding Firewall Rules - IP for Remote Access
  • Password Resets and Azure SQL Server Name Format

Day 40: AZURE BACKUPS, RESTORES, SYNC

  • Automated Backups in Azure SQL Database
  • Long Term & Automated Backup Retentions
  • Backups - Retentions, Audits. LTR Policy
  • Restore Points and Azure SQL Database Recovery
  • Local Backups, Encrypted Backups, Restores
  • Database Level Exports in Portal, SSMS
  • Manage Storage Pools, Failed Storage Recovery
  • Recovery Service Vaults, Policies with RA-GRS
  • BLOB Data Storage: LRS, GRS, and "RA - GRS"
  • SQL Data Sync Service and Sync Groups
  • HUB, MEMBER and SYNC DATABASE
  • Sync Direction, Sync Schema and Sync Interval
  • bi-directional Load Balancing and Failover
  • Conflict Resolution Policy with HUB and MEMBER
  • Cloud to Cloud and Cloud to On-Premise Synchronization
  • Hub to Member and Member to Hub Sync Policies
  • Common Errors and Solutions - POC

Day 35: DTU ARCHITECTURE, ELASTIC QUERIES

  • DTU : Data Transaction Units : Architecture, Pools
  • DTU - Memory and IO Resources for Reads & Writes
  • Bounding Box Model for Optimal Performance
  • Static Pools (DTU) and Elastic Pools (eDTU)
  • eDTUs and Elastic Pool, per Database Settings
  • EDTU Cost, eDTU max/min Limits and Performance
  • Configuring Elastic Pools for Azure SQL Databases
  • Elastic Pools & Tier Selection - Recommendations
  • Elastic Scale for Azure SQL Database - Strategies
  • Vertical Partitioning and Horizontal Partitioning
  • Elastic Database Tool Libraries for Elastic Queries
  • Sharding - Topology for Elastic Query Processing
  • Split-Merge Service for SaaS Software Applications
  • Elastic Database Features - ShardMap, ShardKey
  • MultiShard Queries and Elastic Transactions
  • LOOKUP, HASH and RANGE Strategies for Sharding

    Day 41 : HA-DR: GEO REPICATION, FAILOVER

    • Failover Groups and Active Geo-Replication
    • Read-Write Failover Policy, Automated Failover
    • Geo Replication Configurations - Azure Locations
    • Primary-Secondary and Read / Write Modes
    • GeoReplication : Monitoring and Forced Failover
    • Perform Disaster Recovery with (DR) Drills
    • System Views for Audit of GeoReplication
    • Backup Errors, Geo Replication Errors - Solutions
    • Azure Server Failover Process, Strategies
    • PRIMARY, SECONDARY, FAILOVER POLICY
    • Automated Failover , Manual Failover
    • Edit Server Failover Configurations
    • Adding Databases to Failover Group
    • Normal Failover and Forced Failover
    • Azure Server Failover Groups: Limitations
    • Comparing Server Failover with Always-On

    Day 36 : AZURE DB MIGRATIONS, AZURE DATA STUDIO

    • Data Migration Assistant (DMA) Tool
    • On-premise to Azure SQL Database Migration
    • Logical Server, Virtual Machine, Managed Instance
    • Schema Generation and Compatibility For Migration
    • Generating Data Scripts & Assessment Reports
    • Generate and Validate Schemas. Migrations
    • Migration Scopes : Schema, Data, Schema & Data
    • Compatibility Checks and Assessment Checks
    • Resolving Database Migration Compatibility Issues
    • Azure Data Studio Tool for Database Connections
    • Azure Data Studio Features - Insights, Metrics
    • Comparing Azure Data Studio with SQL OPS Tool
    • SSMS Tool for Azure SQL Database Connections
    • SQLOPS Tool for Azure SQL Database Connections
    • SSDT (Visual Studio) for SQL Database Connections
    • Query Editor for Azure Database Access - in Portal
    • Azure Database Connections with Other Tools

    Day 42: SECURITY MANAGEMENT, ROW LEVEL SECURITY

    • Firewall and Virtual Network Configurations
    • Server-level Firewall & Database Firewall Rules
    • System Stored Procedures for Firewall Settings
    • Logins, Users, Roles and Permissions using T-SQL
    • Creating Logins without Logins. Containment Users
    • GRANT, DENY, REVOKE and WITH GRANT using T-SQL
    • Dynamic Data Masking, Transparent Database Encryptions
    • Data Masking Functions and Column Level Security
    • Vulnerability Assessment (VA) and Threat Detections
    • Implementing RLS - Row Level Security
    • Creating Users with & without Logins
    • Using SECURITY Schema for RLS Functions
    • Creating Security Predicates and Filters
    • Creating Security Policies for RLS
    • EXECUTE AS options for RLS. STATE ON/OFF
    • Data Level Encryption and Connection Encryption
    • Troubleshoot Encryption Errors in Azure SQL DB

    Day 37: Azure SQL DATABASE TUNING, AZURE SEARCH

    • Azure SQL Server Level Tuning Options
    • Azure SQL Database Level Tuning Options
    • Automated Tuning Options and Peak-Loads
    • Force Plan, Create Index and Drop Index
    • Query Performance Insight, Intelligence
    • Index Recommendations with CPU and IO
    • IO Metrics, CPU Metrics & Query Statistics
    • Data File IO, Log File IO, Custom Reports
    • Identify Long Running Queries, Intensive Queries
    • Query Level Recommendations and Query Costs
    • Azure Search Service - Configuration, Pricing Tiers
    • Azure Search for Data Import and Indexer Options
    • Suggester and Analyzer Index Modes for Tuning
    • Retrievable, Facetable, Filterable Indexes
    • Facetable and Searchable Indexes for Tuning
    • Change Tracking Options, Watermark Columns

    Day 43: AZURE POWER SHELL, AZURE CLI

    • Azure Cloud Shell - Concepts, Architecture
    • Azure Power Shell - Install and Configure
    • Installing and Scripting with Power Shell
    • PowerShellGet and Version Paths
    • Cloud Shell to run the Azure Power Shell
    • Power Shell Scripts for Configurations
    • Power Shell Scripts for Job Schedules
    • Power Shell Scripts for Firewall, ARM
    • Azure CLI - Purpose, Real-time Use
    • Command Line Interface for Automations
    • Azure CLI - Downloads and Installations
    • Azure CLI - Command Prompts, Azure Login
    • Command Line Interface for Automations
    • Authorization Code, CLI Communication
    • Azure telemetry Commands and Usage
    • Help, Show, Create and Firewall Commands

    Day 38: AZURE SQL DB & On-Premise, Extended Events

    • Azure SQL Server Architecture Differences
    • Network Protocols and DB Engine Differences
    • File Structure and Filegroup Allocations
    • Secondary Files and FileStream Differences
    • Table Architecture for Partitions, Full Text Queries
    • Query Processing Differences with TDS Packets
    • Query Monitoring and Resources - Dashboards
    • Unsupported Commands with T-SQL Queries
    • SQL Traces : Creation and Audits. Limitations
    • Extended Events and Traces for T-SQL Query Analysis
    • Extended Events Packages, Targets, Actions, Sessions
    • TSQL and SP Debug Events with XEL Files for EventInfo
    • Global Fields and Event Filters with XEL Files @ Traces
    • XE Profiler - Default Templates for Event Profiling
    • Important System Views and Dynamic Management Views

    Day 44: AZURE VIRTUAL MACHINE, OTHER CLOUDS

    • Plan for IaaS, Azure VM. Install Azure Virtual Machines
    • Install/Configure Windows Server, Readhat, Ubuntu
    • SQL Server on Azure VM - Config Settings, Elastic Pools
    • Resource Groups, Resource Pools and eDTUs
    • Verify SQL Instances in Azure VM. sysadmin Account
    • Azure Storage Blades and Pricing Options
    • Azure VM - Settings, Power Shell Scripts
    • Azure SQL Database Creation in Azure VM
    • Azure Virtual Machines ; Security Options
    • Azure SQL Database in VM to SQL Cloud
    • Azure SQL Database in VM to on-Prem
    • Deployment Automation Process, Automation Scripts
    • SQL Database in AWS and Google Cloud
    • Azure Virtual Machine Configuration Settings
    • Azure SQL Database Advantages in Virtual Machines

    Day 39: STRETCH DATABASES, MANAGED INSTANCES

    • Stretch Databases in Azure SQL Databases
    • Stretch Databases - Tuning Benefits, Cautions
    • Table Level Migrations with Azure SQL Databases
    • Compute Performance Levels and DSU Pricing
    • Data Storage and Azure Database Snapshots
    • Remote Data Archive Configuration in Azure
    • Database Master Key [DMK], Cold Data Migration
    • SQL Database Managed Instance - TDE
    • Native Virtual Networks and Scalability
    • Lift - Shift LOB Applications, PaaS Infrastructure
    • Frictionless Migration for On-Premise
    • vCore Purchasing Model for Azure SQL DB
    • Gen 4 and Gen 5 Logical CPUs, Resource Limits
    • Managed Instances Vs AWS & Google Cloud
    • Cell Level Encryption and Always-Encrypted

     

    REAL-TIME PROJECT FOR AZURE SQL DBA

     

    MCSA : 70-764 Exam Guidance, Samples & Material

     

    MCSA : 70-765 Exam Guidance, Samples & Material

    Above course curriculum applicable for registrations from March 20th, 2019.

    24x7 Suport with Real-time Database Training. Course includes ONE Real-time Project. Register Today

    All Classes are Instructor-Led & LIVE. Completely Practical and Real-time with Study Material, Session Notes, Tasks and Mock Tests.

     
    Trainer Profile
    Reviews
    Course Curriculum
    Training Details
    Free Demo
    Register Today
     

    SQL DBA Online Training- Highlights :

    • Completely Practical and Real-time
    • Suitable for Starters + Working Professionals
    • Session wise Handouts and Tasks + Solutions
    • TWO Real-time Case Studies, One Project
    • Weekly Mock Interviews, Certifications
    • Certification & Interview Guidance
     
     
    • Detailed SQL Server Architecture, DB Repairs, Migrations
    • Query Tuning, Stored Procedures, Linked Servers
    • In-Memory, DAC and Contained Databases
    • Routine DBA Activities, Emergency DBA Activities
    • SQL Profiler, SQLDIAG, DTA, DMA, SSMA Tools
    • SQL Clusters, Always-On. SQL DBA in Azure Cloud
    Register Today Other Popular Courses: SQL DBA Training, MSBI Training, SSIS Training, SSAS Training, SSRS Training [+] More Courses