Master administration, security, performance, and high availability with SQL Server 2022
This comprehensive SQL Server administration course is designed to familiarize you with all aspects of the Microsoft SQL Server database management system. You'll start with installation, choosing installation options and automating your installations. Then you'll learn how to use the various tools (SQL Server Management Studio, Configuration Manager, Transact-SQL). You'll gain a better understanding of database management, security, backup and restore, and database messaging configuration. This training will also enable you to monitor your servers and guide you through high-availability options. You'll also be in a better position to decide on the version of your next SQL migration.
Is it for you ?
This course is suitable for professionals who consider themselves beginners or intermediates and who wish to acquire general expertise in SQL Server database management or for professionals who need to be updated on the latest developments with SQL Server 2022, database administrators, developers, analysts, technicians, operations or network managers, system engineers, professionals in a development, database or network position, as well as Web specialists.
Prerequisites
We recommend one of the following prerequisites: Knowledge of basic MS SQL syntax (course BD102); Knowledge of SQL Server usage, database manipulation and SQL language; Basic knowledge of Windows administration; Experience as a database developer.
What You'll Walk Away With
- ✓ Install, configure, and optimize SQL Server 2022 for high performance and reliability
- ✓ Manage databases, automate administrative tasks, and prepare for cloud migration
- ✓ Secure access with roles, permissions, auditing, and data protection techniques
- ✓ Implement backup and recovery strategies to ensure data continuity
- ✓ Monitor and tune performance using Query Store, XEvents, indexing, and execution plans
Training content
1 SQL Server Overview and Installation
- Overview and architecture of Microsoft MS SQL Server.
- What's new in SQL 2022.
- Important pre-installation information.
- Planning the installation of MS SQL Server 2022.
- SQL Server configuration and optimization
- SQL Server 2022 configuration and optimization.
2 Configuring system and user databases.
- Adjusting efficient resources for our server.
- maxDOP and cost threshold for parallelism.
- Optimizing tempDB.
- Data files and logs (MDF, NDF and LDF).
- Using file groups to boost SQL server performance.
- Optimized memory tables and partitioning.
- Sound transaction log management to avoid filling up our disk.
- The benefits of using the full-text search service.
3 Administration tasks
- DBA tasks.
- Administration tools and SQL Server components.
- Executing and automating administrative and scheduled tasks.
- Migration to MS SQL Server 2022 and managing database compatibility.
- Preparing your SQL Servers for migration to the cloud.
4 Security and encryption
- How to manage access, security and permissions.
- Securing connections and sessions.
- Different authentication modes.
- Roles: server roles, database roles, user-created roles, application roles.
- Assigning privileges (Grant, Revoke, Deny).
- Database auditing and the creation of auditable events.
- Use of Dynamic Data Masking.
5 Backups and restores
- Different types of backup (full, differential, transaction log).
- Different recovery modes.
- Creating a database backup and restore strategy.
- Simulation of a major data loss incident.
- How to restore without data loss.
- Optimizing storage with backup compression.
6 Monitoring and performance
- Setting up a maintenance plan using best practices.
- Using eXtended Events (XEvents) to track usage against its predecessor the profiler.
- Use the Query Store to monitor server performance.
- Understand and manage your scheduled tasks securely.
- Manage and configure alerts and database mail.
- Monitor and optimize performance and activity.
- Reading the execution plan.
- Using the performance analyzer with perfmon.
- Defining different index types.
- Managing and optimizing indexes and statistics.
7 Complementary tools & high availability
- Implementing replication.
- Setting up your databases in high-availability mode with AlwaysOn.
- Overview of an integration project (SSIS).
📌 Practical information
Our training sessions are offered in Montreal or Quebec City, in person or in a virtual classroom. Dates and locations are specified when you select your session below. If you have any questions, check out our FAQ.
