Module 1: SQL Server Overview

Module 2: Planning to Install SQL Server

Install SQL Server 2000. Considerations include clustering, default collation, file locations, number of instances, and service accounts.

Configure network libraries.

Upgrade to SQL Server 2000.

• Perform a custom upgrade. 
• Upgrade to SQL Server 2000 from SQL Server 6.5. 
• Upgrade to SQL Server 2000 from SQL Server 7.0. 

Troubleshoot failed installations.

Module 3: Managing Database Files

Configure database options for performance. Considerations include capacity, network connectivity, physical drive configurations, and storage locations.

Create and alter databases.

• Add filegroups. 
• Configure filegroup usage. 
• Expand and shrink a database. 
• Set database options by using the ALTER DATABASE or CREATE DATABASE statements. 
• Size and place the transaction log. 

Modify the database schema.

Optimize data storage.

• Optimize files and filegroups.
• Manage database fragmentation.

Module 4: Managing Security

Configure mixed security modes or Windows Authentication. Considerations include client connectivity, client operating system, and security infrastructure.

Create and manage log ons.

Create and manage database users.

Create and manage security roles. Roles include application, database, and server.

• Add and remove users from roles. 
• Create roles to manage database security. 

Set permissions in a database. Considerations include object permissions, object ownership, and statement permissions.

Create and manage objects. Objects include constraints, indexes, stored procedures, triggers, and views. 

Enforce and manage security by using stored procedures, triggers, views, and user-defined functions. 

Module 5: Performing Administrative Tasks

Configure SQL Mail and SQLAgentMail.

Create a linked server. 

Manage linked servers.

• Manage OLE DB Providers. 
• Configure security mapping. 

Set up IIS virtual directories to support XML.

Create, manage, and troubleshoot SQL Server Agent jobs.

Configure alerts and operators by using SQL Server Agent.

Module 6: Backing Up Databases

Perform disaster recovery operations.

• Perform backups. 

Module 7: Restoring Databases

Perform disaster recovery operations.

• Recover the system state and restore data. 

Module 8: Monitoring SQL Server for Performance

Perform integrity checks. Methods include configuring the Database Maintenance Plan Wizard and using the Database Consistency Checker (DBCC). 

Troubleshoot transactions and locking by using SQL Profiler, SQL Server Enterprise Manager, or Transact-SQL.

Optimize and troubleshoot SQL Server system activity. Activities include cache hits, connections, locks, memory allocation, recompilation, and transactional throughput. 

• Monitor SQL Server system activity by using traces. 
• Monitor SQL Server system activity by using System Monitor.

Optimize hardware resource usage. Resources include CPU, disk I/O, and memory.

• Monitor hardware resource usage by using System Monitor. 
• Resolve system bottlenecks by using System Monitor. 

Optimize database performance. Considerations include indexing, locking, and recompiling.

Module 9: Transferring Data

Import and export data. Methods include the Bulk Insert task, the bulk copy program, Data Transformation Services (DTS), and heterogeneous queries.

Develop and manage DTS packages.

Convert data types. 

Module 10: Maintaining High Availability

Perform disaster recovery operations.

• Configure, maintain, and troubleshoot log shipping. 

Module 11: Introducing Replication

Configure, maintain, and troubleshoot replication services.

Additional Topics

Attach and detach databases.

Manage security auditing. Methods include SQL Profiler and C2 auditing.