Course Duration:  35 Hours

FOR FREE DEMO contact us at:





Microsoft SQL Server DBA online training Course content

Module 1: Introduction to SQL Server

·                     Getting familiar with ITIL Concept

·                     Type and Shifts  DBAs

·                     Roles and Responsibilities of DBA

            History of SQL Server & Code name

            New features of SQL Server 2005 & 2008

            Different Editions of SQL Server

            Tools of SQL Server

·                     Hardware and Software Requirements


Ø  Default Instance

Ø   Named Instances

• SQL Server Services

Ø  Instance  Aware Services

Ø  Instance Unaware Services


Module 2: Installation and configuring SQL Serve


• Installing SQL Server 2005 & 2008.

Ø  Pre – installation steps

Ø  Installations

Ø  Viewing installation process with LOG files

Ø  Common issues.

Ø  Adding /Removing components

Ø  Installing service packs.

Ø  Best Practices after Installing SQL Server

• Configuration

Ø  Configuring various Services with Local and Domain Account

Ø  Startup Parameter

Ø  Configuring data file and log file paths

Ø  Memory configuration

Ø  Remote connections

Ø  Configuring Network protocols, Ports


Module 3: Working with Databases


• Working with databases.

Ø  System Defined databases

Ø  Steps to move  System databases

Ø  Handling TempDb issues.

• Database Architecture.

Ø  Data File  & Log File

Ø  Filegroups

Ø  Extents

Ø  Pages – types

Ø  Page architecture

Ø  Tracking free space

Ø  Log file full – How to solve the problem


• Creating Databases

• Database Snapshots

• Adding files, file groups and Schema


Module 4: Implementing Security

• Security in SQL Server 2008

• Types of Authentications.

Windows Authentication

Ø   Creating logins from windows users and groups

Ø  Orphan logins.

SQL Server Authentication

Ø  Creating SQL logins and testing logins

• Setting authentication Mode

• Security Auditing

• Understanding server roles

• Working with users

• Resolving orphan users issues

• Understanding database roles, custom and application roles

• Understanding permissions

• Encryption and decryption

• Major issues


Module 5: Backup and Restoration

• Understanding Transaction process

• Understanding Transaction Log file

• Checkpoint  & Lazy writer process

• Truncating log file.

• Database Recovery Models

Ø  Full

Ø  Bulk Logged

Ø  Simple

• Setting recovery model

• Database Backups

• Backup Types.

Ø  Full

Ø  Differential

Ø  Transaction Log

Ø  File or Filegroup

• Copy-only, Mirrored and tail log backup

• Performing Restoration operations

Practical Scenario :

Ø  Steps to Restore Master database when Server crashes.

Ø  How to Recover Database from Suspect mode.

Ø  How to Recover Database when Log file is Corrupted.



Module 6: High Availability


• Introduction to High Availability

• Working with Log Shipping

Ø  Log Shipping Requirement

Ø  Configuring Log Shipping

Ø  Monitoring Log Shipping Status

Ø  Manually performing Fail Over

Ø  Transferring logins

Ø  Log shipping  tables and stored procedures

Ø  Fixing log shipping issues

• Working with Database Mirroring

Ø  Difference bet Logshipping and Mirroring

Ø  Operating Modes in Mirroring

Ø  Server  Roles  in Mirroring

Ø  Requirements for Mirroring

Ø  Configuring Mirroring

Ø  Performing failover in Mirroring

Ø  Different Ways to monitor Mirroring Status

Ø  Mirroring system tables and stored procedures.

Ø  Major issues with mirroring.



Module 7: Replication


• Replication and advantages

• Replication Entities

• Replication Architecture

• Replication Agents

• Types of Replications

• Configuring Replication

Ø  Snapshot Replication

Ø  Transactional Replication

Ø  Merge Replication

• Peer to peer replication

• Replication Topologies



Module 8: Automating Administrative Tasks


• Working with Database Mail.

Ø  Mail architecture.

Ø  Configuring Profiles and Accounts

Ø  Creating  Operators

Ø  Sending Mail

• Configuring linked servers

• Implementing Automation

Ø  Configuring SQL Server Agent

Ø  Creating  Jobs

Ø  Managing jobs and resolving errors.

Ø  Monitoring jobs.

Ø  Auto alert when jobs are enabled, disabled or failed.

• Managing replication.

• Monitoring and Tuning Replication


Module 9: Advanced Administration Concepts


• Maintenance plans

• Monitoring and Tuning SQL Server

Ø  Performance counters setup

Ø  Measuring performance of server.

Ø  Tuning queries.

Ø  Tuning databases.

Ø  Tuning physical architecture of databases.

Ø  Using DTA.

• Monitoring Tools

Ø  Performance  Monitor

Ø  SQL Server Profiler

Ø  Database Engine Tuning Advisor.

Ø  Dynamic Management Views.

Ø  SQL Server and Windows Event Logs.

• Troubleshooting

Ø  Physical server performance.

Ø  Connectivity to SQL Server

Ø  Database Performance.

Ø  Using the DAC.

Ø  Shrink Files

Ø  Using DBCC commands.


Module 09: SQL Server Architecture



·           SQL Server Architecture

Ø  Relational Engine

Ø  Storage Engine

Ø  Buffer pool

Ø  Managing execution plans.

·         Locking and Blocking

Ø  Locks

Ø  Deadlocks

Ø  Understanding Blocking.

Ø  Terminating Processes

Ø  Transaction Isolation Levels.


Module 10: Index Management


·         Why Indexes

·         Type of Indexes

Ø  Cluster Index

Ø  Noncluster Index

Ø   Full Text Index

·         Creating Indexes

·         Manage Index Fragmentation

Ø  DBCC Showcontig

Ø  DM.DB_Index_Physical_Stats

Ø  Index Rebuilding and Reorganizing.


·         Manage Statistics

·         Performing database integrity checks


Module 11: SQL Server 2000/2005 to 2008 Upgrade Process

Ø  SSUA Tool

Ø  Upgrade Type

o   Inplace Upgrade

o   Side By Side  Upgrade

Ø  Compatibility level

Ø  Transferring logins

Ø  Transferring Jobs

Ø  DTS to SSIS Migration


Module 12: Clustering

Ø  Why Clustering?

Ø  Overview of Windows Clustering.

Ø  Types of Cluster

Ø  Pre- Requisites to implement Cluster

Ø  Installing  SQL Server Failover Cluster

Ø  Applying service packs and hot fixes

Ø  Performing failover

Ø  Adding node on a SQL Server Failover cluster.

Ø  Troubleshooting cluster issues


Module 13: SQL Server 2008 New Featured

Ø  Central Management Server

Ø  Data Collector

Ø  Auditing

Ø  Resource Governor

Ø  Backup Compression