Maintaining
a Microsoft SQL Server 2005 Database
Course 2780: Five days; Instructor-Led
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. |
Solutient
Corporation of Ohio
6133
Rockside Road, Suite 100 – Cleveland, OH
44131
FOR
MORE INFORMATION, CALL 216-654-0025