Understand the capabilities of Microsoft SQL Server 2000

Install Microsoft SQL Server

Design, create, and manage databases

Create and manage database objects

Design and configure security on SQL Server

Transfer data between SQL servers and configure XML support

Back up a SQL server's databases

Restore database backups

Automate administrative tasks

Design, implement, and manage replication between SQL servers

Configure fault-tolerance through Failover Clustering and standby servers

Optimise the performance of SQL Server

Topic 1A: Exploring the Components of SQL Server

Task 1A-1: Exploring the Features of Microsoft SQL Server

Task 1A-2: Discussing the Components of SQL Server

Task 1A-3: Identifying the Default Databases and System Tables

Task 1A-4: Understanding the SQL Server Architecture

Topic 1B: Administering SQL Server

Task 1B-1: Determining SQL Server Administration Tasks

Task 1B-2: Identifying Administrative Utilities

Topic 2A: Identifying the Requirements for Installing SQL Server

Task 2A-1: Choosing the Hardware and Software for SQL Server 2000

Task 2A-2: Selecting Your Server's Hardware

Task 2A-3: Identifying Where to Install SQL Server

Task 2A-4: Choosing Installation Options for SQL Server

Topic 2B: Installing SQL Server

Task 2B-1: Creating and Configuring a Service Account for SQL Server

Task 2B-2: Performing Unattended Installations

Task 2B-3: Using the SQL Server Service Manager to Manage the SQL Services

Topic 2C: Exploring the SQL Server Installation

Task 2C-1: Starting SQL Server Enterprise Manager

Task 2C-2: Viewing the System Databases Within SQL Server Enterprise Manager

Task 2C-3: Creating and Working with a Database Diagram

Task 2C-4: Using SQL Server Enterprise Manager to Configure Your Server

Topic 2D: Troubleshooting the SQL Server Installation

Task 2D-1: Testing the SQL Server Installation

Task 2D-2: Viewing the SQL Server Log

Task 3A-1: Discussing SQL Server Terminology

Task 3A-2: Identifying the Space Requirements for Databases

Task 3A-3: Viewing Space Statistics

Task 3A-4: Optimizing Database Performance

Topic 3B: Creating Databases

Task 3B-1: Using Transact-SQL to Create a New Database

Task 3B-2: Using SQL Server Enterprise Manager to Create a New Database

Task 3B-3: Changing the Default Filegroup

Task 3B-4: Using SQL Server Enterprise Manager to Configure a Database

Task 3B-5: Using Stored Procedures to View Database Information

Topic 3C: Managing Databases

Task 3C-1: Configuring SQL Server to Automatically Expand a Database

Task 3C-2: Manually Expanding a Database and its Transaction Log

Task 3C-3: Configuring SQL Server to Automatically Shrink a Database File

Task 3C-4: Deleting a Database

Topic 4A: Creating Tables

Task 4A-1: Designing Tables

Task 4A-2: Choosing Data Types for a Table

Task 4A-3: Identifying the Role of Constraints in a Table

Task 4A-4: Using SQL Server Enterprise Manager to Create a Table

Task 4A-5: Modifying the Structure of a Table

Topic 4B: Using Transact-SQL to Work with Tables

Task 4B-1: Using the INSERT Statement to Add Rows to a Table

Task 4B-2: Using the UPDATE Statement

Task 4B-3: Using the DELETE Statement

Task 4B-4: Formatting Columns in a Query

Topic 5A: Identifying the Security Features of SQL Server

Task 5A-1: Identifying the SQL Server Security Components

Task 5A-2: Configuring Your Server’s Authentication Mode

Task 5A-3: Configuring Encryption

Topic 5B: Creating Login Accounts

Task 5B-1: Creating a Login Account for a Windows Account

Task 5B-2: Denying Login Privileges

Task 5B-3: Creating and Testing a SQL Server Login Account

Task 5B-4: Assigning Login Accounts to Server Roles

Topic 5C: Assigning Login Accounts as Database Users

Task 5C-1: Assigning a Login Account as a Database User in SQL Server Enterprise Manager

Task 5C-2: Adding a Database User to a Database Role

Task 5C-3: Creating a User-defined Database Role

Topic 5D: Implementing Statement and Object Permissions

Task 5D-1: Comparing Permission Types

Task 5D-2: Granting Statement Permissions

Task 5D-3: Granting Object Permissions

Task 5D-4: Denying Statement Permissions

Task 5D-5: Denying Object Permissions

Task 5D-6: Revoking Statement and Object Permissions

Task 5D-7: Creating and Using an Application Role

Task 5D-8: Creating and Using a View

Topic 5E: Defining Linked Servers

Task 5E-1: Defining a Linked Server

Task 5E-2: Configuring Linked Server Security

Topic 6A: Introduction to Data Transfer

Task 6A-1: Comparing the Data Transfer Utilities

Task 6A-2: Creating a DTS Package by Using the DTS Import/Export Wizard

Task 6A-3: Using DTS Designer to Modify a Package

Topic 6B: Bulk Data Transfers

Task 6B-1: Using bcp to Generate a Transfer File

Task 6B-2: Using bcp to Import a Transfer File

Topic 6C: Configuring XML Support

Task 6C-1: Creating a Virtual Directory for SQL Server

Task 6C-2: Running a URL Query

Topic 7A: Planning Backups

Task 7A-1: Planning to Back Up Your Server

Task 7A-2: Reviewing the Backup Process

Task 7A-3: Backing Up Databases

Topic 7B: Performing a Backup

Task 7B-1: Creating a Permanent Backup Device

Task 7B-2: Performing a Backup of the Master Database to a Permanent Backup Device

Task 7B-3: Performing a Parallel Backup of the Class_mgr Database

Task 7B-4: Performing a Filegroup Backup

Task 7B-5: Scheduling an Automatic Backup of the Msdb Database

Topic 7C: Designing Your Backup Strategy

Topic 8A: Restoring User-defined Databases

Task 8A-1: Observing an Automatic Recovery

Task 8A-2: Reviewing User-initiated Database Recovery

Task 8A-3: Creating Tables and Backing Up

Task 8A-4: Simulating a Failed Database

Task 8A-5: Restoring the Class_mgr Database

Topic 8B: Recovering System Databases

Task 8B-1: Simulating a Master Database Failure

Task 8B-2: Instructor Demo: Removing the Read-only Attribute From the SQL Server Installation Files

Task 8B-3: Restoring the Master Database

Topic 9A: Configuring SQL Server to Send Mail

Task 9A-1: Verifying the Configuration of the SQL Server Agent Service

Task 9A-2: Instructor Demo: Configuring a Mailbox for the SQL Server Service Account

Task 9A-3: Creating a Mail Profile

Task 9A-4: Configuring SQL Agent Mail

Task 9A-5: Configuring the SQL Server Service to Send and Receive Mail

Task 9A-6: Using SQL Mail to Send Query Results

Topic 9B: Defining SQL Operators

Task 9B-1: Using SQL Server Enterprise Manager to Define Operators

Task 9B-2: Specifying a Fail-Safe Operator

Topic 9C: Managing Jobs

Task 9C-1: Creating a Job

Task 9C-2: Viewing Job History Information

Task 9C-3: Manually Running a Job

Task 9C-4: Running the Database Maintenance Plan Wizard

Topic 9D: Managing Alerts

Task 9D-1: Creating an Alert

Task 9D-2: Configuring Event Forwarding

Task 9D-3: Troubleshooting Alerts

Topic 10A: Introduction to Replication

Task 10A-1: Identifying Publishable Information

Task 10A-2: Choosing a Replication Model

Task 10A-3: Choosing a Replication Strategy

Topic 10B: Configuring Replication

Task 10B-1: Configuring a Publisher/Distributor

Task 10B-2: Configuring Distributor and Publisher Properties

Task 10B-3: Creating a Publication

Task 10B-4: Modifying a Publication

Task 10B-5: Configuring a Pull Subscription

Task 10B-6: Creating an Initial Snapshot for a Publication

Topic 10C: Monitoring and Maintaining Replication

Task 10C-1: Using the Replication Monitor

Task 10C-2: Maintaining the Distribution Database

Task 10C-3: Creating Replication Scripts in SQL Server Enterprise Manager

Topic 10D: Troubleshooting Replication

Task 10D-1: Examining Replication Information in the Error Logs

Topic 11A: Configuring Fault Tolerance in SQL Server 2000

Task 11A-1: Exploring Clustering

Topic 12A: Designing a Strategy for Monitoring SQL Server

Task 12A-1: Discussing Server Optimization and Monitoring Techniques

Task 12A-2: Identifying the Factors That Affect the Performance of SQL Server

Topic 12B: Using Monitoring Tools

Task 12B-1: Observing Your Server’s Current Activity

Task 12B-2: Using SQL Statements and SQL Server Enterprise Manager to Monitor Your Server

Task 12B-3: Using SQL Server Profiler

Task 12B-4: Using SQL Query Analyzer to Monitor a Query

Task 12B-5: Choosing a Monitoring Tool