Maintaining a Microsoft SQL Server 2005 Database

This course is intended for IT Professionals wanting to become skilled on SQL Server 2005 product features and technologies for maintaining a database.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.