1 YEAR UPGRADE
BUYER PROTECTION PLAN
SQL SERVER 2000
FOR
DESIGNING
DATABASES
FREE Monthly
Technology Updates
FREE Downloadable
HTML
FREE Membership to
Access.Globalknowledge
Robert Patton, MCDBA, MCSD, MCSE+I, MCP+I
Jennifer Ogle, MCSE, MCNE, Oracle DBA
TECHNICAL EDITOR and CONTRIBUTOR:
Travis Laird, MCSE, MCDBA, A+, Network+, i-Net+, CIW
“This book is a solid introduction to a critical
component of the Windows 2000 Server family. It
will be a valuable title in your IT library.”
—Richard Martin, Database Administrator and Windows DNA
Developer, MCP+I, MCSE, MCSD, MCDBA, MCT
Dominion Technology Group, Inc.
.
net ENTERPRISE SERVERS
®
™
With over 1,500,000 copies of our MCSE, MCSD, CompTIA, and Cisco
study guides in print, we have come to know many of you personally. By
listening, we've learned what you like and dislike about typical computer
books. The most requested item has been for a web-based service that
keeps you current on the topic of the book and related technologies. In

FOR
.NET ENTERPRISE
SERVERS
114_SQL_FM 1/2/01 2:59 PM Page iii
Syngress Publishing, Inc., the author(s), and any person or firm involved in the writing, editing, or production
(collectively “Makers”) of this book (“the Work”) do not guarantee or warrant the results to be obtained from the
Work.
There is no guarantee of any kind, expressed or implied, regarding the Work or its contents. The Work is sold
AS IS and WITHOUT WARRANTY. You may have other legal rights, which vary from state to state.
In no event will Makers be liable to you for damages, including any loss of profits, lost savings, or other inci-
dental or consequential damages arising out from the Work or its contents. Because some states do not allow
the exclusion or limitation of liability for consequential or incidental damages, the above limitation may not
apply to you.
You should always use reasonable case, including backup and other appropriate precautions, when working
with computers, networks, data, and files.
Syngress Media® and Syngress® are registered trademarks of Syngress Media, Inc. “Career Advancement Through
Skill Enhancement™,” “Ask the Author™,” “Ask the Author UPDATE™,” “Mission Critical™,” and “Hack
Proofing™” are trademarks of Syngress Publishing, Inc. Brands and product names mentioned in this book are
trademarks or service marks of their respective companies.
KEY SERIAL NUMBER
001 58PPL99DSE
002 LSKDJ9878M
003 C3N44T8FQ7
004 KJ675HCC25
005 QCUCA94D26
006 PF62XD2G73
007 DT74HH52A4
008 LKJFARY343
009 65SKNSDAD5
010 6487FPS25N

Bill Richter, Kevin Votel, Brittin Clark, and Sarah MacLachlan of Publishers Group
West for sharing their incredible marketing experience and expertise.
Mary Ging, Caroline Hird, Simon Beale, Caroline Wheeler, Victoria Fuller,
Jonathan Bunkell, and Klaus Beran of Harcourt International for making certain
that our vision remains worldwide in scope.
Anneke Baeten, Annabel Dent, and Laurie Giles of Harcourt Australia for all their
help.
David Buckland, Wendi Wong, Daniel Loh, Marie Chieng, Lucy Chong, Leslie Lim,
Audrey Gan, and Joseph Chan of Transquest Publishers for the enthusiasm with
which they receive our books.
Kwon Sung June at Acorn Publishing for his support.
Ethan Atkin at Cranbury International for his help in expanding the Syngress
program.
Joe Pisco, Helen Moyer, and the great folks at InterCity Press for all their help.
v
114_SQL_FM 1/2/01 2:59 PM Page v
vi
From Global Knowledge
At Global Knowledge we strive to support the multiplicity of learning styles
required by our students to achieve success as technical professionals. As
the world's largest IT training company, Global Knowledge is uniquely
positioned to offer these books. The expertise gained each year from pro-
viding instructor-led training to hundreds of thousands of students world-
wide has been captured in book form to enhance your learning experience.
We hope that the quality of these books demonstrates our commitment to
your lifelong learning success. Whether you choose to learn through the
written word, computer based training, Web delivery, or instructor-led
training, Global Knowledge is committed to providing you with the very
best in each of these categories. For those of you who know Global
Knowledge, or those of you who have just found us for the first time, our

Solutions, LLC. He has worked in the education field for 7 years, with 5
years focused on IT training and consulting. Sean has worked with SQL
Server since version 6. He has developed custom training and delivered
MOC training for a number of years on SQL Server and other development
technologies and has authored numerous Microsoft TechNet sessions on
topics such as supporting SQL and upgrading to SQL Server 2000.
Sean’s consulting experiences have run the gamut from database
administration and application upgrades to .NET development. Sean has
been working on projects with Microsoft since early Beta releases of SQL
Server 2000 and has focused on SQL Server 2000’s administration and
implementation enhancements.
114_SQL_FM 1/2/01 2:59 PM Page vii
viii
Mark Horninger (A+, MCSE+I, MCSD, MCDBA) is President and Founder
of Haverford Consultants, Inc. (www.haverford-consultants.com), located in
the suburbs of Philadelphia, PA. He develops custom applications and
system engineering solutions, specializing primarily in Microsoft operating
systems and Microsoft BackOffice solutions. Mark has over 12 years of
computer consulting experience and has passed 26 Microsoft certified
exams. During his career, Mark has worked on many extensive projects
including database development using SQL 6.5, SQL 7, and SQL 2000;
application development; training; embedded systems development; and
Windows NT and 2000 project rollout planning and implementations. Mark
lives with his wife Debbie and two children in Havertown, PA.
Steve Maier (MCSD) is a Senior Software Engineer for Heidelberg Digital
L.L.C. (www.us.heidelberg.com), located in Rochester, NY. He develops
custom PostScript drivers for Microsoft operating systems. Steve has 10
years of computer programming experience in DOS, Windows, and UNIX.
During his career, Steve has done database development, manufacturing
application development, driver development, and game development. He

architecture. John lives in Philadelphia, PA.
Melissa Craft (CCNA, MCSE, Network+, MCNE, Citrix CCA) is Director of
e-Business Offering Development for MicroAge Technology Services.
MicroAge is a global systems integrator headquartered in Tempe, AZ.
MicroAge provides IT design, project management, and support for dis-
tributed computing systems. Melissa is a key contributor to the business
development and implementation of e-business services. As such, she
develops enterprise-wide technology solutions and methodologies focused
on client organizations. These technology solutions touch every part of a
system’s lifecycle—from network design, testing, and implementation to
operational management and strategic planning.
Melissa holds a bachelor’s degree from the University of Michigan and
is a member of the IEEE, the Society of Women Engineers, and American
MENSA, Ltd. Melissa currently resides in Glendale, AZ with her family,
Dan, Justine, and Taylor. Melissa is the author of Syngress Publishing’s
best-selling Managing Active Directory for Windows 2000 Server (ISBN:
1-928994-07-5).
Cameron Wakefield (MCSD, MCP) is a Senior Software Engineer at
Computer Science Innovations, Inc. (www.csihq.com) in Melbourne, FL,
where he develops custom software solutions ranging from satellite com-
munications to data-mining applications. His development work spans a
broad spectrum including Visual C++, Visual Basic, COM, ADO, ASP,
Delphi, CORBA, and UNIX. Cameron also develops software for a Brazilian
hematology company as well as business-to-business Web applications. He
also teaches Microsoft certification courses for Herzing College (AATP). He
has passed 10 Microsoft certification exams. Cameron’s formal education
was in computer science with a minor in math at Rollins College. He lives
in Rockledge, FL with his wife Lorraine and daughter Rachel.
114_SQL_FM 1/2/01 2:59 PM Page ix
x

The Future of Windows DNA: Microsoft.NET 4
New and Enhanced Features of SQL Server 2000 8
XML Support 10
Development Tools and Technologies 14
Query Analyzer 14
New Data Types 15
Indexed Views 16
Trigger Enhancements 16
Referential Integrity Enhancements 17
User-Defined Functions 18
Index Enhancements 18
Shared Session Information 19
Collation Support 19
Extended Properties 20
Meta Data Services 20
Analysis Services 20
OLAP Enhancements 21
Data Mining 22
SQL Server Administration 23
Windows 2000 Active Directory Integration 24
Scalability and Availability 24
Scalability Enhancements 25
Distributed Partitioned Views 26
Fail-Over Clustering 26
Log Shipping 27
Data Transformation Services 27
Replication Services 28
Active Directory Integration 29
114_SQL_TOC 1/2/01 4:09 PM Page xi
xii Contents

Migrating to SQL Server 2000 46
Upgrading from SQL Server 6.5: Active/Passive Mode 49
Upgrading from SQL Server 6.5: Active/Active Mode 50
Upgrading from SQL Server 7.0: Active/Passive Mode 50
Upgrading from SQL Server 7.0: Active/Active Mode 50
Summary 52
FAQs 54
Chapter 2 Installing and Configuring SQL Server 2000 57
Introduction 58
Planning a SQL Server Installation 59
Installation Requirements 60
Hardware Requirements 60
Software Requirements 61
SQL Server Licensing 62
114_SQL_TOC 1/2/01 4:09 PM Page xii
Contents xiii
Installation Options 63
Local vs. Remote Installation 63
Creating Service Accounts for SQL Server 65
Changing User Accounts 66
Disk Imaging Support 67
Answer File for Automated Installations 67
Multiple Server Instances 68
Collation Options 68
Upgrading to SQL Server 2000 69
Installing SQL Server 71
Standard Installation 71
Advanced Installation 75
Configuring Cluster Support 75
Unattended Installation 76

Data Partitioning 111
114_SQL_TOC 1/2/01 4:09 PM Page xiii
xiv Contents
Creating Distributed Partitioned Views 111
Creating Linked Servers 111
Partitioning Your Data 112
Creating a Distributed View 112
Using and Updating a Distributed View 113
Log Shipping 115
Setting Up Log Shipping 116
Monitoring Log Shipping 120
Indexed Views 121
Requirements for an Indexed View 122
Creating an Indexed View 124
Summary 124
FAQs 125
Chapter 4 Designing and Creating SQL Server Databases 127
Introduction 128
SQL Server 2000 Architecture 128
Relational Databases 129
SQL Server System Databases 131
Master 132
TempDB 132
msdb 133
Model 134
Pubs 134
Northwind 135
Physical Storage Architecture 135
Filegroups 136
Data Files 137

Create Database Wizard 158
Configuring Your Database 158
Southwind Properties | Filegroups 161
Southwind Properties | Data Files 162
Southwind Properties | Transaction Log 164
Southwind Properties | Options 164
Southwind Properties | Permissions 168
Reviewing the Southwind Configuration 168
Using T-SQL to Create and Alter a Database 169
Monitoring and Maintenance 171
Database Maintenance Plan Wizard 171
Maintenance Plan Wizard: Select Databases 172
Maintenance Plan Wizard: Update Data
Optimization Information 172
Maintenance Plan Wizard: Database Integrity Check 174
Maintenance Plan Wizard: Specify the
Database Backup Plan 175
Maintenance Plan Wizard: Specify the
Backup Disk Directory 175
Maintenance Plan Wizard: Specify the
Transaction Log Backup Plan 176
Maintenance Plan Wizard: Specify Transaction
Log Backup Disk Directory 177
Maintenance Plan Wizard: Reports to Generate 177
Maintenance Plan Wizard: Maintenance Plan History 178
Maintenance Plan Wizard: Completing
Your Maintenance Plan 178
Database Modeling Tools 180
Entity-Relationship Diagrams 180
SQL Server Database Designer 184

Server Roles 216
Fixed Roles 216
Database Users 218
Adding New Database Users 219
The Guest User Account 221
Assigning User Permissions 222
Database Roles 225
Fixed Roles 225
User-Defined Roles 227
Implementing Database and Server Security 231
The Scenario 231
User Authentication 232
Operating System Administrative Access 233
Windows 2000 233
SQL Server Logins 234
Assigning Permissions 234
Adding Users to Database Roles 234
Assigning Permissions to Users and Roles 235
114_SQL_TOC 1/2/01 4:09 PM Page xvi
Contents xvii
Network Communications Security 236
Multiprotocol Encryption 236
SSL Support 237
IPSec in Windows 2000 239
Summary 239
FAQs 240
Chapter 6 Administration and Active Directory Integration 243
Introduction 244
Windows 2000 Active Directory Integration 244
Registering SQL Servers in Active Directory 246

SQL Mail 290
Setting Up Operators 292
Defining Alerts 292
Summary 293
FAQs 294
114_SQL_TOC 1/2/01 4:09 PM Page xvii
xviii Contents
Chapter 7 SQL Server Backup and Recovery 295
Introduction 296
Planning and Implementing a Successful Backup
and Recovery Strategy 296
Determining Data Recovery Requirements 296
Frequency of Database Changes 297
Cost of Data Loss and Availability 298
Planning for Hardware Failure 299
The Tape Unit 299
The Disk Unit 300
The Server 301
The Network 301
Selecting a Backup Strategy 302
Backup Strategy Options 302
Database Backup Options 304
Backup Storage 307
Determining Storage Requirements 307
Backup Storage Media 311
Media Sets, Media Families, and Multiple Drives 313
Secure Offsite Storage 315
Sample Backup Scheme 315
Creating a Recovery Strategy 317
Backup and Restore Tools and Techniques 319

Using the SQL Project Wizard 370
English Query OLAP Project 371
The English Query Model 373
Building and Deploying Your English Query Application 376
Implementing Web-Based English Query Applications 376
Testing Your English Query Application 377
Putting It All Together 379
Creating a Web-Based English Query Solution 379
An Overview of Full-Text Search 381
File Filtering 381
Full-Text Search Architecture 382
Microsoft Search Service 382
Performance Considerations for Full-Text Indexes 383
Enabling Full-Text Search 387
Creating a Full-Text Catalog 387
Enabling a Database for Full-Text Search 388
Enabling a Table for Full-Text Search 388
Enabling a Column for Full-Text Search 389
Creating a Full-Text Index on the Products Table
in the Northwind Database 390
Building the Full-Text Index 391
Querying Full-Text Indexes 393
FREETEXT and FREETEXTTABLE 393
CONTAINS and CONTAINSTABLE 395
Administering Full-Text Catalogs and Indexes 398
Backing Up Full-Text Catalogs 399
Populating Full-Text Indexes 401
Scheduling Index Rebuilds 402
Summary 403
FAQs 404

Executing a Package Using the dtsrun.exe Utility 430
Executing a Package Using the dtsrunui.exe Utility 432
Executing a Package Programmatically in Visual Basic 432
The Bulk Copy Program 433
Using BCP 436
SQL-DMO BulkCopy 440
Using the BulkCopy Object 440
The BULK INSERT Command 444
Using BULK INSERT 445
Choosing a Data Import and Export Method 447
Import/Export Job Requirements 447
Existing Data Format 448
Frequency of Import or Export Task 448
Data Manipulation Tasks 448
Performance Considerations 449
Summary 449
FAQs 450
Chapter 10 SQL Server Analysis Services 453
Introduction 454
Online Analytical Processing and Data Mining 454
OLTP vs. OLAP vs. Data Warehousing 455
Data Mining 458
New Features in Analysis Services 459
114_SQL_TOC 1/2/01 4:09 PM Page xx
Contents xxi
OLAP Enhancements 459
Cubes 459
Storage Locations 459
Actions 459
Access from Client Applications 460

Using Your OLAP Solution 482
Querying Cubes 482
HTTP Cube Access 483
Multidimensional Expressions 483
Data Mining in SQL Server 485
Mining Models 485
Relational Data-Mining Models 485
OLAP Data-Mining Models 486
Data Mining Algorithms 486
Creating and Editing Data-Mining Models 486
114_SQL_TOC 1/2/01 4:09 PM Page xxi
xxii Contents
Mining Model Wizard 487
OLAP Mining Model Editor 488
Using Data-Mining Models 488
Data-Mining Training 488
Data-Mining Model Browser 489
Multidimensional Expressions for Data Mining 491
OLE DB for Data Mining 491
Security in Analysis Services 492
Users and Groups 492
Roles 492
Data Security 493
Implementing Security in Analysis Services 493
Accessing Analysis Services Over the Web 494
Configuring IIS for Analysis Services 494
Performance Tuning and Optimization 495
Usage Analysis Wizard 495
Usage-Based Optimization Wizard 496
Summary 496

Additional Information on the
XML Path Language Specification 519
XPath Data Types and Conversions 519
Using XPath Queries 520
Select…For XML 522
FOR XML Syntax and Use 522
Limitations of FOR XML 525
XML Views 525
XML Data-Reduced Schemas 526
Mapping XML Data to Database Tables and Columns 529
SQL Server XML View Mapper 530
Using the SQL Server View Mapper 530
Using and Updating XML Data 534
Updategrams 534
Downloading SQL Server 2000
XML Updategrams Support 534
Understanding Updategrams 535
T-SQL OPENXML Statement 536
sp_xml_preparedocument
and sp_xml_removedocument 536
ActiveX Data Objects 539
XML Support in ADO 2.6 539
Summary 543
FAQs 544
Chapter 12 Database Replication Techniques
and Configuration 545
Introduction 546
SQL Server Replication Architecture 546
Publisher 546
Subscriber 547

Assigning a Distributor Server 565
Creating Publications 566
Create Publication Wizard 567
Adding Subscribers 569
Push vs. Pull Subscriptions 569
Replicating Data Over the Internet 571
Replicating Via a Virtual Private Network 571
Replicating through Microsoft Proxy Server 571
Replication Via FTP 573
Configuring a Publisher or Distributor
to Listen on TCP/IP 573
Configuring a Publication to Allow
Subscribers to Retrieve Snapshots Using FTP 574
Configuring a Subscription to Use FTP
to Retrieve a Snapshot 575
Dealing with Replication Conflicts 575
Replication Conflict Viewer 576
Viewing Conflicts 577
Row-Level vs. Column-Level Conflict Tracking 577
Resolving Conflicts 577
Default Resolvers vs. Custom Resolvers 579
SQL Server CE Edition Replication Features 580
Windows CE Subscribers 581
Replication and Active Directory Integration 582
Registering Publications in Active Directory 582
Browsing and Subscribing to
Publications in Active Directory 584
Replication Performance Considerations 585
Hardware Upgrades 586
114_SQL_TOC 1/2/01 4:09 PM Page xxiv