Module 1: RDBMS-Overview

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

Module 2: SQL Server- Overview

• Anonymous Blocks
• Variables and Constants
• Data Types in PL/pgSQL
• Conditional Statements (IF-THEN-ELSEIF-END IF)
• Looping Constructs (LOOP, WHILE, FOR)

Module 3: Architecture

• 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 ArchitectureVLFs, checkpoint, LSN, Active – In active Logs
• Database ArchitecturePages, Different Types of pages, Data Files, Log files

Module 4: SQL Server Installation

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

Module 5: Database and Tables

• 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

Module 6: SQL Queries

• 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

Module 7: Views, Stored Procedures , Triggers, Cursors ( Respective to Core DBA activity)

• 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

Module 8: Database Recovery Model & Database Isolation level

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

Module 9: Security

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

Module 10: Backups

• 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

Module 11: Restore and Recovery

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

Module 12: SQL Server Jobs, Alert , Mail setup

• 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

Module 13: Database migration /Upgrades

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

Module 14: Server Upgrade / Instance Upgrade

• Prerequisites for Upgrade
• Upgrade Advisor
• Different Types of Upgrade – In place & Side-by-Side
• Service Pack Upgrades and Apply hotfixes
• Checklist for Upgrade
• Post check

Module 15: Indexes and Statistics

• 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

Module 16: Performance troubleshooting

• Locks, Latches, Blocks, Dead locks
• Memory and CPU Bottlenecks
• 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
• Troubleshooting Methodology

Module 17: SQL Server Optimization

• Server Level Optimizations
• Database Level Optimizations
• 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

Module 18: SQL Server Mirroring

• Types of mirroring
• How to configure Mirroring
• How Mirroring Works
• Mirroring Monitor
• Troubleshooting Mirroring Issues

Module 19: SQL Server Always On

• 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
• Troubleshoot Always on Issues

Module 20: SQL Server Clustering

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

Module 21: Replication

• What is replication?
• Different types of Replication
• Configure Replication
• Troubleshoot Replication

Module 22: Other SQL Services

• 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