Below are SQL Server topics which are essential for Database Administrator…

Server, DB Architecture

○ Server Architecture & Protocols

○ Query Processor & Storage Engine

○ Parser, Optimizer, SQL & DB Manager

○ File Manager and Transaction Services

○ IO & Lock Manager, MDAC, CLR, WAL

○ Buffer Manager, Lazy Writer, SQL OS

○ Linked Servers, RPC & Data Access

○ Database Architecture – Files, Pages

○ Primary (.mdf), Secondary Files (.ndf)

○ Filegroups Usage, Read Only Filegroups

○ File Properties: Size & Location

○ LSN, Mini LSN and Virtual Log File

○ Uniform Extent and Mixed Extent

○ Schemas: Creation, Usage, Migrations

BACKUPS – DB, Filegroup, File

○ Database Backups, Filegroup Backups

○ Log File Backups and Log Truncations

○ COPY_ONLY Backups and Real-time Use

○ Mirror Backups and Split Backups

○ Partial Backups – Read-Only Filegroups

○ Format, Compression and Checksum

○ Backup Verification, Retain-Period, Stats

○ Continue On Error and Backup Scripts

○ GUI and Script Backups: Differences

○ Backup History Tables in MSDB – Joins

○ Backup Audits. HOT and COLD Backups

○ Backup Devices – Creation and Usage

○ Using Backup Devices – Advantages

○ Common Errors and Solutions

RESTORES & DB RECOVERY

○ Restore Phases – COPY, REDO, UNDO

○ RECOVERY, NORECOVERY Options

○ STANDBY and REPLACE in Restores

○ File, File Group & Metadata Restores

○ Backup Verifications using GUI, Scripts

○ VERIFYONLY : Backup Verification

○ STATS, UNLOAD, STOPAT and INIT

○ PARTIAL / PIECEMEAL Restores – Use

○ Tail Log Backup Usage in Real-time

○ Restores using GUI and T-SQL Scripts

○ MOVE Options for File Level Restores

○ Point-In-Time Restore, Checkpoint LSN

○ Standby Restores and Read-Only State

○ Common Errors and Solutions

JOBS, MAINTENENCE PLANS

○ SQL Server Agent Service & Agent XPs

○ SQL Agent Jobs – GUI, Script Creations

○ Job Steps – Creation, Edits and Parse

○ Job Executions, Disable/Enable Options

○ Job History Purge. Job Activity Monitor

○ Database Offline and Backup Errors

○ Database Maintenance – Backup Jobs

○ Scheduling Database Maintenance Plans

○ Automated Job Creations using DMPs

○ Backup Cleanup & History Cleanup Jobs

○ Backup Strategies For Minimal Data Loss

○ Backup Options: Block Size, Transfer Size

○ DB Mail Configurations and Alert System

○ DB Mail Profiles, SMTP Email Accounts

○ Operators : Creation, Job Notifications

SECURITY MANAGEMENT

○ Authentication Types & Modifications

○ Windows Logins & SQL Server Logins

○ Logins – Users Mapping, DB Access

○ Server Roles & Database Roles – Usage

○ Object, Column and Schema Security

○ GRANT, WITH GRANT, DENY, REVOKE

○ CONTROL, OWNERSHIP, Authorization

○ Data Encryption: Keys and Certificates

○ Data Encryption with Stored Procedures

○ Job Security : Credentials and Proxies

○ Using Proxies for SSIS Jobs, Repl Jobs

○ Security Scripts and Documentation

○ DMVs for Security Audits, Orphan Users

○ Login, User, Server Principal Audits

DB Migrations, Perf Tuning 

○ CDW : Copy Database Wizard @ SSMS

○ Database Detach and Attach Options

○ SMO Method and Database Scripting

○ CDW SSIS Packages, SSIS Proxies Use

○ Scheduling Database Migration Jobs

○ Detecting and Resolving Orphan Users

○ Containment Databases Authentication

○ Audit Long Running Queries : DMV, DMF

○ Activity Monitor Tool, Server Dashboards

○ Logical I/O, Physical I/O, Database I/O

○ Recent Expensive Queries, Wait Time

○ Active Expensive Queries, Statistics

○ Plan Handle, Execution Time – Audits

○ CPU, IO, Memory Consumption Reports

Indexes, Partitions

○ Indexes: Architecture and Index Types

○ B Tree Structure, IAM Page [Root]

○ Clustered & NonClustered Indexes

○ Included, Columnstore, Online

○ Filtered, Covering, Indexed Views

○ Fill Factor and Pad Index Options

○ Query Store – Settings and Advantages

○ PARTITIONS : Advantages, Performance

○ Partition Functions & Partition Schemes

○ Partitioning Un-partitioned Tables: GUI

○ Partition Compression : ROW and PAGE

○ Auditing Table Partitioned Structures

○ Statistics : Purpose, Auto Creation, Audits

○ NUMA Nodes, Processor, IO Affinity

Health Check Issues, Solutions

○ Alerts : Creation and Notifications

○ DB Suspect Event Alerts (023)

○ Important Perfmon Counters, Alerts

○ Log Space, Memory, Tempdb Alerts

○ Scheduling Alerts & Notifications

○ DBCC CHECKDB : DB Health Checks

○ Allocation Errors, Consistency Errors

○ DBCC ShowContig, Extent Fragmentation

○ Trace Flags and Page Restores

○ DBCC Page: GAM, SGAM and PFS

○ Consistency Errors : Cause & Solutions

○ Allocation Errors : Cause and Solutions

○ Log Space Issues and Log Rebuilds

                ○ Memory & TempDB Issues, Solutions

REPLICATION 

○ Replication Architecture and Topology

○ Publication Types – Purpose, Importance

○ DB Articles, Publications, Subscriptions

○ Distribution DB Configuration, Snapshots

○ Snapshot Replication and Repl Agents

○ Adding Articles to Existing (LIVE) Replica

○ PUSH, PULL Subscriptions. N/W Shares

○ Transactional Replication Configuration

○ Log Reader Agent – Configuration, Keys

○ Replication Monitor – Tracer Tokens

○ Replication Monitor – Warnings, Alerts

○ Replication Monitor – Adding Articles

○ Replication Scripts, Reinitialization

○ Replication Warnings and Agent Alerts

○ Merge Replication and Merge Agent Job

○ Replication Conflicts and ROWGUIDCOL

○ Server Subscription & Client Subscription

○ Peer-Peer Replication Connections, Nodes

○ NodeID and Conflict Detection Options

○ Replication Conflicts and sp_MSRepl

○ sp_changedbowner, backup initialization

○ Replication Conflicts and Priority Settings

○ Replication Verify – Rowcount, Checksum

○ Disabling, Cleaning Replication Topology

○ Replication Strategies for HA and DR Plan

○ Replication for Load Balancing Topologies

○ SQL Server Configuration Manager Tool

○ Common Errors and Solutions

LOG SHIPPING (HA – DR)

○ Log Shipping Topology for HA and DR

○ Primary and Secondary: Recovery Plan

○ Log Shipping Monitor, Jobs and Alerts

○ NORECOVERY Mode – Configuration

○ STANDBY Mode Configuration & Jobs

○ Log Shipping Jobs and Manual Failover

○ Log Shipping Mode Changes – cautions

○ Re-Restoring Log Backups for Recovery

○ LSBackup, LSCopy & LSRestore Jobs

○ LS Job Audits, Dashboards (Reports)

○ TUF Files and Standby Options in LS

○ Broken Log Shipping Chains & Issues

○ Log Shipping Manual Fail-over Options

○ Log Shipping Configuration Precautions

○ Common Errors & Solutions

DB MIRRORING (HA – DR)

○ DB Mirroring Architecture For HA & DR

○ Log Shipping Versus Database Mirroring

○ TCP Endpoints, TCP Network Security

○ Heartbeat and Polling Concepts in DM

○ Automatic Fail-Over Procedures, Tests

○ PARTNER OFFLINE Conditions, Options

○ DB Mirroring Monitors and Commit Loads

○ SYNCHRONOUS & ASYNCHRONOUS

○ DB Mirroring and Port Configurations

○ Mirroring Monitor, Stop/Resume Options

○ Need for Always-On & Higher Availability

○ DB Recovery without Witness. Failover

○ Mirroring Monitor Jobs – Real-time Usage

○ Common Backups Errors & Solutions

PATCHES, UPGRADES, CUs

○ Maintenance: Precautions & Downtime

○ DB Backups, Scripting and Services

○ Service Packs and Patch/Hotfix Activities

○ Cumulative Updates (CU), Hotfix Process

○ Instance Selectivity for Updates, Cautions

○ Verifications, Smoke Test and Rollbacks

○ Multi Instance Updates & Port Changes

○ SERVER Upgrades & VERSION Changes

○ Silent Installation & Installation Repairs

○ Verifications, Smoke Test and Rollbacks

○ System Database REBUILDs using CMD

○ Silent Installation & Installation Repairs

○ SQLCMD Tool and Instance Connections

○ DAC : Dedicated Administration Console

SQL Cluster and Always-On 

○ Detailed Implementation @ Ch 34, 35

○ Windows Clusters For HA and DR

○ Domain Controller (DC) Configuration

○ Active Directory (AD) Accounts, Use

○ SAN [Storage Area Network] and LUN

○ Public IP Address, Private IP Address

○ Windows Level Clusters, MSCS Service

○ Active-Active, Active-Passive Clusters

○ Always On Availability Group [AOAG]

○ Synchronous and Asynchronous Modes

○ Synchronization, Automated Seeding

○ Data Synchronization for AOAG

○ Backup Preferences, Location Options