SQL Server 2016: Administer a Database Infrastructure

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