Overview
Course Instructor: Doug Perez
Course Outline
01. Configuring auditing
- Configure an audit on SQL Server
- Query the SQL Server audit log
- Manage a SQL Server audit
- Configure an Azure SQL database audit
- Analyze audit logs and reports from Azure SQL database
02. Developing a backup strategy
- Configure database recovery models
- Managing transaction log backups
- Back up very large databases
- Configure alerting for failed backups
- Back up databases to Azure
- Configure backup automation
03. Monitoring database activity
- Monitor current sessions
- Identify sessions that cause blocking activity
- Identify sessions that consume tempdb resources
- Configure the data collector
04. Monitoring Queries
- Manage the Query Store
- Configure extended events and trace events
- Identify problematic execution plans
- Troubleshoot server health using extended events
05. Managing indexes
- Identify and repair index fragmentation
- Identify and create missing indexes
- Identify and drop underutilized indexes
- Manage existing columnstore indexes
06. Managing statistics
- Identify and correct outdated statistics
- Implement auto update statistics
- Implement statistics for large tables
07. Monitoring queries
- Create and manage operators
- Create and manage SQL Agent alerts
- Define custom alert actions and failure actions
- Configure database mail
- Configure Policy-Based Management
- Identify available space on data volumes
- Identify the cause of performance degradation
08. Implement log shipping
- Configure log shipping
- Monitor log shipping
09. Implement AlwaysOn availability groups
- Configure Windows clustering
- Create an availability group
- Configure read-only routing
- Manage failover
- Create distributed availability groups
10. Implement failover cluster instances
- Manage shared disks
- Configure cluster shared volumes (CSV)
11. Monitoring database activity
- SQL edition capability map
- Replication definitions
- Publishing industry metaphor concepts
- What can be replicated?
- How replication works
- Replication use cases
- Typical transactional replication use case
12. Replication setup walkthrough
13. Monitoring replication
14. Performance tuning
Skills Learned
After completing this online training course, students will be able to:
- Authenticate and authorize users
- Assign server and database roles
- Authorize users to access resources
- Protect data with encryption and auditing
- Describe recovery models and backup strategies
- Backup SQL Server databases
- Restore SQL Server databases
- Automate database management
- Configure security for the SQL Server agent
- Manage alerts and notifications
- Managing SQL Server using PowerShell
- Trace access to SQL Server
- Monitor a SQL Server infrastructure
- Troubleshoot a SQL Server infrastructure
- Import and export data