Course: MS2780 Maintaining a Microsoft SQL Server 2005 Database

Please note: Elements of this syllabus are subject to change.

This five-day instructor-led course provides students with product knowledge and skills needed 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

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.
- Creating Schemas.
- Configuring a Database.

After completing this module, students will be able to:

- Plan databases.
- Create databases.
- Manage databases.

Module 3: 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.
- Implementing Cryptography in SQL Server.

Lab 3: Securing SQL Server

- Creating Logins.
- Creating and Managing Users.
- Implementing Cryptography.

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 4: 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:

- Backup user databases.
- Restore user databases.
- Recover data from database snapshots.
- Backup and restore system databases.

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.
- Monitoring SQL Server With SQL Server Profiler.
- Implementing DDL Trigger.
- Implementing Event Notifications.

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 (SSIS).
- Using SQL Server Integration Services.

Lab 6: Transferring Data with SQL Server Integration Services

- 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 SQL Server Integration Services.
- 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.
- Create 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.
- Managing Multiple Servers.

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

- Set the Recovery Model
- Backup and Restore
- Start Database Mirroring
- Perform 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.

Lab 9: Implementing Replication

- Creating a Publication.
- Creating A Subscription.

After completing this module, students will be able to:

- Describe considerations for implementing replication.