Course 2780B:  Maintaining a Microsoft SQL Server 2005 Database

Location: Entre Technology Services, LLC  1501 N. 14th St. West, Suite 1511

Registration: Call your Account Manager at 406.256.5700 or use our Registration Forms

Course Specifications

Course Length:  5 days, Monday and Friday, 8:30 am – 4:30 pm

Location:  Entré Technology Services, LLC,1501 N. 14th St. West, Suite 1511

Introduction

Elements of this syllabus are subject to change.

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

Audience

This course is intended for IT Professionals who want 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.

Backup and restore databases.

Manage security.

Monitor SQL Server.

Transfer data into and out of SQL Server.

Automate administrative tasks.

Replicate data between SQL Server instances.

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 2005

Managing a SQL Server 2005 Installation

Lab 1: Installing and Configuring SQL Server 2005

Performing an Installation

Managing SQL Server

Module 2: Managing Databases and Files

This module explains how to manage databases and files.

Lessons

Planning Databases

Creating Databases

Managing Databases

Lab 2: Managing Databases and Files

Creating a Database

Monitoring and Managing Filegroup Usage

Viewing Database Metadata

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

Performing Online Restore Operations

Recovering Data from Database Snapshots

System Database and Disaster Recovery

Lab 3: Disaster Recovery

Implementing a Backup Strategy

Restoring and Recovering a Database

Performing Piecemeal Backup and Restore Operations

Restoring the master Database

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

Protecting the Server Scope

Protecting the Database Scope

Managing Keys and Certificates in SQL Server

Lab 4: Managing Security

Creating Logins and Assigning Server-Scope Permissions

Creating and Managing Users

Using a Certificate to Protect Data

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 Triggers

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

Features of SQL Server Integration Services

Lab 6: Transferring Data

Creating an SSIS Package

Deploying an SSIS Package

Using SSIS to Extract Data, Perform Lookups, Sort, and Split Data

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 Administrative Tasks

Configuring SQL Server Agent

Creating Operators and Jobs

Creating Alerts

Module 8: Implementing Replication

This module explains the purpose of replication, introduces the concepts underpinning replication, and describes how to implement replication in several common scenarios.

Lessons

Overview of Replication

Implementing Replication

Configuring Replication in Some Common Scenarios

Lab 8: Implementing Replication

Creating a Publication

Creating a Subscription

Implementing HTTP Merge Replication

Module 9: 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

Implementing Peer-to-Peer Replication

Lab 9:

Configuring Database Mirroring to Support Failover

Implementing Distributed High Availability