Course Overview
This 5-day hands-on workshop is aimed at database administrators who already have basic knowledge of installing and managing MS SQL Server and want to acquire advanced skills in configuration, optimization, and troubleshooting. The course is also designed for developers with SQL knowledge who want to take the next step by learning, monitoring, and optimizing advanced functionalities such as stored procedures, transaction programming, and locking behavior. All topics in the agenda will be implemented through practical exercises in a dedicated virtual environment.
Moyens d'évaluation :
- Quiz pré-formation de vérification des connaissances (si applicable)
- Évaluations formatives pendant la formation, à travers les travaux pratiques réalisés sur les labs à l’issue de chaque module, QCM, mises en situation…
- Complétion par chaque participant d’un questionnaire et/ou questionnaire de positionnement en amont et à l’issue de la formation pour validation de l’acquisition des compétences
Who should attend
The target audience includes database administrators who already have knowledge of installing and managing MS SQL Server and want to build advanced skills in configuration, optimization, and troubleshooting. It also includes developers who want to learn, monitor, and optimize advanced SQL functionalities such as stored procedures, transaction programming, and locking behavior.
Prerequisites
Desired knowledge includes:
- Knowledge of MS SQL Server administration
- Knowledge of relational databases
- Knowledge of the T-SQL language
Course Objectives
At the end of this course, you should be able to:
- Configure and manage the MS SQL Server, including server services, storage concepts, memory, processor configurations, and TempDB setup.
- Understand and manage the architecture of databases, including data and log files, file groups, pages, and indexes.
- Implement advanced SQL security measures, including login and user management, encryption (Always Encrypted), Dynamic Data Masking, and auditing.
- Analyze and optimize SQL query performance, including understanding execution plans, using the Query Store, and applying auto-tuning techniques.
- Manage and maintain SQL Server databases, including strategies for indexing, statistics maintenance, automated jobs, and performance monitoring.
- Identify and troubleshoot performance bottlenecks, utilizing tools like Perfmon, Profiler, Extended Events, and DMVs to analyze and improve system performance.
- Implement and monitor advanced SQL features, such as stored procedures, transaction programming, isolation levels, and in-memory OLTP databases.
Course Content
Architecture of MS SQL Server
- Connection protocols
- Layers of the database engine
- Query life cycle
SQL Server configuration
- Configuration of database services
- Storage concepts
- Memory configuration
- Processor configuration
- Additional server configurations
- Configuration manager for the interface
- TempDB configuration
Database Architecture
- Database components (Data and Log Files, File Groups, Extents, Pages)
- Page structure and page types
- Handling database files (file configuration, shrink file, etc.)
Indexes and Statistics
- Types of indexes
- Advantages and disadvantages of indexes
- Column-stored indexes
- Statistics and filtered statistics
Security
- Permission concepts in SQL Server
- Creating logins, users, roles, and schemas
- Transferring logins
- Working with instances
- Always Encrypted
- Code and data encryption
- Dynamic data masking
- Server and database auditing
- Password protection and backup encryption
- SQL injection
- Security for SSRS and SSAS
SQL Server Maintenance
- Maintenance strategies for indexes, statistics, and databases
- Automating maintenance jobs
- SSIS DB as storage for maintenance jobs
Identifying Performance Bottlenecks
- Causes of performance bottlenecks
- Strategies for identifying performance bottlenecks
- Using Perfmon for performance parameter analysis
- Overview of key performance parameters
- Event monitoring with Profiler and Extended Events
- Monitoring with DMVs and DBCC commands
- Data Collector
Querying and Execution Plans
- Query processing architecture
- Understanding and interpreting execution plans
- Using the Database Tuning Advisor to improve query performance
- Utilizing the Query Store
- Auto Tuning
SQL Optimization
- Optimizing SQL
- 10 tips for performant SQL
- Locking Behavior of Queries
- Transactions and isolation levels
- Identifying deadlocks
- Influencing execution plans with hints
- Procedures and materialized views
- Using CLRs
Allemand
Allemagne
Moyens Pédagogiques :