In this instructor-led, online training course, students will learn the knowledge and skills to administer a SQL server database infrastructure. Students will be taught how to authenticate and authorize users, protect data with encryption and auditing, backup and restore SQL Server, configure security for the SQL Server agent, and more.
Overview
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
This instructor led, online training course is for those who administer and maintain SQL Server databases.
- Basic knowledge of the Microsoft Windows operating system and its core functionality
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
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
WHO SHOULD ATTEND
This instructor led, online training course is for those who administer and maintain SQL Server databases.
PREREQUISITES
- Basic knowledge of the Microsoft Windows operating system and its core functionality
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
