Administering a SQL Database Infrastructure

Course Overview

1 - SQL Server Security

• Authenticating Connections to SQL Server
• Authorizing Logins to Connect to databases
• Authorization Across Servers
• Partially Contained Databases

2 - Assigning Server and Database Roles

• Working with server roles
• Working with Fixed Database Roles
• Assigning User-Defined Database Roles

3 - Authorizing Users to Access Resources

• Describe and use server roles to manage server-level security.
• Describe and use fixed database roles.
• Use custom database roles and application roles to manage database-level security.

4 - Protecting Data with Encryption and Auditing

• Options for auditing data access in SQL Server
• Implementing SQL Server Audit
• Managing SQL Server Audit
• Protecting Data with Encryption

5 - Recovery Models and Backup Strategies

• Understanding Backup Strategies
• SQL Server Transaction Logs
• Planning Backup Strategies

6 - Backing Up SQL Server Databases

• Backing Up Databases and Transaction Logs
• Managing Database Backups
• Advanced Database Options

7 - Restoring SQL Server Databases

• Understanding the Restore Process
• Restoring Databases
• Advanced Restore Scenarios
• Point-in-Time Recovery

8 - Automating SQL Server Management

• Automating SQL Server management
• Working with SQL Server Agent
• Managing SQL Server Agent Jobs
• Multi-server Management

9 - Configuring Security for SQL Server Agent

• Understanding SQL Server Agent Security
• Configuring Credentials
• Configuring Proxy Accounts

10 - Monitoring SQL Server with Alerts and Notifications

• Monitoring SQL Server Errors
• Configuring Database Mail
• Operators, Alerts, and Notifications
• Alerts in Azure SQL Database

11 - Introduction to Managing SQL Server by using PowerShell

• Getting Started with Windows PowerShell
• Configure SQL Server using PowerShell
• Administer and Maintain SQL Server with PowerShell
• Managing Azure SQL Databases using PowerShell

12 - Tracing Access to SQL Server with Extended events

• Extended Events Core Concepts
• Working with Extended Events

13 - Monitoring SQL Server

• Monitoring activity
• Capturing and Managing Performance Data
• Analyzing Collected Performance Data
• SQL Server Utility

14 - Troubleshooting SQL Server

• A Trouble Shooting Methodology for SQL Server
• Resolving Service Related Issues
• Resolving Connectivity and Log-in issues

15 - Importing and Exporting Data

• Transferring Data to and from SQL Server
• Importing and Exporting Table Data
• Using bcp and BULK INSERT to Import Data
• Deploying and Upgrading Data-Tier Application

Prerequisites

• Basic knowledge of the Microsoft Windows operating system and its core functionality.
• Working knowledge of Transact-SQL.
• Working knowledge of relational databases.
• Some experience with database design.