1. Architecture and configuration
SQL Server architecture provides the basis for later administrative tasks, so the focus is placed on components, editions, and version differences. Participants also work through instance installation, database file layout, system directories, and the use of SSMS, SQLCMD, and Configuration Manager.
- SQL Server architecture – components, versions and editions,
- installation and configuration of the instance,
- database configuration: data and log files, system directories,
- administrative tools: SSMS, SQLCMD, Configuration Manager.
2. Security and access management
Access control in SQL Server combines logins, users, roles, and schemas into a structured security model. This part addresses permission design, encryption mechanisms, and auditing methods used to monitor access and trace user activity in the database environment.
- security model: logins, users, roles, schemas,
- permissions and access control,
- encryption, access auditing and activity tracking.
3. Backups and recovery
Effective recovery depends on matching backup types to business and technical requirements. Full, differential, and transaction log backups are organized into practical schedules, and the recovery process is tested against emergency scenarios to confirm that databases can be restored reliably.
- backup strategies: full, differential, transaction log,
- planning backup schedules,
- database recovery: emergency scenarios, recovery tests.
4. Instance Management and Automation
Automating repetitive administration starts with SQL Server Agent jobs and continues with their scheduling and control. The section explains how to configure alerts, notifications, and conditional logic so that maintenance routines and recurring tasks run in a predictable way.
- SQL Server Agent – creating and managing jobs,
- alerts, notifications and conditional logic,
- maintenance schedules and automatic tasks.
5. Monitoring and performance
Server performance is assessed through Activity Monitor, DMVs, and Performance Monitor, with emphasis on reading operational signals correctly. Query Store and execution plan analysis are then linked with index and statistics management to identify and remove common bottlenecks.
- monitoring of server operation – Activity Monitor, DMVs, Performance Monitor,
- Query Store and analysis of query plans,
- indexes and statistics – creation, optimization, rebuild.
6. Environment maintenance and best practices
Keeping a SQL Server environment stable requires regular work on fragmentation, data reorganization, and version management. Attention is also given to error logs, diagnostic records, and administrative tools such as RedGate or dbForge that support everyday maintenance tasks.
- data fragmentation and reorganization,
- management of SQL Server updates and versions,
- error logs, registers and diagnostics,
- tools supporting the administrator's work (RedGate, dbForge, others).
7. Practical scenarios and adaptation to the environment
Real-world incidents are used to work through typical failures and urgent operational issues in SQL Server environments. The section also includes verification of configuration choices in participants’ own setups, followed by consultations focused on concrete cases and corrective options.
- typical problems and emergency scenarios,
- verification of the configuration in the participants’ environment,
- consultations and discussion on specific cases.