2072 - Administering a Microsoft SQL Server 2000 Database
Course Outline
Module 1: SQL Server Overview
Take a closer look: Download Sample Module 1 (Portable Document Format, 990 KB).
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.