Microsoft SQL Server DBA

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
• Instances

Ø  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