MOC 55353 Administering an SQL Database Infrastructure (Workshop)

SQL Server Security

• Authenticating Connections to SQL Server

• Authorizing Logins to Connect to databases

• Authorization Across Servers

• Partially Contained Databases

Assigning Server and Database Roles

• Working with server roles

• Working with Fixed Database Roles

• Assigning User-Defined Database Roles

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

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

Recovery Models and Backup Strategies

• Understanding Backup Strategies

• SQL Server Transaction Logs

• Planning Backup Strategie

Backing Up SQL Server Databases

• Backing Up Databases and Transaction Logs

• Managing Database Backups

• Advanced Database Options

Restoring SQL Server Databases

• Understanding the Restore Process

• Restoring Databases

• Advanced Restore Scenarios

• Point-in-Time Recovery

Automating SQL Server Management

• Automating SQL Server management

• Working with SQL Server Agent

• Managing SQL Server Agent Jobs

• Multi-server Management

Configuring Security for SQL Server Agent

• Understanding SQL Server Agent Security

• Configuring Credentials

• Configuring Proxy Accounts

Monitoring SQL Server with Alerts and Notifications

• Monitoring SQL Server Errors

• Configuring Database Mail

• Operators, Alerts, and Notifications

• Alerts in Azure SQL Database

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

Tracing Access to SQL Server with Extended events

• Extended Events Core Concepts

• Working with Extended Events

Monitoring SQL Server

• Monitoring activity

• Capturing and Managing Performance Data

• Analyzing Collected Performance Data

• SQL Server Utility

Troubleshooting SQL Server

• A Trouble Shooting Methodology for SQL Server

• Resolving Service Related Issues

• Resolving Connectivity and Log-in issues

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