SQL Server Administration Training
Duration - (6 Half-Days, 4 Hours/Day)
Day 1: SQL Server Fundamentals & Architecture
• Introduction to SQL Server
o Editions, components, and tools (SSMS, Azure Data Studio)
o SQL Server services (Database Engine, Agent, Integration Services, etc.)
• SQL Server Architecture
o Instance vs. Databases
o Pages, Extents, Files, and Filegroups
o Memory and Process Architecture
• Installation & Configuration
o Installing SQL Server 2019/2022
o Best practices for setup (collation, service accounts, tempdb)
o Initial security configuration
Hands-on Lab: Install SQL Server instance and configure basic settings.
Day 2: Security, Authentication & User Management
• Authentication Modes (Windows, SQL Authentication, AD integration)
• Logins, Users, Roles
• Permissions & Securables
• Encryption & Data Protection
o Transparent Data Encryption (TDE)
o Always Encrypted
o Row-Level Security
• SQL Server 2022 New Security Features
o Ledger for immutable data
o Azure AD authentication integration
Confidential StackRoute© An NIIT Venture
All Information within this document is Intellectual property of StackRoute (NIIT Ltd). No part of this
document or the program design or program structure mentioned within can be shared or used within any
organization without the permission of StackRoute (NIIT Ltd).
Hands-on Lab: Create logins, users, and roles with different permissions. Implement row-level security.
Day 3: Backup & Recovery Strategies
• Types of Backups
o Full, Differential, Transaction Log
o File/Filegroup backups
• Recovery Models (Simple, Full, Bulk-Logged)
• Restore Operations
o Point-in-Time Recovery
o Piecemeal Restore
• Backup Encryption & Compression
• SQL Server 2019/2022 Enhancements
o Accelerated Database Recovery (ADR)
o Intelligent Query Processing (benefits during recovery)
Hands-on Lab: Perform full, differential, and log backups; restore a database to a specific point-in-time.
Day 4: Performance Tuning & Monitoring
• Monitoring Tools
o Activity Monitor, DMVs, Extended Events
o Query Store (improvements in SQL 2019/22)
• Indexing Strategies
o Clustered, Non-Clustered, Columnstore indexes
o Index maintenance (Rebuild/Reorganize)
• SQL Server 2019/2022 Performance Features
o Intelligent Query Processing (Batch mode on rowstore, Scalar UDF Inlining, Approximate
Count Distinct)
o Query Store enhancements (better plan forcing, multiple databases)
• Resource Governor for workload management
Confidential StackRoute© An NIIT Venture
All Information within this document is Intellectual property of StackRoute (NIIT Ltd). No part of this
document or the program design or program structure mentioned within can be shared or used within any
organization without the permission of StackRoute (NIIT Ltd).
Hands-on Lab: Use Query Store to identify slow queries and test performance improvements.
Day 5: High Availability & Disaster Recovery
• SQL Server HA/DR Options
o Log Shipping, Database Mirroring, Replication
o Always On Failover Cluster Instances (FCI)
o Always On Availability Groups (AGs)
• Distributed Availability Groups (2019/2022 enhancements)
• Backup to Azure & Hybrid HA/DR
• Monitoring HA/DR health
Hands-on Lab: Configure a basic Always On Availability Group and failover testing.
Day 6: Maintenance, Automation & New Features
• Automation with SQL Server Agent
o Jobs, Alerts, Operators
• Database Maintenance Plans
o Backups, Index Maintenance, Integrity Checks
• Monitoring & Alerting
• SQL Server 2019/2022 New Features Recap
o Big Data Clusters (2019)
o Ledger Tables (2022)
o Azure Synapse Link Integration (2022)
o Contained Availability Groups
• Future Trends & Best Practices
Hands-on Lab: Create a SQL Server Agent job for backup automation and monitoring.
Confidential StackRoute© An NIIT Venture
All Information within this document is Intellectual property of StackRoute (NIIT Ltd). No part of this
document or the program design or program structure mentioned within can be shared or used within any
organization without the permission of StackRoute (NIIT Ltd).