Administering a SQL Database Infrastructure (20764) Training Course

Module 1: SQL Server Security

• Authenticating Connections to SQL Server
• Authorizing Logins to Connect to databases
• Authorization Across Servers
• Partially Contained Databases

Lab : Authenticating Users

• 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

Lab : Assigning server and 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

Lab : Authorizing users to access resources

• 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

Lab : Using Auditing and 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

Lab : Understanding SQL Server recovery models

• 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

Lab : Backing Up Databases

• 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

Lab : Restoring SQL Server Databases

• 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

Lab : Automating SQL 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

Lab : Configuring Security for SQL Server Agent

• 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 Notification

• Monitoring SQL Server Errors
• Configuring Database Mail
• Operators, Alerts, and Notifications
• Alerts in Azure SQL Database

Lab : Monitoring SQL Server with Alerts and Notifications

• 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 SQL Server using PowerShell
• Administer and Maintain SQL Server with PowerShell
• Managing Azure SQL Databases using PowerShell

Lab : Using PowerShell to Manage SQL Server

• Getting Started with PowerShell
• Using PowerShell to Change SQL Server settings

Module 12: Tracing Access to SQL Server with Extended events

• Extended Events Core Concepts
• Working with Extended Events

Lab : 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

Lab : Monitoring SQL Server

Module 14: Troubleshooting SQL Server

• A Trouble Shooting Methodology for SQL Server
• Resolving Service Related Issues
• Resolving Connectivity and Log-in issues

Lab : Troubleshooting Common Issues

• Troubleshoot and Resolve a 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

Lab : Importing and Exporting Data

• 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

This course includes:

• Training from a Microsoft Certified Trainer (MCT)
• Computer / Laptop to use for the duration of the course
• Microsoft official courseware
• Online labs (if available)
• Refreshments