IT Online Training and Placements in USA.

Microsoft Online Training

MS Exchange Server 2003, BizTalk Server, MS Office Sharepoint 2007

 

Courses » Microsoft Courses » Microsoft 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]
    1. Primary data files
    2. Secondary data files
    3. 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
    1. Configuration manager
    2. Net Command
    3. 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]
    1. Migration by using Attach and Detach Method
    2. Migration by using Back and restore method
    3. 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
    1. Full backup
    2. Diff backup
    3. T-log backup
    4. Copy Only
    5. Mirror
    6. 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.