Updating Your Database Administration Skills to Microsoft SQL Server 2005

Prerequisites
Before attending this course, students must have:

MCDBA certification or equivalent knowledge.

Administration experience with Microsoft SQL Server 2000 or Microsoft SQL Server 7.0.

Knowledge of SQL Server 2000/7.0:

Database and server architecture.

Installation and configuration.

Database security.

Database recovery (backup and restore).

SQL Server technologies used for database availability such as log shipping and clustering.

Job automation.

Database and server monitoring.

Troubleshooting.

Replication.
In addition, it is recommended, but not required,that students have completed:

Course 2072 , Administering a Microsoft SQL Server 2000 Database.

Microsoft Certified Professional Exams
No Microsoft Certified Professional exams areassociated with this course currently.

Course Materials
The student kit includes a comprehensive workbookand other necessary materials for this class.

Course Outline
Module1: Overview of SQL Server 2005 Architecture
This module introduces students to the variouscomponents and services that comprise SQL Server. Database administrators areoften required to manage multiple aspects of the database server - not just therelational database engine, so an awareness of the components and functionalityavailable in SQL Server 2005 is essential in order to prepare databaseadministrators for SQL Server 2005 deployments and management.
Lessons

Overview of SQL Server 2005

System Enhancements for Administrators
After completing this module, students will be ableto:

Describe the components of SQL Server 2005.

Describe new and enhanced features for administrators in SQL Server 2005.

Module2: Installing SQL Server 2005
This module explains how to plan for a SQL Server2005 installation, install SQL Server 2005 and maintain a SQL Server 2005installation.
Lessons

Preparing to Install SQL Server 2005

Performing a SQL Server 2005 Installation

Maintaining a SQL Server 2005 Installation
Lab2: Installing SQL Server 2005

Installing a SQL Server Instance.
After completing this module, students will be ableto:

Plan a SQL Server 2005 installation.

Install SQL Server 2005.

Maintain a SQL Server 2005 installation.

Module3: SQL Server 2005 Administrative Tools
This module explains what built-in administration toolsand utilities are available in Microsoft SQL Server 2005 and how they can beused to help database administrators be successful in managing a SQL Server2005 solution.
Lessons

Using SQL Server Management Studio

Using SQL Server Computer Manager

Using the sqlcmd Utility

Using the SQL Management Objects
Lab3: Administering SQL Server 2005

Creating the Personnel database.

Populating the Personnel database.

Backing Up the Personnel Database.
After completing this module, students will be ableto:

Choose the appropriate tool or utility to perform an administrative task.

Use SQL Server Management Studio to manage databases.

Use SQL Server Computer Manager to manage a server.

Use the sqlcmd utility to execute Transact-SQL from the command prompt.

Use SQL Management Objects to script common administrative tasks.

Module4: Monitoring SQL Server 2005
This module discusses the various tools andtechniques you can use to monitor Microsoft SQL Server 2005.
Lessons

Using SQL Profiler

Using DDL Triggers

Using Event Notifications

Using Metadata Views
Lab4: Monitoring SQL Server 2005

Using Profiler

Creating DDL Triggers

Creating Event Notifications
After completing this module, students will be ableto:

Use SQL Profiler to monitor SQL Server 2005.

Use DDL triggers to respond to DDL events.

Use event notifications to respond to database events.

Query SQL Server metadata.

Module5: Maintaining Databases and Indexes
This module explains the tools and utilities inMicrosoft SQL Server 2005 that can help you implement database maintenanceplans, manage database indexes, and tune databases.
Lessons

Managing Indexes

Using the Database Tuning Advisor
Lab5: Maintaining a Database

Defining Indexes

Tuning a Database
After completing this module, students will be ableto:

Maintain indexes in a SQL Server 2005 database.

Use the Database Tuning Advisor to assist with database design.

Maintain SQL Server 2005 databases.

Module6: Securing SQL Server 2005
This module explains the changes and enhancementsto the Microsoft SQL Server security model in SQL Server 2005.
Lessons

SQL Server 2005 Security Overview

Managing SQL Server 2005 Security

Managing Permissions

Managing Certificates
Lab6: Securing a SQL Server 2005 Database

Managing securables

Managing principals

Managing Permissions

Dropping a User that Created Objects.
After completing this module, students will be ableto:

Describe the security architecture of SQL Server 2005.

Manage securables and principals.

Manage permissions.

Use certificates in SQL Server 2005.

Module7: Disaster Recovery
This module you will learn about the enhanced backupand restore functionality in Microsoft SQL Server 2005.
Lessons

Disaster Recovery in SQL Server 2005

Using Database Snapshots

Backup and Restore Operations
Lab7: Managing Disaster Recovery

Creating a Database Snapshot

Backing up a Database
After completing this module, students will be ableto:

Describe common disaster recovery enhancements in SQL Server 2005.

Use database snapshots.

Perform backup and restore operations.

Module8: Data Availability in SQL Server 2005
This module teaches you how to take advantage ofthe high availability features found in SQL Server 2005
Lessons

Introduction to Data Availability

Introduction to Database Mirroring

Configuring Database Mirroring
Lab8: Maintaining Availability

Preparing the Principal and Mirror Server

Establishing a Mirroring Session

Establishing a Witness Server

Executing Transactions

Simulating Principal Server Failure

Restarting the Failed Server

Terminating the Mirror Session
After completing this module, students will be ableto:

Describe the data availability technologies included in SQL Server 2005.

Describe the concepts of database mirroring.

Configure database mirroring.

Module9: SQL Server 2005 Replication
This module explains the new and enhancedreplication features in SQL Server 2005
Lessons

Overview of Replication Enhancements

New Types of Replication

Configuring Replication
After completing this module, students will be ableto:

Describe the enhancements to replication functionality in SQL Server 2005.

Describe the new types of replication available in SQL Server 2005.

Configure replication using SQL Server 2005.