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


  • 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


Related Courses

SQL in Microsoft Access

  14 hours

Advanced SQL, Stored Procedures and Triggers for Microsoft SQL Server

  14 hours

SQL in SQL Server

  14 hours

Transact SQL Basic

  14 hours

Transact SQL Advanced

  7 hours

SQL Fundamentals

  14 hours

SQL Advanced in MySQL

  7 hours


  14 hours

ORACLE PL/SQL Fundamentals

  21 hours

ORACLE SQL Fundamentals

  14 hours

Business Intelligence in MS SQL Server 2008

  14 hours

Developing in SQL Server Business Intelligence

  35 hours

Business Intelligence with SSAS

  14 hours

Administration with Powershell

  35 hours