MS SQL Server 2008 Online Training
Microsoft SQL Server 2008 Online Training
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.