Maintaining a Microsoft SQL Server 2005 Database
Introduction
This 40-hour instructor-led course provides students with product knowledge and skills needed to maintain a Microsoft SQL ServerT 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 wanting 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.
|
| • |
Manage security.
|
| • |
Perform administrative tasks.
|
| • |
Back up databases.
|
| • |
Restore databases.
|
| • |
Monitor SQL Server.
|
| • |
Troubleshoot SQL Server.
|
| • |
Transfer data.
|
| • |
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.
|
| • |
Managing a SQL Server 2005 Installation.
|
Lab 1: Installing SQL Server 2005
| • |
Performing an Installation.
|
| • |
Managing SQL Server.
|
After completing this module, students will be able to:
| • |
Prepare to install SQL Server.
|
| • |
Install SQL Server.
|
| • |
Manage a SQL Server 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: Creating and Managing Databases
| • |
Creating a Database.
|
| • |
Managing Filegroups.
|
| • |
Viewing Metadata.
|
After completing this module, students will be able to:
| • |
Plan databases.
|
| • |
Create databases.
|
| • |
Manage databases.
|
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.
|
| • |
Recovering Data from Database Snapshots.
|
| • |
System Database and Disaster Recovery.
|
Lab 4: Implementing a Disaster Recovery Strategy
| • |
Implementing a Backup Strategy.
|
| • |
Restoring Databases.
|
| • |
Rebuild The Master Database.
|
After completing this module, students will be able to:
| • |
Plan a backup strategy.
|
| • |
Back up user databases.
|
| • |
Restore user databases.
|
| • |
Recover data from database snapshots.
|
| • |
Back up and restore system 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.
|
| • |
Securing the Server Scope.
|
| • |
Securing the Database Scope.
|
| • |
Managing Keys and Certificates in SQL Server.
|
Lab 3: Securing SQL Server
| • |
Creating Logins.
|
| • |
Creating and Managing Users.
|
| • |
Using a Certificate to Encrypt Data.
|
After completing this module, students will be able to:
| • |
Describe the SQL Server security architecture.
|
| • |
Implement security at the server scope.
|
| • |
Implement security at the database and schema scopes.
|
| • |
Use cryptographic functionality in SQL Server.
|
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 Trigger.
|
After completing this module, students will be able to:
| • |
View current activity in SQL Server.
|
| • |
Monitor SQL Server performance with System Monitor.
|
| • |
Monitor SQL Server events with SQL Server Profiler.
|
| • |
Implement DDL Triggers.
|
| • |
Implement Event Notifications.
|
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.
|
Lab 6: Transferring Data with SQL Server Integration Services (SSIS)
| • |
Create an SSIS Package.
|
| • |
Deploying an SSIS Package.
|
After completing this module, students will be able to:
| • |
Describe the tools and technologies for transferring data.
|
| • |
Describe the main features of SSIS.
|
| • |
Transfer and transform data with SSIS.
|
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 Database Administration
| • |
Configuring the SQL Server Agent.
|
| • |
Creating Operators and Jobs.
|
| • |
Creating Alerts.
|
After completing this module, students will be able to:
| • |
Describe automation options in SQL Server.
|
| • |
Configure the SQL Server Agent.
|
| • |
Create jobs and operators.
|
| • |
Create alerts.
|
| • |
Manage multiple servers.
|
| • |
Manage SQL Server Agent security.
|
Module 8: 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.
|
Lab 8: Configuring Database Mirroring
| • |
Setting the Recovery Model.
|
| • |
Backing Up and Restoring the Database.
|
| • |
Starting Database Mirroring.
|
| • |
Performing an Automatic and Manual Failover.
|
After completing this module, students will be able to:
| • |
Describe high availability options for SQL Server.
|
| • |
Implement Server Clustering.
|
| • |
Implement Database Mirroring.
|
| • |
Implement Log Shipping.
|
Module 9: Introduction to Replication
This module explains considerations for implementing replication.
Lessons
| • |
Overview of Replication.
|
| • |
Replication Scenarios.
|
Lab 9: Implementing Replication
| • |
Creating a Publication.
|
| • |
Creating a Subscription.
|
After completing this module, students will be able to:
| • |
Describe replication and its components.
|
| • |
Describe common replication scenarios.
|
 Top of page
| 
