SQL Server 2016: Administer a Database Infrastructure

Learn how to administer SQL Server 2016, and prepare for the Microsoft MCSA Administering a SQL Database Infrastructure (70-764) certification exam.

SQL Server comes with many features for monitoring, securing, optimizing, and supporting your database infrastructure. Learn how to use these features to administer your SQL Server instances, and prepare for the Microsoft MCSA Administering a SQL Database Infrastructure (70-764) certification exam.

Adam Wilbert covers four main skill areas required of SQL Server 2016 administrators: configuring access to data, managing backups, monitoring performance, and implementing the high-availability features of the product. Learn about data encryption, access control, role-level security, and dynamic data masking. Use SQL Server auditing to gain insights into the health and performance of your system, and determine upgrade paths. Discover how to back up SQL Server and perform full or partial restores, and monitor activity. Explore indexing and query execution plan management. Plus, learn to configure availability groups to mitigate hardware failure.

Introduction

• Welcome
• Use the exercise files
• What you should know
• Install the sample database

1. Configure Encryption

• Encryption overview
• Implement cell-level encryption
• Configure transparent data encryption
• Implement Always Encrypted
• Implement backup encryption
• Configure encryption for connections

2. Configure Data Access and Permissions

• Create and maintain users
• Manage database object permissions
• Create and maintain custom roles
• User options for Azure SQL Database
• Configure row-level security
• Configure dynamic data masking

3. Configure Auditing

• SQL Server audits
• Create an audit on SQL Server
• Query the SQL Server audit log
• Configure an Azure SQL Database audit
• Analyze logs from Azure SQL Database

4. Develop a Backup Strategy

• Back up very large databases
• Configure database recovery models
• Manage transaction log backups
• Configure backup automation
• Back up databases to Azure

5. Restore Databases

• Automate and test restores
• Perform page recovery
• Restore file groups
• Perform piecemeal restores

6. Manage Database Integrity

• Identify database corruption
• Implement database consistency checks
• Recover from database corruption

7. Monitor Database Activity

• Monitor current sessions
• Identify causes of blocking activity
• Find sessions that use tempdb resources
• Configure the data collector

8. Monitor Queries

• Manage the Query Store
• Configure trace events
• Identify problematic execution plans
• Server health and extended events

9. Manage Indexes

• Identify and repair index fragmentation
• Identify and create missing indexes
• Identify and drop underutilized indexes
• Manage existing columnstore indexes

10. Manage Statistics

• Statistics objects
• Identify and correct outdated statistics
• Implement auto update statistics
• Implement statistics for large tables

11. Monitor SQL Server Instances

• Configure database mail
• Create and manage operators
• Create and manage SQL Server Agent alerts
• Define failure actions
• Identify available space on data volumes
• Introduction to Policy-Based Management

12. Implement Log Shipping

• Log shipping overview
• Configure log shipping

13. Implement Always On Availability Groups

• Configure Windows clustering
• Create an availability group
• Configure read-only routing
• Manage failover
• Create distributed availability groups

Conclusion

• Next steps