UMBC Training Centers logo

Maintaining a Microsoft SQL Server 2005 Database (Course 2780B)


Course Description | Outline | SQL Server Training | IT Training

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.


  • Preparing to Install SQL Server
  • Installing SQL Server 2005
  • Managing a SQL Server 2005 Installation

Lab : 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.


  • Planning Databases
  • Creating Databases
  • Managing Databases

Lab : 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.


  • 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 : 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.


  • Overview of SQL Server Security
  • Protecting the Server Scope
  • Protecting the Database Scope
  • Managing Keys and Certificates in SQL Server

Lab : 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.


  • Viewing Current Activity
  • Using System Monitor
  • Using SQL Server Profiler
  • Using DDL Triggers
  • Using Event Notifications

Lab : 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.


  • Overview of Data Transfer
  • Introduction to SQL Server Integration Services
  • Using SQL Server Integration Services
  • Features of SQL Server Integration Services

Lab : 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.


  • 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 : 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.


  • Overview of Replication
  • Implementing Replication
  • Configuring Replication in Some Common Scenarios

Lab : 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.


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

Lab : Maintaining High Availability

  • 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.