Introduction
This five-day instructor-led course
provides students with the knowledge and skills to maintain
a Microsoft SQL Server 2008 database. The course focuses on
teaching individuals how to use SQL Server 2008 product
features and tools related to maintaining a database.
Audience
This course is intended for IT
Professionals who administer and maintain SQL Server
databases.
At Course Completion
After completing this course, students
will be able to:
| • |
Install and configure SQL Server
2008.
|
| • |
Manage database files.
|
| • |
Backup and restore databases.
|
| • |
Manage security.
|
| • |
Transfer data into and out of SQL
Server.
|
| • |
Automate administrative tasks.
|
| • |
Replicate data between SQL Server
instances.
|
| • |
Maintain high availability.
|
| • |
Monitor SQL Server.
|
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.
|
Course Outline
Module 1: Installing and Configuring SQL
Server
The students will be introduced to
planning for a SQL Server installation. The students will
then be introduced to installing, configuring, and managing
SQL Server.
Lessons
| • |
Lesson 1: Preparing to Install SQL
Server
|
| • |
Lesson 2: Installing SQL Server
|
| • |
Lesson 3: Configuring a SQL Server
Installation
|
Lab: Installing and Configuring SQL Server
| • |
Exercise 1: (Level 200) Installing
SQL Server
|
| • |
Exercise 2: (Level 200)
Configuring 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.
|
| • |
Install SQL Server.
|
| • |
Manage and configure SQL Server.
|
Module 2: Managing Databases and Files
The students will be introduced to
database planning and creation, and using database options
to control database behavior.
Lessons
| • |
Lesson 1: Planning Databases
|
| • |
Lesson 2: Creating Databases
|
| • |
Lesson 3: Using Policy-Based
Management
|
Lab: Managing Databases and Files
| • |
Exercise 1: (Level 200): Creating
a Database
|
| • |
Exercise 2: (Level 200):
Monitoring and Managing Filegroup Usage
|
| • |
Exercise 3: (Level 200) Creating a
Policy
|
After completing this module, students
will be able to:
| • |
Plan a database implementation
that meets an organization's requirements.
|
| • |
Create a SQL Server database.
|
| • |
Manage a SQL Server database.
|
Module 3: Disaster Recovery
The students will be introduced to
disaster recovery techniques for SQL Server. They will learn
how to perform different types of backup and restore
operations, including online restores and backup and
restores of system databases.
Lessons
| • |
Lesson 1: Planning a Backup
Strategy
|
| • |
Lesson 2: Backing Up User
Databases
|
| • |
Lesson 3: Restoring User Databases
|
| • |
Lesson 4: Performing Online
Restore Operations
|
| • |
Lesson 5: Recovering Data from
Database Snapshots
|
| • |
Lesson 6: System Databases and
Disaster Recovery
|
Lab: Disaster Recovery
| • |
Exercise 1: (Level 200) Designing
a Backup Strategy
|
| • |
Exercise 2: (Level 200)
Implementing a Backup Strategy
|
| • |
Exercise 3: (Level 200) Restoring
and Recovering a Database
|
| • |
Exercise 4: (Level 300) Performing
Piecemeal Backup and Restore Operations
|
| • |
Exercise 5: (Level 200) 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 system
databases.
|
Module 4: Managing Security
The students will be introduced to
protecting SQL Server. Students will learn about the SQL
Server security model and how to use SQL Server security
features to control access to databases and their contents.
Lessons
| • |
Lesson 1: Overview of SQL Server
Security
|
| • |
Lesson 2: Protecting the Server
Scope
|
| • |
Lesson 3: Protecting the Database
Scope
|
| • |
Lesson 4: Protecting the Server
Scope
|
| • |
Lesson 5: Auditing Security
|
Lab: Managing Security
| • |
Exercise 1: (Level 200) Creating
Logins and Assigning Server-Scope Permissions
|
| • |
Exercise 2: (Level 200) Creating
and Managing Users
|
| • |
Exercise 3: (Level 300) Using a
Certificate to Protect Data
|
| • |
Exercise 4: (Level 200)
Implementing SQL Server Audit
|
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.
|
| • |
Audit SQL Server security.
|
Module 5: Transferring Data
The students will be introduced to
transfer data to and from SQL Server using UI and
command-line tools, and learn about transferring and
transforming data with SQL Server Integration Services.
Lessons
| • |
Lesson 1: Overview of Data
Transfer
|
| • |
Lesson 2: Introduction to SQL
Server Integration Services
|
Lab: Transferring Data
| • |
Exercise 1: (Level 200) Using the
Import/Export Wizard
|
| • |
Exercise 2: (Level 200) Performing
a Bulk Load
|
| • |
Exercise 3: (Level 200) Creating
an SSIS Solution
|
After completing this module, students
will be able to:
| • |
Use UI and command-line tools to
import and export data.
|
| • |
Describe the features of SQL
Server Integration Services.
|
Module 6: Automating Administrative Tasks
The students will learn how to automate
routine administrative tasks using jobs, operators, and
alerts.
Lessons
| • |
Lesson 1: Automating
Administrative Tasks in SQL Server
|
| • |
Lesson 2: Using SQL Server Agent
|
| • |
Lesson 3: Creating Maintenance
Plans
|
| • |
Lesson 4: Implementing Alerts
|
| • |
Lesson 5: Managing Multiple
Servers
|
| • |
Lesson 6: Managing SQL Server
Agent security
|
Lab: Automating Administrative Tasks
| • |
Exercise 1: (Level 200)
Configuring SQL Server Agent
|
| • |
Exercise 2: (Level 200) Creating
Operators and Jobs
|
| • |
Exercise 3: (Level 200) Creating
Alerts
|
After completing this module, students
will be able to:
| • |
Define SQL Server administrative
tasks and schedule those 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 those 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 7: Implementing Replication
The students will be introduced to
techniques for configuring SQL Server replication.
Lessons
| • |
Lesson 1: Overview of Replication
|
| • |
Lesson 2: Managing Publications
and Subscriptions
|
| • |
Lesson 3: Configuring Replication
in Some Common Scenarios
|
Lab: Implementing Replication
| • |
Exercise 1: (Level 300)
Implementing Snapshot Replication
|
| • |
Exercise 2: (Level 300)
Implementing Peer-to-Peer Transactional Replication
|
| • |
Exercise 3: (Level 300)
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 8: Maintaining High Availability
The students will be introduced to
concepts and methods for maintaining high availability with
SQL Server.
Lessons
| • |
Lesson 1: Introduction to High
Availability
|
| • |
Lesson 2: Implementing Log
Shipping
|
| • |
Lesson 3: Implementing Database
Mirroring
|
| • |
Lesson 4: Implementing Server
Clustering
|
| • |
Lesson 5: Using Distributed High
Availability Solutions
|
Lab: Maintaining High Availability
| • |
Exercise 1: (Level 300)
Configuring Log Shipping
|
| • |
Exercise 2: (Level 300)
Configuring Database Mirroring
|
| • |
Exercise 3: (Level 300)
Implementing SQL Server Clustering
|
After completing this module, students
will be able to:
| • |
Describe the factors affecting
database availability.
|
| • |
Describe how to implement log
shipping to support fast recovery of a standby SQL
Server database
|
| • |
Describe how to use SQL Server
mirroring to implement a software solution for fast
failover
|
| • |
Explain how to implement
clustering to support fast failover of computers
running SQL Server instances.
|
| • |
Describe how to implement
distributed high availability solutions.
|
Module 9: Monitoring SQL Server
The students will be introduced to
monitoring SQL Server performance and activity.
Lessons
| • |
Lesson 1: Viewing Current Activity
|
| • |
Lesson 2: Using SQL Server
Profiler
|
| • |
Lesson 3: Monitoring with DDL
Triggers
|
| • |
Lesson 4: Using Event
Notifications
|
Lab: Monitoring SQL Server
| • |
Exercise 1: (Level 200) Monitoring
SQL Server Activity
|
| • |
Exercise 2: (Level 300) Tracing
SQL Server Activity
|
| • |
Exercise 3: (Level 200) Using DDL
Triggers
|
| • |
Exercise 4: (Level 200) Using
Event Notifications
|
After completing this module, students
will be able to:
| • |
Examine the current activity in a
SQL Server instance.
|
| • |
Use SQL Server Profiler to trace
server and database activity.
|
| • |
Use DDL triggers to monitor
changes to the structure of database objects.
|
| • |
Use event notifications to capture
and monitor significant events for a SQL Server
instance.
|
Module 10: Troubleshooting and Performance
Tuning
The students will learn how to
troubleshoot a variety of common SQL Server problems.
Students will also learn how to tune SQL Server for improved
performance using a variety of tools.
Lessons
| • |
Lesson 1: Troubleshooting SQL
Server
|
| • |
Lesson 2: Performance Tuning in
SQL Server
|
| • |
Lesson 3: Using Resource Governor
|
| • |
Lesson 4: Using Data Collector
|
Lab: Troubleshooting and Performance
Tuning
| • |
Exercise 1: (Level 300)
Troubleshooting Connectivity Problems
|
| • |
Exercise 2: (Level 300)
Troubleshooting Concurrency Problems
|
| • |
Exercise 3: (Level 300) Using the
Database Engine Tuning Advisor
|
| • |
Exercise 4: (Level 300)
Implementing Resource Governor
|
| • |
Exercise 5: (Level 300)
Implementing Data Collector
|
After completing this module, students
will be able to:
| • |
Troubleshoot common SQL Server
problems, such as connectivity, concurrency, and job
and disk space problems.
|
| • |
Perform basic performance tuning
tasks in SQL Server using the Database Engine Tuning
Advisor, index tuning, and query tuning.
|
| • |
Use Resource Governor to manage
SQL Server workloads and resources.
|
| • |
Use Data Collector to obtain
performance data about your computer and the
instances of SQL Server running on your computer.
|
