Course Outline

Module 1: SQL Server Security

Authenticating Connections to SQL Server

Authorizing Logins to Connect to databases

Authorization Across Servers

Partially Contained Databases

Create Logins

Create Database Users

Correct Application Login Issues

Configure Security for Restored Databases

Module 2: Assigning Server and Database Roles

Working with server roles

Working with Fixed Database Roles

Assigning User-Defined Database Roles

Assigning Server Roles

Assigning Fixed Database Roles

Assigning User-Defined Database Roles

Verifying Security

Module 3: Authorizing Users to Access Resources

Authorizing User Access to Objects

Authorizing Users to Execute Code

Configuring Permissions at the Schema Level

Granting, Denying, and Revoking Permissions on Objects

Granting EXECUTE Permissions on Code

Granting Permissions at the Schema Level

Module 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

Working with SQL Server Audit

Encrypt a Column as Always Encrypted

Encrypt a Database using TDE

Module 5: Recovery Models and Backup Strategies

Understanding Backup Strategies

SQL Server Transaction Logs

Planning Backup Strategies

Plan a Backup Strategy

Configure Database Recovery Models

Module 6: Backing Up SQL Server Databases

Backing Up Databases and Transaction Logs

Managing Database Backups

Advanced Database Options

Backing Up Databases

Performing Database, Differential, and Transaction Log Backups

Performing a Partial Backup

Module 7: Restoring SQL Server 2016 Databases

Understanding the Restore Process

Restoring Databases

Advanced Restore Scenarios

Point-in-Time Recovery

Restoring a Database Backup

Restring Database, Differential, and Transaction Log Backups

Performing a Piecemeal Restore

Module 8: Automating SQL Server Management

Automating SQL Server management

Working with SQL Server Agent

Managing SQL Server Agent Jobs

Multi-server Management

Create a SQL Server Agent Job

Test a Job

Schedule a Job

Configure Master and Target Servers

Module 9: Configuring Security for SQL Server Agent

Understanding SQL Server Agent Security

Configuring Credentials

Configuring Proxy Accounts

Analyzing Problems in SQL Server Agent

Configuring a Credential

Configuring a Proxy Account

Configuring and testing the Security Context of a Job

Module 10: Monitoring SQL Server with Alerts and Notifications

Monitoring SOL Server Errors

Configuring Database Mail

Operators, Alerts, and Notifications

Alerts in Azure SOL Database

Configuring Database Mail

Configuring Operators

Configuring Alerts and Notifications

Testing Alerts and Notifications

Module 11: Introduction to Managing SQL Server by using PowerShell

Getting Started with Windows PowerShell

Configure SOL Server using PowerShell

Administer and Maintain SOL Server with PowerShell

Managing Azure SOL Databases using PowerShell

Getting Started with PowerShell

Using PowerShell to Change SOL Server settings

Module 12: Tracing Access to SQL Server with Extended events

Extended Events Core Concepts

Working with Extended Events

Using the System_Health Extended Events Session

Tracking Page Splits Using Extended Events

Module 13: Monitoring SQL Server

Monitoring activity

Capturing and Managing Performance Data

Analyzing Collected Performance Data

SQL Server Utility

Module 14: Troubleshooting SQL Server

A Trouble Shooting Methodology for SQL Server

Resolving Service Related Issues

Resolving Connectivity and Log-in issues

Troubleshoot and Resolve an SQL Login Issue

Troubleshoot and Resolve a Service Issue

Troubleshoot and Resolve a Windows Login Issue

Troubleshoot and Resolve a Job Execution Issue

Troubleshoot and Resolve a Performance Issue

Module 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

Import and Excel Data Using the Import Wizard

Import a Delimited Text File Using bcp

Import a Delimited Text File using BULK INSERT

Create and Test an SSIS Package to Extract Data

Deploy a Data-Tier Application

Requirements

  • 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
 35 Hours

Testimonials (5)

Upcoming Courses

Related Categories