MS2780 Maintaining a Microsoft SQL Server 2005 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.

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.

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.

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.

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.

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.

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.

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.

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.

After completing this module, students will be able to:

• Describe replication and its components.
• Describe common replication scenarios.