Maintaining a Microsoft SQL Server 2005 Database
Course 2780B: Five days;
Instructor-Led
Course Fee:
$2495.00 (less 10% 4 - 7 day discount) = $2245.00
Course Time: 5 days, 9:00 am - 5:00 pm, or 10 evenings, 6pm - 9pm
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
|
After completing this module, students will be able
to:
| |
Explain how to prepare the hardware and other
resources necessary to install SQL Server 2005.
|
| |
Install SQL Server 2005.
|
| |
Manage and configure a SQL Server 2005
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: Managing Databases and Files
| |
Creating a Database
|
| |
Monitoring and Managing Filegroup Usage
|
| |
Viewing Database Metadata
|
After completing this module, students will be able
to:
| |
Plan how to implement a database that meets
an organization's requirements.
|
| |
Create a SQL Server database.
|
| |
Manage a SQL Server database.
|
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
|
After completing this module, students will be able
to:
| |
Plan a backup strategy for a database.
|
| |
Back up user databases.
|
| |
Restore user databases from backups.
|
| |
Restore data in a user database while it is
online.
|
| |
Recover data for a user database from a
database snapshot.
|
| |
Restore and recover systems 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
|
| |
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
|
After completing this module, students will be able
to:
| |
Describe how SQL Server manages security.
|
| |
Protect SQL Server at the server level.
|
| |
Protect SQL Server databases.
|
| |
Use keys and certificates to protect SQL
Server objects.
|
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
|
After completing this module, students will be able
to:
| |
Examine the current activity in a SQL Server
instance.
|
| |
Use System Monitor to obtain performance data
about your computer and the instances of SQL
Server running on your computer.
|
| |
Use SQL Server Profiler to trace server and
database activity.
|
| |
Implement DDL triggers that enable you to
audit changes to the structure of database
objects.
|
| |
Use event notifications to capture and
monitor significant events for a SQL Server
instance.
|
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
|
After completing this module, students will be able
to:
| |
Describe the problems surrounding data
transfer and the tools that SQL Server 2005
provides to perform data transfer.
|
| |
Describe the purpose of SQL Server
Integration Services.
|
| |
Use SQL Server Integration Services to
transfer data into a SQL Server database.
|
| |
Describe the features of SQL Server
Integration Services.
|
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
|
After completing this module, students will be able
to:
| |
Define SQL Server 2005 administrative tasks
and schedule these tasks to run automatically.
|
| |
Configure SQL Server Agent to support
automatic task scheduling.
|
| |
Script tasks by using SQL Server jobs, and
define operators for managing these jobs.
|
| |
Define alerts to warn operators about events
raised by SQL Server.
|
| |
Define and manage administrative tasks that
span multiple servers.
|
| |
Configure SQL Server Agent security.
|
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
|
After completing this module, students will be able
to:
| |
Describe replication and its components.
|
| |
Configure and implement replication.
|
| |
Use replication to meet the requirements of
some common scenarios.
|
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
|
After completing this module, students will be able
to:
| |
Describe the factors affecting database
availability.
|
| |
Explain how to implement clustering to
support fast failover of computers running
Microsoft SQL Server instances.
|
| |
Describe how to use SQL Server mirroring to
implement a software solution for fast failover.
|
| |
Describe how to implement log shipping to
support fast recovery of a standby SQL Server
database.
|
| |
Explain how to use peer-to-peer replication
to implement high availability in a distributed
environment.
|
|
1.888.326.6886 
