MCDBA SQL Server 2000考试指南

Introduction

Chapter 1 Introducing Microsoft SQL Server 2000

Northwind Distributors Case Study

Microsoft SQL Server 2000 Overview

Implementation Architectures

How Clients Communicate with SQL Server

SQL Server Services

Default Databases

What a Database Includes

New Features in SQL Server 2000

Planning a SQL Server 2000 Installation

What Is the Primary Function of This Server?

What Are the Performance and Redundancy Expectations?

Will Replication Be Required?

How Will SQL Server Be Licensed?

Chapter Review

Questions

Answers

Chapter 2 Installing SQL Server 2000

Installing SQL Server 2000

Performing a Local Installation

Performing a Remote Installation

Installing the Client Utilities

Unattended Installation

Upgrading to SQL Server 2000

Upgrade Requirements

Upgrading SQL Server 7.0 to SQL Server 2000

Upgrading SQL Server 6.5 to SQL Server 2000

Troubleshooting SQL Server Installations

Failed Installations and General Troubleshooting

Connectivity Problems

Uninstalling SQL Server 2000

Chapter Review

Questions

Answers

Chapter 3 Configuring Microsoft SQL Server 2000

Verifying Your SQL Server 2000 Installation

Windows 2000 Service Control Manager

SQL Server Service Manager

SQL Server Administration Tools

SQL Server Enterprise Manager

Client Network Utility

Configure SQL XML Support in IIS

Import and Export Data

SQL Profiler

SQL Query Analyzer

Server Network Utility

Common Configuration Tasks

SQL Server Startup

Connection Management

Configuring Mail Support

Configuring Network Libraries

Introducing the Structured Query Language

Transact-SQL Commands

Using the SQL Query Designer

Chapter Review

Questions

Answers

Chapter 4 Creating SQL Server 2000 Databases

What Is a SQL Server Database?

Where the Data Is Stored

Creating a Database

Modifying a Database

Available Database Options

Setting Database Options

Changing the Size of a Database

Attaching and Detaching a Database

Chapter Review

Questions

Answers

Chapter 5 Creating and Managing Database Objects

Introducing Tables

Primary and Foreign Keys

Creating a Table

Modifying Tables

Deleting Tables

Introducing the Database Designer

Introducing Constraints

Introducing Indexes

Creating an Index

Deleting an Index

Introducing Stored Procedures

Executing Stored Procedures

Creating Stored Procedures

Modifying a Stored Procedure

Deleting a Stored Procedure

Introducing Triggers

Creating a New Trigger

Reviewing and Editing an Existing Trigger

Introducing Views

Examining Existing Views

Accessing the Data in a View

Creating Views

Modifying Views

Deleting Views

Chapter Review

Questions

Answers

Chapter 6 Managing and Monitoring SQL Server 2000 Security

Configuring the SQL Server Security Mode

Windows Authentication Mode

Mixed Mode

Creating and Managing Logins

Delegation and Impersonation

Creating and Managing User Accounts

Creating and Managing Security Roles

Server Roles

Database Roles

Creating Database Roles

Setting permissions in a database

Enforce and manage security

Manage Security Auditing

Auditing Security with the SQL Profiler (SP)

Chapter Reivew

Questions

Answers

Chapter 7 Managing, Monitoring, and Troubleshooting SQL Server 2000 Databases

Optimizing Database Performance

Indexes

The Index Tuning Wizard

Customize Locking

Recompiling

Optimizing Hardware Use

Managing Server Memory

Managing Server Processors

Managing Hard Drives

Optimizing Files and Filegroups

Performing Disaster Recovery Operations

Performing Backups

Recovering and Restoring Data

Configuring, Maintaining, and Troubleshooting Log Shipping

Performing Integrity Checks

Using SQL Server Agent

Creating and Managing Jobs

Configuring Operators and Alerts

Chapter Review

Questions

Answers

Chapter 8 Extracting and Transforming Data with SQL Server 2000

Supporting the Extensible Markup Language (XML)

XML Overview

Setting up IIS

Importing and Exporting Data

Using the Bulk Copy Program (BCP)

Using Data Transformation Services (DTS)

Introducing DTS

Developing and Managing DTS Packages

Transforming Data with DTS

Managing Linked Servers

Creating a Linked Server

Executing Heterogeneous Queries

Managing Replication Services

Replication Service Architecture

Implementing Replication

Chapter Review

Questions

Answers

Chapter 9 Developing a Logical Data Model

Defining Entities

Determining the Primary Entities

Defining the Attributes of Your Entities

Selecting a Level of Normalization

Designing Entity Keys

Primary Key

Foreign Key

UNIQUE Constraints

Designing Attribute Domain Integrity

CHECK Constraints

Data Types

Creating a User-Defined Data Type with the Enterprise Manager

Selecting Attribute Properties

Designing Logical Data Models from Business Scenarios

Exercise #1: Southwest Bookstore

Exercise #1: Southwest Bookstore Suggested Solution

Chapter Review

Questions

Answers

Chapter 10 Implementing the Physical Database

Creating a New Database

Database Properties

Creating the Southwest Bookstore Database

Creating Database Objects

Creating Tables

Creating Indexes

Creating Views

Creating Stored Procedures

Creating User-Defined Functions

Chapter Review

Questions

Answers

Chapter 11 Retrieving and Modifying Data

Performing Bulk Data Operations

Using the Bulk Copy Program (BCP)

Using the Bulk Insert T-SQL Command

Using Data Transformation Services (DTS)

Managing Data Using Transact-SQL

Transact-SQL Commands

Using Cursors with Transact-SQL

Navigating a Cursor

Accessing Data in a Heterogeneous Environment

Creating a Linked Server

Executing Queries to Linked Servers

Accessing Data Using Heterogeneous Queries

Returning Data Using XML

Extensible Markup Language (XML) Overview

Accessing XML Data with a URL

Chapter Review

Questions

Answers

Chapter 12 Programming Business Logic

Designing Stored Procedures

Creating Stored Procedures

Using Input Parameters

Using Output Parameters

Modifying a Stored Procedure Using T-SQL

Deleting a Stored Procedure

Creating Triggers

Creating a New Trigger

Reviewing and Editing an Existing Trigger

Developing User-Defined Functions

Creating Views

Implementing Views

Accessing the Data in a View

Modifying Views

Deleting Views

Chapter Review

Questions

Answers

Chapter 13 Designing a Database Security Plan

How SQL Server Manages Database Access

Windows Authentication Mode

Mixed Mode

SQL Server Logins

SQL Server Roles

Controlling Table Access

Restricting Access at the Table Level

Restricting Access at the Column Level

Controlling Access Using Other Database Objects

Using Stored Procedures

Using Triggers

Using User-Defined Functions

Controlling Access Using Views

Using Application Roles

Chapter Review

Questions

Answers

Chapter 14 Tuning and Optimizing Data Access

Optimizing Database Performance

Indexes

Customize Locking

Analyzing Query Execution Plans

Interactively Executing a Stored Procedure

Using the SQL Profiler to Analyze Performance

Chapter Review

Questions

Answers

Appendix A Microsoft Certification Exam Prep 70-228

Questions

Answers

Appendix B Microsoft Certification Exam Prep 70-229

Questions

Answers