Designing SQL Server 2000 Databases for Net Enterprise Server

Contents
xi
Introduction xxvii
Chapter 1 SQL Server 2000 Overview
and Migration Strategies 1
Introduction 2
Overview of SQL Server 2000: A .NET Enterprise Server 3
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
Queued Updating Subscribers 29
Multiple Server Instance Support 29
Web and Internet Standards Support 31
Web Access Using Hypertext Transfer Protocol 31
Secure Sockets Layer 31
SQL Server 2000 Versions, Features, and Requirements 32
Common Edition Requirements 33
SQL Server Licensing and Pricing 34
Enterprise Edition 35
Hardware Requirements and Capacity Limits 35
Operating System Compatibility 36
Standard Edition 36
Hardware Requirements 36
Software and Operating System Compatibility 37
Personal Edition 37
Hardware Requirements 37
Software and Operating System Compatibility 38
Developer Edition 38
SQL Server 2000 Desktop Engine 39
SQL Server 2000 Windows CE Edition 39
Should You Migrate to SQL Server 2000? 41
How Will SQL Server 2000 Benefit My Organization? 42
Will SQL Server 2000 Fit into My Organization? 43
Steps to a Successful SQL Server Migration 43
Planning a SQL Server Migration 44
Determine Existing and Future
Application Requirements 45
Inventory Existing Database Servers 45
Train Database Administrators and Support Personnel 45
Next Steps to Successful SQL Server Migration 46
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
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
Configuration Options and Settings 76
SQL Server Properties 76
Server Network Utility 86
Client Network Utility 87
SQL Server Agent 89
SQL Mail 90
Summary 92
FAQs 92
Chapter 3 SQL Server Scalability and Availability 93
Introduction 94
Scaling Up vs. Scaling Out 94
TPC Benchmarks 97
SQL Server Fail-Over Clustering 98
SQL 2000 Fail-Over Clustering Architecture 99
Planning for SQL Server Clustering 99
Clustering Capabilities and Requirements 100
Microsoft Cluster Service 101
System Area Networks 101
Shared Disks 102
Log Shipping 102
Implementing Fail-Over Clustering 102
Setting Up Network Adapters 103
Setting Up Shared Disks 103
Setting Up MSCS on NT 4.0 104
Setting Up MSCS on Windows 2000
(Advanced Server/Datacenter Server) 104
Upgrading to SQL Fail-Over Clustering 105
Setting Up SQL Server 2000 Fail-Over Clustering 108
Distributed Partitioned Views 109
Federated Servers 109
Data Partitioning 111
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
Transaction Logs 137
Indexes 138
SQL Server Services 139
SQL Server Service 139
SQL Server Agent Service 142
Microsoft Distributed Transaction Coordinator Service 142
Microsoft Search Service 143
MSSQLServerADHelper Service 143
MSSQLServerOLAPService 143
Creating SQL Server Databases 144
Designing Your Database Solution 144
Database Modeling 145
Designing the Physical Database 146
The Disk Subsystem 146
Capacity and Growth Planning 148
Creating and Configuring Your Database 151
Getting Started 151
Using the Create Database Wizard 153
Create Database Wizard: Name the Database
and Specify Its Location 155
Create Database Wizard: Name the Database Files 155
Create Database Wizard: Define the
Database File Growth 156
Create Database Wizard: Name the
Transaction Log Files 156
Create Database Wizard: Define the
Transaction Log File Growth 157
Create Database Wizard: Completing the
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
Summary 186
FAQs 188
Chapter 5 Database and Server Security 189
Introduction 190
Planning SQL Server Security 190
Understanding SQL Server Security 191
C2 Certification 193
Administration Access and Server Security 194
Configuring the SQL Server 2000 Service Accounts 194
Securing the SQL Server 2000
Executable and Data Files 197
Object and Data Security 197
Securing Login Access to SQL Server 198
Assigning Privileges to Perform Serverwide Operations 199
Granting Access to Databases 199
Grouping Users into Database Roles 200
Using Views, Stored Procedures, and
User-Defined Functions to Simplify Security 201
Network Communications Security 201
Security Options in SQL Server 202
Understanding the Windows Authentication Mode 202
Understanding the SQL Authentication Mode 205
Database Users, Roles, and Permissions 206
Selecting a Security Mode 207
SQL Server and Windows Authentication 207
Windows-Only Authentication Mode 210
Logins 210
Adding New Windows Logins 210
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
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 Server Properties 246
sp_ActiveDirectory_SCP 248
Registering Databases in Active Directory 249
Database Properties 249
SQL Replication Services and Active Directory 249
SQL Server 2000 Replication 250
Registering Publications in Active Directory 257
Locating and Using Publications in Active Directory 258
Analysis Services and Active Directory 260
Registering Analysis Servers in Active Directory 261
Tools and Techniques for SQL Server Administration 262
Windows 2000 Active Directory 262
Active Directory Users and Computers 263
Microsoft Management Console 265
SQL Server Enterprise Manager 266
SQL Server MMC Snap-Ins 273
Moving and Copying SQL Server Databases 275
Enterprise Manager 277
Copy Database Wizard 278
Detaching and Attaching Databases 281
Linked Servers 282
Distributed Queries 284
Database Maintenance Tools 284
DBCC 285
Database Maintenance Plans 286
Maintenance Plan Wizard 286
SQL-DMO 288
Automating Administrative Tasks 290
SQL Server Agent 290
Alerts and Operators 290
SQL Mail 290
Setting Up Operators 292
Defining Alerts 292
Summary 293
FAQs 294
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
The Create Database Backup Wizard 319
The Database Maintenance Plan Wizard 320
Transact-SQL 321
Backing Up SQL Server Databases 322
Performing a Database Backup 323
Backing Up System Databases 335
Restoring SQL Server Databases 344
Restoring a Database Backup 344
Restoring System Databases 348
Database Options and Settings 352
Testing Your Backup and Recovery Strategy 354
Summary 355
FAQs 356
Chapter 8 Microsoft English Query and Full-Text Search 359
Introduction 360
Overview of English Query 360
What’s New in English Query? 362
More Powerful Applications 363
Installing English Query 364
Installing English Query 364
Installation Requirements 365
Creating an English Query Application 366
Planning Your English Query Application 366
Understanding Users’ Questions 366
Creating an English Query Project 367
English Query Project Components 368
English Query SQL Project 369
Creating an English Query SQL Project
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
Chapter 9 Impoting and Exporting Data 407
Introduction 408
Overview of Data Impot and Export Tools 408
Data Transformation Services 410
What’s New in DTS? 410
Data Transformation Services Architecture 412
Packages 413
Tasks 413
Transformations 414
Connections 415
Package Workflow 415
Security in DTS Packages 416
DTS Performance Considerations 417
Creating and Editing DTS Packages 419
Creating a Simple Package 419
DTS Impot/Export Wizard 420
Copying a Table Between Databases 421
DTS Designer 422
Creating a Data Connection 422
Creating a Task 423
Saving DTS Packages 427
Saving to a SQL Server 427
Saving to Meta Data Services 428
Saving to a DTS Package File 428
Saving to a Visual Basic Script File 429
Executing DTS Packages 429
Executing a Package in the DTS Designer Interface 430
Executing a Package in SQL Enterprise Manager 430
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 Impot and Export Method 447
Impot/Export Job Requirements 447
Existing Data Format 448
Frequency of Impot 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
OLAP Enhancements 459
Cubes 459
Storage Locations 459
Actions 459
Access from Client Applications 460
Dimensions 460
Security 460
Integrating the Add-Ins 461
Data-Mining Capabilities 461
The Analysis Services Architecture 461
Analysis Server 461
Analysis Manager 462
Cubes 464
Mining Models 465
PivotTable Service 466
Decision Support Objects 466
Installing Analysis Services 466
Analysis Services Requirements 467
Installing Analysis Services for the First Time 467
Upgrading from Earlier Versions 468
Designing and Building an OLAP Solution 469
Designing and Building a Data Warehouse 469
Normalization vs. Denormalization 470
Components of a Data Warehouse 470
Populating Data Warehouses 470
Using DTS to Transform and Load Data 471
Creating an Analysis Services Database 474
Create a Database and Data Source 474
Designing and Building Cubes 475
Using the Cube Wizard 475
Editing Cubes 479
Data Security in Cubes 479
Defining Measures and Dimensions 480
Understanding Measures and Dimensions 480
Creating and Editing Measures and Dimensions 480
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
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
FAQs 498
Chapter 11 Using XML with SQL Server 499
Introduction 500
Overview of XML and SQL Server Support 500
What Is XML? 500
The Benefits of XML 501
Working with XML 502
XML Documents 502
Extensible Stylesheet Language 504
XML Data-Reduced Schemas 505
XML Path Language 506
XML Support and Limitations in SQL Server 509
Additional XML Resources on the Web 509
W3C.org 510
Biztalk.org 510
XML.org 510
MSDN.Microsoft.com/XML 510
HTTP and URL Query Support 510
Configuring IIS for HTTP Query Support 510
Creating a Database Virtual Directory in IIS 511
Querying SQL Server Using HTTP 512
Supported Query Methods Using HTTP 513
Executing SQL Using HTTP 513
Creating an XML Query Template 515
Executing XML Query Templates 516
XPath Queries 518
Overview of XPath Queries and SQL Server Limitations 518
XPath Limitations in SQL Server 519
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
Distributor 547
Publication 547
Article 547
Subscription 548
SQL Server Agent 548
Replication Agents 548
New Replication Features in SQL Server 549
Replication Compatibility 550
Previous Versions of SQL Server 550
Heterogeneous Publishers and Subscribers 551
Heterogeneous Subscribers 551
Heterogeneous Publishers 552
Designing for Database Replication 552
Replication Requirements 552
Data Location 552
Data Modification 552
Connection Bandwidth and Availability 553
Application and Database Design Considerations 553
Minimize Potential Conflicts 554
Table RowGuid and Identity Values 555
Replication Methods in SQL Server 560
Transactional Replication 560
Queued Updating Subscribers 561
Merge Replication 561
Snapshot Replication 563
Selecting a Replication Method 564
Configuring SQL Server Replication 565
Enabling Server Publishing 565
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
How to Enhance Snapshot and
Transactional Replication Performance 587
How to Enhance Transactional
Replication Performance 588
How to Enhance Merge Replication Performance 588
Replication Backup Strategies 588
Backing Up and Restoring Snapshot Replication 590
Backup and Restore of Transactional Replication 590
Using Log Shipping as a Backup to
Transactional Replication 590
Backing Up and Restoring Merge Replication 591
How to Restore a Replicated Database from Backup 591
Summary 594
FAQs 595
Chapter 13 Programming Tools and Technologies
in SQL Server 597
Introduction 598
Overview of SQL Server Programming 598
New Programming Features in SQL Server 2000 599
Data Types 600
Bigint 600
Sql_variant 601
Table 603
Query Analyzer 604
SQL Debugger 607
User-Defined Functions 609
Scalar User-Defined Functions 610
Table User-Defined Functions 611
Inline User-Defined Functions 612
Referential Integrity Enhancements 613
Cascading Updates and Deletes 613
Trigger Enhancements 614
INSTEAD OF 614
AFTER 616
Indexed Views 617
Meta Data Services 622
Transact-SQL 624
Data Definition Language 625
Data Manipulation Language 626
Data Control Language 631
Data Access Tools and Technologies 633
Command-Line Utilities 633
ADO, OLE DB, and ODBC 634
Programming Administrative Tasks 639
Distributed Management Objects 640
Namespaces 642
Analysis Services Programming 643
DTS Programming 645
Replication Programming 646
Meta Data Services Programming 647
Summary 648
FAQs 649
Chapter 14 Performance-Tuning Tools and Techniques 651
Introduction 652
Partitioning Data and Federated Database Servers 652
Overview 653
Designing Your Tables 656
Configuring the Servers 657
Creating the View 659
Optimizing Query Performance 659
Understanding Indexes 660
Types of Indexes 661
Clustered Indexes 661
Nonclustered Indexes 663
Covering Indexes 664
Optimizing Database Performance with SQL Profiler 665
Exercise 1: Setting Up a Trace with SQL Profiler
for the Northwind Database 667
Index Tuning Wizard 668
Exercise 2: Loading SQL Profiler trace file
into the Index Tuning Wizard 668
SQL Query Analyzer 669
Tips for Writing Better Queries 669
Query Execution Plan 671
Optimizing Server Performance 673
Hardware Configuration 674
Processors 674
Memory 675
Disks 675
Software Configuration 678
SQL Server Settings 679
Windows 2000 Settings 682
Performance Monitor 684
Summary 688
FAQs 690
Index 695

Download
Another Web Programming Books
Another Database Books