After completing this 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

This instructor led, online training course is for those who administer and maintain SQL Server databases.

We recommend that students have:

• Basic knowledge of the Microsoft Windows operating system and its core functionality

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

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

Monitoring database activity

• Monitor current sessions
• Identify sessions that cause blocking activity
• Identify sessions that consume tempdb resources
• Configure the data collector

Monitoring Queries

• Manage the Query Store
• Configure extended events and trace events
• Identify problematic execution plans
• Troubleshoot server health using extended events

Managing indexes

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

Managing statistics

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

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

Implement log shipping

• Configure log shipping
• Monitor log shipping

Implement AlwaysOn availability groups

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

Implement failover cluster instances

• Manage shared disks
• Configure cluster shared volumes (CSV)

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

Replication setup walkthrough
Monitoring replication
Performance tuning