Administering Microsoft SQL Server 2019 Databases
Course Outline for 5 Days
Day-1
Introduction: Architecture of SQL Server 2019
This Module explains the basic and required knowledge of architecture, which is must for one
to understand the working and internals of SQL Server.
Certain topics may be covered in the middle of the modules, as per the understanding of the
participants. Also, few topics are in more detail as the part of the curriculum.
Lessons
Concept and architecture of database, datafiles and filegroups
Concept and Architecture of Log file and Recovery Models
Concept and Architecture of Sql Server Memory
Module 1: Introduction to SQL Server 2019 and its Toolset
This module introduces the entire SQL Server platform and its major tools. It covers editions,
versions, basics of network listeners, and concepts of services and service accounts.
Lessons
Introduction to the SQL Server Platform
Working with SQL Server Tools
Configuring SQL Server Services
Module 2: Preparing Systems for SQL Server 2019
This module covers planning for an installation related to SQL Server I/O requirements, 32 bit vs
64 bit, memory configuration options and I/O subsystem pre-installation checks using SQLIOSim
and SQLIO.
Lessons
Overview of SQL Server Architecture
Planning Server Resource Requirements
Pre-installation Testing for SQL Server
Module 3: Installing and Configuring SQL Server 2019
This module details installing and configuring SQL Server.
Lessons
Preparing to Install SQL Server
Installing SQL Server
Upgrading and Automating Installation
Lab : Installing and Configuring SQL Server
Review installation requirements
Install the SQL Server instance
Perform Post-installation Setup and Checks
Configure Server Memory
Module 4: Working with Databases
This module describes how data is stored in databases, how to create databases, and how to
move databases either within a server or between servers.
Lessons
Overview of SQL Server Databases
Working with Files and Filegroups
Moving Database Files
Day-2
Module 5: Understanding SQL Server 2019 Recovery Models
This module describes the concept of the transaction log and SQL Server recovery models. It
introduces the different backup strategies available with SQL Server.
Lessons
Backup Strategies
Understanding SQL Server Transaction Logging
Planning a SQL Server Backup Strategy
Lab : Understanding SQL Server Recovery Models
Plan a backup strategy
Configure Recovery Models
Review recovery models and strategy
After completing this module, students will be able to:
Describe the critical concepts surrounding backup strategies.
Explain the transaction logging capabilities within the SQL Server database engine.
Plan a SQL Server backup strategy.
Module 6: Backup of SQL Server 2019 Databases
This module describes SQL Server Backup and the backup types.
Lessons
Backing up Databases and Transaction Logs
Managing Database Backups
Working with Backup Options
Lab : Backup of SQL Server Databases
Investigate backup compression
Transaction log backup
Differential backup
Copy-only backup
Partial backup
After completing this module, students will be able to:
Back up databases and transaction logs.
Manage database backups.
Work with more advanced backup options.
Module 7: Restoring SQL Server 2019 Databases
This module describes the restoration of databases.
Lessons
Understanding the Restore Process
Restoring Databases
Working with Point-in-time recovery
Restoring System Databases and Individual Files
Lab : Restoring SQL Server 2019 Databases
Determine a restore strategy
Restore the database
Using STANDBY mode
Day-3
Module 8: Authenticating and Authorizing Users
This module covers SQL Server security models, logins and users.
Lessons
Authenticating Connections to SQL Server
Authorizing Logins to Access Databases
Authorization Across Servers
Lab : Authenticating and Authorizing Users
Create Logins
Correct an Application Login Issue
Create Database Users
Correct Access to Restored
After completing this module, students will be able to:
Describe how SQL Server authenticates connections.
Describe how logins are authorized to access databases.
Explain the requirements for authorization across servers.
Module 9: Assigning Server and Database Roles
This module covers fixed server roles, user-defined server roles, fixed database roles and user-
defined database roles.
Lessons
Working with Server Roles
Working with Fixed Database Roles
Creating User-defined Database Roles
Lab : Assigning Server and Database Roles
Assign Server Roles
Assign Fixed Database Roles
Create and Assign User-defined Database Roles
Check Role Assignments
Module 10: Authorizing Users to Access Resources
This module covers permissions and the assignment of permissions.
Lessons
Authorizing User Access to Objects
Authorizing Users to Execute Code
Configuring Permissions at the Schema Level
Lab : Authorizing Users to Access Resources
Assign Schema-level Permissions
Assign Object-level Permissions
Test Permissions
Day-4
Module 11: Automating SQL Server 2019 Management
This module covers SQL Server Agent, jobs and job history.
Lessons
Automating SQL Server Management
Working with SQL Server Agent
Managing SQL Server Agent Jobs
Monitoring SQL Server Errors
Configuring Operators, Alerts and Notifications
Module 12: Performing Ongoing Database Maintenance
This module covers database maintenance plans.
Lessons
Ensuring Database Integrity
Maintaining Indexes
Automating Routine Database Maintenance
Lab : Performing Ongoing Database Maintenance
Check database integrity using DBCC CHECKDB
Correct index fragmentation
Create a database maintenance plan
Investigate table lock performance
Day-5
Module 13: Tracing Access to SQL Server 2019
This module covers SQL Profiler and SQL Trace stored procedures.
Lessons
Capturing Activity using SQL Server Profiler
Improving Performance with the Database Engine Tuning Advisor
Working with Tracing Options
Module 14: Monitoring SQL Server 2019
This module introduces DMVs and the configuration of data collection.
Lessons
Monitoring Activity
Capturing and Managing Performance Data
Analyzing Collected Performance Data
Module 15: Troubleshooting Common SQL Server 2019 Administrative Issues
This module covers common issues that require troubleshooting and gives guidance on where
to start looking for solutions.
Lessons
SQL Server Troubleshooting Methodology
Resolving Service-related Issues
Resolving Concurrency Issues
Resolving Login and Connectivity Issues
Module 16: Introduction to Various High-availability Solutions in SQL Server 2019
(Theory Only)
This module discusses the various technologies supported in SQL Server to implement High
availability solutions. Finally, this module provides a brief overview of existing high-availability
technologies and explains the process and guidelines for determining the appropriate high-
availability technology that can be used to meet business requirements and constraints.
Lessons
Log Shipping
Database Mirroring
Replication
Clustering
AlwaysOn
