SQL Server Administration Part1 Course Syllabus
Full curriculum breakdown — modules, lessons, estimated time, and outcomes.
Overview: This comprehensive SQL Server Administration Part 1 course provides a hands-on foundation for managing SQL Server environments effectively. Designed for beginners, it covers essential administrative tasks including installation, security, backup and restore, automation, and monitoring. With approximately 6 hours of on-demand video and practical demonstrations using both T-SQL and GUI tools, learners gain real-world skills needed to support production databases. The course emphasizes proactive maintenance and automation best practices, preparing students for roles as database administrators or engineers in enterprise environments.
Module 1: Installing & Configuring SQL Server
Estimated time: 0.75 hours
- Planning SQL Server editions and instance types
- Performing silent installations and applying service packs
- Configuring network protocols (TCP/IP, Named Pipes)
- Setting default ports and verifying connectivity
Module 2: Security Fundamentals
Estimated time: 1 hour
- Differentiating between logins and users
- Managing fixed server and database roles
- Enforcing password policies and authentication modes (Windows vs. SQL)
- Implementing contained databases for portability
Module 3: Database Management Basics
Estimated time: 0.75 hours
- Creating databases with optimal filegroup design
- Planning data and log file sizing strategies
- Modifying file properties and autogrowth settings
- Ensuring predictable storage performance
Module 4: Backup & Restore Strategies
Estimated time: 1 hour
- Performing full, differential, and transaction log backups using T-SQL and GUI
- Understanding recovery models (Simple, Full, Bulk-Logged)
- Executing point-in-time restores and tail-log backups
- Validating backup integrity and restore procedures
Module 5: Automating Maintenance Tasks
Estimated time: 0.75 hours
- Scheduling DBCC CHECKDB for database integrity checks
- Configuring index maintenance and statistics updates
- Setting up SQL Server Agent jobs for automation
- Using Ola Hallengren scripts or built-in maintenance plans
Module 6: Monitoring & Performance Basics
Estimated time: 1 hour
- Querying Dynamic Management Views (DMVs) for performance insights
- Analyzing wait statistics and expensive queries
- Monitoring index usage and missing indexes
- Using Performance Monitor and SQL Server logs for proactive alerts
Module 7: High Availability Foundations
Estimated time: 0.75 hours
- Comparing backup strategies with high-availability options
- Introduction to log shipping and database mirroring
- Understanding prerequisites for Always On Availability Groups
- Preparing for advanced HA/DR topics in Part 2
Module 8: Best Practices & Troubleshooting
Estimated time: 0.5 hours
- Implementing baseline configurations and security hardening
- Applying common fixes for known issues
- Using Extended Events for lightweight diagnostics
- Differentiating Extended Events from deprecated SQL Trace
Prerequisites
- Familiarity with basic relational database concepts
- Basic understanding of Windows Server environments
- No prior SQL Server administration experience required
What You'll Be Able to Do After
- Install and configure SQL Server instances securely
- Manage logins, users, roles, and permissions effectively
- Implement reliable backup and restore strategies for disaster recovery
- Automate routine maintenance tasks using SQL Server Agent
- Monitor server health and troubleshoot performance issues using DMVs and logs