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.

• SAP
• Microsoft Technologies
• Oracle Apps
• Testing tools
• Peoplesoft
• Data Warehouse
• Network/System Administration
• Databases
• Middleware Technologies
• Other Technologies