Module: 1

SQL Server Architecture

Introduction to SQL Server 2008 Overview on RDBMS and Beyond Relational

System Databases
What’s New in SQL Server 2008

SQL Server Component Overview

The Relational Engine
The Command Parser
The Query Optimizer
The SQL Manager
The Database Manager
The Query Executor
The Storage Engine

Pages & Extents

Pages
Extents [Uniform & Mixed]
Managing Extent Allocations
Tracking Free Space

Files and File groups

Database FilesDatabase File groups [Primary & User-defined]
Primary data files
Secondary data files
Log file

Memory Architecture

32-bit Vs 64-bit Architecture
Dynamic Memory Management
Effects of min and max server memory
Buffer Management
Using AWE
The Buffer Pool and the Data Cache
Checkpoints

Thread and Task Architecture

Allocating Threads to a CPU
Using the lightweight pooling Option
Thread and Fiber Execution
Hot Add CPU

Module: 2
Installing, Upgrading, Configuration, Managing services & Migration

SQL server 2005/2008 Installation

Planning the System/Pre-Requisites
Installing SQL server 2005
Installing SQL server 2008
Installing Analysis Services
Installing & Configuring Reporting Services
Best Practices on Installation
Uninstalling SQL server
Common Installation Issues
Practical Troubleshooting on a Failed Installation

Upgrading to SQL server 2005/2008

Upgrading the server by applying service packs
Upgrading the server by applying Hot fixes
In-Place Up gradation from SQL server 2005 to 2008
Pre-Upgrade Checks
Upgrade advisor
Best Practices to follow while upgrading

Configuring SQL Server

Configuring Network Protocols from SQL Server configuration manager
Configuring features by using SQL Sever surface area configuration manager
Configuring other settings through SP_Configure
Dedicated Administrator Connection
Connecting to DAC
Configuring Database Settings
Configuring Memory Settings
Configuring Database Mail
Configuring Tempdb
Best Practices on configuration/Database settings

Managing services

Starting and Stopping Services throughStart Up parameters
Configuration manager
Net Command
Command Prompt [sqlsrvr.exe
Starting SQL server in single user mode
Starting SQL server with minimal configuration

Migrating SQL server

Side-By- Side Migration Techniques
Difference between in-place & Side by Side Migration/Upgradation
Advantages/Disadvantages of In-Place to Side-by-Side
Migrating DatabasesMigrating Logins [Fixing Orphaned Users]
Migration by using Attach and Detach Method
Migration by using Back and restore method
Migration by using Copy Database Wizar
Migrating Jobs
Migrating DTS Packages to SSIS
Import & Export

Module: 3
Security, Automation & Monitoring

Security

Security Principles
Server Roles
Server and Database Principles
Database Roles
Creating Logins and mapping Users to databases
Creating Schemas
Server & Database Securable
Granting to Object level Permissions
Best Practices on security

Automating Administrative Tasks

About SQL server Agent
Creating Jobs, Alerts and Operators
Scheduling the Jobs
Creating Maintenance Plans
Working with Job activity Monitor
Resolving failure Jobs
Best Practices on Job maintenance

Monitoring SQL Server

The Goal of Monitoring
Choosing the Appropriate Monitoring Tools
Monitoring SQL Server process by activity Monitor
Monitoring Job activities by Job activity Monitor
Monitoring SQL Server Error Logs/Windows by Log File Viewer
Best Practices on Monitoring

Module: 4
High Availability and Replication

T-Log Architecture

Transaction Log Logical Architecture
Transaction Log Physical Architecture
Checkpoint Operation
Write-Ahead Transaction Log
Managing T-log

Backup & Restore:

Recovery Models [Simple, Bulk-Logged & Full]
How Backup Works
Types of backupsCompressed backups
Full backup
Diff backup
T-log backup
Copy Only
Mirror
Tail-Lo
Restoring Modes [With Recovery, No Recovery, Read only/Standby]
Performing Restore (point-in-time recovery)
Disaster Recovery Planning
Case study on developing and executing a Backup Plan
Resolving Backup failures in Real time scenarios
Best Practices on Backup & Recovery

Log Shipping

Log-Shipping Architecture
Building DRS for log-shipping
Pre-requisites/Log-Shipping Process
Deploying Log Shipping
Working with Log Shipping Monitor
Logs hipping Role changing [Fail-Over]
Removing Log Shipping
Frequently Raised Errors In Log-Shipping
Best Practices on Log-Shipping

Database Mirroring

Overview of Database Mirroring
Operating Modes in Database Mirroring
Pre-Requisites for Database Mirroring
Deploying Database Mirroring
Fail-Over from Principle to Mirror
Working with Database mirroring monitor
Advantages & Disadvantages of database mirroring
Database Snapshots
Using Database Snapshots for reporting purposes.
Best practices on Mirroring

Replication

Replication Overview
Replication Models (snapshot/Transactional/Merge)
Replication agents
Configuring Distributor
Deploying Transactional Replication for High Availability
Creating Subscriptions [Homogeneous / heterogeneous]
Monitoring Replication by using replication monitor
Scripting Replication
Best Practices on Replication

Clustering SQL server

What is a cluster & Overview of Windows Clustering
SQL Server Clustering concepts
Installing and configuring SQL Server 2005 clustering
Installing Services pack & Hot fixes on a cluster
Cluster administrator
Moving Groups between nodes
Installing SQL Server 2008 on a Windows Server 2008 cluster
Adding a node on a SQL Server 2008 Failover Cluster
Applying patches on a SQL Server 2008 cluster
Best Practices on clustering

Module: 5
Performance Tuning & Indexing

Indexing

Index Architecture
How to optimally take advantage of indexes
Clustered & Non-Clustered indexes
Index Fragmentation
Index Defragmentation options\update Statistics
How to determine fragmentation
Best Practices on Indexing

Performance Tuning

Factors That Impact Performance
Configuring Server and Agent property settings
Tools used Activity Monitor, SQL Profiler, Database Tuning Advisor
Working with Activity Monitor
Blocking [SP_Who2]
SQL Profiler [How to capture events data by using Profiler]
Deadlocks and deadlock chain detection.
Analyzing the data by using DTA [Database Tuning Advisor]
Performance Monitor [System Monitor]
Dynamic Management Views (DMV)
Best Practices on Performance Tuning

Practical Trouble shooting on Performance

Case Study A: Performance Counters Setup-Collect-Analyze

Case Study B: Performance Counters- Thresholds

Case Study C: Performance Tuning on OLAP processing

Module: 6
Corporate awareness and Handling the environment

Defining the process
How they implement the security
How they implement the process by using CRM tools
Knowledge on MSE and other CRM tools.