SQL Server Overview
This module introduces the main terminology and concepts used in SQL Server administration.
Topics include:
- SQL Server architecture
- SQL Server instances
- Core database administration concepts
- Instance-level and database-level administration
- SQL Server services
- Administration tools
- Fundamental characteristics of a SQL Server environment
This section establishes the foundation for the administrative topics covered throughout the rest of the course.
SQL Server Installation
This module focuses on planning and installing SQL Server instances.
Participants examine:
- Default instances
- Named instances
- Selecting required SQL Server services
- Installation considerations
- Installation options
- Configuring the tempdb database
- Default file locations
- Component placement
The module also covers:
- GUI-based installation
- Unattended installation from the command line
- Automated installation options
- Upgrading existing SQL Server instances
The aim is to help learners understand both how to install SQL Server and how to make appropriate configuration decisions before and during deployment.
Working with SQL Databases
This module examines how SQL Server stores data and how databases are created and maintained.
Topics include:
- SQL Server data storage
- Creating databases
- Configuring databases
- Database management
- Database files
- Data integrity
- Introduction to indexing concepts
- Routine database administration
Participants develop a clearer understanding of the structures and management processes behind SQL Server databases.
Backup and Restore SQL Databases
This module covers the backup and recovery processes required to maintain database availability and protect organisational data.
Topics include:
- Recovery models
- Designing backup strategies
- Database backups
- Backup types
- Restore operations
- Database recovery
- Restoring databases in different recovery scenarios
- Matching backup and restore approaches to operational requirements
Participants learn how to select and apply an appropriate recovery strategy based on business and technical needs.
SQL Server Authentication
This module focuses on managing access to SQL Server.
Topics include:
- Authentication concepts
- SQL Server logins
- Database users
- Instance-level access
- Database-level access
- Relationships between logins and users
- Creating and maintaining user accounts
- Linked Server authentication
Participants examine the complete access process, from authenticating a connection to authorising access to database resources.
Server and Database Roles
This module covers role-based security within SQL Server.
Topics include:
- Server-level roles
- Database-level roles
- Fixed server roles
- Database roles
- Role membership
- Role-based permission management
- Creating roles
- Maintaining roles
Participants learn how roles can be used to create more manageable and consistent security models.
Database Object Security
This module examines access control for database-level objects.
Objects covered include:
- Tables
- Views
- Stored Procedures
- Functions
Topics include:
- Object-level permissions
- Restricting access to database objects
- Granting permissions
- Revoking permissions
- Defining appropriate access levels
- Applying database object security
Data Security
This module explores additional methods for protecting SQL Server data.
Topics include:
- Row-level security
- Column-level security
- Dynamic Data Masking
- Auditing
- Encryption
- Protecting sensitive information
- Monitoring data access
- Supporting security and compliance requirements
Participants learn how data protection can extend beyond user access control to include how data is exposed, monitored, and encrypted.
Automating SQL Server Management
This module focuses on automating recurring SQL Server administration activities.
Topics include:
- Administration automation
- SQL Server Agent
- Automated jobs
- Job scheduling
- Automating routine maintenance tasks
- SQL Server Agent security
- Alert management
- Notifications
- Responding automatically to operational events
- Improving consistency in administrative processes
Participants learn how SQL Server automation features can reduce repetitive manual effort and improve operational reliability.
