Microsoft SQL Server 2008 Administration

5 days

Course Overview

This course is comprehensive and hands-on, it is aimed at quickly getting students familiar with the Microsoft SQL Server 2008 administration concepts, tools and utilities.

Course Objective

  • Perform an Installation of SQL Server 2008
  • Upgrade Previous Versions of SQL Server to SQL Server 2008
  • Configure SQL Server Post Installation
  • Plan and Create Databases
  • Configure and Manage Databases
  • Create and Manage Tables in a Database
  • Implement and Manage Data Integrity
  • Implement and Manage SQL Server Indexes
  • Implement and Manage Index Fragmentation
  • Create and Manage Partitions
  • Query Data In Partitions
  • Understand and Implement SQL Server 2008 Security
  • Encrypt Data in a Database
  • Implement Security Auditing
  • Implement Policy-Based Management
  • Understand Data Transfer Utilities
  • Backup and Restore Databases
  • Create and Implement Database Snapshots
  • Automate Tasks in SQL Server 2008
  • Use the SQL Server 2008 Tools and Utilities to Monitor Performance
  • Understand the Concepts of Maintaining High Availability
  • Implement Database Mirroring
  • Implement Log Shipping
  • Troubleshoot and Performance Tune SQL Server 2008
  • Understand and Implement Replication

Target student

This 5 day course is intended for students who need to learn the skills necessary to maintain a Microsoft SQL Server 2008 database infrastructure. Being comprehensive and hands-on, it is aimed at quickly getting students familiar with the Microsoft SQL Server 2008 administration concepts, tools and utilities.

Prerequisites

No previous knowledge of SQL Server 2008 is assumed although an understanding of
relational database concepts would be helpful. An understanding of working in a Microsoft Windows operating system environment. An understanding of relational databases and design principles. Some experience of the SQL language would be beneficial

Course Outline

Install and Configure SQL Server 2008

  • Installation Prerequisites
  • The Installation Process
  • Upgrading from Previous Versions
  • Post Install Configuration Options

Creating, Configuring and Managing Databases

  • Planning to Implement a Database
  • Creating a Database
  • Creating and Managing Filegroups
  • Configuring and Managing a Database
  • Database Schemas

Creating and Managing Tables

  • Creating a Table
  • Native SQL Server Data Types
  • Columns and Column Properties
  • User-Defined Types

Implementing and Managing Data Integrity

  • Introducing Data Integrity
  • Using Constraints to Implement Data Integrity
  • Using Triggers to Implement Data Integrity

Creating and Managing Indexes

  • SQL Server Index Architecture
  • Creating Clustered Indexes
  • Creating Non-Clustered Indexes
  • Included Columns
  • Managing Index Fragmentation
  • Managing Statistics

Creating and Managing Partitions

  • Partition Functions
  • Partition Schemes
  • Partitioning Tables and Indexes
  • Query Data in Partitions
  • Managing Partitions

SQL Server 2008 Security

  • Overview of SQL Server Security
  • Security at the Server Level
  • Security at the Database Level
  • Encrypting Data
  • Security Auditing

Policy-Based Management

  • Designing Policies
  • The Central Management Server
  • Importing and Exporting Policies

Transferring Data

  • Overview of Data Transfer
  • Examining the Data Transfer Utilities
  • Bulk Loading Data
  • Using the Import/Export Wizard
  • Introducing SQL Server Integration Services(SSIS)

Backups and Recovery

  • Backup Types and Strategies
  • Backing up a Database
  • Restoring a Database
  • Online Restore Operations
  • Database Snapshots
  • Recovering System Databases

SQL Server Automation

  • Overview of SQL Server Automation
  • The SQL Server Agent Service
  • Creating Maintenance Plans
  • Creating and Managing Jobs
  • Creating and Managing Operators
  • Creating and Managing Alerts
  • Managing Multiple Servers
  • Managing SQL Server Agent Security

Monitoring SQL Server

  • Overview of Performance Monitoring
  • Viewing the Current Activity
  • Using System Monitor
  • Using SQL Server Profiler
  • Using DDL Triggers
  • Using Event Notifications

Maintaining High Availability

  • Introduction to High Availability
  • Implementing Server Clustering
  • Implementing Database Mirroring
  • Implementing Log Shipping

Troubleshooting and Performance Tuning

  • Troubleshooting SQL Server
  • Performance Tuning in SQL Server
  • Using the Database Engine Tuning Advisor
  • Using Resource Governor
  • Using Data Collector

Introduction to Replication

  • Overview of Replication
  • Replication Types and Models
  • Configuring and Implementing Replication
  • Creating Publications
  • Creating Subscriptions
  • Snapshot Replication
  • Peer-to-Peer Transactional Replication
  • HTTP Merge Replication

Querying Full Text Indexes (Optional item)

  • Overview of Full Text Indexes
  • Full Text Indexing and the Querying Process
  • How SQL Server Implements Full Text Indexes
  • Overview of Full Text Search
  • Using the CONTAINS Predicate
  • Using the FREETEXT Predicate
  • Using the Full Text Functions
  • Differences Between Full Text Functions andPredicates
  • Combining Full Text Search and Transact-SQLPredicates