Microsoft - Administering a SQL Database
Infrastructure
Code: OD20764
URL: View Online
In this course, professionals who administer and maintain SQL Server databases and who develop applications
that deliver content from SQL Server databases will gain the knowledge and skills to administer a SQL server
database infrastructure.
This course incorporates material from the Official Microsoft Learning Product 20764: Administering a SQL
Database Infrastructure and can assist you in preparing for the 70-764: Administering a SQL Database
Infrastructure exam.
Skills Gained
Authenticate and authorize users.
Assign server and database roles.
Authorize users to access resource.
Protect data with encryption and auditing.
Recovery models and backup strategies.
Back up SQL Server databases.
Restore SQL Server databases.
Automate database management.
Configure security for the SQL Server agent.
Manage alerts and notifications.
Manage SQL Server using PowerShell.
Trace access to SQL Server.
Monitor a SQL Server infrastructure.
Troubleshoot a SQL Server infrastructure.
Import and export data.
Who Can Benefit
Individuals who administer and maintain SQL Server databases, performing database administration and
maintenance as their primary area of responsibility, or who work in environments where databases play a
key role in their primary job.
Individuals who develop applications that deliver content from SQL Server databases.
Prerequisites
Basic knowledge of the Microsoft Windows operating system and its core functionality.
Working knowledge of Transact-SQL.
Working knowledge of relational databases.
Some experience with database design.
Course Details
1. SQL Server Securit y
Authenticating connections to SQL Server
Authorizing logins to access databases
Authorization across servers
Partially contained databases
2. Assigning Server and Dat abase Roles
Working with server roles
Working with fixed database roles
Creating user-defined database roles
3. Aut horizing Users t o Access Resources
Authorizing user access to objects
Authorizing users to execute code
Configuring permissions at the schema level
4. Prot ect ing Dat a wit h Encrypt ion and Audit ing
Options for auditing data access in SQL Server
Implementing SQL Server audit
Managing SQL Server audit
Protecting data with encryption
5. Recovery Models and Backup St rat egies
Understanding Backup strategies
SQL Server transaction login
Planning a SQL Server backup strategy
6. Backup of SQL Server Dat abases
Backing up databases and transaction logs
Managing database backups
Working with backup options
7. Rest oring SQL Server 2016 Dat abases
The restore process
Restoring databases
Working with point-in-time recovery
Advanced Restore Scenarios
8. Aut omat ing SQL Server Management
Working with SQL Server agent
Managing SQL Server agent jobs
Multiserver management
9. Configuring Security for SQL Server Agent
SQL Server Agent Security
Configuring credentials
Configuring proxy accounts
10. Monit oring SQL Server wit h Alert s and Not ificat ions
Configuring database mail
Monitoring SQL Server errors
Configuring operators, alerts and notifications
Alerts in Azure SQL database
11. Int roduct ion t o Managing SQL Server by Using PowerShell
Configure SQL Server using PowerShell
Administer and Maintain SQL Server using PowerShell
Managing Azure SQL Server databases using PowerShell
12. Tracing Access t o SQL Server wit h Ext ended Event s
Extended Events Core Concepts
Working with Extended Events
13. Monit oring SQL Server
Monitoring activity
Capturing and managing performance data
Analyzing collected performance data
SQL Server utility
14. Troubleshoot ing SQL Server
SQL Server troubleshooting methodology
Resolving service related issues
Resolving login and connectivity issues
15. Import ing and Export ing Dat a
Transferring data to/from SQL Server
Importing and exporting table data
Using BCP and BULK INSERT to import data
Deploying and upgrading data-tier applications
Lab 1: Authenticating Users
Lab 2: Assigning Server and Database Roles
Lab 3: Authorizing Users to Access Resources
Lab 4: Using Auditing and Encryption
Lab 5: Understanding SQL Server Recovery Models
Lab 6: Backing Up SQL Server Databases
Lab 7: Restoring SQL Server User Databases
Lab 8: Automating SQL Server Management
Lab 9: Configuring Security for SQL Server Agent
Lab 10: Monitoring SQL Server with Alerts and Notifications
Lab 11: Using PowerShell to Manage SQL Server
Lab 12: Extended Events
Lab 13: Monitoring SQL Server
Lab 14: Troubleshooting Common Issues
Lab 15: Importing and Exporting Data
ExitC ertified® C orporation and iMVP ® are registered tradem arks of ExitC ertified ULC and Generated Aug 7 10:01:14 UTC 2018
ExitC ertified C orporation and Tech Data C orporation, respectively
C opyright ©2018 Tech Data C orporation and ExitC ertified ULC & ExitC ertified C orporation.
All Rights Reserved.
