Microsoft SQL Server Database Administration Training Course

Course overview

SQL Server administrators are expected to keep business systems available while managing growing databases, unpredictable workloads, recovery requirements, security controls, and maintenance windows. This course addresses the operational work behind those expectations: diagnosing blocking and slow queries, planning recoverability, controlling access, automating repeatable tasks, and making evidence-based decisions about capacity and high availability. Participants learn how to administer SQL Server instances with an approach that supports both daily service reliability and audit-ready operational control.

The course covers SQL Server architecture, instance and database configuration, storage and file management, authentication and authorisation, backup and restore strategy, SQL Server Agent automation, performance monitoring, and high-availability options. Participants work with SQL Server Management Studio, Transact-SQL, Dynamic Management Views, Extended Events, and maintenance plans to investigate realistic incidents. They build practical skills in restoring databases to a point in time, analysing waits and blocking, managing indexes and statistics, scheduling jobs, and documenting recovery and maintenance procedures.

Instruction combines focused technical demonstrations with guided labs on a configured SQL Server environment. Each day uses operational scenarios such as a failed backup job, a rapidly growing transaction log, a blocked application workload, or a recovery request after accidental data deletion. By the end of the week, each participant produces a SQL Server administration runbook containing an instance health-check routine, backup and restore plan, job schedule, monitoring queries, escalation thresholds, and an improvement plan for a nominated production environment.

The course is designed for professionals who already work with SQL Server databases and need to administer them with greater independence, consistency, and technical judgement. It is equally valuable to managers seeking to reduce reliance on individual experts and establish repeatable database operations.

Course objectives

By the end of this course, participants will be able to:

• Configure SQL Server instances, databases, files, filegroups, and recovery models for operational requirements
• Implement least-privilege access using logins, users, database roles, schemas, and SQL Server permissions
• Design and test full, differential, transaction log, copy-only, and point-in-time restore procedures
• Automate maintenance and operational checks with SQL Server Agent jobs, schedules, alerts, and operators
• Diagnose blocking, deadlocks, waits, and resource bottlenecks using DMVs, activity monitoring, and Extended Events
• Tune database performance through index analysis, statistics maintenance, execution-plan interpretation, and Query Store evidence
• Evaluate SQL Server high-availability and disaster-recovery options using RPO, RTO, failover, and operational trade-offs
• Produce a SQL Server administration runbook with monitoring queries, recovery steps, job ownership, and escalation criteria

Benefits of attending

Target competencies

Who should attend

• SQL Server Database Administrators — who need to operate, secure, recover, and tune production SQL Server environments
• Systems Administrators — who support Windows Server and need practical responsibility for SQL Server instances
• Database Developers — who must diagnose database-side performance issues and deploy changes safely
• Infrastructure Engineers — who plan storage, resilience, monitoring, and capacity for SQL Server workloads
• Data Engineers — who maintain SQL Server-based data pipelines and need reliable scheduling and recovery controls
• IT Operations Managers — who need to standardise database maintenance, incident response, and service reporting

Requirements and prerequisites

Participants should have practical experience using Windows Server and SQL Server Management Studio, including connecting to an instance, running basic Transact-SQL SELECT statements, and navigating database objects. Familiarity with tables, indexes, transactions, user accounts, and the difference between full and transaction log backups is assumed. Participants should also understand basic command-line or PowerShell concepts, although scripting expertise is not required. Prior DBA job experience is helpful but not essential. This is not a beginner SQL course: no prior clustering, Always On, or advanced query-tuning experience is required, but complete newcomers to SQL and relational databases should first gain those foundations.

Training methodology

The five-day programme alternates instructor-led technical sessions with hands-on administration labs in SQL Server Management Studio. Participants configure databases, execute backup and restore sequences, inspect Dynamic Management Views, capture evidence with Extended Events, create SQL Server Agent jobs, and resolve staged production incidents. Short case discussions require participants to justify recovery, security, and availability choices against business requirements. Daily exercises feed into an end-of-course application workshop, where participants assemble a practical administration runbook and prioritised action plan for their own SQL Server estate.

Course outline

Day 1: SQL Server architecture, configuration and security

• SQL Server instance architecture and service accounts
• System databases and database lifecycle management
• Data files, log files, filegroups, and autogrowth settings
• Recovery models and transaction log behaviour
• Authentication modes, logins, users, and database roles
• Schema ownership, permission inheritance, and least-privilege design
• SQL Server Management Studio administration and Transact-SQL health checks

Workshop: Configure a new database and role model, then produce a baseline instance configuration and access-control checklist.

Day 2: Backup, restore and disaster recovery operations

• Recovery objectives: RPO, RTO, retention, and recovery scenarios
• Full, differential, transaction log, copy-only, and tail-log backups
• Backup verification, checksums, compression, encryption, and media management
• Restore sequences, recovery states, and point-in-time recovery
• File, filegroup, page, and piecemeal restore considerations
• Database consistency checks with DBCC CHECKDB
• Backup failure diagnosis and restore validation procedures

Workshop: Respond to an accidental data-deletion scenario by restoring a database to a specified point in time and documenting the recovery sequence.

Day 3: Performance monitoring and query troubleshooting

• SQL Server workload baselining and capacity indicators
• Dynamic Management Views for sessions, requests, waits, and I/O analysis
• Blocking chains, deadlock graphs, and transaction troubleshooting
• Wait statistics and resource bottleneck classification
• Execution plans, cardinality estimates, and Query Store analysis
• Index design, fragmentation, rebuilds, reorganisations, and statistics maintenance
• Extended Events sessions for targeted performance investigation

Workshop: Investigate a simulated slow application workload and produce a diagnostic report identifying blocking, waits, and recommended corrective actions.

Day 4: Automation, maintenance and availability design

• SQL Server Agent jobs, schedules, proxies, operators, and alerts
• Maintenance plans versus scripted maintenance routines
• Database Mail and operational notification design
• PowerShell automation with the dbatools module
• Monitoring thresholds for storage, jobs, backups, and database growth
• Always On availability groups, failover cluster instances, and log shipping
• High-availability design trade-offs for planned and unplanned outages

Workshop: Build a scheduled maintenance and alerting workflow, then recommend an availability approach for a business-critical SQL Server service.

Day 5: Operational governance and administration runbook

• SQL Server patching, cumulative updates, and change-control preparation
• Capacity planning for data growth, transaction logs, memory, and storage
• Database deployment controls and post-change validation
• Audit evidence for access reviews, backup tests, and maintenance compliance
• Incident triage, escalation paths, and stakeholder communications
• Administration standards for naming, ownership, documentation, and review cycles
• Runbook structure for repeatable database operations

Workshop: Complete and peer-review a SQL Server administration runbook containing health checks, backup tests, job schedules, incident actions, and a 90-day improvement plan.

Tools & standards covered

Microsoft SQL Server 2022, SQL Server Management Studio (SSMS), SQL Server Agent, dbatools PowerShell module

A typical training day

Live online deliveries follow the same structure in the East Africa Time zone, with shorter screen blocks and longer breaks.

What the fee includes

How you can take this course

Certification

Participants who complete the full five days receive the Skillset Development Certificate of Completion, stating the course title, course code, dates and delivery format — suitable for professional-development records and employer reimbursement.

Frequently asked questions