Azure SQL DBA (Admin) - Classroom Training

Complete Real-time and Practical Azure SQL Database Training with Real-time Scenarios. This course includes complete practical approach to Azure SQL Database Administration (Azure SQL DBA) activities including On-premise to Azure Azure Database Migrations, Troubleshooring, Security Management, Geo Replication, Azure Server Failover, Storage Management, Virtual Machines, In-depth Query Tuning, Stretch Databases, Azure Shell, Power Shell, In - Memory Tables, Azure Search, Sharding, and SQL Managed Instances. This Azure SQL DBA course also includes One Real-time Project with Routine DBA Activiteis and Emergency DBA Activities.

Azure SQL DBA (with SQL Server, Azure SQL DB Admin)

  PLAN A PLAN B
Azure SQL DBA - Job Profile Azure SQL DBA Complete DBA
Applicable For Experienced DBA Starters
Course Duration 2.5 Weeks 8 Weeks
Azure SQL Migrations Check-Symbol-for-Yes Check-Symbol-for-Yes
Query Tuning in Azure SQL DB Check-Symbol-for-Yes Check-Symbol-for-Yes
Azure Search & Sharding Check-Symbol-for-Yes Check-Symbol-for-Yes
Built-In Intelligence Check-Symbol-for-Yes Check-Symbol-for-Yes
TDE, DDM, PITR & RLS Check-Symbol-for-Yes Check-Symbol-for-Yes
Stretch Databases in Azure Check-Symbol-for-Yes Check-Symbol-for-Yes
HA - DR in Azure SQL Databases Check-Symbol-for-Yes Check-Symbol-for-Yes
Azure Virtual Machines Check-Symbol-for-Yes Check-Symbol-for-Yes
Azure PowerShell, Azure CLI Croos-symbol-for-Yes Croos-symbol-for-Yes
SQL Server, DB Architecture Check-Symbol-for-No Check-Symbol-for-Yes
Query Tuning and Execution Plans Check-Symbol-for-No Check-Symbol-for-Yes
HA and DR Stategies Check-Symbol-for-No Check-Symbol-for-Yes
DB Maintenance, Repairs Check-Symbol-for-No Check-Symbol-for-Yes
Security, Locks Check-Symbol-for-No Check-Symbol-for-Yes
Errors, Issues & Solutions Check-Symbol-for-No Check-Symbol-for-Yes
Clustering & Always-ON Check-Symbol-for-No Check-Symbol-for-Yes
AAG in Windows & LINUX Check-Symbol-for-No Check-Symbol-for-Yes
Real-time Project with Solution Check-Symbol-for-No Check-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 6000/- INR 15000/-

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

S. No Timings (IST) Start Date  
Schedules for Chapters 31 to 42 (For Azure DBA)
1 6:30 AM - 8:30 AM (W) Jan 4th Register
2 10:00 AM - 12 PM (W) Dec 21st Register
Schedules for Chapter 1 to 30 (For Core SQL DBA)
4 6:30 AM - 7:30 AM Jan 28th Register
5 10:15 AM - 11:15 AM Dec 16th Register
6 7:30 PM - 8:30 PM Dec 23rd Register

If above schedules does not work, opt for Azure SQL DBA Video Training

 

SQL DBA TRAINING HIGHLIGHTS :

✔ In-depth Tuning ✔ HA-DR Issues ✔ In-Memory Data
✔ Graph Tables ✔ Stretch Databases ✔ DAC, PBM
✔ DB Migrations ✔ Licensing Plans ✔ VN, VPN Config
✔ DMA Tool ✔ SSMA Tool ✔ POCs, RCA
✔ Ticketting Tool ✔ PSSDIAG ✔ LiteSpeed
✔ Always-On ✔ Windows Clusters ✔ Linux (RHEL)
✔ BLOB Storage ✔ Power Shell ✔ Google Cloud
✔ Virtual Machines ✔ Azure CLI ✔ AWS Cloud

Register Today for Azure SQL DBA Training

Azure SQL DBA Training (with SQL Server, Azure DBA) Course Contents:

Brochure PDF-Image

Register Today

Part 1 : SQL DBA with SQL Server, Security, HA-DR, Errors & Solutions

Module I: SQL Server & T-SQL

Installation, Architecture, Indexes

Module II: Basic SQL DBA

Backup-Restores, Jobs, Tuning, Security, Migrations

Module III: Advanced SQL DBA

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

Day 1: SQL DBA - JOB ROLES, INTRO

  • Introduction to Databases, DBMS
  • Introduction to Microsoft SQL Server
  • Microsoft SQL Server Advantages, Use
  • Versions and Editions of SQL Server
  • SQL DBA Job Roles, Responsibilities
  • Routine, Maintenance DBA Activities
  • Emergency SQL DBA Activities
  • Azure Cloud For SQL DB Admins
  • SQL Server in Microsoft Azure Cloud
  • SQL DBA Roles in Azure Cloud
  • SQL DBA Roles in Azure VMs
  • SQL Server Pre-requisites : S/W, H/W
  • SQL DBA Course : Lab Plan
  • System Configuration Checker Tool

Day 12: BACKUPS - DB, Filegroup, File

  • 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

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
  • Replication Monitor - Tracer Tokens
  • Replication Monitor - Warnings, Alerts
  • Replication Monitor - Usage and Options
  • Replication Scripts, Adding Articles
  • Replication Warnings and Agent Alerts

Day 2: INSTALLATIONS, FIREWALL

  • SQL Server 2016 / 2017 Installation
  • SQL Server 2019 Installation
  • SQL Server Instance Configuration
  • Default Instance & Server Properties
  • Named Instance & Server Properties
  • Ports for Default and Named Instances
  • DBEngine and Replication Components
  • Service and Service Account Use
  • Authentication Modes and Logins
  • Windows, Mixed Mode Authentication
  • FileStream, Collation Properties
  • Firewall Warnings and Solutions
  • Firewall Configuration in Real-time
  • SQLServr.exe and SQLBrowser.exe

Day 13: 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

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 Conflicts and sp_MSRepl
  • sp_changedbowner, backup initialization
  • Replication Conflicts and Priority Settings
  • Replication Verify - Rowcount, Checksum
  • Disabling, Cleaning Replication Topology
  • Replication Strategies for HA and DR Plan
  • Replication for Load Balancing Topologies
  • Common Backups Errors & Solutions

Day 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
  • SELECT Statement for Table Retrieval

Day 14: 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 Size, Transfer Size
  • DB Mail Configurations and Alert System
  • DB Mail Profiles, SMTP Email Accounts
  • Operators : Creation, Job Notifications

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
  • LS Job Audits, Dashboards (Reports)
  • TUF Files and Standby Options in LS
  • Broken Log Shipping Chains & Issues
  • Common Backups Errors & Solutions

Day 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
  • 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
  • ALTER & DROP Table Statements

Day 15: SECURITY MANAGEMENT - 1

  • 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

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
  • DB Mirroring and Port Configurations
  • Mirroring Monitor, Stop/Resume Options
  • Need for Always-On & Higher Availability
  • DB Recovery without Witness. Failover
  • Mirroring Monitor Jobs - Real-time Usage
  • Common Backups Errors & Solutions

Day 5: SQL Basics - 3

  • Schemas : Group Tables in Database
  • Using Schemas for Table Creation
  • Using Schemas in Table Relations
  • Table Migrations across Schemas
  • Using Default Schema : "dbo"
  • Import and Export Wizard in SSMS
  • Performing Bulk Operations in SSMS
  • Excel File Imports / Exports
  • SQL Server Native Client Connection
  • Executing SSIS Packages, Data Loads
  • Temporary Tables : Real-time Use
  • Local and Global Temporary Tables
  • # and ## Prefix, Scope of Usage
  • Temporary Versus Permanent Tables

Day 16: SECURITY MANAGEMENT - 2

  • Data Encryption: Keys and Certificates
  • Encrypt Passwords : ENCRYPTBYCERT
  • Certificate Backups and Realtime Use
  • Job Security : Credentials and Proxies
  • Using Proxies for SSIS Jobs, Repl Jobs
  • Detecting and Resolving Orphan Users
  • Orphan Users : Prevention Mechanism
  • Containment Database Settings, Partial
  • Containment Databases Authentication
  • Security Scripts and Documentation
  • DMVs for Security Audits, Orphan Users
  • Scripts: Server Principals and Logins
  • Scripts: Database Principals and Users
  • Audit: Logins - Roles - User Permissions

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
  • 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 : CONSTRAINTS, KEYS

  • Constraints and Keys - Data Integrity
  • NULL, NOT NULL Property on Tables
  • UNIQUE KEY Constraint and Usage
  • PRIMARY KEY Constraint and Usage
  • FOREIGN KEY Constraint, References
  • CHECK Constraint Usage & Conditions
  • DEFAULT Constraint Usage and Rules
  • Identity Property : Seed & Increment
  • Database Diagrams (E R), Relations
  • Install and Use Database Diagrams
  • Composite Keys Creation from GUI
  • Candidate Keys and Foreign Keys
  • Adding Keys to Existing Tables
  • Constraint Priority and Executions

Day 17: DB Migrations, Health Checks

  • 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
  • Database Migrations & Orphan Users
  • Database Health Check Commands
  • Allocation Errors, Consistency Errors
  • DBCC ShowContig, Extent Fragmentation
  • DBCC for Databases & Object Integrity
  • Trace Flags and EstimateOnly
  • DBCC Page: GAM, SGAM and PFS
  • DBCC Page: DIFF, ML and Bitmap

Day 28: ALWAYS ON AVAILABILITY

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

Day 7: JOINS, T-SQL Queries : Level 1

  • 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
  • One-way and Two Way Data Comparisons
  • Join Queries with "ON" Conditions
  • Join Unrelated Tables in SQL Server
  • NULL, IS NULL Operators in Joins
  • CROSS JOIN and CROSS APPLY
  • CROSS JOIN Versus CROSS APPLY
  • Need for Joins in Database Audits
  • Important Join Queries in T-SQL

Day 18: ISSUES & SOLUTIONS

  • Alerts : Creation and Notifications
  • SQL Server Agent Operators
  • Using Operators in Alerts
  • DB Suspect Event Alerts (023)
  • Perfmon Counters for Alerts
  • Log Space, Memory, Tempdb Alerts
  • Scheduling Alerts & Notifications
  • Database Suspect Errors : Solutions
  • Consistency Errors : Cause & Solutions
  • Allocation Errors : Cause and Solutions
  • Log Space Issues and Solutions
  • Memory & TempDB Issues, Solutions
  • DBCC ShrinkDB and Page Restores
  • Rebuilds: Log File, Memory, Tempdb

Day 29: 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
  • 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

Day 8: View, Procedure, Function Basics

  • Views : Types, Usage in Real-time
  • System Predefined Views and Audits
  • Listing Databases, Tables, Schemas
  • Functions : Types, Usage in Real-time
  • Scalar, Inline and Multi-Line Functions
  • System Predefined Functions, Audits
  • DBId, DBName, ObjectID, ObjectName
  • Variables & Parameters in SQL Server
  • Procedures : Types, Usage in Real-time
  • User & System Predefined Procedures
  • Parameters and Dynamic SQL Queries
  • Sp_help, Sp_helpdb and sp_helptext
  • sp_pkeys, sp_rename and sp_help
  • When to use Which Database Objects

Day 19: Tuning 1 - Audits & Partitions

  • 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
  • Factors Impacting LIVE Query Executions
  • Query Store - Settings and Advantages
  • PARTITIONS : Advantages, Performance
  • Partition Functions & Partition Schemes
  • Partitioning Un-partitioned Tables: GUI
  • Partition Compression : ROW and PAGE
  • Auditing Table Partitioned Structures

Day 30: INCIDENT MANAGEMENT

  • SLA and OLA Process For SQL DBAs
  • Tiers & Levels of Production Support
  • Ticketing Tools, Priority Tickets
  • Immediate, High, Normal Priorities
  • Planned Priorities and Activities
  • Impact, Urgency and SLA Metrics
  • Real-time Server Environment
  • Licensing and Pricing Options
  • Core Based Licenses. Device CALs
  • user CALs and Multiplexing Concept
  • Versions Comparisons and Capacity
  • Editions Comparisons and Capacity
  • RAID Levels and Performance Benefits
  • RAID Levels for Data Recovery, Parity

Day 9: Triggers & Linked Servers

  • Triggers - Purpose, Real-world Usage
  • FOR/AFTER Triggers - Real time Use
  • INSTEAD OF Triggers - Real time Use
  • INSERTED, DELETED Memory Tables
  • DML Automations using Memory Tables
  • Read Only Tables using DML Triggers
  • Enable Triggers and Disable Triggers
  • Database Level, Server Level Triggers
  • Linked Servers and Real-time Usage
  • Creating Linked Servers in SQL Server
  • Security Options and Access Options
  • Data Access, RPC and RPC Out Settings
  • Linked Servers for Remote Joins
  • Using Aliases with Linked Servers
  • Table Data Replication using Triggers

Day 20: Tuning 2 - Index Management

  • Statistics : Purpose, Auto Creation
  • Auto Creation of Statistics in Indexes
  • Auto Creation of Statistics in Queries
  • Auto Update of Statistics using DMP
  • Internal and External Fragmentation
  • Index Rebuilding Process and Audits
  • Database Maintenance Plans Jobs
  • Last Used, Page Count, Fragmentation
  • Index Page Count and Index Condition
  • Degree Of Parallelism [DOP] Settings
  • Resumable Indexes: ONLINE, RESUME
  • PAUSE & RESUME in Index Rebuilds
  • Fast, Detailed Scans. NoRecompute
  • Statistics : Index and Column Statistics
  • Manual and Auto Update of Statistics
SQL DBA REAL-TIME PROJECT
ROUTINE SQL DBA ACTIVITIES :
  • 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
  • Adhoc MDMP Files and Audits
  • Monitoring HA & DR Mechanisms
  • Backup Audits and Recovery Reports
  • Database Refresh, Schema Refresh
  • Object Refresh and Recompilations
  • Idle Sessions and Precautions
  • Index Management and Integrity
  • Deadlock : Monitoring, Prevention
  • Monitor Server Health, Dashboards
  • Activity Monitor Tool & Reports
  • Perfmon Tool & Important Counters
  • Profiler Tool and DTA Tool
  • 3rd Party Tools : Realtime Use
  • 3rd Party Tools: Installations
  • Server Registrations and Operations
  • Log File Audits and Data Recovery
  • Restore Schedules and Backup Reads
EMERGENCY SQL DBA ACTIVITIES :
  • Server Down Issues, Solutions
  • Database Down Issues, Solutions
  • Synchronization Issues, Solutions
  • Slow Queries Issues, Solutions
  • Database Response Issues, Solutions
  • Slow Replication Issues, Solutions
  • Replication @ HA-DR Issues, Solutions
  • Hot CPU and Processor Node, Priority
  • CPU Thread Counts, Windows Fibers
  • Contained Databases, Orphan Users
  • Important Events, Errors & Solutions
  • Memory Dump Files and Solutions
  • Firewall Issues, Port Configurations
  • Service Startup Issues, Solutions
  • DAC (Dedicated Admin Access) Issues
  • System Database Rebuilds, SQL CMD
  • Database Rebuilds and Precautions
  • Table Rebuilds and Precautions
  • Log Space Rebuilds and Precautions
  • Memory Rebuilds and Precautions
  • Always-On (AOAG): Issues & Solutions
  • Always-On (AOAG) : Data Loss Issues

Day 10: Transactions, Architecture

  • 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
  • Server Architecture and Protocols
  • Database Engine and Query Processor
  • Parser, Optimizer, SQL & DB Manager
  • Storage Engine Components, SQL OS
  • Transaction Services, Buffer Manager
  • Lock Manager, IO Manager, MDAC
  • CLR, WAL, Lazy Writer, Checkpoint

Day 21: Tuning 3 - TUNING TOOLS

  • Tuning Tools : Workload Files, Trace Files
  • Profiler Tuning Template, SP Events
  • DTA, Profiler Trace : Recommendations
  • DTA with .SQL and Procedure Cache
  • Perfmon Tool: Total Memory & TPS
  • Perfmon Counters, Real-time Tracking
  • Processor, Disk, Memory, I/O Counters
  • Execution Plan Analysis and Internals
  • Estimated Execution Plan : Usage
  • Query Costs : IO Cost, CPU Cost
  • Query Costs: SubTree & Operator Cost
  • Spooling and Tempdb Usage
  • NUMA Nodes and IO Affinity
  • Processor Thread Counts and DOP

Day 11: DB, Index Architecture

  • Database Architecture - Data Files
  • Database Architecture - Log Files
  • Primary (mdf), Secondary Files (ndf)
  • Filegroups Usage, ReadOnly Filegroups
  • Database Files : Size and Location
  • Pages, Extents. Uniform, Mixed Extents
  • Transaction Log File [LDF], LSN, VLF
  • Checkpoint and Lazy Writer Threads
  • 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

Day 22: Tuning 4 - Lock Management

  • LOCKS : Types, Concurrency Control
  • Lock Types and Lock Escalations
  • X, S, IS, IX,U, MD, Sch-M, Sch-S
  • Lock Audits : SP_WHO2, SP_LOCK
  • sysprocesses and Lock Waits
  • Auditting and Avoiding Blocking
  • Deadlock Simulation and Prevention
  • Deadlock Audits & Events in Profiler
  • Deadlock Graphs and XDL Files
  • Isolation Levels and Query Blocking
  • Row Versions and Page Versions
  • ReadComitted, UnComitted, Snapshot
  • Repeatable Reads and Phantom Reads
  • Read Comitted Snapshot Isolations

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

Our SQL DBA Online 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.
Additional info on 3rd party Tools, Ticketing Tools [LiteSpeed, Foglight] & Windows Clusters are also included in this course - upon sucessful completion of the Project.

Part 2: Azure SQL DBA [Applicable for Plans A & B]

Module IV: AZURE SQL DATABASE

Module V: AZURE SQL DBA

Day 31: AZURE CLOUD INTRO, CONFIGURATION

  • 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
  • SQL Database Implementations in Azure Platform
  • Logical Servers, Virtual Machines, Managed Instance
  • Installing SSMS and Azure Data Studio (ADS) Tools
  • Creating Azure Account and Free Subscriptions
  • Creating Azure SQL Server (Logical Server)
  • Firewall Settings for Azure SQL Server (Logical Server)
  • Adding Firewall Rules - IP for Remote Access
  • Server Properties and Status Check. Server Name Format
  • Password Resets and Azure SQL Server Name Format
  • Creating Azure SQL Databases and Pricing Tiers
  • Azure SQL Database Access from SSMS Tool

Day 37 : BACKUPS / RESTORES, HA-DR Level 1

  • Automated Backups in Azure SQL Database
  • Long Term & Automated Backup Retentions (bacpac)
  • Backups - Retentions, Audits. LTR Policy
  • Restore Points and Azure SQL Database Recovery
  • Geo-Redundant Backups and Advantages in Azure
  • Azure Storage Explorer Tool using Backups
  • Disaster Recovery (DR) Advantages in Azure
  • 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
  • Azure Server Failover Process, Strategies
  • Automated Failover , Manual & Forced Failover
  • Backup Errors, Geo Replication Errors - Solutions

DAY 32 : LICENSING, DTU ARCHITECTURE

  • Basic, Standard and Premium Plans For SQL DB
  • vCore Based Purchasing Options and Data Size
  • General Purpose and Business Critical Plans
  • Compute Tier : Provisioned and Serverless
  • Compute Generation : Gen 4 and Gen 5 Types
  • DTUs Allocation for Database Size, Cost Models
  • 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

Day 38 : HA-DR: Level 2, Azure Always-On

  • 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 / On-Premise Synchronization
  • Hub to Member and Member to Hub Sync Policies
  • Azure Server Failover Process, Strategies
  • PRIMARY, SECONDARY, FAILOVER POLICY
  • Automated Failover , Manual Failover
  • Adding Databases to Failover Group
  • Normal Failover and Forced Failover
  • Configuring and Using Azure Always-On
  • Load Balancing with Azure Always-On

DAY 33 : AZURE STORAGE, MIGRATIONS

  • Azure Storage : Purpose, Azure BLOB Data
  • Azure Storage Account Types and Creation
  • LRS, GRS and RA-GRS Azure Storage Accounts
  • Classic Deployment Model Instances in Azure
  • SQL Storage Management (SMB), Azure Storage
  • Azure Data Lake Storage (ADLS) : File System
  • Storage Containers, Storage Explorer Tool Usage
  • Database Migrations To Azure SQL
  • Database Migrations From Azure SQL
  • Database Exports and Imports in Azure
  • Data Migration Assistant (DMA) Tool
  • Migration Scopes : Schema, Data, Schema & Data
  • Schema Generation and Compatibility For Migration
  • Compatibility Checks and Assessment Checks

Day 39 : SECURITY MANAGEMENT, RLS

  • 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, WITH GRANT Permissions
  • Threat Detection, Transparent Database Encryption
  • Vulnerability Assessment, Dynamic Data Masking
  • Implementing RLS - Row Level Security
  • Creating Users with and without Logins
  • Using SECURITY Schema for RLS Functions
  • Creating Security Predicates and Filters
  • Security Policies for Row Level Security
  • EXECUTE AS options for RLS. STATE ON/OFF

DAY 34 : AZURE SQL DATABASE TUNING

  • 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, Recommendations
  • IO Metrics, CPU Metrics & Query Statistics
  • Data File IO, Log File IO, Custom Reports
  • 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 40 : 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
  • 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
  • Azure telemetry Commands and Usage
  • Help, Show, Create and Firewall Commands

DAY 35 : XEL GRAPHS, STRETCH DATABASES

  • SQL Traces : Creation and Audits. Limitations
  • SQL Traces : Event Class, Category, Filter, Conditions
  • Extended Events Package, Target, Action, Session
  • TSQL and SP Debug Events with Global Fields
  • XE Objects : Catalog & Dynamic Management Views
  • XE Profiler - Templates for Event Profiling
  • Comparing XE Profiler with SQL Profiler Tool
  • 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
  • Geo Backup Of Tables and Hybrid Cloud Settings
  • Remote Data Archive Configuration in Azure
  • Database Master Key [DMK], Cold Data Migration
  • Real-time Advantages of Stretch Databases

Day 41 : AZURE VIRTUAL MACHINE - WINDOWS

  • IaaS, Azure VM. Install Azure Virtual Machines
  • Install/Configure Windows Server, Redhat, Ubuntu
  • SQL Server 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, DB Creation in Azure VM
  • On-Premise & Azure Virtual Machines: Similarities
  • SSMS, SQL Server Instance, Agent 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 36 : ALERTS, ON-PREMISE DIFFERENCES

  • Azure SQL Server Architecture Differences
  • Network Protocols and DB Engine Differences
  • File Structure and Filegroup Allocations
  • Secondary Files and FileStream Differences
  • Query Processing Differences with TDS Packets
  • Query Monitoring and Resources - Dashboards
  • Unsupported Commands with T-SQL Queries
  • On-premise Versus Azure : Advantages
  • On-premise Versus Azure : Limitations
  • 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

Day 42 : LINUX VM, MANAGED INSTANCES

  • SQL Server on LINUX - RHEL (Red Hat Linux)
  • Advantages of LINUX RHEL Over Windows
  • LINUX RHEL Security Management, Firewall
  • Storage, vCores and Storage Options in VM
  • RHEL Clusters for SQL Server on LINUX
  • Failover Mode and Node Majority - TCP
  • Deployment Models and Azure Resource Manager
  • RHEL Cluster Resource Manager Settings
  • Pacemaker and SQL Server Nodes in AG
  • 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

MCSA 70 - 764 Certification : Guidance, Sample Papers, Material


MCSA 70 - 765 Certification : Guidance, Sample Papers, Material

Above course curriculum applicable for registrations from Dec 1st, 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