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 Microsoft 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 SQL Server 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. |
