SQL Server 2022 DBA

SQL Server 2022 DBA Training Curriculum

MS SQLServer 2022

DatabaseAdministration
With AlwaysOn & Clustering
Techniques
Module 1: SQL Server
Architecture
Introduction to SQL Server 2019/2022
✓ Overview on RDBMS and Beyond
Relational
✓ Components & Services of SQL Server
2019/2022
✓ Roles of production DBA
✓ System Databases
✓ Master, Model, MSDB, Tempdb & Resource
SQL Server 2022 Editions & Capacity
Planning
✓ Principal Editions [Enterprise, BI, Standard]
✓ Specialized Editions [Web]
✓ Breadth Editions [Developer, Express]
✓ Hardware Requirements
✓ OS and Software requirements
Pages & Extents
✓ Pages
✓ Extents [Uniform & Mixed]
✓ Database Files o Primary data files
o Secondary data files o Log files
✓ Database File groups [Primary &
Userdefined]
Thread and Task Architecture
✓ Allocating threads to CPU
✓ Affinity Mask
✓ IO and Processor affinity mask

Memory Architecture
✓ 32-bit Vs 64-bit Architecture
✓ Dynamic Memory Management
✓ Effects of min and max server memory
✓ Buffer Management
✓ The Relational Engine
✓ The Command Parser
✓ The Query Optimizer
✓ The Query Executor
✓ Using AWE
✓ Configuring Memory Settings
✓ The Buffer Pool and the Data Cache
✓ Checkpoints
✓ Hot Addition of memory
✓ Difference between Checkpoint & Lazy
writer
T-Log Architecture
✓ Transaction Log Logical Architecture
✓ Transaction Log Physical Architecture
✓ Recovery phases [Analysis, Redo & Undo]
✓ Checkpoint Operation
✓ Write-Ahead Transaction Log
✓ Managing T-log
✓ Truncating and shrinking the log file
✓ Managing T-Log issues by using DBCC
commands
Module 2: Installing,
Upgrading, Configuration,
Managing services and
Migration
SQL server 2019/2022 Installation
✓ Planning the System/Pre-Requisites
✓ Installing SQL server 2019/2022
✓ Installing Analysis Services
✓ Installing & Configuring Reporting Services
✓ Uninstalling SQL server

Upgrading to SQL server 2019/2022
✓ Upgrading the server by applying service
packs
✓ Upgrading the server by applying Hot fixes
✓ In-Place Vs. Side-by-Side upgradations
✓ Pre-Upgrade Checks/pre-requisites
✓ Upgrade advisor
✓ In-Place Upgradation from SQL server
2012/2016 to 2019/2022
✓ Best Practices to follow while upgrading
Managing services
✓ Server/Engine Connectivity issues
✓ Security/Firewall access issues
✓ Starting and Stopping Services through
✓ Configuration manager
✓ Net Command
✓ Management Studio
✓ Start Up parameters
✓ Starting SQL server in single user mode
✓ Starting SQL server with minimal
configuration
Configuring SQL Server 2022
✓ Configuring Network Protocols from SQL
Server configuration manager
✓ Configuring Client Protocols from SQL
Server configuration manager
✓ Dedicated Administrator Connection
✓ Enabling advanced features by using facets
✓ Connecting to DAC
✓ Configuring Database Mail
✓ Configuring Registered servers
✓ Configuring Central Management Servers
[CMS]
✓ Querying data from different servers by
using CMS
✓ Configuring other settings through
SP_Configure
✓ Configuring Server memory settings
✓ Configuring Database Settings
✓ Tempdb configuration 

Migrating SQL server
✓ Side-By- Side Migration Techniques
✓ Difference between in-place & Side by Side
Migration/Upgradation
✓ Advantages/Disadvantages of In-Place to
Side-by-Side
✓ Migrating Databases
1. Migration by using Attach and
Detach Method
2. Migration by using Back and
restore method
✓ Migrating Logins [Fixing Orphaned Users]
✓ Creating and migrating linked servers
✓ Migrating Jobs
✓ Data movement by using Import & Export
wizard
Module 3: Security,
Automation & Monitoring
Automating Administrative Tasks
✓ About SQL server Agent
✓ Creating Jobs, Alerts and Operators
✓ Scheduling the Jobs
✓ Working with Job activity Monitor
✓ Resolving failure Jobs
✓ Configuring Alert system in SQL server
agent
Monitoring SQL Server
✓ The Goal of Monitoring
✓ Choosing the Appropriate Monitoring Tools
✓ Monitoring health status by using server
Dashboard & DMV’s
✓ Monitoring Job activities by job activity
monitor
✓ Monitoring SQL Server process by server
activity monitor
✓ Monitoring SQL Server Error
Logs/Windows by log file viewer

Security
✓ Security Principles & Authentications
✓ Server and Database Roles
✓ User-defined server roles
✓ Server and Database Principles
✓ Server & Database Securable
✓ Creating Logins and mapping Users to
databases
✓ Creating Schemas & credentials
✓ Default Schema for Groups
✓ Enabling contained databases
✓ Creating users for contained databases
✓ Connecting to contained databases from
SSMS
✓ Granting to Object level Permissions
Module 4: Backup & Restore,
High Availability &
Replication
Backup & Restore:
✓ Recovery Models [Simple, Bulk-Logged &
Full]
✓ How Backup Works
✓ Types of backups
1. Full backup
2. Diff backup
3. T-log backup
4. Copy Only
5. Mirror
6. Tail-Log
7. Compressed backups
✓ Restoring Modes [With Recovery, No
Recovery, Read only/Standby]
✓ Disaster Recovery Planning
✓ Performing Restore (point-in-time
recovery)
✓ Partial availability of database.
✓ Database Recovery advisor
✓ Backup strategy: Developing and executing
a Backup Plan
✓ Creating Maintenance Plans

✓ Resolving Backup failures in Real time
scenarios
Log Shipping
✓ Log-Shipping Architecture
✓ Building DRS for log-shipping
✓ Pre-requisites/Log-Shipping Process
✓ Deploying Log Shipping
✓ Working with Log Shipping Monitor
✓ Logs hipping Role changing [Fail-Over]
✓ Removing Log Shipping
✓ Frequently Raised Errors In Log-Shipping
Database Mirroring
✓ Overview of Database Mirroring
✓ Operating Modes in Database Mirroring
✓ Pre-Requisites for Database Mirroring
✓ Deploying Database Mirroring
✓ Fail-Over from Principle to Mirror
✓ Working with Database mirroring monitor
✓ Advantages & Disadvantages of database
mirroring
✓ Database Snapshots
✓ Using Database Snapshots for reporting
purposes.
AlwaysOn Availability Groups
✓ AlwaysOn Overview
✓ Understanding Concepts and Terminology
✓ Availability Modes
✓ Types of fail-overs
✓ Pre-requisites for AlwaysOn configuration
✓ Configuring Availability Groups
✓ Monitoring Availability groups
✓ Add/remove database/replica
✓ Suspend/resume an availability database
✓ Backups on Secondary
✓ AlwaysOn Failover Cluster Instances
✓ Online Operations

Replication
✓ Replication Overview
✓ Replication Models
✓ (snapshot/Transactional/Merge/Peer to
Peer)
✓ Replication agents
✓ Configuring Distributor
✓ Deploying Transactional Replication for
High Availability
✓ Deploying Merge Replication for
Bidirectional
✓ Creating Subscriptions [Homogeneous /
heterogeneous]
✓ Monitoring Replication by using replication
monitor
Module 5: Windows & SQL
Server Clustering
Windows Server 2019 Clustering
✓ What is a cluster and Overview of Windows
cluster
✓ Server cluster technologies
1. Server clusters
2. NLB clusters [Network load balancing]
✓ Basic architecture of server clusters
✓ Networks in clustering [Public & Private]
✓ How cluster works
✓ Health Detection [Looks alive, Is alive]
✓ Introduction to Windows Server 2019
✓ Basic elements of a cluster with single
quorum.
✓ Adding Roles and features by using server
manager
✓ Introduction to fail-over cluster manger
✓ Validating the cluster configuration
✓ Creating cluster through fail-over cluster
manager
✓ Configuring MSDTC as a cluster aware
application

✓ Active – passive Vs Active – Active Clustering
✓ Adding/Evicting Nodes to/from the cluster
✓ Adding volumes to the roles/services in the
cluster
✓ Simulating the failover for the resources
Installing SQL Server 2019 Fail-Over
Clustering
✓ Pre-SQL Server Installation Tasks.
✓ Configure SQL Server–Related Service
Accounts and Service Account Security
✓ Stop Unnecessary Processes or Services
✓ Check for Pending Reboots
✓ Install SQL Server Setup Support Files
✓ SQL Server 2019 Setup
✓ Install the First Node
✓ Add Nodes to the Instance
✓ Perform Post installation Tasks
✓ Verify the Configuration
✓ Set the Preferred Node Order for Failover
✓ Configure a Static TCP/IP Port for the SQL
Server Instance
Administering a SQL Server 2019
Failover Cluster
✓ Install SQL Server Service Packs, Patches,
and Hot fixes
✓ Introducing Failover Cluster Management
✓ Monitoring the Cluster Nodes
✓ Adding volumes to cluster roles
✓ Clustered SQL Server Administration
✓ Fail over resources/roles between the
nodes
✓ Automatic failover & Failback
✓ Destroying a Cluster- Using Failover Cluster
Management
✓ Uninstalling a Failover Clustering Instance

Module 6: Performance
Tuning, Indexing &
Optimizing SQL Server
Optimizing SQL server
✓ Policy based management
✓ Policy based management implementation
✓ Creating Policy & Condition
✓ Evaluating polices
✓ Resource governor
✓ Resource pool & Workloads
✓ Using resource governor from SSMS
✓ Monitoring Resource governor
Indexing
✓ Index Architecture
✓ How to optimally take advantage of indexes
✓ Clustered & Non-Clustered indexes
✓ Covering Index or index with included
column
✓ Column store Index Overview
✓ Column store Index Fundamentals and
Architecture
✓ Creating column store index to improve the
performance
✓ Index Fragmentation
✓ How to determine fragmentation
✓ Creating maintenance plan for
rebuilding/re-organizing indexes

Locking & Concurrency
✓ Isolation Levels in SQL Server
✓ Locking in SQL Server
✓ Resolving concurrency effects in SQL Server
✓ Lock modes – Shared, Update, Exclusive,
Intent, Schema, bulk-update, key-range
✓ Lock escalation in SQL server
✓ Blocking [SP_Who2]
✓ Resolving blocking issues in SQL Server
✓ Working with Activity Monitor
✓ Live & Dead Locks
✓ Trace flags to capture dead locks
✓ Capturing dead lock information in error
logs
✓ SQL Profiler [How to capture events data by
using Profiler]
✓ Capturing deadlock events in profiler
✓ Deadlocks and deadlock chain detection.
Performance Tuning
✓ Factors That Impact Performance
✓ Tools used SQL Profiler, Database Tuning
Advisor, System Monitor
✓ Introduction to Database Tuning Advisor
[DTA]
✓ Analyzing the profiler data by using DTA
✓ Performance Monitor [System Monitor]
✓ Correlate SQL Profiler Data with
Performance Monitor Data

SQL Server DBA Training Certificate

We provide a SQL Server DBA course completion certificate that is absolutely free and optional. The certificate can be printed out like a hard copy or given digitally according to your requirement. You can link it to your professional LinkedIn accounts which can be listed in your resume.