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.

Course Outline:

1. Authenticating and Authorizing Users

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

2. Assigning Server and Database Roles

• Working with server roles
• Working with fixed database roles
• Creating 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. SQL Server Recovery Models

• Backup strategies
• SQL Server transaction login
• Planning a SQL Server 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 Databases

• The restore process
• Restoring databases
• Working with point-in-time recovery
• Restoring system databases and individual files

8. Automating 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. Monitoring SQL Server with Alerts and Notifications

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

11. Managing SQL Server by using PowerShell

• Configure SQL Server using PowerShell
• Administer SQL Server using PowerShell
• Maintain the SQL Server environment using PowerShell
• Managing Azure SQL Server databases using PowerShell

12. Tracing Access to SQL Server

• Capturing activity using SQL Server profiler
• Improving performance with the database engine tuning advisor
• Working with tracing options
• Distributed replay
• Monitoring locks

13. Monitoring SQL Server

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

14. Troubleshooting SQL Server

• SQL Server troubleshooting methodology
• Resolving service related issues
• Resolving login and connectivity issues
• Troubleshooting common 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