Microsoft SQL Server 2000 Resource Kit Book

PART 1 INTRODUCING SQL SERVER 2000 AND THIS RESOURCE KIT Page 1
CHAPTER 1 Introducing the SQL Server 2000 Resource Kit Page 3
 Inside the Resource Kit Page 3
 Additional Sources of Information Page 10
  SQL Server 2000 Product Documentation Page 10
  SQL Server 2000 Internet Sites Page 11
 Conventions Used in This Resource Kit Page 11
 Resource Kit Support Policy Page 11
CHAPTER 2 New Features in SQL Server 2000 Page 13
 Relational Database Enhancements Page 13
 XML Integration of Relational Data Page 18
 Graphical Administration Enhancements Page 19
 Replication Enhancements Page 20
 Data Transformation Services Enhancements Page 24
 Analysis Services Enhancements Page 25
  Cube Enhancements Page 25
  Dimension Enhancements Page 28
  Data Mining Enhancements Page 29
  Security Enhancements Page 31
  Client Connectivity Enhancements in PivotTable Service Page 32
  Other Enhancements Page 32
 Meta Data Services Enhancements Page 34
  Meta Data Browser Enhancement Page 34
  XML Encoding Enhancements Page 34
  Repository Engine Programming Enhancements Page 35
  Repository Engine Modeling Enhancements Page 37
 English Query Enhancements Page 40
 Documentation Enhancements Page 42
PART 2 PLANNING Page 45
CHAPTER 3 Choosing an Edition of SQL Server 2000 Page 47
 Introduction Page 47
 SQL Server 2000 Server Editions Explained Page 48
  SQL Server 2000 Enterprise Edition Page 48
   Scalability Requirements Page 49
   Availability/Uptime Page 49
   Performance Page 49
   Advanced Analysis Page 50
  SQL Server 2000 Standard Edition Page 50
 SQL Server 2000 Editions for Special Uses Page 51
  SQL Server 2000 Personal Edition Page 51
  SQL Server 2000 Developer Edition Page 51
  SQL Server 2000 Evaluation Edition Page 52
  SQL Server 2000 Windows CE Edition Page 52
  SQL Server 2000 Desktop Engine Page 53
 Obtaining SQL Server 2000 Page 54
 Conclusion Page 55
CHAPTER 4 Choosing How to License SQL Server Page 57
 Licensing Model Changes Page 57
 What is a Processor License? Page 58
 Upgrades Page 58
 Choosing a Licensing Model Page 59
  Mixed License Environments Page 60
  Licensing for a Failover Cluster Configuration Page 60
  Licensing for a Multi-Instance Configuration Page 60
   Licensing in Multi-Tier Environments (Including Multiplexing or Pooling) Page 61
  SQL Server 2000 Personal Edition Licensing Page 61
  SQL Server 2000 Desktop Engine Licensing Page 61
 Switching Licenses Page 62
CHAPTER 5 Migrating Access 2000 Databases to SQL Server 2000 Page 63
 Migration Options Page 64
 Before You Migrate Page 64
 Migration Tools Page 65
  Upsizing Wizard Page 65
  SQL Server Tools Used in Migrations Page 66
   SQL Server Enterprise Manager Page 66
   Data Transformation Services (DTS) Page 66
   SQL Query Analyzer Page 67
   SQL Profiler Page 67
 Moving Data Page 67
 Migrating Access Queries Page 68
  Limitations in Upsizing Queries Page 69
  Migrating Access Queries into User–Defined Functions Page 71
  Migrating Access Queries into Stored Procedures and Views Page 71
   Converting Make-Table and Crosstab Queries Page 72
  Migrating Access Queries into Transact-SQL Scripts Page 73
 Additional Design Considerations for Queries Page 73
 Verifying SQL Server–Compliant Syntax Page 75
  Access and SQL Server Syntax Page 76
  Visual Basic Functions Page 78
  Access and SQL Server Data Types Page 79
 Migrating Your Applications Page 80
  Creating a Client/Server Application Page 80
   Converting Code Page 80
   Forms Page 81
  Optimizing the Application for the Client/Server Environment Page 81
  Optimizing Data Structure Page 82
CHAPTER 6 Migrating Sybase Databases to SQL Server 2000 Page 83
 Why Migrate to SQL Server 2000? Page 83
 Understanding the Migration Process Page 86
 Reviewing Architectural Differences Page 87
 Migrating Tables and Data Page 90
 Reviewing the Differences Between Sybase T-SQL and Transact-SQL Page 91
  Transaction Management Page 91
   ROLLBACK Triggers Page 91
   Chained Transactions Page 91
   Transaction Isolation Levels Page 92
   Cursors Page 93
   Cursor Error Checking Page 93
   Index Optimizer Hints Page 94
   Optimizer Hints for Locking Page 94
   Server Roles Page 94
   Raising Errors Page 96
   PRINT Page 96
   Partitioned Tables vs. Row Locking Page 96
  Join Syntax Page 98
  Subquery Behavior Page 98
  Grouping Results Page 99
  System Stored Procedures Page 99
   DUMP/LOAD Page 100
 Understanding Database Administration Differences Page 101
 Migration Checklist Page 103
CHAPTER 7 Migrating Oracle Databases to SQL Server 2000 Page 105
  Target Audience Page 105
 Overview Page 105
  SQL Language Extensions Page 106
  ODBC Page 106
  OLE DB Page 107
  Organization of This Chapter Page 107
 Architecture and Terminology Page 108
  Definition of Database Page 108
  Database System Catalogs Page 109
  Physical and Logical Storage Structures Page 110
  Striping Data Page 110
  Transaction Logs and Automatic Recovery Page 111
  Backing Up and Restoring Data Page 112
  Networks Page 113
  Database Security and Roles Page 114
   Database File Encryption Page 114
   Network Security Page 114
   Login Accounts Page 114
   Groups, Roles, and Permissions Page 115
   Database Users and the guest Account Page 115
   sysadmin Role Page 116
   db_owner Role Page 117
 Defining Database Objects Page 117
  Database Object Identifiers Page 119
  Qualifying Table Names Page 119
  Creating Tables Page 121
  Table and Index Storage Parameters Page 122
  Creating Tables With SELECT Statements Page 122
  Views Page 123
  Indexes Page 125
   Clustered Indexes Page 125
   Nonclustered Indexes Page 127
   Index Syntax and Naming Page 127
   Index Data Storage Parameters Page 128
   Ignoring Duplicate Keys Page 129
   Indexes on Computed Columns Page 129
  Using Temporary Tables Page 129
  Data Types Page 130
   Using Unicode Data Page 131
   User-Defined Data Types Page 132
   SQL Server timestamp Columns Page 132
  Object-Level Permissions Page 133
 Enforcing Data Integrity and Business Rules Page 134
  Entity Integrity Page 135
   Naming Constraints Page 135
   Primary Keys and Unique Columns Page 135
   Adding and Removing Constraints Page 136
   Generating Unique Values Page 138
  Domain Integrity Page 139
   DEFAULT and CHECK Constraints Page 139
   Nullability Page 140
  Referential Integrity Page 141
   Foreign Keys Page 142
  User-Defined Integrity Page 143
   Stored Procedures Page 143
   Delaying the Execution of a Stored Procedure Page 145
   Specifying Parameters in a Stored Procedure Page 146
   Triggers Page 146
 Transactions, Locking, and Concurrency Page 149
  Transactions Page 149
  Locking and Transaction Isolation Page 151
  Dynamic Locking Page 152
  Changing Default Locking Behavior Page 152
  SELECT…FOR UPDATE Page 154
  Explicitly Requesting Table-Level Locks Page 154
  Handling Deadlocks Page 155
  Remote Transactions Page 156
  Distributed Transactions Page 156
  Two-Phase Commit Processing Page 157
 SQL Language Support Page 157
  SELECT and Data Manipulation Statements Page 157
   SELECT Statements Page 158
   INSERT Statements Page 159
   UPDATE Statements Page 160
   DELETE Statements Page 162
   TRUNCATE TABLE Statement Page 163
   Manipulating Data in Identity and timestamp Columns Page 163
   Locking Requested Rows Page 164
   Row Aggregates and the Compute Clause Page 164
   Join Clauses Page 164
   Using SELECT Statements as Table Names Page 166
   Reading and Modifying BLOBs Page 166
  Functions Page 167
   Number/Mathematical Functions Page 167
   Character Functions Page 168
   Date Functions Page 169
   Conversion Functions Page 170
   Other Row-Level Functions Page 170
   Aggregate Functions Page 171
   Conditional Tests Page 171
   Converting Values to Different Data Types Page 172
   User-Defined Functions Page 174
  Comparison Operators Page 175
   Pattern Matches Page 176
   Using NULL in Comparisons Page 177
   String Concatenation Page 177
  Control-of-Flow Language Page 177
   Keywords Page 178
   Declaring Variables Page 179
   Assigning Variables Page 179
   Statement Blocks Page 180
   Conditional Processing Page 181
   Repeated Statement Execution (Looping) Page 181
   GOTO Statement Page 182
   PRINT Statement Page 182
   Returning from Stored Procedures Page 182
   Raising Program Errors Page 183
 Implementing Cursors Page 184
  Cursor Syntax Page 184
  Declaring a Cursor Page 185
  Opening a Cursor Page 186
  Fetching Data Page 186
  CURRENT OF Clause Page 187
  Closing a Cursor Page 187
  Cursor Example Page 187
 Tuning Transact–SQL Statements Page 188
 Using XML Page 190
 Using ODBC Page 190
  Recommended Conversion Strategy Page 191
  ODBC Architecture Page 191
  Forward-Only Cursors Page 192
  Server Cursors Page 193
  Scrollable Cursors Page 194
  Strategies for Using SQL Server Default Result Sets and Server Cursors Page 195
  Multiple Active Statements (hstmt) per Connection Page 196
  Data Type Mappings Page 196
  ODBC Extended SQL Page 198
  Outer Joins Page 198
  Date, Time, and Timestamp Values Page 199
  Calling Stored Procedures Page 199
  Native SQL Translation Page 200
  Manual Commit Mode Page 200
 Developing and Administering Database Replication Page 201
  ODBC, OLE/DB, and Replication Page 202
 Migrating Your Data and Applications Page 203
  Data Migration Using DTS Page 203
  Oracle Call Interface (OCI) Page 204
  Embedded SQL Page 205
  Developer 2000 and Third-Party Applications Page 208
  Internet Applications Page 209
PART 3 DATABASE ADMINISTRATION Page 211
CHAPTER 8 Managing Database Change Page 213
 Preparing for a Changing Environment Page 213
  Conflicting Goals Page 214
  Managing the Development Environment Page 215
   Development Database Process Page 215
   Control: Helping or Hindering? Page 216
   Duplication of the Production Database Page 219
   Security Page 219
   Using Command Line Scripts for Implementation Page 220
   Expecting the Unexpected During Implementation Page 224
  Managing the QA Environment Page 225
   Implementing in QA Page 225
   QA Administration Page 226
  Managing Production Implementations Page 227
   Owning the Change: Production vs. DBA Page 228
   When a Good Plan Comes Together Page 229
  Conclusion Page 231
   Further Reading Page 231
CHAPTER 9 Storage Engine Enhancements Page 233
  Storage Engine Enhancements Page 234
  Interacting with Data Page 237
   Reading Data More Effectively Page 238
   Concurrency Page 239
  Tables and Indexes Page 241
   In-Row Text Page 241
   New Data Types Page 242
   Indexes Page 242
  Logging and Recovery Page 244
   Recovery Models Page 246
  Administrative Improvements Page 249
   Dynamic Tuning Page 251
  Data Storage Components Page 252
   Files, Filegroups, and Disks Page 253
  Innovation and Evolution Page 254
CHAPTER 10 Implementing Security Page 255
 Introduction Page 255
 New Security Features Page 255
  Secure Setup Page 255
  C2 Security Evaluation Completed Page 256
  Kerberos and Delegation in Windows 2000 Environments Page 256
  Security Auditing Page 257
  Elimination of the SQLAgentCmdExec Proxy Account Page 258
  Server Role Enhancements Page 259
  Encryption Page 259
   Network Encryption Using SSL/TLS Page 259
   Encrypted File System Support on Windows 2000 Page 260
   Server-Based Encryption Enhanced Page 260
   DTS Package Encryption Page 261
  Password Protection Page 261
   Backups and Backup Media Sets Page 261
   SQL Server Enterprise Manager Page 261
   Service Account Changes Using SQL Server Enterprise Manager Page 261
  SUID Column Page 261
 Security Model Page 262
  Authentication Modes Page 263
  Using SIDs Internally Page 263
  Roles Page 264
   Public Role Page 264
   Predefined Roles Page 264
   User-Defined Roles Page 266
   Application Roles Page 266
  Securing Access to the Server Page 269
  Securing Access to the Database Page 273
   User-Defined Database Roles Page 274
   Permissions System Page 276
   Granting and Denying Permissions to Users and Roles Page 276
   Ownership Chains Page 279
 Implementation of Server-Level Security Page 280
   Use of SIDs Page 280
   Elimination of SUIDs Page 280
   Generation of GUIDs for Non-Trusted Users Page 281
   Renaming Windows User or Group Accounts Page 281
   sysxlogins System Table Page 281
 Implementation of Object-Level Security Page 284
  How Permissions Are Checked Page 284
   Cost of Changing Permissions Page 285
   Changes to Windows User or Group Account Names Page 285
   sysprocedures System Table Removed Page 286
   WITH GRANT OPTION Page 286
   sysusers System Table Page 286
   sysmembers System Table Page 287
   syspermissions System Table Page 287
   sysprotects System Table Page 288
  Named Pipes and Multiprotocol Permissions Page 288
 Upgrading from SQL Server 7.0 Page 289
 Upgrading from SQL Server 6.5 Page 289
  Upgrade Process Page 289
   Analyzing the Upgrade Output Page 290
   Preparing the SQL Server 6.5 Security Environment Page 291
 Setting Up a Secure SQL Server 2000 Installation Page 292
  Service Accounts Page 293
  File System Page 295
  Registry Page 296
  Auditing Page 296
  Profiling for Auditing Page 297
  Backup and Restore Page 298
   Security of Backup Files and Media Page 298
   Restoring to Another Server Page 298
   Attaching and Detaching Database Files Page 300
  General Windows Security Configurations Page 300
   Additional Resources Page 301
CHAPTER 11 Using BLOBs Page 303
 Designing BLOBs Page 304
  BLOB Storage in SQL Server Page 304
  Learning from the TerraServer Design and Implementation Page 312
  BLOBs in Special Operations Page 315
 Implementing BLOBs Page 316
  BLOBs on the Server Page 318
  BLOBs on the Client Page 325
 Working with BLOBs in SQL Server Page 336
PART 4 AVAILABILITY Page 337
CHAPTER 12 Failover Clustering Page 339
 Enhancements to Failover Clustering Page 339
 Windows Clustering Page 340
  Microsoft Cluster Service Components Page 341
   Hardware Page 341
   Operating System Page 342
   Virtual Server Page 343
   SQL Server 2000 Page 343
   Components Page 343
   Instances of SQL Server Page 344
   How SQL Server 2000 Failover Clustering Works Page 346
 Configuring SQL Server 2000 Failover Cluster Servers Page 347
  Software Requirements Page 347
   Memory Page 348
   Networking Page 351
   Location Page 352
   Hardware Compatibility List Page 352
  Configuration Worksheets Page 352
 Implementing SQL Server 2000 Failover Clustering Page 353
  Prerequisites Page 354
  Installation Order Page 355
  Creating the MS DTC Resources (Windows NT 4.0, Enterprise Edition Only) Page 356
  Best Practices Page 357
   Using More IP Addresses Page 357
   Configuring Node Failover Preferences Page 358
   Memory Configuration Page 359
   Using More Than Two Nodes Page 364
   Failover/Failback Strategies Page 366
 Maintaining a SQL Server 2000 Failover Cluster Page 367
  Backing Up and Restoring Page 367
   Backing Up to Disk Page 368
   Backing Up to Tape Page 368
   Snapshot Backups Page 368
   Backing Up an Entire Clustered System Page 368
  Ensuring a Virtual Server Will Not Fail Due to Other Service Failures Page 369
  Adding, Changing, or Updating a TCP/IP Address Page 369
  Adding or Removing a Cluster Node from the Virtual Server Definition Page 370
 Troubleshooting SQL Server 2000 Failover Clusters Page 371
 Finding More Information Page 372
CHAPTER 13 Log Shipping Page 373
 How Log Shipping Works Page 373
  Components Page 373
   Database Tables Page 374
   Stored Procedures Page 375
   log_shipping_monitor_probe User Page 376
  Log Shipping Process Page 376
   Bringing a Secondary Server Online as a Primary Page 378
 Configuring Log Shipping Page 378
  Keeping the Data in Sync Page 378
  Servers Page 379
   Location Page 379
   Connectivity Page 380
  Keeping Old Transaction Log Files Page 380
  Thresholds Page 380
  Installation Considerations Page 381
  Preparation Worksheet Page 382
 Log Shipping Tips and Best Practices Page 384
  Secondary Server Capacity Page 384
  Generating Database Backups from the Secondary Page 385
  Keeping Logins in Sync Page 385
  Monitoring Log Shipping Page 385
  Modifying or Removing Log Shipping Page 385
  Log Shipping Interoperability Between SQL Server 7.0 and SQL Server 2000 Page 386
  Using the Log Shipped Database to Check the Health of the Production Database Page 386
  Using the Log Shipped Database for Reporting Page 387
  Combining Log Shipping and Snapshot Backups Page 387
  Terminating User Connections in the Secondary Database Page 387
  Warm Standby Role Change Page 388
  Failback to Primary Page 388
   Network Load Balancing and Log Shipping Page 389
  Log Shipping and Replication Page 389
  Log Shipping and Application Code Page 390
  Log Shipping and Failover Clustering Page 390
  Monitor Server Page 390
  Using Full-Text Search with a Log Shipped Database Page 390
 Troubleshooting Page 391
CHAPTER 14 Data Center Availability: Facilities, Staffing, and Operations Page 393
 Data Centers Page 393
 Facility and Equipment Requirements Page 394
  The Data Center Facility Page 394
  Data Center Hardware Page 396
  Data Communication Within the Data Center Page 397
 Staffing Recommendations Page 397
 Operational Guidelines Page 402
  General Operations Page 402
   Quality Assurance Page 402
   Change Control Page 402
   Emergency Preparedness Page 403
  SQL Server Operations Page 404
   Security Page 404
   Monitoring Page 405
   Backup and Recovery Page 408
   Maintenance Page 408
 Application Service Providers Page 409
 Summary Page 410
CHAPTER 15 High Availability Options Page 413
 The Importance of People, Policies, and Processes Page 413
  Are There Any 100 Percent Solutions? Page 414
  Meeting High Uptime Page 414
  Uptime Solutions and Risk Management Page 414
  People: The Best Solution Page 415
   Roles of DBAs? Page 415
  The Essentials of an Operations Plan Page 416
  Planning Redundancy Page 416
  Segmenting Your Solutions Page 417
  Manual Procedures Page 417
  Increased Corporate Awareness: The Importance of Communication Page 417
  High Availability and Mobile and Disconnected Devices Page 418
 The Technical Side of High Availability Page 418
  Hardware Alternatives Page 418
   Disk Drives Page 419
   RAID Page 419
   SANS Page 420
   Disk Configuration Page 420
   RAID Solutions Page 420
  Software Alternatives Page 422
   Windows Clustering and SQL Server 2000 Failover Clustering Page 422
   Cluster Option 1 – Shared Disk Backup Page 425
   Cluster Option 2 – Snapshot Backup Page 425
   Option 3 – Failover Clustering Page 426
   Detail Configuration Showing Database Placement Page 427
   Network Load Balancing Page 428
  SQL Server Alternatives Page 429
   Database Maintenance and Availability Page 430
   Backup and Restore Page 430
   Two-Phase Commit Page 431
   Replication Page 432
   Replication: Immediate Updating with Queued Updating as a Failover Page 434
   Log Shipping Page 435
   Message Queuing Page 436
  Combining SQL Server Solutions Page 437
   Server Clusters, Hardware Mirroring, and Replication Page 438
   Log Shipping with Network Load Balancing Page 438
 Conclusion Page 441
CHAPTER 16 Five Nines: The Ultimate in High Availability Page 443
 Determine Your Desired Level of Nines Page 443
 Achieving High Availability with SQL&