55316: Administering a SQL Database Training

This module describes SQL Server security models, logins, users, partially contained databases, and cross-server authorization.

Lessons

Authenticating Connections to SQL Server

Authorizing Logins to Connect to databases

Authorization Across Servers

Partially Contained Databases

Lab 1: SQL Server Security

Authenticating Connections to SQL Server

Authorizing Connections to databases

Authorization across server instances

Authorizing Connections to databases

After completing this module, students will be able to:

SQL Server basic concepts.

SQL Server connection authentication.

User login authorization to databases.

Partially contained databases.

Authorization across servers.

This module explains how to use roles at the server and database level to manage user permissions.

Lessons

Working with Server Roles

Working with Fixed Database Roles

User-Defined Database Roles

Lab 1: Assigning Server and Database Roles

Using Server Roles

Using Database Roles

Using User-defined Database Roles & Application Roles

After completing this module, students will be able to:

SQL Server basic concepts.

SQL Server connection authentication.

User login authorization to databases.

Partially contained databases.

Authorization across servers.

This module explains how to authorize users to access server and database roles. It also describes how to manage permissions at different levels in a SQL Server instance.

Lessons

Authorizing User Access to Objects

Authorizing Users to Execute Code

Configuring Permissions at the Schema Level

Lab 1: Authorizing Users to Access Resources

Assigning Fixed and User-Defined Server Roles

Managing Database Roles and Users

Configure Permissions at the Schema Level

After completing this module, students will be able to:

Authorize user access to objects.

Authorize users to execute code.

Configure permissions at the schema level.

This module describes the available options for auditing and how to manage audit features. It also describes how to configure and implement data encryption.

Lessons

Options for auditing data access in SQL Server

Implementing SQL Server Audit

Managing SQL Server Audit

Protecting Data with Encryption

Lab 1: Using Auditing and Encryption

Auditing with Temporal Tables

Using SQL Server Audit

View Audit Output

Using Dynamic Data Masking

After completing this module, students will be able to:

Describe the options for auditing data access.

Implement SQL Server Audit.

Manage SQL Server Audit.

Describe and implement methods of encrypting data in SQL Server.

Implement encryption

In this module, you will learn how to use the available backup features for databases and transaction logs to create backup strategies.

Lessons

Understanding Backup Strategies

SQL Server Transaction Logs

Planning Backup Strategies

Lab 1: Understanding SQL Server Recovery Models

Backup Databases

Transaction Log Backups

Shrinking a database

After completing this module, students will be able to:

Describe various backup strategies.

Describe how database transaction logs function.

Plan SQL Server backup strategies.

In this module, you will learn how to apply various backup strategies.

Lessons

Backing Up Databases and Transaction Logs

Managing Database Backups

Advanced Database Options

Lab 1: Backing Up Databases

Backing Up Databases

Verifying Backups

Using Advanced Backup Features

After completing this module, students will be able to:

Perform backups of SQL Server databases and transaction logs.

Manage database backups.

Describe advanced backup options.

In this module, you will see how to restore user and system databases and how to implement point-in-time recovery.

Lessons

Understanding the Restore Process

Restoring Databases

Advanced Restore Scenarios

Point-in-Time Recovery

Lab 1: Restoring SQL Server Databases

Determining the order of restores

Restoring databases

Restore encrypted backup

Point-in-Time restore

After completing this module, students will be able to:

Explain the restore process.

Restore databases.

Perform advanced restore operations.

Perform a point-in-time recovery.

This module describes how to use SQL Server Agent to automate jobs, how to configure security contexts for jobs, and how to implement multi-server jobs.

Lessons

Automating SQL Server management

Working with SQL Server Agent

Managing SQL Server Agent Jobs

Multi-server Management

Lab 1: Automating SQL Server Management

Using SQL Server Agent

Scripting SQL Server Agent jobs

Viewing job history

Multimaster management

After completing this module, students will be able to:

Describe methods for automating SQL Server Management.

Configure jobs, job step types, and schedules.

Manage SQL Server Agent jobs.

Configure master and target servers.

This module explains how to configure SQL Server Agent to use a minimal privilege security environment and how to use credentials and proxy accounts to run jobs securely.

Lessons

Understanding SQL Server Agent Security

Configuring Credentials

Configuring Proxy Accounts

Lab 1: Configuring SQL Server Agent

Assigning a security context to job steps

Create credentials

Create a proxy account

After completing this module, students will be able to:

Explain SQL Server Agent security.

Configure credentials.

Configure proxy accounts.

This module covers the configuration of Database Mail, alerts, and notifications for a SQL Server instance, and the configuration of alerts for Microsoft Azure SQL Database.

Lessons

Monitoring SQL Server Errors

Configuring Database Mail

Operators, Alerts, and Notifications

Alerts in Azure SQL Database

Lab 1: Monitoring SQL Server with Alerts and Notifications

Working with Database Engine Error Logs

Configuring Database Mail

Configure Operators and Alerts

Configuring Alerts in Azure SQL Database (Optional)

After completing this module, students will be able to:

Monitor SQL Server errors.

Configure database mail.

Configure operators, alerts, and notifications.

Work with alerts in Azure SQL Database.

This module explains how to use Windows PowerShell with Microsoft SQL Server and Azure SQL Database. It also describes how to improve efficiency and reliability by scripting tasks and jobs.

Lessons

Getting Started with Windows PowerShell

Configure SQL Server using PowerShell

Administer and Maintain SQL Server with PowerShell

Managing Azure SQL Databases using PowerShell

Lab 1: Using PowerShell to Manage SQL Server

Exploring SQL Server Management Objects (SMOs)

Configure database and Instance features with PowerShell

Manage logins and backups with PowerShell

Create an Azure SQL Database with PowerShell

After completing this module, students will be able to:

Describe the benefits of PowerShell and its fundamental concepts.

Configure SQL Server by using PowerShell.

Administer and maintain SQL Server by using PowerShell.

Manage an Azure SQL Database by using PowerShell.

This module explains how to monitor performance metrics for SQL Server and Azure SQL Database. It also describes troubleshooting strategies and usage scenarios for working with Extended Events.

Lessons

Extended Events Core Concepts

Working with Extended Events

Lab 1: Using SQL Server Extended Events

Create Extended Events sessions

Working with Extended Events sessions

After completing this module, students will be able to:

Describe Extended Events core concepts.

Create and query Extended Events sessions.

This module explains how to monitor databases with the goal of proactively dealing with potential issues. It also describes how to use the built-in tools provided to analyze instance and server activity.

Lessons

Monitoring activity

Capturing and Managing Performance Data

Analyzing Collected Performance Data

Lab 1: Monitoring SQL Server

Using Performance Monitor

Configuring Data Collection

Viewing the Reports

After completing this module, students will be able to:

Monitor current activity.

Capture and manage performance data.

Analyze collected performance data.

Configure SQL Server Utility.

This module explains how to resolve common issues that may arise when working with SQL Server systems. It also describes a methodology for resolving general database server issues.

Lessons

Applying a Troubleshooting Methodology

Resolving Service-Related Issues

Resolving Connectivity and Login issues

Lab 1: Troubleshooting SQL Server

Troubleshooting errors

Troubleshooting services

Troubleshooting logins

After completing this module, students will be able to:

Describe a troubleshooting methodology for SQL Server.

Resolve service-related issues.

Resolve login and connectivity issues.

This module explains how to use SQL Server native tools to import and export data to and from SQL Server and Azure SQL Databases.

Lessons

Transferring Data to and from SQL Server

Importing and Exporting Table Data

Using bcp and BULK INSERT to Import Data

Deploying Data-Tier Applications

Lab 1: Importing and Exporting data

Disabling and Enabling Constraints

Using the Import and Export Wizard

Import with bcp and BULK INSERT

Working with DACPACs and BACPACs

After completing this module, students will be able to:

Describe tools and techniques for transferring data.

Import and export table data.

Use bcp and BULK INSERT to import data.

Use data-tier applications to import and export database applications