2780: Maintaining a Microsoft SQL Server 2005 Database

2780: Maintaining 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