Module I: SQL Server & T-SQL
Installation, Architecture, DB Basics
|
Module II: Basic SQL DBA
Backup-Restores, Jobs, Performance Tuning, Security
|
Module III: Advanced SQL DBA
HA-DR, Errors & Solutions, Always-On, SLA
|
Ch 1: DATABASE INTRO & SQL DBA
- Data, Databases and RDBMS Software
- Database Types : OLTP, DWH, OLAP
- Microsoft SQL Server Advantages, Use
- Versions and Editions of SQL Server
- SQL : Purpose, Real-time Usage Options
- SQL Versus Microsoft T-SQL [MSSQL]
- Microsoft SQL DBA: Job Roles
- Routine SQL DBA Activities in Realtime
- Emergency SQL DBA Activities, Job Work
- Maintenance SQL DBA Activities
- SQL Server Components and Usage
- Database Engine Component and OLTP
- 24 x 7 Online Lab for Remote DB Access
- Software Installation Pre-Requisites
|
Ch 11: BACKUPS & MANAGEMENT
- Database Backups, Filegroup Backups
- Log File Backups and Log Truncations
- COPY_ONLY Backups and Real-time Use
- Mirror Backups and Split Backups
- Partial Backups - ReadOnly Filegroups
- Format, Compression and Checksum
- Backup Verification, RetainDays, Stats
- ContinueOnError 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 Backups Errors, Solutions
|
Ch 21: REPLICATION For HA - Level 1
- Replication Architecture and Topology
- Publication Types - Purpose, Importance
- DB Articles, Publications, Subscriptions
- Distributor and Distribution Database
- Replication Topologies and HA DR Plan
- Replication Jobs: Types and Usage
- One Way and Two Way Replication
- PUSH and PULL Subscriptions
- Snapshot Publication: Creation, Filters
- Snapshot Replication and Repl Agents
- Log Reader Agent - Configuration, Keys
- Transactional Replication Configuration
- Security Options with Log Reader Agent
- Replication Monitor - Tracer Tokens
- Replication Monitor - Warnings, Alerts
|
Ch 2: SQL SERVER INSTALLATIONS
- System Configuration Checker Tool
- Versions and Editions of SQL Server
- SQL Server and SSMS Installation Plan
- SQL Server Pre-requisites : S/W, H/W
- SQL Server 2022 / 2019 Installation
- SQL Server 2017 Installation
- Instance Name and Server Features
- Instances : Types and Properties
- Default Instance, Named Instances
- Port Numbers, Instance Differences
- Service and Service Account Use
- Authentication Modes and Logins
- Windows Logins and SQL Logins
- FileStream and Collation Properties
|
Ch 12: 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 Backups Errors,Solutions
|
Ch 22: REPLICATION For HA - Level 2
- Merge Replication and Merge Agent Job
- Replication Conflicts and ROWGUIDCOL
- Subscription Reinitialization & Expiry
- Server Subscription & Client Subscription
- Scripting Snapshot Replication
- Scripting Transactional Replication
- Limitations with Traditional Replication
- Peer - Peer Replication : Realtime Usage
- Configure Peer-Peer Replication
- Multi Master Replications & Node IDs
- Peer-Peer Replication Connections
- Replication Conflicts and Priority Settings
- Disabling, Cleaning Replication Topology
- Replication: Load Balancing Topologies
- Common Replication Errors, Solutions
|
Ch 3: SSMS Tool, SQL BASICS - 1
- SQL Server Management Studio
- Local and Remote Connections
- System Databases: Master and Model
- MSDB, TempDB, Resource Databases
- Creating Databases : Files [MDF, LDF]
- Creating Tables in User Interface
- Data Insertion & Storage. Limitations
- SQL : Purpose and Real-time Usage
- SQL Versus T-SQL : Basic Differences
- DDL, DML, SELECT, DCL and TCL
- Creating Tables using SQL Scripts
- Data Storage, Inserts - Basic Level
- Table Data Verifications with Select
- SELECT Statement for Table Retrieval
|
Ch 13: 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 Maintenance - Backup Jobs
- Scheduling Database Maintenance Plans
- Backup Cleanup & History Cleanup Jobs
- Backup Strategies For Minimal Data Loss
- Backup Options: Block & Transfer Size
- DB Mail Configurations and Alert System
- DB Mail Profiles, SMTP Email Accounts
- Operators : Creation and Real-time Use
- Linking Jobs with Operators, Notifications
|
Ch 23: 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
- Comparing Replication Vs Log Shipping
- Limitations of Log Shipping For HA DR
- Log Shipping Errors and Solutions
|
Ch 4: SQL BASICS - 2
- Creating Databases & Tables in SSMS
- Single Row Inserts, Multi Row Inserts
- Rules for Data Insertion Statements
- SELECT Statement @ Data Retrieval
- SELECT with WHERE Conditions
- Batch Concept and Go Statement
- AND and OR Operators Usage
- IN Operator and NOT IN Operator
- Between, Not Between Operators
- LIKE and NOT LIKE Operators
- UPDATE Statement & Conditions
- DELETE & TRUNCATE Statements
- Logged and Non-Logged Operations
- ADD, ALTER and DROP Columns
|
Ch 14: RECOVERY, DB SNAPSHOTS
- Database Recovery Models, Logging
- Database Log File Architecture
- Full Recovery Mode, Real-time Usage
- Simple Recovery Mode, Real-time Use
- Bulk Logged Recovery Model, Imports
- Recovery Models & Selectivity
- Limitations with Simple Recovery Model
- Database Snapshots : Creation, Use
- Creating & Testing DB Snapshots
- Read and Write Operations
- Querying Database Snapshots
- DB Snapshots: Page Versioning
- Database Snapshots: Realtime Use
- Comparing DB Backups & Snapshots
|
Ch 24: 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
- Database Mirroring Advantages
- Common Mirroring Errors, Solutions
|
Ch 5: SQL Basics - 3, T-SQL INTRO
- Database Objects : Tables and Schemas
- Schemas : Group Tables in Database
- Schemas : Security Management Object
- Creating Schemas & Batch Concept
- Using Schemas for Table Creation
- Data Storage in Tables with Schemas
- Data Retreival and Usage with Schemas
- Table Migrations across Schemas
- Import and Export Wizard in SSMS
- Data Imports with Excel File Data
- Performing Bulk Operations in SSMS
- Temporary Tables : Real-time Use
- Local and Global Temporary Tables
- # and ## Prefix, Scope of Usage
|
Ch 15: SECURITY MANAGEMENT
- Authentication Types & Modifications
- Windows Logins : Creation and Usage
- SQL Server Logins : Creation, Usage
- Password Policies; User Creations
- Logins - Users Mapping, DB Access
- Server Roles & Database Roles - Usage
- Password Resets & Security Policies
- Server Logs - Security Management
- Log Archives: Login Failure Errors
- Object Security and Column Security
- Schema Security and Built-In Schemas
- GRANT, WITH GRANT, DENY, REVOKE
- CONTROL, OWNERSHIP, Authorization
- Execute, References, View Definition
|
Ch 25: HEALTH CHECKS & ERRORS
- Alerts : Creation and Notifications
- DB Suspect Event Alerts (023)
- Important Perfmon Counters, Alerts
- Log Space, Memory, Tempdb Alerts
- DBCC CHECKDB : DB Health Checks
- Allocation Errors, Consistency Errors
- DBCC ShowContig, Fragmentation
- Trace Flags and EstimateOnly
- 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
- DBCC ShrinkDB and Page Restores
|
Ch 6 : CONSTRAINTS, INDEXES
- Constraints and Keys - Data Integrity
- NULL, NOT NULL Property on Tables
- UNIQUE KEY Constraints: Importance
- PRIMARY KEY Constraint: Importance
- FOREIGN KEY Constraint: Importance
- REFERENCES, CHECK and DEFAULT
- Candidate Keys and Identity Property
- Database Diagrams and ER Models
- Relationships Verification and Links
- Indexes : Basic Types and Creation
- Index Sorting and Search Advantages
- Clustered and NonClustered Indexes
- Primary Key and Unique Key Indexes
- Need for Indexes - working with Keys
|
Ch 16: DATABASE MIGRATIONS
- Creating Credentials @ Server Security
- Creating Proxies & Agent Security
- Linking Credentials and Proxies
- Scritping Credentials, Proxies
- Realtime Usage of Proxies
- Using Proxies for DB Migrations
- 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
- SSMS Connections & Containment
|
Ch 26: LOCK MANAGEMENT
- LOCKS : Types and Isolation Levels
- S, X, IX,U, MD, Sch-M and Sch-S
- Lock Audits : SP_WHO2 & SP_LOCK
- sysprocesses and Lock Waits : Audits
- Open Transactions, Query Blocking
- Lock Hints and Isolation Levels
- Read Committed, Read Uncommitted
- Serializable and Repeatable Read
- Snapshot Isolation, Page Versioning
- Read Committed Snapshot Row Version
- Choosing Correct Isolation Level
- Profiler Tool and Lock Templates
- Profiler Filters, Column Selections
- Deadlock Audits and Deadlock Graphs
|
Ch 7: JOINS & LINKED SERVERS
- JOINS - Table Comparisons Queries
- INNER JOINS For Matching Data
- OUTER JOINS For (non) Match Data
- Left Outer Joins with Example Queries
- Right Outer Joins with Example Queries
- FULL Outer Joins - Realtime Scenarios
- Join Queries with "ON" Conditions
- CROSS JOIN and CROSS APPLY
- One-way, Two way Data Comparisons
- Linked Servers Configurations
- Linked Servers: RPC Settings & Tests
- Linked Servers, Remote Joins in TSQL
- Multi Server Connections, DB Access
- 2 Part, 3 Part, 4 Part Name Conventions
|
Ch 17: SERVER ARCHITECTURE, AUDITS
- SQL Server Architecture : Protocols
- Named Pipes, TCP IP; Storage Engine
- Query Processor: Parser, Optimizer
- File Manager and Database Files
- Transaction Services, Buffer Manager
- CLR, WAL, Lazy Writer, Checkpoint
- Lock Manager, IO Manager, MDAC
- 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
|
Ch 27: PATCHES, UPGRADES, CUs
- Establishing Downtime For Maintenance
- Precautions for Maintenance Activities
- DB Backups, Scripting and Services
- Service Packs and Patch/Hotfix Activities
- Cumulative Updates (CU), Hotfix Process
- 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
|
Ch 8: VIEW, SPs, FUNCTIONS BASICS
- Database Objects: Overview & Usage
- Views: Types, Usage in Real-time
- System Predefined Views and Audits
- Listing Databases, Tables, Schemas
- Functions: Types, Usage in Real-time
- System Predefined Functions, Audits
- DBId, DBName, ObjectID, ObjectName
- Using Parameters in SQL Server
- Dynamic Joins for Database Audits
- Procedures: Types, Usage in Real-time
- User & System Predefined Procedures
- Parameters and Dynamic SQL Queries
- Sp_help, Sp_helpdb and sp_helptext
- sp_pkeys, sp_rename and sp_help
|
Ch 18: PAGE ARCHITECTURE, INDEXES
- Database Data Files and Pages
- Data Pages, Mixed / Uniform Extents
- Database Log Files and Pages
- Virtual Log Files (VLF) and LSN
- MINI LSN and Log File Usage Report
- Database Files : Size and Location
- 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
- Database Properties & Query Store
|
Ch 28: SLA, OLA PROCESS FOR DBA
- SLA and OLA Process, Ticketing Tools
- Realtime Job Environment: SLA
- Ticketing and Process; KB Articles
- Immediate, High, Normal Priorities
- Impact, Urgency and SLA Metrics
- Licensing and Pricing Options
- Core Based Licenses. Device CALs
- User CALs and Multiplexing Concept
- Versions, Editions Comparisons
- Edition Upgrades & Precuations
- Command Line Installations
- Version Checks & Edition Checks
- Build Numbers, Server Configurations
- Client Protocols, Ports & Server Aliases
|
Ch 9: TRIGGERS & TRANSACTIONS
- Triggers - Purpose, Real-world Usage
- FOR/AFTER Triggers - Real time Use
- INSTEAD OF Triggers - Real time Use
- INSERTED, DELETED Memory Tables
- Using Triggers for Data Replication
- Enable Triggers and Disable Triggers
- Database Level, Server Level Triggers
- Auditing Triggers and Real World Use
- Transactions : Types, ACID Properties
- Transaction Types and AutoCommit
- EXPLICIT & IMPLICIT Transactions
- COMMIT and ROLLBACK Statements
- Open Transaction Scenarios & Cause
- Query Blocking Scenarios @ Real-time
|
Ch 19: TUNING: INDEX MANAGEMENT
- PARTITIONS : Advantages, Performance
- Table Partitions : Creation & Advantages
- Partition Functions & Partition Schemes
- Partitioning Un-partitioned Tables: GUI
- Verifying / Auditting Partitioned Tables
- Partition Compression: ROW and PAGE
- Auditing Table Partitioned Structures
- Statistics: Real-time Use, Performance
- Statistics: Manual & Auto Creations
- Internal and External Fragmentation
- Index Rebuilding Process and Audits
- Database Maintenance Plans Jobs
- Last Used, Page Count, Fragmentation
- Index Page Count, Degree Of Parallelism
|
Ch 29: SQL DBA PROJECT - Level 1
- Audit Login Failures : Server Logs
- Monitoring Connectivity Issues
- Database Refresh and MSDTC
- Adhoc Memory Dump Files
- PLE (Page Life Expectancy) Issues
- Object Refresh and Recompilations
- Server Registrations and Operations
- Lock Monitoring Operations
- Index Management Options
- Open Transactions, Blocking
- Metadata Sync-up Issues
- Stored Procedure Recompilations
- Backup and HA-DR Strategies
- Db Restores and DB Repairs
- Health Checks, Issues, Solutions
|
Ch 10: DB Architecture & Group By
- Database Architecture: Data Files
- Database Architecture: Log Files
- Primary and Secondary Data Files
- Log Files Creation and Realtime Use
- Creating and Using Filegroups
- Linking Tables with Filegroups
- Scripting Database and Objects
- GROUP BY: Importance, Realtime Use
- GROUP BY Queries and Aggregations
- Group By Queries with Having Clause
- Group By Queries with Where Clause
- Using WHERE and HAVING in T-SQL
- Using Group By in Data Audits
- Using Group By with Joins, Audits
|
Ch 20: TUNING TOOLS, EXEC PLANS
- Tuning Tools: Workload Files, .trc Files
- Profiler Tuning Template, SP Events
- DTA, Profiler Trace : Recommendations
- PDS: Physical Design Structures
- Index, Stats, Partition Recommendations
- DTA with Query Execution Cache
- Perfmon Tool : Usage, Permon Counters
- Real-time Tracking: CPU, Memory, IO
- Execution Plan Analysis and Internals
- Query Costs: IO Cost and CPU Cost
- Query Costs: SubTree & Operator Cost
- NUMA Nodes, Processor, IO Affinity
- Thread Count, Degree of Parallelism
- Stored Procedure Recompilations
|
Ch 30: SQL DBA PROJECT - Level 2
- Server Down Issues and Solutions
- Database Down Issues and Solutions
- Data Missing Issues and Solutions
- Hot CPU and Resource Allocations
- Port Level Issues and Solutions
- Online & Offline Backups, Certificates
- Ticketing Tools : SLA & OLA Concepts
- Incident Management and Ticketing
- Immediate, High and Medium Priority
- Levels of Support for Production DBA
- 3rd Party Tools and Real-time Use
- Automated Backups, Log File Readers
- Licensing and Pricing Options, CALs
- Device CALs and User CALs
- Multiplexing with Server Licenses
|
