Introduction
This course
provides students with the knowledge and skills
required to install, configure, administer, and
troubleshoot the client-server database
management system of Microsoft SQL Server 2000.
Course 2072 is a revision of Course 832,
System Administration for Microsoft SQL Server
7.0.
At Course
Completion
At the end of
the course, students will be able to:
|
• |
Describe
SQL Server architecture. |
|
• |
Plan for a
SQL Server installation, and then
install an instance of SQL Server. |
|
• |
Manage
files and databases, including
determining resource requirements. |
|
• |
Choose a
login security method, configure login
security, plan and implement database
permissions, and describe how to help
protect SQL Server in an enterprise
network. |
|
• |
Perform
and automate administrative tasks and
create custom administrative tools. |
|
• |
Back up
databases and implement a backup
strategy. |
|
• |
Restore
databases. |
|
• |
Monitor
and optimize SQL Server performance. |
|
• |
Transfer
and migrate data into databases. |
|
• |
Maintain
the high availability of SQL Server. |
|
• |
Describe
how to replicate data from one SQL
Server to another. |
Microsoft
Certified Professional Exams
This course
will help the student prepare for the following
Microsoft Certified Professional exam:
Prerequisites
Before
attending this course, students must have:
|
• |
Experience
using the Microsoft Windows 2000
operating system to:
|
• |
Connect clients running Windows
2000 to networks and the
Internet. |
|
• |
Configure the Windows 2000
environment. |
|
• |
Create and manage user accounts. |
|
• |
Manage access to resources by
using groups. |
|
• |
Configure and manage disks and
partitions, including disk
striping and mirroring. |
|
• |
Manage data by using NTFS. |
|
• |
Implement Windows 2000 security. |
|
• |
Optimize performance in Windows
2000. |
For students who do not meet these
prerequisites, the following courses
provide students with the necessary
knowledge and skills:
|
• |
Course 2151, |
|
• |
Course 2152,
|
|
|
• |
An
understanding of basic relational
database concepts, including:
|
• |
Logical and physical database
design. |
|
• |
Data integrity concepts. |
|
• |
Relationships between tables and
columns (primary key and foreign
key, one-to-one, one-to-many,
and many-to-many). |
|
• |
How data is stored in tables
(rows and columns). |
For students who do not meet these
prerequisites, the following course
provides students with the necessary
knowledge and skills:
|
|
• |
Knowledge of basic Transact-SQL syntax
(SELECT, INSERT, UPDATE, and DELETE
statements).
For students who do not meet these
prerequisites, the following course
provides students with the necessary
knowledge and skills:
|
|
• |
Familiarity with the role of the
database administrator. |
The course
materials, lectures, and lab exercises are in
English. To benefit fully from the instruction,
students need an understanding of the English
language and completion of the prerequisites.
Course
Materials
The course
materials are yours to keep. The student kit
includes a comprehensive workbook and other
necessary materials for this class.
The following
software is provided for use in the classroom:
|
• |
Microsoft
SQL Server 2000, Enterprise Edition
Evaluation Copy |
Course Outline
Module 1:
SQL Server Overview
The following
topics are covered in this module:
|
• |
What Is
SQL Server |
|
• |
SQL Server
Integration |
|
• |
SQL Server
Databases |
|
• |
SQL Server
Security |
|
• |
Working
with SQL Server |
The following
lab is covered in this module:
At the end of
this module, you will be able to:
|
• |
Describe
SQL Server 2000 and its supported
operating system platforms. |
|
• |
Describe
SQL Server architecture. |
|
• |
Describe
SQL Server databases. |
|
• |
Describe
SQL Server security. |
|
• |
Describe
SQL Server querying, implementation,
administration, and data warehousing
activities, as well as client
application design options. |
Module 2:
Planning to Install SQL Server
The following
topics are covered in this module:
|
• |
Hardware
Installation Considerations |
|
• |
SQL Server
2000 Editions |
|
• |
Software
Installation Considerations |
|
• |
Methods of
Installing SQL Server |
|
• |
Verifying
the Installation |
|
• |
Configuring SQL Server Enterprise
Manager |
|
• |
Troubleshooting |
The following
lab is covered in this module:
At the end of
this module, you will be able to:
|
• |
Determine
hardware requirements for SQL Server
2000 and the SQL Server management
tools. |
|
• |
Describe
the various SQL Server Editions. |
|
• |
Describe
the different types of licensing. |
|
• |
Determine
software installation options that are
appropriate for your environment. |
|
• |
Describe
various methods of installing an
instance SQL Server and install it by
using SQL Server Setup. |
|
• |
Verify the
installation of SQL Server. |
|
• |
Configure
SQL Server Enterprise Manager. |
|
• |
Troubleshoot the installation. |
Module 3:
Managing Database Files
The following
topics are covered in this module:
|
• |
Introduction to Data Structures |
|
• |
Creating
Databases |
|
• |
Managing
Databases |
|
• |
Placing
Database Files and Logs |
|
• |
Optimizing
the Database Using Hardware-based RAID |
|
• |
Optimizing
the Database Using Filegroups |
|
• |
Optimizing
the Database Using Filegroups with
Hardware-based RAID |
|
• |
Capacity
Planning |
|
• |
Performance Considerations |
The following
lab is covered in this module:
|
• |
Managing
Database Files |
At the end of
this module, you will be able to:
|
• |
Describe
how SQL Server stores data and handles
transactions. |
|
• |
Create a
database, including specifying options
during and after database creation. |
|
• |
Grow,
shrink, or delete a database. |
|
• |
Determine
the placement of database files and
transaction logs for performance and
fault tolerance. |
|
• |
Optimize a
database by using hardware-based RAID. |
|
• |
Determine
when and how to use filegroups to
optimize a database. |
|
• |
Optimize a
database by using filegroups with
hardware-based RAID. |
|
• |
Estimate
the amount of space that a database
requires. |
Module 4:
Managing Security
The following
topics are covered in this module:
|
• |
Implementing an Authentication Mode |
|
• |
Assigning
Logins to Users and Roles |
|
• |
Assigning
Permissions to Users and Roles |
|
• |
Managing
Security Within SQL Server |
|
• |
Managing
Application Security |
|
• |
Managing
SQL Server Security in the Enterprise |
The following
labs are covered in this module:
|
• |
Managing
Security |
|
• |
Managing
Permissions |
|
• |
Managing
Application Security |
At the end of
this module, you will be able to:
|
• |
Implement
Windows Authentication Mode and Mixed
Authentication Mode. |
|
• |
Assign
login accounts to database user accounts
and roles. |
|
• |
Assign
permissions to user accounts and roles. |
|
• |
Manage
security within SQL Server. |
|
• |
Manage
security with views and stored
procedures. |
|
• |
Create and
use application roles to manage
application security. |
|
• |
Manage SQL
Server security in the enterprise
environment. |
Module 5:
Performing Administrative Tasks
The following
topics are covered in this module:
|
• |
Configuration TasksRoutine SQL Server
Administrative TasksAutomating Routine
Maintenance TasksCreating
AlertsTroubleshooting SQL Server
AutomationAutomating Multiserver Jobs |
The following
labs are covered in this module:
|
• |
Configuring SQL Server |
|
• |
Creating
Jobs and Operators |
|
• |
Creating
Alerts |
At the end of
this module, you will be able to:
|
• |
Perform
common SQL Server configuration tasks. |
|
• |
Describe
how to upgrade SQL Server version 6.5
and SQL Server 7.0 to SQL Server 2000. |
|
• |
Describe
routine database administration tasks. |
|
• |
Automate
routine maintenance tasks by creating
and scheduling jobs. |
|
• |
Create
alerts and operators. |
|
• |
Troubleshoot automated jobs, alerts, or
notifications. |
|
• |
Automate
administrative jobs in a multiserver
environment. |
Module 6:
Backing Up Databases
The following
topics are covered in this module:
|
• |
Preventing
Data Loss |
|
• |
Setting
and Changing a Database Recovery Model |
|
• |
SQL Server
Backup |
|
• |
When to
Back Up Databases |
|
• |
Performing
Backups |
|
• |
Types of
Backup Methods |
|
• |
Planning a
Backup Strategy |
|
• |
Performance Considerations |
The following
lab is covered in this module:
At the end of
this module, you will be able to:
|
• |
Create
backup files and backup sets. |
|
• |
Back up
user and system databases by using
Transact-SQL and SQL Server Enterprise
Manager. |
|
• |
Back up
databases that are created on multiple
files and filegroups. |
|
• |
Apply the
appropriate backup options to each of
the different SQL Server 2000 backup
methods. |
|
• |
Use the
BACKUP LOG statement to back up and
clear transaction logs. |
|
• |
Design an
appropriate backup strategy. |
Module 7:
Restoring Databases
The following
topics are covered in this module:
|
• |
SQL Server
Recovery Process |
|
• |
Preparing
to Restore a Database |
|
• |
Restoring
Backups |
|
• |
Restoring
Databases from Different Backup Types |
|
• |
Restoring
Damaged System Databases |
The following
lab is covered in this module:
At the end of
this module, you will be able to:
|
• |
Describe
the SQL Server recovery process. |
|
• |
Verify
backups and perform specific tasks that
enable the restore process. |
|
• |
Use the
RESTORE statement to get information
about a backup file before you restore a
database, file, or transaction log. |
|
• |
Restore
backups from different backup types and
use the appropriate options. |
|
• |
Restore
damaged system databases. |
Module 8:
Monitoring SQL Server for Performance
The following
topics are covered in this module:
|
• |
Why to
Monitor SQL Server |
|
• |
Performance Monitoring and Tuning |
|
• |
Tools for
Monitoring SQL Server |
|
• |
Common
Monitoring and Tuning Tasks |
The following
lab is covered in this module:
At the end of
this module, you will be able to:
|
• |
Describe
the reasons why monitoring SQL Server
2000 is important. |
|
• |
Develop a
performance monitoring and tuning
methodology. |
|
• |
Describe
the tools available for monitoring SQL
Server. |
|
• |
Perform
common monitoring and tuning tasks by
using counters and appropriate tools. |
Module 9:
Transferring Data
The following
topics are covered in this module:
|
• |
Introduction to Transferring Data |
|
• |
Tools for
Importing and Exporting Data in SQL
Server |
|
• |
Introduction to DTS |
|
• |
Transforming Data with DTS |
The following
lab is covered in this module:
At the end of
this module, you will be able to:
|
• |
Describe
the rationale for, and the process of,
importing, exporting, and transforming
data. |
|
• |
Describe
the tools for importing and exporting
data in SQL Server 2000. |
|
• |
Transform
data by using Data Transformation
Services (DTS). |
|
• |
Create and
edit a DTS package by using the DTS
Import and DTS Export Wizards. |
Module 10:
Maintaining High Availability
The following
topics are covered in this module:
|
• |
Introduction to Availability |
|
• |
Increasing
Availability Using Failover Clustering |
|
• |
Standby
Servers and Log Shipping |
The following
lab is covered in this module:
|
• |
Automating
the Maintenance of a Standby Server |
At the end of
this module, you will be able to:
|
• |
Determine
availability requirements and strategies
for a Microsoft Windows Server System
environment. |
|
• |
Use SQL
Server failover clustering. |
|
• |
Configure
a standby server and use log shipping to
maintain its integrity. |
Module 11:
Introducing Replication
The following
topics are covered in this module:
|
• |
Introduction to Distributed Data |
|
• |
Introduction to SQL Server Replication |
|
• |
SQL Server
Replication Agents |
|
• |
SQL Server
Replication Types |
|
• |
Physical
Replication Models |
The following
lab is covered in this module:
|
• |
Implementing Replication |
At the end of
this module, you will be able to:
|
• |
Describe
the various methods to distribute data
in SQL Server 2000. |
|
• |
Explain
the publisher-subscriber metaphor,
including articles, publications, and
subscriptions. |
|
• |
Describe
SQL Server replication agents. |
|
• |
Explain
the SQL Server replication types. |
|
• |
Describe
the physical replication models. |
