MS 20764C:Administering a SQL Database Infrastructure Training Course

Module 1: SQL Server Security

• Authenticating Connections to SQL Server

• Authorizing Logins to Connect to databases

• Authorization Across Servers

• Partially Contained Databases

• Create Logins

• Create Database Users

• Correct Application Login Issues

• Configure Security for Restored Databases

  Module 2: Assigning Server and Database Roles

• Working with server roles

• Working with Fixed Database Roles

• Assigning User-Defined Database Roles

• Assigning Server Roles

• Assigning Fixed Database Roles

• Assigning User-Defined Database Roles

• Verifying Security

  Module 3: Authorizing Users to Access Resources

• Authorizing User Access to Objects

• Authorizing Users to Execute Code

• Configuring Permissions at the Schema Level

• Granting, Denying, and Revoking Permissions on Objects

• Granting EXECUTE Permissions on Code

• Granting Permissions at the Schema Level

  Module 4: Protecting Data with Encryption and Auditing

• Options for auditing data access in SQL Server

• Implementing SQL Server Audit

• Managing SQL Server Audit

• Protecting Data with Encryption

• Working with SQL Server Audit

• Encrypt a Column as Always Encrypted

• Encrypt a Database using TDE

  Module 5: Recovery Models and Backup Strategies

• Understanding Backup Strategies

• SQL Server Transaction Logs

• Planning Backup Strategies

• Plan a Backup Strategy

• Configure Database Recovery Models

  Module 6: Backing Up SQL Server Databases

• Backing Up Databases and Transaction Logs

• Managing Database Backups

• Advanced Database Options

• Backing Up Databases

• Performing Database, Differential, and Transaction Log Backups

• Performing a Partial Backup

  Module 7: Restoring SQL Server 2016 Databases

• Understanding the Restore Process

• Restoring Databases

• Advanced Restore Scenarios

• Point-in-Time Recovery

• Restoring a Database Backup

• Restring Database, Differential, and Transaction Log Backups

• Performing a Piecemeal Restore

  Module 8: Automating SQL Server Management

• Automating SQL Server management

• Working with SQL Server Agent

• Managing SQL Server Agent Jobs

• Multi-server Management

• Create a SQL Server Agent Job

• Test a Job

• Schedule a Job

• Configure Master and Target Servers

  Module 9: Configuring Security for SQL Server Agent

• Understanding SQL Server Agent Security

• Configuring Credentials

• Configuring Proxy Accounts

• Analyzing Problems in SQL Server Agent

• Configuring a Credential

• Configuring a Proxy Account

• Configuring and testing the Security Context of a Job

  Module 10: Monitoring SQL Server with Alerts and Notifications

• Monitoring SOL Server Errors

• Configuring Database Mail

• Operators, Alerts, and Notifications

• Alerts in Azure SOL Database

• Configuring Database Mail

• Configuring Operators

• Configuring Alerts and Notifications

• Testing Alerts and Notifications

  Module 11: Introduction to Managing SQL Server by using PowerShell

• Getting Started with Windows PowerShell

• Configure SOL Server using PowerShell

• Administer and Maintain SOL Server with PowerShell

• Managing Azure SOL Databases using PowerShell

• Getting Started with PowerShell

• Using PowerShell to Change SOL Server settings

  Module 12: Tracing Access to SQL Server with Extended events

• Extended Events Core Concepts

• Working with Extended Events

• Using the System_Health Extended Events Session

• Tracking Page Splits Using Extended Events

  Module 13: Monitoring SQL Server

• Monitoring activity

• Capturing and Managing Performance Data

• Analyzing Collected Performance Data

• SQL Server Utility

  Module 14: Troubleshooting SQL Server

• A Trouble Shooting Methodology for SQL Server

• Resolving Service Related Issues

• Resolving Connectivity and Log-in issues

• Troubleshoot and Resolve an SQL Login Issue

• Troubleshoot and Resolve a Service Issue

• Troubleshoot and Resolve a Windows Login Issue

• Troubleshoot and Resolve a Job Execution Issue

• Troubleshoot and Resolve a Performance Issue

  Module 15: Importing and Exporting Data

• Transferring Data to and from SQL Server

• Importing and Exporting Table Data

• Using bcp and BULK INSERT to Import Data

• Deploying and Upgrading Data-Tier Application

• Import and Excel Data Using the Import Wizard

• Import a Delimited Text File Using bcp

• Import a Delimited Text File using BULK INSERT

• Create and Test an SSIS Package to Extract Data

• Deploy a Data-Tier Application