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.
For more information or to give feedback, send e-mail to info@OneApex.com or call 949-597-2100
 |
 |
 |
