Main menu

Zero to Hero SQL Server DBA Course Plan

Level 1 (Foundation Stage) RegisterNow_Pic3

Level 2 (HA DR Stage)

Level 3 (Advanced Stage)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Level 1 (Foundation Stage) Course Details – 30 Days RegisterNow_Pic3

Module 1:
Course Introduction
Course Goals
Course Outline
What is SQL Server
SQL Server Certifications
SQL Server Online Resources

Module 2:
Introduction to SQL Server
Evolution of database and its Ancient History
What is DBMS/RDBMS
Different RDBMS
History of SQL Server
Versions of SQL Server
Editions of SQL Server
Hardware and Software requirements of SQL Server
SQL Server Licensing and Pricing
Tools of SQL Server (GUI and CUI)
Introduction to SQL Server Architecture (Basic Version)

Module 3:
SQL Server 2008R2/2012/2014 Installations
Prerequisites of SQL Server Installation
Installing SQL Server 2008 R2 on Win-2008 and Service Packs
Installing SQL Server 2012/2014 on Win-2012 and Service Packs
Default Instance and Named Instance
Instance Aware and Unaware Services
Service Accounts (Inbuilt and Customized)
Collation Settings
Authentication Modes and their Importance
Client-Server Understanding
Unattended Installations and Parameters
Slipstream Installations
SYSPREP Installations (Image Based)
Powershell Installation Integration
Service Pack Vs Cumulative Updates Vs Hotfixes
Troubleshooting failed SQL Server Installs & Service Packs

Module 4:
Database & File Storage Structures in SQL Server
Databases and their Importance
Types of Files and their classifications
Datafile Architecture
-Pages and Extents
-Filegroups
-Autogrowth
Logfile Architecture
-Virtual Log Files
-Log Record
-LSN
Database Creation (GUI Screens and Commands)
Database Snapshot Creation and Sparse Files
Detach/Attaching database
Shrinking databases and files

Module 5:
Security in SQL Server
Principals and Securables
Instance Level Security
-Logins
-Server Roles (Customized)
-Permissions
Database Level Security
-Users
-Database Roles
-Application Roles
-Permissions
Object Level Security
-Database Objects
-Permissions
Certificates
Symmetric and Asymmetric Keys (Keys)
Credentials
Proxy Accounts

Module 6:
Backup and Recovery in SQL Server
Recovery Models
Backups and its Types
-Full
-Differential
-Tlog
-File and Filegroup
-Stripe/Mirror/Copy Only/Tail Log/Partial/Partial Differential
Backup Strategies
Backup Compression
Restoration
-Full Database
-File and Filegroup
-Piece Meal
-Page
Recovery Phases and Explanation
Standby Recovery options and TUF file
Corruptions
-System Database Corruption Issues
-User Database Corruption Issues
-Page Corruption issues
-Suspect Database State

Module 7:
Automation in SQL Server
SQL Server Agent
MSDB and its importance
Jobs
Alerts
Operators
Maintenance Plans
Troubleshooting Job Failures and Maintenance Plan failures
Configuring Database Mail
SQL Mail Vs Database Mail
Realtime Project on configuring Critical Alerts (Alert Management)

 

 

Level 2 (HA DR Stage) – 30 Days RegisterNow_Pic3

Module 1: Log Shipping
High Availability Vs Disaster Recovery
HA DR Terminologies and Differences
Log Shipping and its Introduction
Terminologies in Log Shipping
Implementing Log Shipping (Standalone/Domain Level)
Merits and Demerits of Log Shipping
Troubleshooting Log Shipping
Scenarios on Log Shipping and how to resolve with min effort
Log Shipping differences in 2005 and other versions

Module 2: Failover Clustering
Clustering and Architecture of Windows Clustering
Terminologies in Clustering
Types of Windows Clusters(NLB/CLB/Failover) & SQL Clusters
Requirements for Windows and SQL Server Clustering
Implementation of Windows Cluster (Windows 2008R2 /2012 R2)
Implementation of SQL Server Cluster (SQL 2008 R2 / 2012 / 2014)
Merits and Demerits of Clustering
Troubleshooting Cluster issues both (Windows and SQL)
Scenarios on Clustering and how to resolve with min effort
Clustering differences in 2005, 2008R2, 2012 and 2014
Cluster Scenarios (Adding Disk, Disk Dependency)

Module 3: Mirroring
Mirroring Architecture and Operating Modes
Terminologies in Mirroring
Mirroring Components (Endpoints, Quorum, TSafety)
Merits and Demerits of Mirroring
Troubleshooting using Mirror Monitor
Scenarios in Mirroring

Module 4: AlwaysON Availability Groups
AlwaysON Availability Groups Introduction and Implementation
Terminologies and Architecture (inline with Mirroring and Clustering)
AlwaysON Availability Groups Concepts and Components
Merits and Demerits of AlwaysON AG
Implementation on a Windows Cluster without SAN
AlwaysON Availability Groups Additional Scenarios
Monitoring Metrics, Dashboard, Internals, Health checks, Failover, Switchover

Module 5: Replication
Replication Model and Architecture
Terminologies in Replication
Types of Replications and Replication Agents
Replication Topologies (Subscription Types)
Implementation of All Replication
Merits and Demerits of Replication
Troubleshooting Replication issues (through Replication Monitor)
Scenarios on Replication and how to resolve with min effort

 

 

Level 3 (Advanced Stage) – 40 Days RegisterNow_Pic3

Module 1: Advanced Architecture 
Understanding SQL Server Architecture (Advanced Version)
Memory Architecture of SQL Server
AWE and its implementation through switches /3GB, /PAE
Processor Architecture (Scheduler and Worker Threads)
Monitoring SQL Server CPU, Memory and IO Utilization – (With Script Library)
Using Views-DMVs and Documented-Undocumented commands

Module 2: Lock Block and Deadlock
Isolation Levels
ACID Properties
Locking
Blocking
Deadlocks (Monitoring and Troubleshooting using Extended Events)
Livelock
Lock Escalations Rules

Module 3: Indexes
Indexes Introduction and Architecture
B-Tree Internals and Types of Indexes
Table/Index Fragmentation and its Types
Lookups, Scans and Seeks
Column Store Indexes

Module 4: Performance Improvements
Tempdb Usage Issues and Recommendations
Instance/Database Level Settings
Parameter Tuning – MAXDOP, Max/Min Server Memory, Packet Size, Worker Threads
Performance Monitoring Tools
-Perfmon
-Profiler
-Extended Events

Module 5: Miscellaneous Concepts
Upgradation & Migration from SQL Server 2000/2005-to-2012/2014
-Inplace and Side-by-Side with Implementation
-Real Time Project Work
Troubleshooting Scenarios
-LogFile Full Issues
-DataFile Full Issues
-TempDB Full Issues
-Instance/Database Connectivity Issues
-ITIL Best Practices (Incident and Change Management)
-Query Running Slow
-Application Running Slow
-100% CPU and 100% Memory
-Identifying Top Queries based on CPU/Memory

Module 6: SQL Server 2008R2/2012/2014 New Features
Policy Based Management
Centralized Management Servers
Resource Governor
Auditing
Memory Architecture Enhancements (SQL 2012)
Indirect Checkpoints (SQL 2012)
Sequence and Pagination (SQL 2012)
Introduction to SQL Server 2016 New Features

RegisterNow_Pic3