Administering a SQL Database Courseware (55316AC)

This five-day course comprehensively covers the principles of SQL Server security and the assignment of server and database roles. It gives students an in-depth understanding of the SQL Server security models, the process of authenticating and authorizing logins to connect to databases, and the workings of partially contained databases and cross-server authorization. It demonstrates how to manage user permissions effectively through the use of roles at the server and database levels.

The course also delves into authorizing users to access resources, both server and database roles, and how to manage permissions at various levels within a SQL Server instance. Another significant area of focus is the protection of data through encryption and auditing. Students get exposure to the options for auditing, managing audit features, and implementing data encryption.

Moreover, students learn about recovery models and backup strategies. The course details the use of backup features for databases and transaction logs, the application of different backup strategies, and the restoration of user and system databases. They will also understand how to implement point-in-time recovery.

Automation of SQL Server Management is covered in the course as well, highlighting the use of SQL Server Agent to automate jobs, the configuration of security contexts for jobs, and the implementation of multi-server jobs. Additionally, the course focuses on monitoring SQL Server with alerts and notifications, configuring Database Mail, alerts, and notifications for a SQL Server instance, and the configuration of alerts for Microsoft Azure SQL Database.

It offers an introduction to managing SQL Server using PowerShell, aiming to improve efficiency and reliability by scripting tasks and jobs. Students gain practical experience in working with Windows PowerShell for Microsoft SQL Server and Azure SQL Database.

The course provides a deep dive into tracing access to SQL Server with Extended Events and monitoring performance metrics for SQL Server and Azure SQL Database. The students are acquainted with troubleshooting strategies and usage scenarios for working with Extended Events.

Lastly, students learn the methodology for monitoring databases to proactively deal with potential issues, applying troubleshooting methodology, and resolving service-related issues. The course wraps up with a discussion on importing and exporting data, explaining the tools and techniques for transferring data, and how to use SQL Server's native tools for data import and export.