• Database systems Overview
• Relational Database Systems
• Normalization
• CODD Rules
• ACID Properties
• Database Design

• What is SQL SERVER?
• Usage of SQL Server
• SQL Server Version
• SQL Server Editions
• SQL Server Instance

• SQL Server Engine Architecture
• Storage Engine: Buffer Manager, Lock Manager, File Manager, Transaction Services
• Query Processor: Parser, Optimizer, SQL Manager, Database Manager, Query Executor
• SQLOS API, Protocols, External Components,
Transaction Log Architecture
• VLFs, checkpoint, LSN, Active – In active Logs
Database Architecture
• Pages, Different Types of pages, Data Files, Log files

• Installation checklist
• Step by step Installation & Configuration
• System DB’s , User DB’s
• Post Configuration (Memory Settings, Tempdb Settings, Port, DB configuration, Maintenance jobs
• Automatic Installation
• SQL Server Clients –SSMS, SQL CMD
• SSMS Overview
• SQL Server Configuration Manager

• Create Database, Schemas
• Drop Database, Schemas
• File Groups, File growth, File Shrink
• Partition
• Capacity planning
• User Tables, system Tables, Temporary Tables
• DML, DDL, DCL
• Constraints, Data Types, Different types of Keys
• System objects

• Understanding of TSQL Language
• Select, where, Order by , union
• Different Types of joins
• Operators: Logical, Compound, Comparison, Bitwise and Arithmetic
• Tsql Query and Sub Queries

• Create, update and Drop View
• Different Types Of view
• Create user Stored Procedures, system SPs , Xps
• When to compile and recompile – Effects of recompiling SP
• What is Cursors and Triggers
• How and when it could be used
• Place Views, Cursors, Triggers, queries, sub-queries in Stored Procedure

• Different Types of Recovery Model
• How choose Recovery Model
• What is isolation level?
• Different types of isolation level
• How to choose isolation level
• Impact of Isolation level

• Authentication Modes
• Fixed Server Roles, Database Roles
• Grant and Revoke Permissions
• Logins, Users, Credentials, Schemas
• Orphan Users
• Security Audit

• LSN – Related to Backups
• Different Types of Backups
• Backup – Internals on how backup actually runs
• Perform Backup using TSQL, SSMS
• Compressed Backups
• Schedule Backups using Jobs
• Verifying Backups
• Overview on external backups Tools

• Automatic Recovery
• Recovery / No Recovery/Standby
• Restore using Tsql , SSMS
• Point in time Restore
• Restoring System Databases
• Restore Database, Restore Logs

• Create SQL Server jobs
• Automate tasks using SQL Server jobs
• Common Automation tasks using SQL jobs
• Setting and Notify using Alert
• Setting and Configuring SQL Mail
• Sending Automatic Mail , reports from SQL Server

• Backup & Restore
• Detach & Attach
• Export & Import
• Compatibility level

• Pre-requisites for Upgrade
• Upgrade Advisor
• Different Types of Upgrade – In place & Side-by-Side
• Service Pack Upgrades and Apply hot fixes
• Checklist for Upgrade
• Post check

• What are indexes
• B –Tree Structure
• Clustered , Non-Clustered and Heap Index
• Column Store Indexes
• Index Internals –How index works
• Fragmentation in indexes
• Fill factor in indexes
• How Index increases Performance
• Page Split, Key Lookup, RID Lookup
• What is Statistics
• Update Statistics

• Locks, Latches, Blocks, Dead locks
• Memory and CPU Bottle Necks
• Disk Latency
• Different Wait types
• Identify Long Running Queries
• How to read Execution Plans
• Different Operators in Execution Plans
• Table Scan, Index Scan, Index Seek
• Estimated Vs Actual Execution Plans
• How identify Bad Execution Plan
• DMVs to work on Performance Troubleshooting
• Trouble Shooting Methodology

• Activity Monitor
• SQL Server Profiler
• Extended Events

• Server Level Optimizations
• Database Level Optimizations
• Tempdb Optimization
• Data Partition
• Resource Governor
• Query Optimization
• Resource Optimization
• OS level Optimization
• Maintenance Plan (Rebuild, Reorg, update Stats, Check DB, Checkalloc)
• Optimize for Unknown
• Plan Caching and Recompilation

• Stretch Database
• Query Store
• In-Memory Tables
• Buffer Pool Extension
• Always On Enhancements
• Updatable Coulmnstore Indexes
• Backup Encryption

• Types of mirroring
• How to configure Mirroring
• How Mirroring Works
• Mirroring Monitor
• Trouble Shooting Mirroring Issues

• Always on Architecture
• Prerequisites for Always on
• Quorum in always on
• Configuring Always on
• Availability Replica and Readable Secondary Replica
• Manual /Automatic Replica Role Change
• Trouble Shoot Always on Issues

• What is Clustering?
• How to Setup Clustering
• Quorum Settings
• Active and Passive Nodes
• Heart beat in Clusters
• Failover in clustering

• What is replication?
• Different types of Replication
• Configure Replication
• Trouble Shoot Replication

• Overview of SQL Server Integration Services
• Overview of SQL Server Reporting Services
• Overview on Basic Power shell related to Database Administration
• Tsql Queries , DMVs used in DBA day to day Operations

click the links explore..