微软培训与认证系列教材832B：System Administration for Microsoft SQL ServerTM 7.0（影印版）

About This Course

Introduction

Introductions

Course Materials

Prerequisites

Course Outline

Microsoft Certified Professional Program

Facilities

Module 1:SQL Server Overview

What Is SQL Server

SQL Server Platforms

SQL Server Integration with Windows NT

SQL Server Integration with Microsoft BackOffice

SQL Server Services

SQL Server Software

SQL Server Architecture

Communication

Application Development

Administration

SQL Server Security

Login Authentication

Database User Accounts and Roles,

Permission Validation

SQL Server Databases

Types of Databases

Database Objects

Referring to SQL Server Objects

System Tables

Metadata Retrieval

Working with SQL Server

Designing an Application for SQL Server

Implementing a SQL Server Database

Administering a SQL Server Database

Lab 1.1: SQL Server Overview

Review

Module 2:Installing and Configuring SQL Server

Minimum Hardware and Software Requirements

SQL Server 7.0 Editions

SQL Server Installation Options.

Licensing Mode

Installation Path

Character Set

Sort Order

Unicode Collation

Network Support

SQL Server Services Logon Account

Running SQL Server Setup

Unattended Installation

Verifying the Installation

Reviewing Installation Results

Starting SQL Server Services

Connecting to SQL Server

Preparing to Use SQL Server

Configuring SQL Server Enterprise Manager

Configuring SQL Server

Troubleshooting

Recommended Practices

Lab 2.1: Installing and Configuring SQL Server

Review

Module 3: Managing Security

Implementing an Authentication Mode

Authentication Processing

Choosing an Authentication Mode

Steps in Implementing an Authentication Mode

Creating Login Accounts

Demonstration: Setting Up Login Accounts

Lab 3.1: Managing Security

Assigning Login Accounts to Users and Roles

Assigning Login Accounts to User Accounts

Assigning Login Accounts to Roles

Fixed Server Roles

Fixed Database Roles

User-defined Database Roles

Assigning Permissions to Users and Roles

Types of Permissions

Granting, Denying, and Revoking Permissions

Granting Permissions to Allow Access

Denying Permissions to Prevent Access

Revoking Granted and Denied Permissions

Planning Security

Demonstration: Assigning Login Accounts to

User Accounts and Roles

Lab 3.2: Managing Permissions

Managing Application Security

Managing Security with Views and Stored Procedures

Managing Client Application Security with Application Roles

Creating Application Roles

Activating Application Roles

Recommended Practices

Lab 3.3: Managing Application Security

Review

Module 4: Managing Database Files

Introduction to Databases

How Data Is Stored

Multimedia Presentation: Transactions

How the Transaction Log Works,

Creating Databases

Setting Database Options

Modifying Databases

Managing Data and Log File Growth

Expanding a Transaction Log

Shrinking a Database or File

Dropping a Database

Managing Databases on Multiple Disks

Leveraging the Windows NT Server Fault Tolerance

Creating Filegroups

Capacity Planning

Determining the Size of model Database and System Tables

Estimating the Amount of Data in Tables

Performance Considerations

Recommended Practices

Lab 4.1: Managing Database Files

Review

Module 5: Backing Up Databases

Preventing Data Loss

SQL Server Backup

Performing and Storing Backups

When to Back Up Databases

Backing Up System Databases

Backing Up User Databases

Activities That Are Restricted During Backup

Performing Backups

Creating Permanent Backup Files

Creating Temporary Backup Files

Using Multiple Backup Files to Store Backups

Using the BACKUP Statement

Backing Up to a Tape Device

Specifying Tape Options.

Types of Backup Methods

Performing a Full Database Backup

Performing a Differential Backup

Performing a Transaction Log Backup

Using the NO_TRUNCATE Option

Clearing the Transaction Log

Performing a Database File or Filegroup Backup

Restrictions on Backing Up Database Files or Filegroups

Demonstration: Using SQL Server Enterprise Manager to

Perform Backups

Planning a Backup Strategy

Full Database Backup Strategy

Full Database and Transaction Log Backup Strategy

Differential Backup Strategy

Database File or Filegroup Backup Strategy

Performance Considerations

Recommended Practices

Lab 5.1: Backing Up Databases

Review

Module 6: Restoring Databases

SQL Server Recovery Process

SQL Server Activities During the Restore Process

Preparing to Restore a Database

Verifying Backups

Performing Specific Tasks Before Restoring Backups

Restoring Backups

Using the RESTORE Statement

Initiating the Recovery Process

Specifying Restore Options

Restoring Databases from Different Backup Types

Restoring from a Full Database Backup

Restoring from a Differential Backup

Restoring a Transaction Log Backup

Specifying a Point in Time

Restoring from a File or Filegroup Backup

Using a Standby SQL Server

Setting Up a Standby SQL Server

Using the Standby SQL Server as a Read-Only Server

Using the STANDBY Option

Replacing a Production Server with a Standby SQL Server

Restoring Damaged System Databases

Recommended Practices

Lab 6.1: Restoring Databases

Review,

Module 7: Automating Administrative Tasks

Reasons to Automate

Introduction to SQL Server Automation

Multimedia Presentation: Automating SQL Server Administration

Writing Events to the Application Log

Preparing to Automate

Configuring Mail

Automating Routine Maintenance Tasks

Creating Jobs

Verifying Permissions

Defining Job Steps

Determining Action Flow Logic for Each Job Step

Scheduling Jobs

Creating Operators to Notify

Reviewing and Configuring Job History

Lab 7.1: Creating Jobs and Operators

Creating Alerts

Using Alerts to Respond to Potential Problems

Creating Alerts to Respond to SQL Server Errors

Creating Alerts on a User-defined Error

Responding to Performance Condition Alerts

Assigning a Fail-Safe Operator

Troubleshooting SQL Server AutomationTroubleshooting Alerts

Lab 7.2: Creating Alerts

Automating Multiserver Jobs

Defining Multiserver Jobs

Demonstration: Creating a Master Job

Publishing SQL Server Data on the Web

Creating a Web Page

Formatting the Web Page

Generating the Web Page

Managing Web Assistant Jobs

Recommended Practices

Lab 7.3: Publishing SQL Server Data on the Web

Review

Module 8: Transferring Data

Introduction to Importing and Exporting Data

Why to Import and Export Data

Why to Transform Data

Tools for Importing and Exporting Data in SQL Server

Introduction to Data Transformation Services

DTS Overview

DTS Process

DTS Tools

Transforming Data with DTS

Creating a DTS Package

Transforming and Mapping Data

Defining Transformation Tasks

Defining Workflows

Defining Data Lineage

Executing and Scheduling a DTS Package

Demonstration: Defining a DTS Package

Recommended Practices

Lab 8.1: Transferring Data

Review

Module 9: Monitoring and Maintaining SQL Server

Why Monitor SQL Server

Reasons to Monitor SQL Server

Factors That Affect Performance

Detecting Performance Bottlenecks

Tools for Monitoring SQL Server

Common Monitoring Tasks

Using the Microsoft Event Viewer

Using SQL Server Performance Monitor

Using Current Activity in SQL Server Enterprise Manager

Using Transact-SQL to Monitor SQL Server

Using SQL Server Profiler

Generating a Query History

Demonstration: Using SQL Server Profiler

Using SQL Server Query Analyzer

Creating a Maintenance Plan for SQL Server

Developing a Database Maintenance Plan

Automating the Database Maintenance Plan Tasks

Recommended Practices

Lab 9.1: Monitoring SQL Server

Review

Module lO:Introducing Replication

Introduction to Distributed Data

The Need for Distributed Data

Considerations for Distributing Data

Methods to Distribute Data

Introduction to SQL Server Replication

The Publisher-Subscriber Metaphor

Publications and Articles

Filtering Data

Subscriptions

SQL Server Replication Agents

SQL Server Replication Types

Overview of the Replication Types

Multimedia Presentation: SQL Server Replication

Considerations for Using Merge Replication

Physical Replication Models

Overview of the Replication Models

Combining Replication Models and Types

Central Publisher/Remote Distributor Example

Central Subscriber/Multiple Publishers Example

Multiple Publishers/Multiple Subscribers Example

Recommended Practices

Review

Module 11 :Planning and Setting Up Replication

Planning Replication

Design Considerations

Determining a Replication Solution

Data Definition Issues

Configuring Network Security Access

Preparing the Servers

Setting Up a Distributor

Configuring a Distributor

Setting Up a Publisher

Setting Up a Subscriber

Publishing

Creating Publications

Publishing Considerations

Initial Synchronization

Subscribing

Setting Up Push and Pull Subscriptions,

Using the Immediate Updating Subscribers Option

Performance Considerations

Recommended Practices

Demonstration: Implementing Replication

Lab 11.1: Implementing Replication

Review

Module 12:Managing Replication

Monitoring and Troubleshooting Replication

Using SQL Server Replication Monitor

Maintaining Replication

Using Replication Scripts

Monitoring SQL Server Replication Performance

Viewing Replication Agent Histories

Troubleshooting Replication

Replicating in Heterogeneous Environments

Replicating Data with ODBC

Publishing Data to Heterogeneous Subscribers

Replicating from Heterogeneous Databases

Publishing on the Intemet

Recommended Practices

Lab 12.1: Maintaining Replication

Review

Appendix A: Database Schemas

Lab 1.1 :SQL Server Overview

Objectives

Exercise 1 Using SQL Server Books Online

Lab 2.1: Installing and Configuring SQL Server

Objectives

Before You Begin

Exercise 1 Preparing to Install SQL Server

Exercise 2 Installing SQL Server

Exercise 3 Verifying the Installation

Exercise 4 Configuring SQL Server Agent and SQL Server

Enterprise Manager

Exercise 5 Creating Shared Registration Information

Exercise 6 Troubleshooting Installation Problems

Exercise 7 Creating a Database Diagram

Lab 3.1: Managing Security

Objectives

Before You Begin

Exercise 1 Configuring SQL Server Authentication Mode

Exercise 2 Authorizing Users to Access SQL Server

Exercise 3 Revoking Access from, and Denying

Access to, Windows NT Users or Groups

Exercise 4 Registering Your Partner's SQL Server

Lab 3.2:Managing Permissions

Objectives

Before You Begin

Exercise 1 Creating Database Roles

Exercise 2 Assigning Statement Permissions

Exercise 3 Assigning Object Permissions

Lab 3.3:Managing Application Security

Objectives

Before You Begin

Exercise 1 Creating and Activating an Application Role

Exercise 2 Implementing Permissions by Using Views and

Stored Procedures

Lab 4.1: Managing Database Files

Objectives

Before You Begin

Exercise 1 Using the Create Database Wizard

Exercise 2 Creating a Database

Exercise 3 Modifying a Database

Exercise 4 Viewing and Changing Database Options by Using

Transact-SQL Statements

Exercise 5 Deleting a Database

Lab 5.1: Backing Up Databases

Objectives

Before You Begin

Exercise 1 Creating Permanent Backup Files

Exercise 2 Backing Up Databases

Exercise 3 Backing Up a Tramaction Log

Exercise 4 Performing a Differential Backup

Lab 6.1: Restoring Databases

Objectives

Before You Begin

Exercise 1 Restoring from a Full Database Backup

Exercise 2 Simulating and Capturing Database Activity

Exercise 3 Restoring Full Database, Differential, and Transaction

Log Backups

Lab 7.1 :Creating Jobs and Operators

Objectives

Before You Begin

Exercise 1 Configuring SQL Server Agent to Send Messages

Exercise 2 Creating Operators

Exercise 3 Using the Create Job Wizard

Exercise 4 Creating a Job with Multiple Steps,

If Time Permits Using SQL Mail

Lab 7.2:Creating Alerts

Objectives

Before You Begin

Exercise 1 Using the Create Alert Wizard

Exercise 2 Creating an Alert on a User-defined Error Message

Exercise 3 Creating Performance Condition Alerts

If Time Permits Assigning a Fail-Safe Operator

Lab 7.3: Publishing SQL Server Data on the Web

Objectives

Before You Begin

Exercise 1 Creating a Web Page with the Web Assistant Wizard

Exercise 2 Updating a Web Page Automatically

When Data Changes

If Time Permits Creating a Web Page with a Template File and

Transact-SQL

Lab 8.1: Transferring Data

Objectives

Before You Begin

Exercise 1 Importing Data from a Query

Exercise 2 Modifying a DTS Package Using DTS Designer,

Exercise 3 Transferring SQL Server 7.0 Objects

If Time Permits Exporting Data to a Text File

Importing Data Using the Bulk Insert Task

Lab 9.1: Monitoring SQL Server

Objectives

Before You Begin

Exercise 1 Using Windows NT Performance Monitor

Exercise 2 Using SQL Server Profiler

Exercise 3 Using SQL Server Query Analyzer

Exercise 4 Monitoring Locking Information

Exercise 5 Creating a Database Maintenance Plan

If Time Permits Creaiing a Query History trace file

Lab 10.1:Implementing Replication

Objectives

Before You Begin

Exercise 1 Logging in to and Registering a Server

Exercise 2 Configuring Transactional Replication

Exercise 3 Creating a Merge Publication

Exercise 4 Creating a Pull Subscription and Resolving Conflicts

Lab 11.1:Maintaining Replication

Objectives

Before You Begin

Exercise 1 Generating a Replication Script,

Exercise 2 Modifying Replication Maintenance Properties

Classroom Setup Guide

Classroom Requirements

Classroom Corrfignration

Setup Instructions