Administering a SQL Database Infrastructure (55353AC)

This course provides an in-depth understanding of SQL Server security, management, and data protection techniques, designed for database administrators and IT professionals who manage SQL Server environments. Covering essential security measures, backup and recovery strategies, automation, monitoring, and troubleshooting, this course equips participants with the skills needed to secure, maintain, and optimize SQL Server databases.

The course begins with SQL Server Security, focusing on authenticating connections, authorizing logins, and managing security across servers. You'll learn about partially contained databases, allowing you to manage security at the database level, independent of the server, enhancing flexibility and control.

In the Assigning Server and Database Roles module, you'll explore how to work with server roles, fixed database roles, and user-defined roles to manage access and permissions. This section provides the foundational knowledge needed to effectively control and delegate administrative tasks within SQL Server.

The Authorizing Users to Access Resources section covers the use of server roles, database roles, and application roles to manage security. You’ll learn how to implement these roles to control access at both the server and database levels, ensuring data integrity and protection.

In Protecting Data with Encryption and Auditing, you’ll explore various methods for auditing data access and implementing encryption to protect sensitive information. Topics include configuring SQL Server Audit, managing audits, and using encryption techniques to secure your data against unauthorized access.

The Recovery Models and Backup Strategies module provides an overview of different backup strategies, transaction logs, and planning effective backup policies. You’ll learn how to ensure data safety and prepare for various recovery scenarios to minimize data loss.

In Backing Up SQL Server Databases, you’ll delve into the process of backing up databases and transaction logs, managing backups, and exploring advanced database options. This section equips you with the skills to implement reliable backup solutions for your SQL Server environment.

The Restoring SQL Server Databases section covers the restore process, including basic restores, advanced scenarios, and point-in-time recovery. You’ll learn to handle different types of restores, ensuring you can recover data effectively in various failure scenarios.

Automating SQL Server Management focuses on automating administrative tasks using SQL Server Agent. You’ll learn to create and manage jobs, set up multi-server management, and use automation to streamline routine maintenance tasks.

In the Configuring Security for SQL Server Agent module, you’ll understand how to secure SQL Server Agent, configure credentials, and set up proxy accounts, ensuring that automated tasks run securely under the correct permissions.

The Monitoring SQL Server with Alerts and Notifications section teaches you how to monitor SQL Server errors, configure Database Mail, and set up alerts and notifications. You’ll also explore setting up alerts in Azure SQL Database to maintain proactive monitoring across different environments.

Introduction to Managing SQL Server by using PowerShell provides a hands-on introduction to using PowerShell for SQL Server management. You’ll learn how to configure, administer, and maintain SQL Server instances and manage Azure SQL Databases using PowerShell scripts.

In Tracing Access to SQL Server with Extended Events, you’ll explore core concepts of Extended Events and how to use them to capture and analyze SQL Server activity. This section helps you monitor performance, troubleshoot issues, and gain insights into server behavior.

The Monitoring SQL Server module covers tools and techniques for monitoring SQL Server activity, capturing performance data, and analyzing collected information. You’ll learn how to use SQL Server Utility and other monitoring tools to optimize database performance.

Troubleshooting SQL Server equips you with a methodology for identifying and resolving common SQL Server issues. Topics include service-related problems, connectivity, and login issues, providing you with the skills to troubleshoot effectively and maintain a reliable SQL Server environment.

The course concludes with Importing and Exporting Data, covering data transfer techniques using tools like bcp and BULK INSERT, and deploying data-tier applications. You’ll learn how to import, export, and upgrade data effectively, supporting your organization’s data management needs.

By the end of this course, you will have gained the skills needed to secure, manage, and troubleshoot SQL Server environments. You’ll be prepared to implement best practices for database security, backup and recovery, automation, and performance monitoring, ensuring the integrity and efficiency of your SQL Server databases.