Data Private Public

Administering a SQL Database Infrastructure (55353AC)

5 days

Microsoft SQL Server Microsoft Data SQL Databases

Learn to secure, back up, automate, monitor, troubleshoot, and maintain SQL Server and Azure SQL database infrastructure in this five-day instructor-led course.

Talk to a Learning Consultant Register for a Public Class Request a Private Class

Register or Request Training

Course Overview

This five-day instructor-led course teaches database administrators and other IT professionals how to secure, maintain, and monitor SQL Server infrastructure. Learn to configure authentication and authorization, protect data with encryption and auditing, implement backup and recovery strategies, and automate administration with SQL Server Agent and PowerShell.

The course also covers Extended Events, performance monitoring, troubleshooting, data import and export, and selected administrative tasks for Azure SQL. It is primarily intended for professionals who administer or maintain SQL Server databases and is also relevant to developers whose applications use SQL Server data.

Course Benefits

Authenticate logins and authorize users to access databases.
Assign and manage server and database roles.
Protect sensitive data through encryption and SQL Server auditing.
Plan and implement database backup and recovery strategies.
Restore databases, including point-in-time and advanced recovery scenarios.
Automate administrative tasks with SQL Server Agent.
Secure SQL Server Agent by configuring credentials and proxies.
Configure alerts and notifications for proactive monitoring.
Administer SQL Server and Azure SQL with PowerShell.
Capture and analyze activity with Extended Events.
Monitor SQL Server usage and performance.
Troubleshoot service, login, connectivity, and performance issues.
Import and export data and manage data-tier applications.

Delivery Methods

Public Class
Live expert-led online training from anywhere. Guaranteed to run .

Private Class
Delivered for your team at your site or online.

Course Outline

SQL Server Security
1. Authenticate logins and authorize users to access databases
2. Configure SQL Server login options
3. Manage authorization across servers and partially contained databases
Assigning Server and Database Roles
1. Understand fixed and custom database roles
2. Assign and manage server roles
Authorizing Users to Access Resources
1. Use database roles and application roles
2. Implement role-based access to secure data
Protecting Data with Encryption and Auditing
1. Configure SQL Server Audit and manage audit logs
2. Encrypt sensitive data and enforce auditing policies
Recovery Models and Backup Strategies
1. Understand backup strategies for transactional integrity
2. Plan SQL Server recovery using appropriate models
Backing Up SQL Server Databases
1. Perform full and differential database backups
2. Manage backup files and automate backup jobs
Restoring SQL Server Databases
1. Use restore options and perform point-in-time recovery
2. Handle advanced restore scenarios
Automating SQL Server Management
1. Configure SQL Server Agent for scheduled jobs
2. Automate repetitive tasks using job steps and notifications
Configuring Security for SQL Server Agent
1. Set up credentials, proxies, and secure agent access
Monitoring with Alerts and Notifications
1. Create operators and configure alerting
2. Monitor Azure SQL Database events and performance
Managing SQL Server with PowerShell
1. Use PowerShell to configure SQL Server
2. Administer and maintain SQL Server infrastructure
3. Manage Azure SQL with PowerShell
Tracing Access with Extended Events
1. Capture login and query activity
2. Use Extended Events to analyze SQL Server performance
Monitoring SQL Server
1. Monitor usage metrics and server performance
2. Analyze collected data using SQL Server Utility
Troubleshooting SQL Server
1. Resolve service, login, and connectivity issues
2. Apply a troubleshooting methodology for performance
Importing and Exporting Data
1. Import and export data using BCP and BULK INSERT
2. Deploy and manage data-tier applications

Class Materials

Each student receives a comprehensive set of materials, including course notes and all class examples.

Class Prerequisites

Experience in the following is required for this Microsoft SQL Server class:

Basic knowledge of the Microsoft Windows operating system and its core functionality
Working knowledge of Transact-SQL
Working knowledge of relational databases

Experience in the following would be useful for this Microsoft SQL Server class:

Some experience with database design

Prerequisite Courses

Courses that can help you meet these prerequisites:

Have questions about this course?

We can help with curriculum details, delivery options, pricing, or anything else. Reach out and we’ll point you in the right direction.

Talk to a Learning Consultant