About the Book
Competency-based learning for the academic
classroom.
Developed for academic courses, MICROSOFT®
SQL SERVER™ 2000 SYSTEM ADMINISTRATION helps students build the skills
they need on the job and for MCP Exam 70-228—an elective on the Windows®
2000 MCSE track. The textbook delivers Microsoft courseware adaptable for
either a full 16-week semester or a 6-week, 8-week, or 10-week course.
The book is filled with competency-based practice exercises that students
complete on their own, while the companion project manual features additional
skill-building activities ideal for your classroom’s computer lab.
Along with the textbook and project manual,
students get a Student CD-ROM containing study aids, simulation video clips
that demonstrate common procedures, an electronic version of the textbook,
and detailed information on how to begin a career in Information Technology.
Plus, they get a 120-day evaluation version of SQL Server 2000 Enterprise
Edition.
An Instructor CD-ROM containing a complete
set of instructor support materials—including lecture outlines, teaching
tips, PowerPointâ slides, test banks, and an instructor lab manual—is
also available.
Related Books
MCSE Training Kit (Exam 70-219): Designing a Microsoft® Windows® 2000 Directory Services Infrastructure
MCSE Microsoft® Windows® 2000 Professional Readiness Review; Exam 70-210
MCSE Training Kit (Exam 70-220): Designing Microsoft® Windows® 2000 Network Security
Table of Contents
About This Book
xvii
CHAPTER 1 Overview
of SQL Server 2000 1
About
This Chapter 1
Lesson 1: What Is SQL Server
2000? 1
The SQL
Server 2000 Environment 2
SQL Server
2000 Components 2
SQL Server
2000 Relational Database Engine 4
SQL Server
2000 Analysis Services 4
Application
Support 4
Additional
Components 5
SQL Server
2000 Editions 7
Integration
with Windows 2000 and Windows NT 4.0 10
Lesson
Summary 12
Lesson 2: What Are the SQL
Server 2000 Components? 12
Server
Components 13
Client-Based
Administration Tools and Utilities 14
Client
Communication Components 14
SQL Server
Books Online 18
Lesson
Summary 19
Lesson 3: What Is the Relational
Database Architecture? 19
System
and User Databases 19
Physical
Structure of a Database 20
Logical
Structure of a Database 21
Lesson
Summary 22
Lesson 4: What Is the Security
Architecture? 23
Authentication
23
Authorization
25
Lesson
Summary 26
Review 26
CHAPTER 2 Installing SQLServer
2000 27
About
This Chapter 27
Before
You Begin 28
Lesson 1: Planning to Install
SQL Server 2000 28
What
Is the Minimum Hardware Required? 28
Exceeding
Minimum Computer Hardware Requirements 31
Lesson
Summary 32
Lesson 2: Deciding SQL Server
2000 Setup Configuration Options 32
Determining
the Appropriate User Account for the SQL Server and SQL Server Agent Services
32
Practice:
Creating a Dedicated Windows 2000 User Account 34
Choosing
an Authentication Mode 34
Determining
Collation Settings 35
Practice:
Determining Your Windows Collation 37
Selecting
Network Libraries 37
Deciding
on a Client Licensing Mode 38
Lesson
Summary 39
Lesson 3: Running the SQL
Server 2000 Setup Program 39
Running
the SQL Server 2000 Setup Program 40
Understanding
Installation Types 41
Selecting
a Setup Type 42
Practice:
Installing a Default Instance of SQL Server 2000 48
Lesson
Summary 49
Lesson 4: Using Default,
Named, and Multiple Instances of SQL Server 2000 49
Installing
Multiple Instances of SQL Server 2000 50
Using
Multiple Instances of SQL Server 2000 Effectively and Appropriately 51
Understanding
Shared Components Between Instances 51
Understanding
Unique Components Between Instances 51
Working
with Default and Named Instances of SQL Server 2000 52
Lesson
Summary 52
Lesson 5: Performing Unattended
and Remote Installations of SQL Server 2000 53
Performing
an Unattended Installation of SQL Server 2000 53
Creating
Setup Initialization Files for SQL Server 2000 53
Practice:
Performing an Unattended Installation of a Named Instance of SQL Server
2000 55
Performing
a Remote Installation of SQL Server 2000 57
Lesson
Summary 58
Lesson 6: Troubleshooting
a SQL Server 2000 Installation 58
Reviewing
SQL Server 2000 Setup Program Log Files 59
Accessing
SQL Server 2000 Troubleshooting Information Online 59
Reviewing
the SQL Server Error Log and the Windows Application Log 60
Practice:
Reviewing the SQL Server Error Log and the Windows Application Log 60
Lesson
Summary 61
Review 61
CHAPTER 3 Preparing to Use SQL
Server 2000 63
About
This Chapter 63
Before
You Begin 63
Lesson 1: Reviewing the
Results of Installation 64
What
Files and Folders Were Added? 64
What
Permissions Were Set in the NTFS File System 67
Practice:
Reviewing the Files and Folders That Were Created 68
What
Registry Keys Were Added 70
What
Permissions Were Set on Registry Keys 71
Practice:
Reviewing Permissions on Registry Keys 73
What
Programs Were Added to the Start Menu 74
Lesson
Summary 74
Lesson 2: Starting, Stopping,
Pausing, and Modifying SQL Server 2000 Services 75
What
Is the Default Configuration for Each SQL Server Service? 75
Starting,
Stopping, and Pausing SQL Server 2000 Services 76
Practice:
Starting SQL Server Services 79
Changing
the SQL Server or SQL Server Agent Service Account After Setup 80
Lesson
Summary 81
Lesson 3: Working with Osql,
SQL Query Analyzer, and SQL Server Enterprise Manager 82
Working
with Osql 82
Practice:
Using Osql to Query SQL Server 2000 Instances 84
Working
with SQL Query Analyzer 85
Practice:
Using SQL Query Analyzer to Query SQL Server 2000 Instances 87
Working
with SQL Server Enterprise Manager 89
Practice:
Working with the SQL Server Enterprise Manager MMC Console 91
Lesson
Summary 93
Review 94
CHAPTER 4 Upgrading to SQL Server
2000 95
About
This Chapter 95
Before
You Begin 95
Lesson 1: Preparing to Upgrade
96
Working
with Multiple Versions of SQL Server on the Same Computer 96
Choosing
the Appropriate Upgrade Process and Method 97
Determining
Hardware and Software Requirements 99
Preparing
for the Actual Upgrade 100
Lesson
Summary 101
Lesson 2: Performing a Version
Upgrade from SQL Server 7.0 101
Performing
a Version Upgrade 101
Performing
Post-Upgrade Tasks 103
Manually
Upgrading Meta Data Services Tables and the Repository Database 103
Lesson
Summary 104
Lesson 3: Performing an
Online Database Upgrade from SQL Server 7.0 104
Performing
an Online Database Upgrade 104
Performing
Post-Upgrade Tasks 110
Lesson
Summary 110
Lesson 4: Performing a Version
Upgrade from SQL Server 6.5 111
Performing
a Version Upgrade 111
Troubleshooting
a SQL Server 6.5 Upgrade 118
Specifying
a Backward Compatibility Level for Upgraded Databases 119
Lesson
Summary 119
Review 119
CHAPTER 5 Understanding System
and User Databases 121
About
This Chapter 121
Before
You Begin 121
Lesson 1: Understanding
the Database Architecture 122
Introducing
Data Files 122
Practice:
Viewing the Properties of a Data File 123
Allocating
Space for Tables and Indexes 124
Storing
Index and Data Pages 125
Lesson
Summary 126
Lesson 2: Understanding
the Transaction Log Architecture 127
Introducing
Transaction Log Files 128
How the
Transaction Log Works 129
Introducing
Recovery Models 132
Practice:
Viewing the Properties of a Transaction Log and a Database 133
Lesson
Summary 134
Lesson 3: Understanding
and Querying System and Database Catalogs 134
Introducing
System Tables 134
Retrieving
System Information 136
Practice:
Querying System Tables Directly 136
Practice:
Querying System Tables Using System Stored Procedures 137
Practice:
Querying System Tables Using System Functions 140
Practice:
Querying System Tables Using Information Schema Views 141
Lesson
Summary 142
Review 142
CHAPTER 6 Creating and Configuring
User Databases 143
About
This Chapter 143
Before
You Begin 143
Lesson
1: Creating a User Database 144
Creating
a User Database 144
Using
SQL Server Enterprise Manager to Create a User Database 147
Practice:
Creating a Database Using the Create Database Wizard in SQL Server Enterprise
Manager 151
Practice:
Creating a Database Directly Using SQL Server Enterprise Manager 154
Using
the CREATE DATABASE Transact-SQL Statement to Create a User Database 155
Practice:
Creating a Database in SQL Query Analyzer Using the CREATE DATABASE Transact-SQL
Statement 157
Scripting
Databases and Database Objects Using SQL Server Enterprise Manager 158
Practice:
Generating a Transact-SQL Script to Re-create the TSQLDB Database 159
Lesson
Summary 160
Lesson
2: Setting Database Options 161
Introducing
Database Options 161
Viewing
Database Option Settings 161
Modifying
Database Options 162
Lesson
Summary 163
Lesson
3: Managing User Database Size Using Automatic File Growth Appropriately
163
Practice:
Configuring Automatic Filegrowth Settings Using SQL Server Enterprise Manager
164
Using
Automatic File Shrinkage Appropriately 165
Controlling
Data File Size Manually 165
Practice:
Modifying Data File Size Using SQL Server Enterprise Manager 166
Controlling
Transaction Log File Size Manually 167
Creating
Additional Data and Transaction Log Files 168
Lesson
Summary 169
Lesson
4: Placing Database Files on Multiple Disks 170
Introducing
RAID 170
Introducing
Filegroups 170
Configuring
Your SQL Server 2000 Disk Subsystem for Performance, Fault Tolerance, and
Recoverability 172
Moving
Data and Transaction Log Files 174
Lesson
Summary 177
Review 177
CHAPTER 7 Populating a Database
179
About
This Chapter 179
Before
You Begin 179
Lesson 1: Transferring and
Transforming Data 180
Importing
Data 180
DTS Data
Transformations 181
Introducing
the Data Transfer Tools 182
Lesson
Summary 182
Lesson 2: Introducing Microsoft
Data Transformation Services (DTS) 183
Understanding
a DTS Package 183
DTS Package
Storage 188
Introducing
DTS Tools 188
Lesson
Summary 190
Lesson 3: Transferring and
Transforming Data with DTS Graphical Tools 190
Using
the DTS Import/Export Wizard 190
Practice:
Transferring Tables and Data from the Northwind Database Using the DTS
Import/Export Wizard 198
Using
DTS Designer 202
Practice:
Creating a Data Transfer and Transform Package Using DTS Designer 208
Extending
DTS Package Functionality 213
Lesson
Summary 214
Lesson 4: Working with DTS
Packages 215
Understanding
DTS Package Storage Options 215
Using
DTS Package Execution Utilities 217
Using
DTS Package Logs and Exception Files 217
Performing
Disconnected Edits 218
Lesson
Summary 218
Lesson 5: Using the Bulk
Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement 218
Copying
Data in Bulk Using Text Files 219
Using
Bcp 219
Practice:
Importing Data Using Bcp 220
Using
the BULK INSERT Transact-SQL Statement 222
Optimizing
Bulk Copy Operations 222
Lesson
Summary 223
Review 223
CHAPTER 8 Developing a
Data Restoration Strategy 225
Lesson 1: Understanding
Data Restoration Issues 226
Lesson 2: Understanding
the Types of Database Backups 231
Lesson 3: Understanding
the Restoration Process 235
Review 240
CHAPTER 9 Backing Up and Restoring
SQL Server 243
About
This Chapter 243
Before
You Begin 243
Lesson 1: Understanding
Backup Terms, Media, and Devices 244
Defining
Terms 244
Selecting
Backup Media 244
Creating
Permanent Backup Devices 246
Practice:
Creating Backup Devices Using Transact-SQL 248
Lesson
Summary 249
Lesson 2: Backing Up Databases,
Files, Filegroups, and Transaction Logs 249
Perform
Backups Using SQL Server Enterprise Manager 249
Practice:
Backing Up the master Database Using the Create Database Backup Wizard
254
Practice:
Backing Up the msdb Database Directly Using SQL Server Enterprise Manager
259
Perform
Backups Using Transact-SQL 260
Practice:
Performing Backups Using Transact-SQL 262
Lesson
Summary 264
Lesson 3: Restoring a User
Database 264
Determining
the Data Restoration Sequence 265
Practice:
Retrieving Backup Media Information 267
Performing
Restorations Using SQL Server Enterprise Manager 268
Practice:
Performing a Complete Database Restoration 273
Practice:
Performing a Database Restoration to a Specified Point in Time 278
Performing
Restorations Using Transact-SQL 281
Practice:
Performing Restorations Using Transact-SQL 283
Lesson
Summary 285
Lesson 4: Restoring and
Rebuilding System Databases 285
Restoring
the Master Database 285
Rebuilding
the System Databases 287
Lesson
Summary 288
Review 288
CHAPTER 10 Managing Access to
SQL Server 2000 289
About
This Chapter 289
Before
You Begin 289
Lesson 1: Understanding
the Authentication Process 290
Understanding
the SQL Server 2000 Authentication Process 290
Comparing
Windows Authentication and SQL Server Authentication 291
Selecting
a SQL Server 2000 Authentication Mode 293
Practice:
Switching SQL Server 2000 Authentication Modes 295
Understanding
Security Account Delegation 295
Lesson
Summary 298
Lesson 2: Understanding
the Authorization Process 298
Understanding
Server-Wide Permissions 298
Understanding
Database-Specific Permissions 299
Lesson
Summary 301
Lesson 3: Creating and Managing
Logins 302
Granting
Access Using SQL Server Enterprise Manager 302
Practice:
Creating a Login Using the Create Login Wizard 306
Practice:
Creating a Login Directly Using SQL Server Enterprise Manager 311
Granting
Access Using Transact-SQL Statements 314
Practice:
Granting SQL Server 2000 and Database Access Using Transact-SQL 318
Viewing
Access Information 319
Practice:
Viewing SQL Server 2000 Access Information 324
Lesson
Summary 326
Review 326
CHAPTER 11 Managing SQL Server
Permissions 329
About
This Chapter 329
Before
You Begin 329
Lesson 1: Granting Database-Specific
Permissions 330
Implementing
Permissions 330
Practice:
Creating and Testing Permission Conflicts 332
Managing
Statement Permissions 333
Practice:
Granting and Testing Statement Permissions 337
Managing
Object Permissions 340
Viewing
Permissions 346
Practice:
Granting and Testing Object Permissions 346
Lesson
Summary 351
Lesson 2: Using Application
Roles 351
Understanding
Application Roles 351
Creating
Application Roles 352
Activating
and Using Application Roles 353
Practice:
Creating and Testing Application Roles 353
Lesson
Summary 355
Lesson 3: Designing an Access
and Permissions Strategy 355
Permitting
Administrator Access 355
Using
Windows Groups and SQL Server 2000 Server Roles 356
Providing
SQL Server 2000 Access 356
Providing
Database Access 357
Using
Fixed Database Roles for Administrative Access 357
Providing
Data Access 357
Lesson
Summary 358
Review 358
CHAPTER 12 Performing Administrative
Tasks 361
About
This Chapter 361
Before
You Begin 361
Lesson 1: Performing Configuration
Tasks 362
Configuring
Windows 2000 (and Windows NT 4.0) 362
Configuring
the SQL Server Service 365
Configuring
the Service Account 374
Configuring
the SQL Server Agent Service 375
Registering
SQL Server 2000 Instances with SQL Server Enterprise Manager 378
Sharing
Registration Information 383
Lesson
Summary 384
Lesson 2: Setting Up Additional
Features 384
Setting
Up SQL Mail and SQLAgentMail 385
Setting
Up Linked Servers 389
Practice:
Setting Up and Testing a Linked Server Configuration 393
Creating
an ODBC SQL Server Data Source 396
Configuring
SQL Server XML Support in IIS 399
Practice:
Creating an IIS Virtual Directory 404
Lesson
Summary 406
Lesson 3: Performing Maintenance
Tasks 406
Updating
Distribution Statistics 406
Maintaining
Indexes 407
Maintaining
Full-Text Indexes 407
Lesson
Summary 408
Review 409
CHAPTER 13 Automating Administrative
Tasks 411
About
This Chapter 411
Before
You Begin 411
Lesson 1: Defining Operators
412
Methods
of Notification 412
Fail-Safe
Operators 413
Creating
Operators 413
Creating
a Fail-Safe Operator 415
Practice:
Creating Operators and Setting a Fail-Safe Operator 416
Lesson
Summary 418
Lesson 2: Creating Jobs
418
Implementing
Jobs 418
Creating
Jobs 422
Practice:
Creating a Job Using the Create Job Wizard 426
Practice:
Creating a Job Using SQL Server Enterprise Manager Directly 431
Using
Transact-SQL 433
Reviewing
Jobs and Job History 434
Practice:
Reviewing and Modifying a Job and Viewing Job History 437
Lesson
Summary 439
Lesson 3: Configuring Alerts
439
Defining
Alerts 440
Configuring
Alerts 442
Practice:
Creating an Event Alert Using the Create Alert Wizard 446
Practice:
Creating a Performance Condition Alert Using SQL Server Enterprise Manager
Directly 450
Reviewing
Alerts and Alert History 452
Lesson
Summary 453
Lesson 4: Creating a Database
Maintenance Plan 453
Using
the Database Maintenance Plan Wizard 454
Viewing
and Modifying Database Maintenance Plans 460
Using
the Sqlmaint Utility 462
Lesson
Summary 462
Lesson 5: Creating Multiserver
Jobs 462
Creating
Master and Target Servers 463
Creating
Jobs for Target Servers 466
Monitoring
Jobs on Target Servers 467
Practice:
Creating a Multiserver Job 470
Lesson
Summary 472
Review 472
CHAPTER 14 Monitoring SQL Server
Performance and Activity 475
About
This Chapter 475
Before
You Begin 475
Lesson 1: Developing a Performance
Monitoring Methodology 476
Establishing
Monitoring Goals 476
Determining
Resources and Activities to Monitor 478
Lesson
Summary 479
Lesson 2: Choosing Among
Monitoring Tools 479
Using
System Monitor 480
Using
Task Manager 481
Using
SQL Profiler 482
Using
SQL Query Analyzer 484
Using
the SQL Server Enterprise Manager Current Activity Window 485
Using
Transact-SQL 485
Using
SNMP 488
Lesson
Summary 488
Lesson 3: Performing Monitoring
Tasks 489
Monitoring
Resource Usage 489
Practice:
Monitoring System Resources Using System Monitor and Task Manager 498
Monitoring
Stored Procedures, Transact SQL Batches, and User Activity 499
Practice:
Monitoring SQL Batches and Stored Procedures 503
Monitoring
Current Locking and User Activity 504
Practice:
Monitoring Blocking Problems Using the Current Activity Window and System
Stored Procedures 509
Lesson
Summary 511
Review 512
CHAPTER 15 Using SQL Server Replication
513
About
This Chapter 513
Before
You Begin 513
Lesson 1: Introducing Replication
514
Describing
Replication 514
Understanding
the Types of Replication 517
Selecting
a Physical Replication Model 521
Choosing
Replication Implementation Tools 522
Lesson
Summary 523
Lesson 2: Planning for Replication
523
Planning
for Replication Security 523
Filtering
Published Data 524
Choosing
Initial Snapshot Options 525
Lesson
Summary 526
Lesson 3: Implementing Replication
526
Configuring
Distributor and Publisher Properties 526
Practice:
Configuring a Distributor 534
Creating
a Publication 535
Practice:
Creating a Transactional Publication 545
Practice:
Creating a Merge Publication 553
Configuring
Push Subscriptions 556
Practice:
Creating a Push Subscription 563
Configuring
a Pull Subscription 565
Practice:
Creating a Pull Subscription 571
Lesson
Summary 573
Lesson 4: Monitoring and
Administering Replication 573
Monitoring
with Replication Monitor 573
Practice:
Monitoring Replication 577
Reviewing
and Modifying Distributor Properties 578
Reviewing
and Modifying Publication Properties 579
Reviewing
and Modifying Replication Agent Profile 580
Reviewing
and Modifying Subscription Properties 582
Practice:
Resolving Conflicts Interactively Using Windows Synchronization Manager
589
Lesson
Summary 590
Review 591
CHAPTER 16 Maintaining High Availability
593
About
This Chapter 593
Before
You Begin 593
Lesson 1: Using Standby
Servers 594
Using
a Standby Server 594
Automating
Log Shipping 596
Practice:
Creating a Standby Server 604
Monitoring
Log Shipping 607
Practice:
Monitoring Log Shipping 611
Bringing
a Standby Server Online 612
Practice:
Bringing a Standby Server Online as the Primary Server 613
Lesson
Summary 615
Lesson 2: Using Failover
Clustering 615
Introducing
MSCS 616
Creating
a Virtual Server on a Server Cluster 617
Lesson
Summary 619
Review 619
APPENDIX 621
GLOSSARY 637
INDEX 667 |
