Call 0917 798 1811 | Email hello@ivtstechnology.asia
About the Course
Overview
55316A is the Community Courseware equivalent of retired MOC Course 20764A -
Administering a SQL Database Infrastructure. The 55316A five-day instructor-led course will
provide training in how to administer and maintain SQL Server, Azure SQL Database and other
cloud-based SQL Server databases. IT professionals who work in environments where
databases play a key role in their job will find this material useful. By using demonstrations and
hands-on lab exercises, students will learn to carry out these important tasks.
About This Course
This five-day instructor-led course provides students who manage SQL Server and Azure SQL databases
with the knowledge and skills needed to administer a SQL server database infrastructure. The material
will also be useful to individuals who develop applications that deliver content from SQL Server
databases. This material updates and replaces course 20764C.
Audience Profile
The primary audience for this course is individuals who administer and maintain SQL Server databases.
These individuals perform database administration and maintenance as their primary area of
responsibility, or work in environments where databases play a key role in their primary job.
The secondary audiences for this course are individuals who develop applications that deliver content
from SQL Server databases.
At Course Completion
Authenticate and authorize users
Assign server and database roles
Authorize users to access resources
Use encryption and auditing features to protect data
Describe recovery models and backup strategies
Backup and Restore SQL Server databases
Automate database management
Configure security for the SQL Server agent
Manage alerts and notifications
Managing SQL Server using PowerShell
Trace access to SQL Server
Monitor a SQL Server infrastructure
Troubleshoot a SQL Server infrastructure
Import and export data
Prerequisites
Experience using applications on Windows Servers
Experience working with SQL Server or another RDMS
COURSE OUTLINE
Module 1: SQL Server Security
This module describes SQL Server security models, logins, users, partially contained databases, and
cross-server authorization.
Lessons
Authenticating Connections to SQL Server
Authorizing Logins to Connect to databases
Authorization Across Servers
Partially Contained Databases
Lab 1: SQL Server Security
Authenticating Connections to SQL Server
Authorizing Connections to databases
Authorization across server instances
Authorizing Connections to databases
After completing this module, students will be able to:
SQL Server basic concepts.
SQL Server connection authentication.
User login authorization to databases.
Partially contained databases.
Authorization across servers.
Module 2: Assigning Server and Database
This module explains how to use roles at the server and database level to manage user permissions.
Lessons
Lesson 1: Working with Server Roles
Lesson 2: Working with Fixed Database Roles
Lesson 3: User-Defined Database Roles
Lab 1: Assigning Server and Database Roles
Using Server Roles
Using Database Roles
Using User-defined Database Roles & Application Roles
After completing this module, students will be able to:
SQL Server basic concepts.
SQL Server connection authentication.
User login authorization to databases.
Partially contained databases.
Authorization across servers.
Module 3: Authorizing Users to Access Resources
This module explains how to authorize users to access server and database roles. It also describes how
to manage permissions at different levels in a SQL Server instance.
Lessons
Authorizing User Access to Objects
Authorizing Users to Execute Code
Configuring Permissions at the Schema Level
Lab 1: Authorizing Users to Access Resources
Assigning Fixed and User-Defined Server Roles
Managing Database Roles and Users
Configure Permissions at the Schema Level
After completing this module, students will be able to:
Authorize user access to objects.
Authorize users to execute code.
Configure permissions at the schema level.
Module 4: Protecting Data with Encryption and Auditing
This module describes the available options for auditing and how to manage audit features. It also
describes how to configure and implement data encryption.
Lessons
Options for auditing data access in SQL Server
Implementing SQL Server Audit
Managing SQL Server Audit
Protecting Data with Encryption
Lab 1: Using Auditing and Encryption
Auditing with Temporal Tables
Using SQL Server Audit
View Audit Output
Using Dynamic Data Masking
After completing this module, students will be able to:
Describe the options for auditing data access.
Implement SQL Server Audit.
Manage SQL Server Audit.
Describe and implement methods of encrypting data in SQL Server.
Implement encryption
Module 5: Recovery Models and Backup Strategies
In this module, you will learn how to use the available backup features for databases and transaction logs
to create backup strategies.
Lessons
Understanding Backup Strategies
SQL Server Transaction Logs
Planning Backup Strategies
Lab 1: Understanding SQL Server Recovery Models
Backup Databases
Transaction Log Backups
Shrinking a database
After completing this module, students will be able to:
Describe various backup strategies.
Describe how database transaction logs function.
Plan SQL Server backup strategies
Module 6: Backing Up SQL Server Databases
In this module, you will learn how to apply various backup strategies.
Lessons
Backing Up Databases and Transaction Logs
Managing Database Backups
Advanced Database Options
Lab 1: Backing Up Databases
Backing Up Databases
Verifying Backups
Using Advanced Backup Features
After completing this module, students will be able to:
Perform backups of SQL Server databases and transaction logs.
Manage database backups.
Describe advanced backup options.
Module 7: Restoring SQL Server Databases
In this module, you will see how to restore user and system databases and how to implement point-intime
recovery.
Lessons
Understanding the Restore Process
Restoring Databases
Advanced Restore Scenarios
Point-in-Time Recovery
Lab 1: Restoring SQL Server Databases
Determining the order of restores
Restoring databases
Restore encrypted backup
Point-in-Time restore
After completing this module, students will be able to:
Explain the restore process.
Restore databases.
Perform advanced restore operations.
Perform a point-in-time recovery.
Module 8: Automating SQL Server Management
This module describes how to use SQL Server Agent to automate jobs, how to configure security contexts
for jobs, and how to implement multi-server jobs.
Lessons
Automating SQL Server management
Working with SQL Server Agent
Managing SQL Server Agent Jobs
Multi-server Management
Lab 1: Automating SQL Server Management
Using SQL Server Agent
Scripting SQL Server Agent jobs
Viewing job history
Multimaster management
After completing this module, students will be able to:
Describe methods for automating SQL Server Management.
Configure jobs, job step types, and schedules.
Manage SQL Server Agent jobs.
Configure master and target servers.
Module 9: ConNiguring Security for SQL Server Agent
This module explains how to configure SQL Server Agent to use a minimal privilege security environment
and how to use credentials and proxy accounts to run jobs securely.
Lessons
Understanding SQL Server Agent Security
Configuring Credentials
Configuring Proxy Accounts
Lab 1: Configuring SQL Server Agent
Assigning a security context to job steps
Create credentials
Create a proxy account
After completing this module, students will be able to:
Explain SQL Server Agent security.
Configure credentials.
Configure proxy accounts.
Module 10: Monitoring SQL Server with Alerts and NotiNications
This module covers the configuration of Database Mail, alerts, and notifications for a SQL Server
instance, and the configuration of alerts for Microsoft Azure SQL Database.
Lessons
Monitoring SQL Server Errors
Configuring Database Mail
Operators, Alerts, and Notifications
Alerts in Azure SQL Database
Lab 1: Monitoring SQL Server with Alerts and Notifications
Working with Database Engine Error Logs
Configuring Database Mail
Configure Operators and Alerts
Configuring Alerts in Azure SQL Database (Optional)
After completing this module, students will be able to:
Monitor SQL Server errors.
Configure database mail.
Configure operators, alerts, and notifications.
Work with alerts in Azure SQL Database.
Module 11: : Introduction to Managing SQL Server by using PowerShell
This module explains how to use Windows PowerShell with Microsoft SQL Server and Azure SQL
Database. It also describes how to improve efficiency and reliability by scripting tasks and jobs.
Lessons
Getting Started with Windows PowerShell
Configure SQL Server using PowerShell
Administer and Maintain SQL Server with PowerShell
Managing Azure SQL Databases using PowerShell
Lab 1: Using PowerShell to Manage SQL Server
Exploring SQL Server Management Objects (SMOs)
Configure database and Instance features with PowerShell
Manage logins and backups with PowerShell
Create an Azure SQL Database with PowerShell
After completing this module, students will be able to:
Describe the benefits of PowerShell and its fundamental concepts.
Configure SQL Server by using PowerShell.
Administer and maintain SQL Server by using PowerShell.
Manage an Azure SQL Database by using PowerShell.
Module 12: Tracing Access to SQL Server with Extended Events
This module explains how to monitor performance metrics for SQL Server and Azure SQL Database. It
also describes troubleshooting strategies and usage scenarios for working with Extended Events.
Lessons
Extended Events Core Concepts
Working with Extended Events
Lab 1: Using SQL Server Extended Events
Create Extended Events sessions
Working with Extended Events sessions
After completing this module, students will be able to:
Describe Extended Events core concepts.
Create and query Extended Events sessions.
Module 13: Monitoring SQL Server
This module explains how to monitor databases with the goal of proactively dealing with potential issues.
It also describes how to use the built-in tools provided to analyze instance and server activity.
Lessons
Monitoring activity
Capturing and Managing Performance Data
Analyzing Collected Performance Data
Lab 1: Monitoring SQL Server
Using Performance Monitor
Configuring Data Collection
Viewing the Reports
After completing this module, students will be able to:
Monitor current activity.
Capture and manage performance data.
Analyze collected performance data.
Configure SQL Server Utility.
Module 14: Troubleshooting SQL Server
This module explains how to resolve common issues that may arise when working with SQL Server
systems. It also describes a methodology for resolving general database server issues.
Lessons
Applying a Troubleshooting Methodology
Resolving Service-Related Issues
Resolving Connectivity and Login issues
Lab 1: Troubleshooting SQL Server
Troubleshooting errors
Troubleshooting services
Troubleshooting logins
After completing this module, students will be able to:
Describe a troubleshooting methodology for SQL Server.
Resolve service-related issues.
Resolve login and connectivity issues.
Module 15: Importing and Exporting Data
This module explains how to use SQL Server native tools to import and export data to and from SQL
Server and Azure SQL Databases.
Lessons
Transferring Data to and from SQL Server
Importing and Exporting Table Data
Using bcp and BULK INSERT to Import Data
Deploying Data-Tier Applications
Lab 1: Importing and Exporting data
Disabling and Enabling Constraints
Using the Import and Export Wizard
Import with bcp and BULK INSERT
Working with DACPACs and BACPACs
After completing this module, students will be able to:
Describe tools and techniques for transferring data.
Import and export table data.
Use bcp and BULK INSERT to import data.
Use data-tier applications to import and export database applications