Description
Gain the skills to install, configure, secure, monitor, and maintain Microsoft SQL Server databases. Learn to manage storage, automate tasks, implement backup and recovery strategies, and monitor performance. This course focuses on advanced administrative techniques for optimizing SQL Server environments.
Objectifs pédagogiques
- Install and configure Microsoft SQL Server
- Plan and implement backup and recovery strategies
- Continuously monitor database performance and changes
- Restore databases from various backup types
- Configure database file storage
- Automate and schedule administrative tasks
- Manage user permissions and secure data with encryption
Public concerné
Database administrators
IT operations managers
System engineers
Technical administrators working with SQL Server infrastructures
Prérequis
Basic experience with Windows Server administration
Ability to manipulate databases and write SQL queries
Déroulé du programme
1
Module 1: Installing and Configuring SQL Server
- Administrator responsibilities and available tools
- Overview of SQL Server architecture
- Installation methods and configuration (SSMS, SSDT)
- SQL Server Configuration Manager and client utilities
- System vs. user databases; database objects
- Lab: Install SQL Server, connect via SSMS, query system databases.
2
Module 2: Managing Storage and Databases
- Create and manage databases, storage engine overview
- Table and index partitioning: types and schemas
- Database snapshots and file group management
- Bulk inserts and data import (BULK INSERT, BCP)
- Lab: Create and modify databases, filegroups, snapshots, and constraints.
3
Module 3: Security and Encryption
- Connection security, session protection, authentication modes
- Server, database, application, and custom roles
- Privilege delegation, ownership chaining, execution context
- Contained databases and security models
- Encryption architecture: keys, certificates, TDE
- SQL auditing and metadata inspection
- Lab: Implement permissions, manage encryption keys and certificates.
4
Module 4: Task Automation and Agent Configuration
- SQL Server Agent setup, job creation and scheduling
- MSDB database and job execution logs
- Alerts, notifications, operators, Database Mail
- Multi-server job scheduling
- Index maintenance, integrity checks, and log redirection
- Introduction to SQL Server Data Tools (SSDT)
- Lab: Automate administrative jobs and configure monitoring alerts.
5
Module 5: Backup and Restore Strategies
- Backup types and recovery models
- Designing backup plans (full, differential, log, filegroup)
- Backup encryption and security
- Backup via T-SQL, multi-file/multi-family backups
- Restore scenarios: different database, log, page, and master restores
- Accelerated recovery
- Lab: Design and script backup plans; simulate and execute restore operations.
6
Module 6: Monitoring and Performance Optimization
- Detecting and resolving blocking and deadlocks
- Server and database-level configuration
- Resource Governor and workload control
- DDL triggers for change tracking
- Dynamic Management Views (DMVs) and metadata inspection
- SQL diagnostic and performance analysis tools
- Lab: Monitor database changes, use DMVs, resolve deadlocks.
Informations
Durée
5 jour(s)
35h
Tarif
Sur demande
Formations similaires
Autre
Module 11 : Techniques élémentaires de méthodes d’apprentissage
2 jour(s)
Sur demande
Autre
Accueil - Maîtrise des Incivilités et Prévention de l'Agressivité en Milieu Hospitalier - SAU de Cochin - Port Royal
2 jour(s)
Sur demande
Autre
Administering Windows Server Hybrid Core Infrastructure (AZ-800)
4 jour(s)
Sur demande
Autre
Administration and Troubleshooting of Active Directory Domain Services (AD DS)
4 jour(s)
Sur demande