This course is a soup-to-nuts course that will teach you everything you need to build, configure a server, maintain a SQL Server disaster recovery plan, and how to design and manage a secure solution. You'll learn how to automate daily, weekly, and hourly tasks (like backups), the details of security, SQL Server clustering, replication, disaster recovery, and using jobs and database maintenance tasks. This course consists of all practical trouble shooting issue which you arise in day to day activities. Each and every topic is covered with unique case studies. This course is divided into 5 modules and totals more than 40 hours of instructor-led online/onsite training for SQL Server 2008.

Audience

This course is intended for IT Professionals who administer and maintain SQL Server databases.

Course fee - 400$

For any queries on how to pay, mail us to billing@sqlservermasters.com

No prior experience is presumed.

Hands-on/Lecture Ratio

This class is 70% hands-on, 30% lecture.

Software Needed on Each Student PC

• Windows Server 2003 or 2008, Windows XP Professional, with at least 1GB RAM
• SQL Server 2008

Microsoft SQL Server 2008 DBA Outline:

Module-1 SQL Server Architecture

Introduction to SQL Server 2008

• Overview on RDBMS and Beyond Relational
• 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

• Pages
  • Primary data files
  • Secondary data files
  • Log files
• Extents [Uniform & Mixed]

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

Module-2 Installing, Upgrading, Configuration, Managing services and 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 Server and Agent property settings
• Configuring Database Settings
• Configuring Memory Settings
• Configuring Database Mail
• Configuring Alert system in SQL server agent
• Best Practices on configuration/Database settings

Managing services

• Starting and Stopping Services through
  • Configuration manager
  • Net Command
  • Command Prompt [sqlsrvr.exe]
• Start Up parameters
• 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 Databases
  • Migration by using Attach and Detach Method
  • Migration by using Back and restore method
  • Migration by using Copy Database Wizard
• Migrating Logins [Fixing Orphaned Users]
• Migrating Jobs
• Migrating DTS Packages
• 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

Monitoring SQL Server

• The Goal of Monitoring
• Choosing the Appropriate Monitoring Tools
• Monitoring Job activities
• Monitoring SQL Server Error Logs
• Monitoring Event 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 backups
  • Full backup
  • Diff backup
  • T-log backup
  • Copy Only
  • Mirror
  • Tail-Log
  • Compressed backups
• 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
• Best practices on Mirroring
• Database Snapshots
• Using Database Snapshots for reporting purposes.

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/2008 clustering
• Installing Services pack & Hot fixes on a cluster
• Cluster administrator
• Moving Groups between nodes
• 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
• 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

After completing this course, students will be able to:

• Install and configure SQL Server 2008.
• Manage database files.
• Backup and restore databases.
• Manage security.
• Managing indexes and finding performance bottlenecks
• Automate administrative tasks.
• Replicate data between SQL Server instances.
• Maintain high availability.[log-shipping, Mirroring]
• Clustering SQL Server.

All students will receive:

• More than 500 pages of fully-illustrated case studies courseware
• A copy of Guide to SQL Server Database Administration