Hands-On SQL Server Database Administration: On-Premises Infrastructure & Operations

Topics covered

• Class 1 (free trial class): Build your company network
• Class objective: Understand what a DBA does, and build the domain controller the whole course runs on. Instructor-led content: Introductions and course goals. What a DBA does: availability, security, recovery, performance and change. Career paths and real job postings. Virtual machines and hypervisors. Domains, Active Directory and DNS. The course lab and why it starts with a domain controller. Hands-on work: Create the DC01 virtual machine and install Windows Server. Set a static IP address, install AD DS and promote DC01 to a domain controller. Student deliverable: A working domain controller. Between-class practice: Following the lab guide, create OUs, named users, the DBA-Admins group and the svc-sqlengine and svc-sqlagent service accounts.
• Class 2: SQL Server architecture and installation in a domain
• Class objective: Install SQL Server on a domain server with domain service accounts, and understand what was installed. Instructor-led content: Database Engine, Agent, Browser and supporting services. Instance vs. database; default vs. named instance; system databases. Edition and feature selection, collation, authentication mode and data directories. Service accounts, ports and protocols. Hands-on work: Create SQL01 and join it to the domain. Install SQL Server Developer Edition with the domain service accounts, then SSMS. Connect, check the version, create a test database and inspect the error log. Student deliverable: Installation evidence pack and environment inventory. Between-class practice: Document the installation choices and one way a production install would differ.

Topics covered

• Class 3: Database files, filegroups and storage layout
• Class objective: Understand how SQL Server stores data and transaction log records. Instructor-led content: MDF, NDF and LDF files; logical vs. physical names. Filegroups and placement. Autogrowth, initial sizing and disk capacity. Data vs. log I/O patterns. Hands-on work: Create a database with multiple files and filegroups. Change growth settings and inspect the metadata. Student deliverable: Storage-layout worksheet. Between-class practice: Explain why data and log files have different I/O patterns.
• Class 4: System databases, tempdb and instance settings
• Class objective: Operate system databases carefully and set the instance up correctly from day one. Instructor-led content: The roles of master, model, msdb and tempdb. tempdb files, growth and restart behavior. Max server memory, MAXDOP and cost threshold for parallelism. Compatibility level, database-scoped options, database states and ownership. Hands-on work: Inspect the system databases and tempdb. Apply safe instance and database settings in the lab. Student deliverable: Instance configuration checklist. Between-class practice: Write a tempdb and memory recommendation for the lab server.

Topics covered

• Class 5: Authentication, logins and users
• Class objective: Implement a clear authentication model using the domain. Instructor-led content: Windows vs. SQL authentication. AD groups as logins. Server logins and database users. SID mapping and orphaned users. Default database and password policy. Hands-on work: Create a login for the DBA-Admins group and users for named accounts. Create and repair an orphaned user. Student deliverable: Login and user mapping lab. Between-class practice: Document least-privilege access for an application account.
• Class 6: Roles, permissions and least privilege
• Class objective: Grant access deliberately without overusing server roles. Instructor-led content: Fixed server and database roles. GRANT, DENY and REVOKE. Schema ownership and object permissions. Separation of duties. The Dedicated Administrator Connection. Hands-on work: Build read-only, writer and executor access models. Test permissions as different domain users. Student deliverable: Security matrix. Between-class practice: Reduce one deliberately over-privileged account.

Topics covered

• Class 7: Recovery models and backup strategy
• Class objective: Design backups from business recovery requirements. Instructor-led content: Simple, full and bulk-logged recovery models. Full, differential and log backups. RPO, RTO and backup frequency. CHECKSUM, compression and storing backups separately. Hands-on work: Establish a log chain. Create full, differential and log backups with T-SQL and SSMS. Student deliverable: Backup strategy. Between-class practice: Check whether the strategy meets the stated RPO.
• Class 8: Automating and validating backups
• Class objective: Turn manual backups into an operational process. Instructor-led content: SQL Server Agent jobs and schedules. Retention and cleanup. Backup history in msdb. RESTORE VERIFYONLY and why only a test restore proves a backup. Hands-on work: Create scheduled backup jobs. Query the backup history and test verification. Student deliverable: Automated backup job plus evidence. Between-class practice: Prepare a daily backup review checklist.

Topics covered

• Class 9: Restore fundamentals and recovery sequences
• Class objective: Restore correctly from common backup chains. Instructor-led content: RESTORE DATABASE and RESTORE LOG; NORECOVERY, RECOVERY and STANDBY. Full, differential and log chains. MOVE for new file locations. The risks of WITH REPLACE. Hands-on work: Restore to a new name and location. Restore a full backup plus a differential and logs. Student deliverable: Restore runbook. Between-class practice: Write a restore sequence in T-SQL without the SSMS dialogs.
• Class 10: Point-in-time recovery and disaster drills
• Class objective: Recover from accidental data loss. Instructor-led content: Tail-log backups. STOPAT. Recovery forks. Restoring system databases. Validating data after recovery. Hands-on work: Simulate an accidental delete and recover to the moment before it. Validate the data and record the recovery time. Student deliverable: Point-in-time recovery report. Between-class practice: Explain what changes if one log backup is missing.

Topics covered

• Class 11: Integrity checks and corruption response
• Class objective: Detect corruption and choose a safe response. Instructor-led content: Page verify and CHECKSUM. DBCC CHECKDB: purpose and scheduling. Physical vs. logical consistency. Why repair is the last resort. Restore-based recovery and escalation. Hands-on work: Run CHECKDB on healthy and prepared corrupt databases. Interpret the corruption output. Student deliverable: Integrity runbook. Between-class practice: Write a corruption escalation note.
• Class 12: Database maintenance and operational hygiene
• Class objective: Build recurring health tasks without blind maintenance. Instructor-led content: Index and statistics maintenance principles. Maintenance plans vs. scripted maintenance. Integrity checks, cleanup and capacity review. Maintenance windows and their impact. Hands-on work: Create a maintenance calendar. Implement one maintenance job you can monitor. Student deliverable: Maintenance calendar. Between-class practice: Explain which tasks must not overlap.

Topics covered

• Class 13: SQL Server Agent jobs, schedules and operators
• Class objective: Automate repeatable administrative tasks and handle failures. Instructor-led content: Job steps, schedules and ownership. Retry and failure flow; output logging. Operators, alerts and Database Mail. Limits of job history. Hands-on work: Create a multi-step job with deliberate failure handling. Configure an email notification path in the lab. Student deliverable: Agent automation pack. Between-class practice: Troubleshoot a failed job.
• Class 14: Administrative scripting with T-SQL and PowerShell
• Class objective: Use repeatable scripts safely. Instructor-led content: Metadata queries and server inventory. Writing scripts that are safe to run twice. The SqlServer PowerShell module and dbatools. Exporting results for audit. Hands-on work: Build an inventory script. Run a PowerShell query against SQL01 and export the results. Student deliverable: Reusable inventory script. Between-class practice: Add comments and safety checks to the script.

Topics covered

• Class 15: Monitoring with logs, DMVs and baselines
• Class objective: Collect evidence before declaring a server healthy or unhealthy. Instructor-led content: The SQL Server error log and Windows Event Viewer. DMVs for sessions, requests, waits, I/O and database state. Performance Monitor counters. Baselines; reactive vs. proactive monitoring. Hands-on work: Capture idle and busy baselines. Find a long-running request you generate. Student deliverable: Baseline worksheet. Between-class practice: Record normal values for the lab.
• Class 16: Extended Events and incident evidence
• Class objective: Collect targeted evidence with low overhead. Instructor-led content: Sessions, events, actions, targets and predicates. The system_health session. Capturing deadlocks, errors and autogrowth. Why Profiler is no longer the default. Hands-on work: Inspect system_health. Create a small session that captures blocking and errors. Student deliverable: Extended Events evidence file. Between-class practice: Decide when a capture should be stopped.

Topics covered

• Class 17: Index fundamentals for administrators
• Class objective: Understand index structures well enough to maintain and troubleshoot them. Instructor-led content: Heaps, clustered and nonclustered indexes. Key lookups and covering indexes. Fragmentation and page density. The write cost of every index. Columnstore indexes in brief. Hands-on work: Create and compare indexes. Observe how the execution plan changes. Student deliverable: Index comparison worksheet. Between-class practice: Weigh the read benefit against the write cost for one index.
• Class 18: Statistics, plans and maintenance decisions
• Class objective: Use statistics and plans to make better maintenance decisions. Instructor-led content: What statistics are; the histogram. Auto-create and auto-update. Estimated vs. actual plans. When to update statistics vs. rebuild or reorganize an index. Hands-on work: Inspect statistics and plans. Change the data distribution and observe the estimates. Student deliverable: Statistics observation report. Between-class practice: Document one maintenance decision and the evidence for it.

Topics covered

• Class 19: Transactions, locks and blocking
• Class objective: Diagnose blocking without treating every lock as a problem. Instructor-led content: ACID in practice. Lock modes and duration. Blocking chains and the head blocker. Isolation levels and row versioning (snapshot and RCSI). Hands-on work: Create a blocking scenario. Identify the head blocker and resolve it safely. Student deliverable: Blocking incident note. Between-class practice: Repeat the diagnosis without killing any session.
• Class 20: Deadlocks and concurrency troubleshooting
• Class objective: Tell deadlocks apart from blocking and respond correctly. Instructor-led content: The deadlock cycle and victim selection. Reading a deadlock graph. Common causes. Working with developers on a fix. Hands-on work: Generate a controlled deadlock. Capture and read its graph. Student deliverable: Deadlock analysis worksheet. Between-class practice: Recommend two ways to remove the deadlock.

Topics covered

• Class 21: HA/DR design and the second server
• Class objective: Choose HA/DR technologies from business requirements, and add the second server to the lab. Instructor-led content: HA vs. DR. RPO, RTO and failure domains. Log shipping, replication, failover cluster instances and availability groups compared. Why backups stay part of every HA/DR design. Hands-on work: Map business scenarios to HA/DR patterns. Create SQL02, join it to the domain and install SQL Server. Student deliverable: HA/DR decision matrix. Between-class practice: Finish SQL02 and defend one HA/DR strategy in writing.
• Class 22: Log shipping and recovery operations
• Class objective: Implement and operate log shipping. Instructor-led content: Primary, secondary and monitor roles. Backup, copy and restore jobs. Standby vs. no-recovery. Failover and failback. Hands-on work: Configure log shipping from SQL01 to SQL02. Observe the lag and perform a controlled role change. Student deliverable: Log shipping runbook. Between-class practice: Document what to monitor and the alert thresholds.

Topics covered

• Class 23: Building a failover cluster and an availability group
• Class objective: Build the components that make an availability group work. Instructor-led content: Windows Server Failover Clustering and quorum. Replicas and synchronization modes. Synchronous vs. asynchronous commit. Automatic vs. manual failover. The listener and client connectivity. Hands-on work: Build a two-node cluster from SQL01 and SQL02 with a file share witness on DC01. Enable Always On and create an availability group with a listener. Student deliverable: Availability group architecture diagram for your own lab. Between-class practice: Prepare a pre-failover checklist.
• Class 24: Operating availability groups and failover
• Class objective: Operate an availability group and verify health after a failover. Instructor-led content: Adding databases and seeding. Planned failover. Readable secondaries and backups on a secondary. Logins and jobs that must exist on both servers. Troubleshooting synchronization. Hands-on work: Perform a planned failover. Verify the state, listener connectivity and jobs. Student deliverable: Failover evidence checklist. Between-class practice: Write a failover communication template.

Topics covered

• Class 25: Encryption, auditing and data protection
• Class objective: Protect data at rest and collect security evidence. Instructor-led content: The encryption hierarchy. TDE and backing up its certificate and keys. Backup encryption. Always Encrypted, row-level security and dynamic data masking: what each protects against. SQL Server Audit. Hands-on work: Enable TDE in the lab and back up the certificate. Create a simple audit and review its events. Student deliverable: Security evidence pack. Between-class practice: Write a warning about what happens if the TDE certificate is lost.
• Class 26: Service accounts, network security and hardening
• Class objective: Reduce the attack surface of the server. Instructor-led content: Least-privileged domain service accounts and group Managed Service Accounts. TCP/IP, ports, firewall and TLS. Disabling unused features and protocols. Hardening sa and authentication. Hands-on work: Review SQL01 against a hardening checklist. Change one safe network or security setting and test it. Student deliverable: Hardening checklist. Between-class practice: Prepare a before-and-after change record.

Topics covered

• Class 27: Patching, cumulative updates and change control
• Class objective: Plan patching as a controlled change. Instructor-led content: Builds, versions and the support lifecycle. Planning cumulative updates and rollback. Restart impact and patching an availability group in the right order. Change tickets and validation. Hands-on work: Create patch plans for a standalone server and for the availability group. Run pre- and post-patch validation queries. Student deliverable: Patch change plan. Between-class practice: Create a rollback decision checklist.
• Class 28: Migration and upgrade planning
• Class objective: Choose and carry out the right migration path. Instructor-led content: Side-by-side vs. in-place upgrades. Migrating with backup and restore, log shipping or an availability group. Compatibility level and validation. Logins, jobs, linked servers and other server-level objects. Hands-on work: Migrate a database with backup and restore. Migrate selected logins and jobs with scripts. Student deliverable: Migration checklist. Between-class practice: List every non-database object a migration must include.

Topics covered

• Class 29: Capacity: memory, CPU, storage and tempdb
• Class objective: Read resource signals in context. Instructor-led content: Max server memory and OS headroom, reviewed under load. CPU saturation and schedulers. Storage latency and log throughput. tempdb contention and capacity. Hands-on work: Capture metrics under a workload. Recommend one justified change. Student deliverable: Capacity review report. Between-class practice: Define the thresholds that should start an investigation.
• Class 30: Operational runbooks, documentation and handover
• Class objective: Turn technical work into repeatable operations. Instructor-led content: Server inventory and ownership. Backup, restore, failover and patch runbooks. Change evidence. What a good handover contains. Hands-on work: Assemble the runbooks from the course. Peer-review them for missing verification steps. Student deliverable: SQL Server operations pack. Between-class practice: Prepare the capstone environment.

Topics covered

• Class 31: Capstone: build and operate a SQL Server environment
• Class objective: Bring together installation, security, backup, monitoring and automation. Instructor-led content: The capstone scenario and acceptance criteria. An independent build window. The instructor observes process and safety. Hands-on work: Build and configure the environment. Implement security, backups, Agent jobs and monitoring. Demonstrate a restore and one incident diagnosis. Student deliverable: Capstone environment plus evidence. Between-class practice: Fix any failed acceptance criteria.
• Class 32: Capstone recovery, incident response and handover
• Class objective: Prove the environment can be recovered and explained. Instructor-led content: A failure or incident is injected. Recovery validation and post-incident review. Operational handover. Interview preparation and next steps, including the Azure and Performance Tuning courses. Hands-on work: Respond using your runbooks. Restore, fail over or troubleshoot as needed. Present the environment and the lessons learned. Student deliverable: Final capstone report and handover. Between-class practice: Final feedback and a recommendation for the next course.