Microsoft Course 2072: Administering a Microsoft SQL Server 2000 Database
Course Length: Five Days

At Course Completion

At the end of the course, students will be able to:

Microsoft Certified Professional Exams
This course will help the student prepare for the following Microsoft Certified Professional exam:

• Exam 70-228, System Administration for Microsoft SQL Server 2000

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:

• SQL Server Overview

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:

• Installing SQL Server

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

Take a closer look: Download Sample Module 4 (Portable Document Format, 1.08 MB).

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:

• Backing Up Databases

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:

• Restoring Databases

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:

• Monitoring SQL Server

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:

• Transferring Data

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.

One Campbell Plaza, Suite 2B
St. Louis, MO 63139
Phone: (314) 644-6400
Fax: (314) 644-3670
www.premier-ks.com