Complete Microsoft SQL Server Database Administration (DBA) Course

Course Description (for the course page)

Complete Microsoft SQL Server Database Administration (DBA) Course is free to take from start to finish—students get full access to every lesson, lab, and hands-on exercise at no cost.

To earn the credential, students complete the curriculum and pass the Certification Exam. After passing, they can purchase the Professional SQL DBA School Certificate for $49—an official, resume-ready certificate they can use on LinkedIn, job applications, and their professional portfolio.

Curriculum Structure (Job-Ready, Production-Focused)

Recommended structure: 16 sections, ~130–160 lessons, 25–40 labs, downloadable scripts/runbooks/templates, and a final certification exam.

Below is a complete, buildable LearnPress outline (each section includes what to teach + sessions/lessons).

Section 1 — Orientation: DBA Mindset, Tools, and How to Study Like a Pro

Goal: Teach students how DBAs work in the real world (evidence-first, change control, risk management).
Lessons

1. What a SQL Server DBA actually does (daily/weekly/monthly responsibilities)

2. The “DBA workflow”: observe → measure → change → validate → document

3. Environments: Dev/Test/Stage/Prod and why they matter

4. Your lab roadmap (what you will build in this course)

5. Required tools: SSMS, Azure Data Studio, PowerShell, Git basics (for scripts)

6. DBA documentation standards: runbooks, incident notes, change logs

7. The “Top mistakes new DBAs make” (and how to avoid them)

8. Lab: Create your DBA notebook + folder structure (scripts, backups, logs, notes)

Section 2 — Database Fundamentals (RDBMS) for DBAs

Goal: Make students understand databases, not just click buttons.
Lessons

1. Data vs information: how databases support applications

2. Tables, rows, columns, constraints (what DBAs must enforce)

3. Keys: PK/FK/Unique/Check constraints (why they prevent data disasters)

4. Relationships and referential integrity

5. Transactions and ACID in practice (and what breaks it)

6. Normalization (what to normalize, what to denormalize—and when)

7. Index concept introduction (why indexes are “data structures,” not magic)

8. Lab: Design a small schema and enforce constraints properly

Section 3 — T-SQL Foundations (DBA Must-Haves)

Goal: Students become fluent in reading/writing SQL needed for DBA work.
Lessons

1. SELECT fundamentals (projection, filtering, sorting)

2. WHERE, ORDER BY, TOP, DISTINCT, NULL handling

3. GROUP BY, HAVING, aggregate functions

4. Joins (INNER/LEFT/RIGHT/FULL) and common join mistakes

5. Subqueries vs joins (when each is appropriate)

6. INSERT/UPDATE/DELETE and safe patterns

7. Temp tables vs table variables (practical DBA usage)

8. Lab: Build “report queries” against a sample database (AdventureWorks)

Section 4 — Intermediate T-SQL for Real Operations

Goal: Prepare students for troubleshooting and tuning tasks.
Lessons

1. Common table expressions (CTEs)

2. Window functions (ROW_NUMBER, RANK, SUM OVER)

3. CASE expressions and robust business logic

4. Date/time handling pitfalls (GETDATE vs SYSDATETIME, time zones concept)

5. Error handling: TRY/CATCH and defensive scripts

6. Transactions in T-SQL (BEGIN/COMMIT/ROLLBACK patterns)

7. Stored procedures: parameters, output, return codes

8. Lab: Build a stored procedure with validation + error handling

Section 5 — Lab Setup: Build a DBA-Grade Environment

Goal: Students can install SQL Server correctly and repeatably.
Lessons

1. Hardware and sizing basics (CPU, memory, storage tiers)

2. Virtualization options (VMware/VirtualBox/Hyper-V) — choose one

3. Install Windows Server (or Windows 11 Pro lab) and harden basics

4. Service accounts strategy (what to do in lab vs production)

5. Install SQL Server (edition guidance + components)

6. Install SSMS and essential tooling

7. Configure file locations: data/log/tempdb separation (best practice)

8. Lab: Build a “clean install checklist” students can reuse

Section 6 — SQL Server Architecture: How It Really Works

Goal: Teach internals that drive performance and troubleshooting.
Lessons

1. SQL Server process model and services overview

2. Databases vs instances (what lives where)

3. Pages/extents and why they matter to I/O

4. Transaction log internals (log records, VLFs, log truncation)

5. Buffer pool and memory grants (why queries “spill”)

6. tempdb purpose and common bottlenecks

7. System databases: master/model/msdb/tempdb (dos and don’ts)

8. Lab: Inspect internal metadata (DMVs introduction safely)

Section 7 — Core DBA Administration: Configuration That Prevents Outages

Goal: Students learn safe, standard instance configuration.
Lessons

1. Server configuration philosophy: stable defaults + controlled changes

2. MAXDOP and Cost Threshold for Parallelism (how to set correctly)

3. Memory configuration (min/max server memory; OS reserved)

4. File growth strategy (autogrowth sizing, instant file initialization concept)

5. tempdb best practices (file count strategy, growth, contention reduction)

6. SQL Server Agent basics (jobs, operators, schedules)

7. Database options: compatibility level, AUTO_CLOSE, AUTO_SHRINK (what NOT to use)

8. Lab: Apply a “baseline configuration” and document every change

Section 8 — Security & Access Control (Real-World DBA Security)

Goal: Students can secure SQL Server properly and explain decisions.
Lessons

1. Authentication modes (Windows vs Mixed) and when to use each

2. Logins vs users vs roles (server and database level)

3. Principle of least privilege (how to design access)

4. Permissions: GRANT/DENY/REVOKE and auditing access

5. Schema ownership and security boundaries

6. Protecting secrets: credentials, proxies, and avoiding plaintext

7. SQL injection risk awareness (what DBAs should watch for)

8. Lab: Build roles for read-only/reporting/app-service/admin

Section 9 — Backup & Restore Mastery (Your #1 DBA Responsibility)

Goal: Students become confident restoring under pressure.
Lessons

1. Recovery models (Simple/Full/Bulk-Logged) and business impact

2. Backup types: full, differential, log, copy-only

3. Backup compression, checksum, verification, retention strategy

4. Restore patterns: full + diff + logs (log chain integrity)

5. Point-in-time recovery (PITR) and when it’s required

6. Restore to a new server (migration and disaster recovery)

7. Corruption basics and DBCC CHECKDB overview (safely)

8. Lab: Full DR drill — simulate “oops delete” and recover to a time

Section 10 — Maintenance & Automation (SQL Agent + Repeatable Playbooks)

Goal: Students automate the work DBAs do daily.
Lessons

1. Maintenance plans vs script-based maintenance (pros/cons)

2. Index maintenance strategy (rebuild vs reorganize, fragmentation reality)

3. Statistics maintenance and why it matters

4. Integrity checks and scheduling patterns

5. Backup automation with logging and alerting

6. SQL Agent job design: idempotent jobs, retries, notifications

7. Basic PowerShell for DBAs (run scripts, export results, schedule)

8. Lab: Create a full “nightly maintenance” job set with logging

Section 11 — Monitoring & Troubleshooting (Evidence-First DBA)

Goal: Students learn to diagnose issues fast without guessing.
Lessons

1. What to monitor: uptime, error logs, CPU, memory, I/O, waits, sessions

2. Wait stats (how to interpret without overreacting)

3. DMVs every DBA uses (sessions, requests, locks, indexes, stats)

4. Blocking and deadlocks: detection and remediation

5. Extended Events fundamentals (better than Profiler for modern SQL Server)

6. Query Store fundamentals (when it helps, when it misleads)

7. Alerts: severity, error numbers, job failures, disk space, backup failures

8. Lab: Build a “15-minute triage” script pack for incidents

Section 12 — Performance Tuning: Query Plans + Indexing (Job-Ready Skill)

Goal: This is where students become valuable.
Lessons

1. Reading execution plans (operators, estimated vs actual)

2. Common plan killers: implicit conversions, bad cardinality, missing stats

3. Index design: clustered vs nonclustered; key columns vs INCLUDE

4. SARGability and why the optimizer ignores your index

5. Parameter sniffing (what it is and safe mitigation options)

6. Tempdb spills, memory grants, and “why it’s slow sometimes”

7. Concurrency: isolation levels, lock escalation, row versioning concept

8. Lab: Tune 5 slow queries and document “before/after” results

Section 13 — High Availability & Disaster Recovery (HA/DR) You Can Explain

Goal: Students understand options and can implement a lab version.
Lessons

1. HA vs DR (RTO/RPO explained clearly)

2. Always On Availability Groups overview (concepts, replicas, failover)

3. Log shipping (simple DR and why companies still use it)

4. Backup/restore DR patterns vs AG patterns

5. Failover testing discipline (how to test without causing incidents)

6. What DBAs must monitor in HA setups

7. DR documentation: runbooks, contact trees, checklists

8. Lab: Implement a basic HA/DR simulation (even if simplified)

Section 14 — Data Movement & Admin Utilities (DBA Real Tasks)

Goal: Make students operationally useful.
Lessons

1. Import/export with SSMS and pitfalls

2. BCP basics (bulk data movement)

3. Linked Servers overview (risks and safe usage)

4. SQL Server Agent proxies and SSIS overview (admin-level, not dev-heavy)

5. Database mail basics (alerts and reporting)

6. Basic replication awareness (what it is; when DBAs get pulled in)

7. Handling large tables: partitioning concepts (intro)

8. Lab: Move data between databases safely with validation

Section 15 — Compliance, Auditing, and Production Readiness

Goal: Students learn “enterprise behaviors” that get them hired.
Lessons

1. Security auditing basics (who changed what and when)

2. Change management: approvals, rollback plans, maintenance windows

3. Backups compliance: proof, reports, and restore evidence

4. Documentation: build a “DBA handover document”

5. Least privilege review and access recertification

6. Hardening checklist (what to disable, what to restrict)

7. Operational KPIs for DBAs (backup success rate, job success, incident MTTR)

8. Lab: Create a production readiness checklist for a new SQL instance

Section 16 — Capstone: Real-World DBA Scenarios (Portfolio-Ready)

Goal: Students finish with proof of skill, not just videos.
Capstone Labs

1. Build a fresh SQL Server instance from scratch using your install checklist

2. Create a maintenance framework (backups + integrity + stats + index + alerts)

3. Simulate an outage: blocking storm + slow queries + failed job + disk pressure

4. Produce an incident report: cause, fix, prevention, and monitoring plan

5. Perform a point-in-time restore and document steps + evidence

Deliverables students can download/submit:

• DBA baseline configuration report

• Backup/restore evidence log (screenshots + output)

• Performance tuning “before/after” report

• Incident runbook + postmortem template

Certification Exam (After Course Completion)

Placement: After Section 16
Attempts: 5
Passing: 80%
Questions: 50
Time: 75 minutes recommended
Certificate purchase: Unlocked only after passing
Certificate fee: $49 (one-time, after passing)