Get the knowledge and skills you need to maintain a Microsoft SQL Server 2005 database in this 5-day course. Learn how to use SQL Server 2005 product features and tools related to maintaining a database.

This course incorporates material from the following Official Microsoft Learning Product (OMLP):

• Course 2780: Maintaining a Microsoft^® SQL Server 2005 Database

What You'll Learn

• 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 using SSIS
• Automate administrative tasks
• Replicate data between SQL Server instances
• Maintain high availability

Who Needs to Attend

IT professionals who want to become skilled on SQL Server 2005 product features and technologies for implementing a database.

Prerequisites

• Basic knowledge of the Microsoft Windows operating system and its core functionality
• Working knowledge of Transact-SQL or course 6136: SQL Server 2005 T-SQL with Advanced Topics
• Working knowledge of relational databases
• Some experience with database design

Follow-On Courses

There are no follow-ons for this course.

Course Outline

1. Installing and Configuring SQL Server 2005

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

2. Managing Databases and Files

• Planning Databases
• Creating Databases
• Managing Databases

3. Disaster Recovery

• 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

4. Managing Security

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

5. Monitoring SQL Server

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

6. Transferring Data

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

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

8. Implementing Replication

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

9. Maintaining High Availability

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

Labs

Lab 1: Installing and Configuring SQL Server 2005

• Performing an Installation
• Managing SQL Server

Lab 2: Managing Databases and Files

• Creating a Database
• Monitoring and Managing Filegroup Usage
• Viewing Database Metadata

Lab 3: Disaster Recovery

• Implementing a Backup Strategy
• Restoring and Recovering a Database
• Performing Piecemeal Backup and Restore Operations
• Restoring the master Database

Lab 4: Managing Security

• Creating Logins and Assigning Server-Scope Permissions
• Creating and Managing Users
• Using a Certificate to Protect Data

Lab 5: Monitoring SQL Server

• Monitoring SQL Server Performance
• Tracing SQL Server Activity
• Implementing DDL Triggers

Lab 6: Transferring Data

• Creating an SSIS Package
• Deploying an SSIS Package
• Using SSIS to Extract Data, Perform Lookups, Sort, and Split Data

Lab 7: Automating Administrative Tasks

• Configuring SQL Server Agent
• Creating Operators and Jobs
• Creating Alerts

Lab 8: Implementing Replication

• Creating a Publication
• Creating a Subscription
• Implementing HTTP Merge Replication

Lab 9: Maintaining High Availability

• Configuring Database Mirroring to Support Failover
• Implementing Distributed High Availability