Audience profile

The primary audience for this course is individuals who administer and maintain SQL Server databases. These individuals perform database administration and maintenance as their primary area of responsibility, or work in environments where databases play a key role in their primary job.

Prerequisites

• M20761 Querying Data with Transact SQL.

Course outline

SQL SERVER SECURITY

• Authenticate Connections to SQL Server
• Authorize Logins to Access Databases Across Servers
• Understand Partially Contained Databases

Lab: Authenticating Users

ASSIGNING SERVERS & DATABASE ROLES

• Work with Server Roles and Fixed Database Roles
• Create User-Defined Database Roles

Lab: Assigning Server and Database Roles

 AUTHORIZING USERS TO ACCESS RESOURCES

• Provide User Access to Objects
• Approve Users to Execute Code
• Configure Permissions at the Schema Level

Lab: Authorizing Users to Access Resources

PROTECTING DATA WITH ENCRYPTION AND AUDITING

• Learn the Options for Auditing Data Access in SQL Server
• Implement and Manage SQL Server Audits
• Protect Data with Encryption

Lab: Using Auditing and Encryption

RECOVERY MODELS AND BACKUP STRATEGIES

• Deploy Secure Backup Strategies
• Understand SQL Server Transaction Login
• Plan a SQL Server Backup Strategy

Lab: Understanding SQL Server Recovery Models

BACKING UP SQL SERVER DATABASES

• Back up Databases and Transaction Logs
• Manage Database Backups
• Deploy Different Backup Options

Lab: Backing up SQL Server Databases

 RESTORING SQL SERVER 2016 DATABASES

• Learn About the Restore Process
• Work with Point-in-Time Recovery
• Restore System Databases and Individual Files

Lab: Restoring SQL Server User Databases

AUTOMATING SQL SERVER MANAGEMENT

• Automate SQL Server Management
• Explore the SQL Server Agent
• Manage SQL Server Agent Jobs
• Understand Multi-Server Management

Lab: Automating SQL Server Management

CONFIGURING SECURITY FOR SQL SERVER AGENT

• Maintain SQL Server Agent Security
• Configure Credentials and Proxy Accounts

Lab: Configuring Security for SQL Server Agent

MONITORING SQL SERVER WITH ALERTS & NOTIFICATIONS

• Create Settings for Database Mail
• Monitor SQL Server Errors
• Work with Operators, Alerts and Notifications
• Learn How Alerts Work in Azure SQL Database

Lab: Monitoring SQL Server with Alerts and Notifications

INTRODUCTION TO MANAGING SQL SERVER BY USING POWERSHELL

• Configure and Administer SQL Server Using PowerShell
• Maintain the SQL Server Environment Using PowerShell
• Manage Azure SQL Server Databases Using PowerShell

Lab: Administering SQL Server Using PowerShell

TRACING ACCESS TO SQL SERVER WITH EXTENDED EVENTS

• Capture Activity Using SQL Server Profiler
• Improve Performance with the Database Engine Tuning Advisor
• Work with Tracing Options
• Perform Distributed Replays
• Monitor Locks

Lab: Tracing Access to SQL Server

MONITORING SQL SERVER

• Oversee SQL Server Activity
• Capture and Manage Performance Data
• Analyze Collected Performance Data
• Assess SQL Server Utility

Lab: Monitoring SQL Server

TROUBLESHOOTING SQL SERVER

• Learn the SQL Server Troubleshooting Methodology
• Fix Service Related Issues
• Resolve Login and Connectivity Issues
• Troubleshoot Other Common Issues

Lab: Troubleshooting Common Issues

IMPORTING AND EXPORTING DATA

• Transfer Data to and from SQL Server
• Import and Export Table Data
• Apply BCP and BULK INSERT to Import Data
• Deploy and Upgrade Data-Tier Applications

Lab: Importing and Exporting Data