2780 - Maintaining a Microsoft SQL Server 2005 Database

Five days; Instructor-Led

Introduction

Elements of this syllabus are subject to change.

This five-day instructor-led course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2005 database. The course focuses on teaching individuals how to use SQL Server 2005 product features and tools related to maintaining a database.

Audience

This course is intended for IT Professionals who want to become skilled on SQL Server 2005 product features and technologies for maintaining a database.

At Course Completion

After completing this course, students will be able to:

   •  Install and configure SQL Server 2005.

   •  Manage database files.

   •  Backup and restore databases.

   •  Manage security.

   •  Monitor SQL Server.

   •  Transfer data into and out of SQL Server.

   •  Automate administrative tasks.

   •  Replicate data between SQL Server instances.

   •  Maintain high availability.

Prerequisites

Before attending this course, students must have:

   •  Basic knowledge of the Microsoft Windows operating system and its core functionality.

   •  Working knowledge of Transact-SQL.

   •  Working knowledge of relational databases.

   •  Some experience with database design.

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

   •  Course 2778: Writing Queries Using Microsoft SQL Server 2005 Transact-SQL.

   •  Course 2779: Implementing a Microsoft SQL Server 2005 Database.

Course Outline

Module 1: Installing and Configuring SQL Server 2005

This module explains how to plan for and install SQL Server 2005, how to manage a SQL Server 2005 installation, and how to use the SQL Server 2005 administrative tools.

Lessons

   •  Preparing to Install SQL Server

   •  Installing SQL Server 2005

   •  Managing a SQL Server 2005 Installation

Lab 1: Installing and Configuring SQL Server 2005

   •  Performing an Installation

   •  Managing SQL Server

  
After completing this module, students will be able to:

   •  Explain how to prepare the hardware and other resources necessary to install SQL Server 2005.

   •  Install SQL Server 2005.

   •  Manage and configure a SQL Server 2005 installation.

Module 2: Managing Databases and Files

This module explains how to manage databases and files.

Lessons

   •  Planning Databases

   •  Creating Databases

   •  Managing Databases

Lab 2: Managing Databases and Files

   •  Creating a Database

   •  Monitoring and Managing Filegroup Usage

   •  Viewing Database Metadata

After completing this module, students will be able to:

   •  Plan how to implement a database that meets an organization's requirements.

   •  Create a SQL Server database.

   •  Manage a SQL Server database.

Module 3: Disaster Recovery

This module explains how to plan and implement a backup and restore strategy.

Lessons

   •  Planning a Backup Strategy

   •  Backing Up User Databases

   •  Restoring User Databases

   •  Performing Online Restore Operations

   •  Recovering Data from Database Snapshots

   •  System Database and Disaster Recovery

Lab 3: Disaster Recovery

   •  Implementing a Backup Strategy

   •  Restoring and Recovering a Database

   •  Performing Piecemeal Backup and Restore Operations

   •  Restoring the master Database

After completing this module, students will be able to:

   •  Plan a backup strategy for a database.

   •  Back up user databases.

   •  Restore user databases from backups.

   •  Restore data in a user database while it is online.

   •  Recover data for a user database from a database snapshot.

   •  Restore and recover systems databases.

Module 4: Managing Security

This module explains how to manage principals, securables, and permissions, and how to implement cryptography in a SQL Server database.

Lessons

   •  Overview of SQL Server Security

   •  Protecting the Server Scope

   •  Protecting the Database Scope

   •  Managing Keys and Certificates in SQL Server

Lab 4: Managing Security

   •  Creating Logins and Assigning Server-Scope Permissions

   •  Creating and Managing Users

   •  Using a Certificate to Protect Data

After completing this module, students will be able to:

   •  Describe how SQL Server manages security.

   •  Protect SQL Server at the server level.

   •  Protect SQL Server databases.

   •  Use keys and certificates to protect SQL Server objects.

Module 5: Monitoring SQL Server

This module explains how to monitor SQL Server performance and activity.

Lessons

   •  Viewing Current Activity

   •  Using System Monitor

   •  Using SQL Server Profiler

   •  Using DDL Triggers

   •  Using Event Notifications

Lab 5: Monitoring SQL Server

   •  Monitoring SQL Server Performance

   •  Tracing SQL Server Activity

   •  Implementing DDL Triggers

After completing this module, students will be able to:

   •  Examine the current activity in a SQL Server instance.

   •  Use System Monitor to obtain performance data about your computer and the instances of SQL Server running on
       your computer.

   •  Use SQL Server Profiler to trace server and database activity.

   •  Implement DDL triggers that enable you to audit changes to the structure of database objects.

   •  Use event notifications to capture and monitor significant events for a SQL Server instance.

Module 6: Transferring Data

This module explains how to transfer and transform data.

Lessons

   •  Overview of Data Transfer

   •  Introduction to SQL Server Integration Services

   •  Using SQL Server Integration Services

   •  Features of SQL Server Integration Services

Lab 6: Transferring Data

   •  Creating an SSIS Package

   •  Deploying an SSIS Package

   •  Using SSIS to Extract Data, Perform Lookups, Sort, and Split Data

After completing this module, students will be able to:

   •  Describe the problems surrounding data transfer and the tools that SQL Server 2005 provides to perform data
       transfer.

   •  Describe the purpose of SQL Server Integration Services.

   •  Use SQL Server Integration Services to transfer data into a SQL Server database.

   •  Describe the features of SQL Server Integration Services.

Module 7: Automating Administrative Tasks

This module explains how to use the SQL Server Agent to automate administrative tasks.

Lessons

   •  Automating Administrative Tasks in SQL Server 2005

   •  Configuring the SQL Server Agent

   •  Creating Jobs and Operators

   •  Creating Alerts

   •  Managing Multiple Servers

   •  Managing SQL Server Agent Security

Lab 7: Automating Administrative Tasks

   •  Configuring SQL Server Agent

   •  Creating Operators and Jobs

   •  Creating Alerts

After completing this module, students will be able to:

   •  Define SQL Server 2005 administrative tasks and schedule these tasks to run automatically.

   •  Configure SQL Server Agent to support automatic task scheduling.

   •  Script tasks by using SQL Server jobs, and define operators for managing these jobs.

   •  Define alerts to warn operators about events raised by SQL Server.

   •  Define and manage administrative tasks that span multiple servers.

   •  Configure SQL Server Agent security.

Module 8: Implementing Replication

This module explains the purpose of replication, introduces the concepts underpinning replication, and describes how to implement replication in several common scenarios.

Lessons

   •  Overview of Replication

   •  Implementing Replication

   •  Configuring Replication in Some Common Scenarios

Lab 8: Implementing Replication

   •  Creating a Publication

   •  Creating a Subscription

   •  Implementing HTTP Merge Replication

After completing this module, students will be able to:

   •  Describe replication and its components.

   •  Configure and implement replication.

   •  Use replication to meet the requirements of some common scenarios.

Module 9: Maintaining High Availability

This module explains how to implement high availability technologies with SQL Server 2005.

Lessons

   •  Introduction to High Availability

   •  Implementing Server Clustering

   •  Implementing Database Mirroring

   •  Implementing Log Shipping

   •  Implementing Peer-to-Peer Replication

Lab 9:

   •  Configuring Database Mirroring to Support Failover

   •  Implementing Distributed High Availability

After completing this module, students will be able to:

   •  Describe the factors affecting database availability.

   •  Explain how to implement clustering to support fast failover of computers running Microsoft SQL Server instances.

   •  Describe how to use SQL Server mirroring to implement a software solution for fast failover.

   •  Describe how to implement log shipping to support fast recovery of a standby SQL Server database.

   •  Explain how to use peer-to-peer replication to implement high availability in a distributed environment.