Chennai :

9710033388 / 9884062253

Tanjore :

+919655141145

MS SQL Server DBA Course Syllabus

Duration : 3 Months
Category :MSSQL, Database, Microsoft

The objective of the MS SQL Server training program is to equip learners with comprehensive, hands-on expertise in administering, managing, and optimizing Microsoft SQL Server in enterprise environments. This course focuses on essential DBA competencies including installation and configuration, database architecture, security and user management, backup & recovery strategies, indexing, performance tuning, automation, and high-availability solutions such as Always On and replication.

Learners will gain practical experience using SQL Server Management Studio (SSMS) and related tools to monitor workloads, troubleshoot bottlenecks, manage schema objects, and support mission-critical applications. The program emphasizes industry best practices, real-time scenarios, and the operational skill set required in modern database infrastructure.

By the end of the course, learners will be able to confidently deploy, maintain, secure, and optimize SQL Server environments, preparing them for roles such as SQL Server DBA, Database Engineer, Database Support Analyst, and Infrastructure Administrator.

  • Definition of Database
  • Advantages of DBMS
  • Definition of DBMS
  • Types of Databases
  • Disadvantages of DBMS
  • What is SQL Server?
  • SQL Server Versions
  • Usage of SQL Server
  • SQL Server Instance
  • SQL Server Editions
  • SQL Server Engine Architecture
  • Query Processor: Parser, Optimizer, SQL Manager, Database Manager, Query Executor
  • Storage Engine: Buffer Manager, Lock Manager, File Manager, Transaction Services
  • Transaction Log Architecture: VLFs, checkpoint, LSN, Active – In active Logs
  • SQLOS API, Protocols, External Components
  • Database Architecture: Pages, Different Types of pages, Data Files, Log files
  • Installation checklist
  • System DB’s , User DB’s
  • Step by step Installation & Configuration
  • Automatic Installation
  • Post Configuration (Memory Settings, Settings, Port, DB configuration, Maintenance jobs)
  • SSMS Overview
  • SQL Server Clients – SSMS, SQL CMD
  • SQL Server Configuration Manager
  • Create Database, Schemas
  • File Groups, File growth, File Shrink
  • Drop Database, Schemas
  • Capacity planning
  • Partition
  • DML, DDL, DCL
  • User Tables, system Tables, Temporary Tables
  • System objects
  • Constraints, Data Types, Different types of Keys
  • Understanding of TSQL Language
  • Different Types of joins
  • Select, where, Order by, union
  • TSql Query and Sub Queries
  • Operators: Logical, Compound, Comparison, Bitwise and Arithmetic
  • Create, update and Drop View
  • Create user Stored Procedures, system SPs, Xps
  • Different Types Of view
  • What is Cursors and Triggers
  • When to compile and recompile – Effects of recompiling SP
  • Place Views, Cursors, Triggers, queries, sub-queries in Stored Procedure
  • How and when it could be used
  • Different Types of Recovery Model
  • What is the isolation level?
  • How choose Recovery Model
  • How to choose isolation level
  • Different types of isolation level
  • Impact of Isolation level
  • Authentication Modes
  • Grant and Revoke Permissions
  • Fixed Server Roles, Database Roles
  • Orphan Users
  • Logins, Users, Credentials, Schemas
  • Security Audit
  • LSN – Related to Backups
  • Backup – Internals on how backup actually runs
  • Different Types of Backups
  • Compressed Backups
  • Perform Backup using TSQL, SSMS
  • Verifying Backups
  • Schedule Backups using Jobs
  • Overview on external backups Tools
  • Automatic Recovery
  • Restore using TSQL, SSMS
  • Recovery / No Recovery / Standby
  • Restoring System Databases
  • Point in time Restore
  • Restore Database, Restore Logs
  • Create SQL Server jobs
  • Common Automation tasks using SQL jobs
  • Automate tasks using SQL Server jobs
  • Setting and Configuring SQL Mail
  • Setting and Notify using Alert
  • Sending Automatic Mail, reports from SQL Server
  • Backup & Restore
  • Export & Import
  • Detach & Attach
  • Compatibility level
  • Prerequisites for Upgrade
  • Different Types of Upgrade – In place & Side-by-Side
  • Upgrade Advisor
  • Checklist for Upgrade
  • Service Pack Upgrades and Apply hotfixes
  • Post check
  • What are indexes
  • Clustered, Non-Clustered and Heap Index
  • B –Tree Structure
  • Index Internals – How index works
  • Column Store Indexes
  • Fill factor in indexes
  • Fragmentation in indexes
  • Page Split, Key Lookup, RID Lookup
  • What is Statistics
  • How Index increases Performance
  • Update Statistics
  • Locks, Latches, Blocks, Dead locks
  • Disk Latency
  • Memory and CPU Bottlenecks
  • Identify Long Running Queries
  • Different Wait types
  • Different Operators in Execution Plans
  • How to read Execution Plans
  • Estimated Vs Actual Execution Plans
  • Table Scan, Index Scan, Index Seek
  • DMVs to work on Performance Troubleshooting
  • How identify Bad Execution Plan
  • Troubleshooting Methodology
  • Activity Monitor
  • Extended Events
  • SQL Server Profiler
  • Server Level Optimizations
  • Data Partition
  • Database Level Optimizations
  • Query Optimization
  • Resource Governor
  • OS level Optimization
  • Resource Optimization
  • Optimize for Unknown
  • Maintenance Plan (Rebuild, Reorg, update Stats, Check DB, Checkalloc)
  • Plan Caching and Recompilation
  • Stretch Database
  • In-Memory Tables
  • Query Store
  • Always On Enhancements
  • Buffer Pool Extension
  • Backup Encryption
  • Updateable Columnstore Indexes
  • Types of mirroring
  • How Mirroring Works
  • How to configure Mirroring
  • Troubleshooting Mirroring Issues
  • Mirroring Monitor
  • Always on Architecture
  • Quorum in always on
  • Prerequisites for Always on
  • Availability Replica and Readable Secondary Replica
  • Configuring Always on
  • Troubleshoot Always on Issues
  • Manual / Automatic Replica Role Change
  • What is Clustering?
  • Quorum Settings
  • How to Setup Clustering
  • Heart beat in Clusters
  • Active and Passive Nodes
  • Failover in clustering
  • What is replication?
  • Configure Replication
  • Different types of Replication
  • Troubleshoot Replication
  • Overview of SQL Server Integration Services
  • Overview on Basic Power shell related to Database Administration
  • Overview of SQL Server Reporting Services
  • TSql Queries, DMVs used in DBA day to day Operations

Alexzender Alex

CSE Teacher

Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusa dolore mque laudantium totam rem aperiam eaqipsa quae ab illo inventore veritatvolup tatem quia voluptas sit aspernatur aut odit aut fugit sed quia conseque.

Nathaniel Bustos

Manager

Latanya Kinard

Web Designer

Recent Blogs

Related Course

Where Our Alumini Works

CallUs
WhatsApp
Instagram
Facebook
YouTube