Administering a SQL Database Infrastructure (M20764)

1. SQL Server Security

• Authenticating connections to SQL Server
• Authorizing logins to connect to databases
• Authorization across servers
• Partially contained databases

2. Assigning Server and Database Roles

• Working with server roles
• Working with fixed database roles
• Assigning User-Defined Database Roles

3. Authorizing Users to Access Resources

• Authorizing user access to objects
• Authorizing users to execute code
• Configuring permissions at the schema level

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

5. Recovery Models and Backup Strategies

• Understanding Backup strategies
• SQL Server transaction login
• Planning a backup strategy

6. Backup of SQL Server Databases

• Backing up databases and transaction logs
• Managing database backups
• Working with backup options

7. Restoring SQL Server 2016 Databases

• Understanding the Restore Process
• Restoring databases
• Point-in-time recovery
• Advanced Restore Scenarios
• Performing a Piecemeal Restore

8. Automating SQL Server Management

• Automating SQL Server management
• Working with SQL Server agent
• Managing SQL Server agent jobs
• Multiserver management

9. Configuring Security for SQL Server Agent

• Understanding SQL Server Agent Security
• Configuring credentials
• Configuring proxy accounts

10. Monitoring SQL Server with Alerts and Notifications

• Monitoring SQL Server errors
• Configuring database mail
• Operators, alerts and notifications
• Alerts in Azure SQL database

11. Introduction to Managing SQL Server by using PowerShell

• Getting Started with Windows PowerShell
• Configure SQL Server using PowerShell
• Administer and Maintain SQL Server using PowerShell
• Managing Azure SQL Server databases using PowerShell

12. Tracing Access to SQL Server with Extended events

• Extended Events Core Concepts
• Working with Extended Events

13. Monitoring SQL Server

• Monitoring activity
• Capturing and managing performance data
• Analyzing collected performance data
• SQL Server utility

14. Troubleshooting SQL Server

• A Trouble Shooting Methodology for SQL Server
• Resolving service related issues
• Resolving login and connectivity issues

15. Importing and Exporting Data

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