This five-day, instructor-led course provides students who administer and maintain SQL Server databases with the knowledge and skills to administer a SQL Server database infrastructure. Additionally, it will be useful to people who develop applications that serve content from SQL Server databases.

Addressed to

  • The primary audience for this course is people 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.


35 hours


  • Face-to-face

  • Virtual live through digital platforms

  • on site

Related Courses

  • 20761-C: Querying Data with Transact-SQL

  • 20762-C: Developing SQL Databases

It includes

  • 35 hours of training

  • Trained instructors

  • Participant manual in digital format

  • Proof of participation


Lesson 1: SQL Server Security

  • Authenticate connections to SQL Server

  • Authorize logins to connect to databases

  • Authorization between servers

  • Partially contained databases

Lab: User Authentication

  • Create logins

  • Create database users

  • Successful app login issues

  • Configure security for restored databases

Lesson 2: Assigning Database and Server Roles

  • Work with server roles

  • Work with fixed database roles

  • Assign user-defined database roles

Lab : Server and Database Role Assignment

  • Assign server roles

  • Assign fixed database roles

  • Assign user-defined database roles

  • Checking security

Lesson 3: Authorizing users to access resources

  • Authorize user access to objects

  • Authorize users to run code

  • Set schema-level permissions

Lab : Authorize Users to Access Resources

  • Granting, denying, and revoking permissions on objects

  • Granting EXECUTE permissions on code

  • Granting permissions at the schema level

Lesson 4: Protecting data with encryption and auditing

  • Options for auditing data access in SQL Server

  • Implementation of SQL Server auditing

  • Manage SQL Server auditing

  • Data protection with encryption

Lab : Using Auditing and Encryption

  • Work with SQL Server auditing

  • Encrypt a column as always encrypted

  • Encrypt a database using TDE

Lesson 5: Recovery Models and Backup Strategies

  • Understanding Backup Strategies

  • SQL Server transaction logs

  • Planning of backup strategies

Lab : Understanding SQL Server Recovery Models

  • Plan a backup strategy

  • Configure database recovery models

Lesson 6: SQL Server Database Backup

  • Backup of databases and transaction logs

  • Manage database backups

  • Advanced database options

Lab: Database Backup

  • Database backup

  • Backing up database, differential, and transaction logs

  • Performing a partial backup

Lesson 7: Restoring SQL Server 2016 Databases

  • Understanding the restore process

  • restore databases

  • Advanced restore scenarios

  • Recovery at a certain time

Lab : Restoring SQL Server Databases

  • Restoring a database backup

  • Restrict database, differential, and transaction log backups

  • Performing a staged restore

Lesson 8: Automating SQL Server Administration

  • SQL Server management automation

  • Work with SQL Server Agent

  • Manage SQL Server Agent Jobs

  • Management of multiple servers

Lab : Automating SQL Server Administration

  • Create a SQL Server Agent job

  • try a job

  • Schedule a job

  • Configure master and destination servers

Lesson 9: Configuring Security for SQL Server Agent

  • Understanding SQL Server Agent Security

  • Configure credentials

  • Configure proxy accounts

Lab : Security Configuration for SQL Server Agent

  • Analyzing problems in SQL Server Agent

  • Set up a credential

  • Set up a proxy account

  • Configure and test the security context of a job

Lesson 10: SQL Server Monitoring with Alerts and Notifications

  • SQL Server error monitoring

  • Configure database mail

  • Operators, alerts and notifications

  • Alerts in Azure SQL Database

Lab : SQL Server Monitoring with Alerts and Notifications

  • Configure database mail

  • Configure operators

  • Set up alerts and notifications

  • Test alerts and notifications

Lesson 11: Introduction to SQL Server Administration Using PowerShell

  • Introduction to Windows PowerShell

  • Configure SQL Server with PowerShell

  • Manage and maintain SQL Server with PowerShell

  • Manage Azure SQL databases with PowerShell

Lab : Using PowerShell to Administer SQL Server

  • Introduction to PowerShell

  • Using PowerShell to change SQL Server settings

Lesson 12: Tracking Access to SQL Server with Extended Events

  • Basics of Extended Events

  • Work with extended events

Lab: Extended Events

  • Using the System_Health Extended Event Session

  • Tracking page splits using extended events

Lesson 13: Monitoring SQL Server

  • tracking activity

  • Performance data capture and management

  • Analysis of collected performance data

  • SQL Server Utility

Lab : SQL Server Monitoring

Lesson 14: Troubleshooting SQL Server

  • A troubleshooting methodology for SQL Server

  • Troubleshooting service-related issues

  • Troubleshoot connectivity and login issues

Lab: Troubleshooting Common Problems

  • 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

Lesson 15: Importing and exporting data

  • Data transfer to and from SQL Server

  • Import and export of table data

  • Using bcp and BULK INSERT to import data

  • Deploying and updating data tier applications

Lab: Data Import and Export

  • Import and data from Excel with 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

Leave a Reply

Your email address will not be published.