SQL Server 2005/2008

Introduction to SQL Server 2005/2008

• Overview on RDBMS and Beyond Relational
• System Databases
• What’s New in SQL Server 2008

Installation of SQL Server 2005/2008

• H/W Requirements
• S/W Requirements
• Multiple Instances
• User Databases
• SSMS Environment
• Trouble Shooting Techniques

Upgrading to SQL Server 2005/2008

• Upgrading the server by applying service packs
• Upgrading the server by Appling hot fixes
• In-place up gradation from SQL Server 2005 to 2008
• Upgrade Adviser

Pages & Extents

• Pages
• Extents
• Types of Extents
• Managing Extent Allocation
• Tracking Free Space

File & File groups

• Database files
• Primary Data files
• Secondary Data files
• Log files
• Database File groups (Primary & User-defined)

Memory Architecture

• 32-bit Vs 64-bit Architecture
• Dynamic Memory Management
• Effects of min and Max server memory
• Using AWE

Configuring SQL Server

• Configuring Network protocols from SQL Server Manager
• Configuring features by using SQL Server surface area Manager
• Configuring other settings through sp_configure
• Dedicated Administrator Connection (DAC)
• Connecting to DAC
• Configuring Database Mail
• Configuring Alert System in SQL Server agent
• Configuring Database Settings
• Configuring Memory Settings

Managing Services

• Starting and Stopping Services through

Configure Manger

• Net Command
• Command Prompt (sqlsrvr.exe)
• Start Up Parameter
• Starting SQL Server in single user mode
• Default Parameter and Advanced Parameter

Migrating SQL Server

• Difference between in-place & Side by Side
• Migrating by using Back and restore method
• Migrating logins
• Migrating Jobs
• Migrating DTS Packages to SSIS
• Import & Export

Security, Automation

• Security Principles
• Server and Database Principle
• Database Role
• Creating Logins and mapping Users to databases
• Creating Schemas

Automating Administrative Tasks

• About SQL Server Agent
• Creating jobs, Alert and Operators
• Scheduling the job
• Creating Maintenance Plans
• Working with job activity monitor

Monitoring SQL Server

• Monitoring SQL Server Process by activity Monitor
• Monitoring job activities by job activity Monitor
• Monitoring SQL Server Error Logs/Windows by log file viewer

High Availability and Replication

Backup & Restore

• Recovery Models (Simple, Full & Bulk – Logged)
• How Backups works
• Types of Backups
• Full Backup
• Diff Backup
• T-log Backup
• Copy Only
• Mirror
• Tail – Log
• Compressed Backups
• Restoring Modes (With Recovery, No Recovery, Read Only/Stand by)
• Performing Restore (Point –in – time Recovery)

Log Shipping

• Log Shipping Architecture
• Building DRS for Log – Shipping
• Pre – requisites/log-shipping Process
• Deploying Log Shipping
• Working with Log Shipping Monitor
• Log shipping role changing
• Removing Log Shipping

Database Mirroring

• Overview of Database Mirroring
• Operating Modes in Database Miring
• Pre – Requisites for Database Mirroring
• Deploying Database Mirroring
• Fail – over from principle to Mirror
• Working with Database Mirroring Monitor
• Advantage & Disadvantage of Database Mirroring
• Database Snapshots
• Using Database Snapshots for reporting purpose

Replication

• Replication Overview
• Replication Models (snapshot/Transactional/Merge)
• Replication agents
• Configuration Distributor
• Deploying Transaction Replication for High Availability
• Creating Subscriptions (Homogeneous / Heterogeneous)
• Monitoring replication by using replication monitor
• Scripting Replication

Clustering SQL Server

• What is a cluster & overview of windows Clustering
• SQL Server Clustering Concepts
• Installing Services pack & hot fixes on a cluster
• Installing and Configuring SQL Server 2005 clustering
• Moving Groups between nodes
• Cluster Administrator
• Adding a node on a SQL Server 2008 Failover cluster
• Applying patches on a SQL Server 2008 cluster

Performance Tuning & Indexing

• Index Architecture
• How to optimally take advantage of indexes
• Clustered & Non – clustered indexes
• Index fragmentation
• Indexing De-fragmentation options update Statistics