| PART 1 INTRODUCING SQL SERVER 2000 AND THIS RESOURCE KIT | 1 |
| CHAPTER 1 Introducing the SQL Server 2000 Resource Kit | 3 |
| Inside the Resource Kit | 3 |
| Additional Sources of Information | 10 |
| SQL Server 2000 Product Documentation | 10 |
| SQL Server 2000 Internet Sites | 11 |
| Conventions Used in This Resource Kit | 11 |
| Resource Kit Support Policy | 11 |
| CHAPTER 2 New Features in SQL Server 2000 | 13 |
| Relational Database Enhancements | 13 |
| XML Integration of Relational Data | 18 |
| Graphical Administration Enhancements | 19 |
| Replication Enhancements | 20 |
| Data Transformation Services Enhancements | 24 |
| Analysis Services Enhancements | 25 |
| Cube Enhancements | 25 |
| Dimension Enhancements | 28 |
| Data Mining Enhancements | 29 |
| Security Enhancements | 31 |
| Client Connectivity Enhancements in PivotTable Service | 32 |
| Other Enhancements | 32 |
| Meta Data Services Enhancements | 34 |
| Meta Data Browser Enhancement | 34 |
| XML Encoding Enhancements | 34 |
| Repository Engine Programming Enhancements | 35 |
| Repository Engine Modeling Enhancements | 37 |
| English Query Enhancements | 40 |
| Documentation Enhancements | 42 |
| PART 2 PLANNING | 45 |
| CHAPTER 3 Choosing an Edition of SQL Server 2000 | 47 |
| Introduction | 47 |
| SQL Server 2000 Server Editions Explained | 48 |
| SQL Server 2000 Enterprise Edition | 48 |
| Scalability Requirements | 49 |
| Availability/Uptime | 49 |
| Performance | 49 |
| Advanced Analysis | 50 |
| SQL Server 2000 Standard Edition | 50 |
| SQL Server 2000 Editions for Special Uses | 51 |
| SQL Server 2000 Personal Edition | 51 |
| SQL Server 2000 Developer Edition | 51 |
| SQL Server 2000 Evaluation Edition | 52 |
| SQL Server 2000 Windows CE Edition | 52 |
| SQL Server 2000 Desktop Engine | 53 |
| Obtaining SQL Server 2000 | 54 |
| Conclusion | 55 |
| CHAPTER 4 Choosing How to License SQL Server | 57 |
| Licensing Model Changes | 57 |
| What is a Processor License? | 58 |
| Upgrades | 58 |
| Choosing a Licensing Model | 59 |
| Mixed License Environments | 60 |
| Licensing for a Failover Cluster Configuration | 60 |
| Licensing for a Multi-Instance Configuration | 60 |
| Licensing in Multi-Tier Environments (Including Multiplexing or Pooling) | 61 |
| SQL Server 2000 Personal Edition Licensing | 61 |
| SQL Server 2000 Desktop Engine Licensing | 61 |
| Switching Licenses | 62 |
| CHAPTER 5 Migrating Access 2000 Databases to SQL Server 2000 | 63 |
| Migration Options | 64 |
| Before You Migrate | 64 |
| Migration Tools | 65 |
| Upsizing Wizard | 65 |
| SQL Server Tools Used in Migrations | 66 |
| SQL Server Enterprise Manager | 66 |
| Data Transformation Services (DTS) | 66 |
| SQL Query Analyzer | 67 |
| SQL Profiler | 67 |
| Moving Data | 67 |
| Migrating Access Queries | 68 |
| Limitations in Upsizing Queries | 69 |
| Migrating Access Queries into User–Defined Functions | 71 |
| Migrating Access Queries into Stored Procedures and Views | 71 |
| Converting Make-Table and Crosstab Queries | 72 |
| Migrating Access Queries into Transact-SQL Scripts | 73 |
| Additional Design Considerations for Queries | 73 |
| Verifying SQL Server–Compliant Syntax | 75 |
| Access and SQL Server Syntax | 76 |
| Visual Basic Functions | 78 |
| Access and SQL Server Data Types | 79 |
| Migrating Your Applications | 80 |
| Creating a Client/Server Application | 80 |
| Converting Code | 80 |
| Forms | 81 |
| Optimizing the Application for the Client/Server Environment | 81 |
| Optimizing Data Structure | 82 |
| CHAPTER 6 Migrating Sybase Databases to SQL Server 2000 | 83 |
| Why Migrate to SQL Server 2000? | 83 |
| Understanding the Migration Process | 86 |
| Reviewing Architectural Differences | 87 |
| Migrating Tables and Data | 90 |
| Reviewing the Differences Between Sybase T-SQL and Transact-SQL | 91 |
| Transaction Management | 91 |
| ROLLBACK Triggers | 91 |
| Chained Transactions | 91 |
| Transaction Isolation Levels | 92 |
| Cursors | 93 |
| Cursor Error Checking | 93 |
| Index Optimizer Hints | 94 |
| Optimizer Hints for Locking | 94 |
| Server Roles | 94 |
| Raising Errors | 96 |
| PRINT | 96 |
| Partitioned Tables vs. Row Locking | 96 |
| Join Syntax | 98 |
| Subquery Behavior | 98 |
| Grouping Results | 99 |
| System Stored Procedures | 99 |
| DUMP/LOAD | 100 |
| Understanding Database Administration Differences | 101 |
| Migration Checklist | 103 |
| CHAPTER 7 Migrating Oracle Databases to SQL Server 2000 | 105 |
| Target Audience | 105 |
| Overview | 105 |
| SQL Language Extensions | 106 |
| ODBC | 106 |
| OLE DB | 107 |
| Organization of This Chapter | 107 |
| Architecture and Terminology | 108 |
| Definition of Database | 108 |
| Database System Catalogs | 109 |
| Physical and Logical Storage Structures | 110 |
| Striping Data | 110 |
| Transaction Logs and Automatic Recovery | 111 |
| Backing Up and Restoring Data | 112 |
| Networks | 113 |
| Database Security and Roles | 114 |
| Database File Encryption | 114 |
| Network Security | 114 |
| Login Accounts | 114 |
| Groups, Roles, and Permissions | 115 |
| Database Users and the guest Account | 115 |
| sysadmin Role | 116 |
| db_owner Role | 117 |
| Defining Database Objects | 117 |
| Database Object Identifiers | 119 |
| Qualifying Table Names | 119 |
| Creating Tables | 121 |
| Table and Index Storage Parameters | 122 |
| Creating Tables With SELECT Statements | 122 |
| Views | 123 |
| Indexes | 125 |
| Clustered Indexes | 125 |
| Nonclustered Indexes | 127 |
| Index Syntax and Naming | 127 |
| Index Data Storage Parameters | 128 |
| Ignoring Duplicate Keys | 129 |
| Indexes on Computed Columns | 129 |
| Using Temporary Tables | 129 |
| Data Types | 130 |
| Using Unicode Data | 131 |
| User-Defined Data Types | 132 |
| SQL Server timestamp Columns | 132 |
| Object-Level Permissions | 133 |
| Enforcing Data Integrity and Business Rules | 134 |
| Entity Integrity | 135 |
| Naming Constraints | 135 |
| Primary Keys and Unique Columns | 135 |
| Adding and Removing Constraints | 136 |
| Generating Unique Values | 138 |
| Domain Integrity | 139 |
| DEFAULT and CHECK Constraints | 139 |
| Nullability | 140 |
| Referential Integrity | 141 |
| Foreign Keys | 142 |
| User-Defined Integrity | 143 |
| Stored Procedures | 143 |
| Delaying the Execution of a Stored Procedure | 145 |
| Specifying Parameters in a Stored Procedure | 146 |
| Triggers | 146 |
| Transactions, Locking, and Concurrency | 149 |
| Transactions | 149 |
| Locking and Transaction Isolation | 151 |
| Dynamic Locking | 152 |
| Changing Default Locking Behavior | 152 |
| SELECT…FOR UPDATE | 154 |
| Explicitly Requesting Table-Level Locks | 154 |
| Handling Deadlocks | 155 |
| Remote Transactions | 156 |
| Distributed Transactions | 156 |
| Two-Phase Commit Processing | 157 |
| SQL Language Support | 157 |
| SELECT and Data Manipulation Statements | 157 |
| SELECT Statements | 158 |
| INSERT Statements | 159 |
| UPDATE Statements | 160 |
| DELETE Statements | 162 |
| TRUNCATE TABLE Statement | 163 |
| Manipulating Data in Identity and timestamp Columns | 163 |
| Locking Requested Rows | 164 |
| Row Aggregates and the Compute Clause | 164 |
| Join Clauses | 164 |
| Using SELECT Statements as Table Names | 166 |
| Reading and Modifying BLOBs | 166 |
| Functions | 167 |
| Number/Mathematical Functions | 167 |
| Character Functions | 168 |
| Date Functions | 169 |
| Conversion Functions | 170 |
| Other Row-Level Functions | 170 |
| Aggregate Functions | 171 |
| Conditional Tests | 171 |
| Converting Values to Different Data Types | 172 |
| User-Defined Functions | 174 |
| Comparison Operators | 175 |
| Pattern Matches | 176 |
| Using NULL in Comparisons | 177 |
| String Concatenation | 177 |
| Control-of-Flow Language | 177 |
| Keywords | 178 |
| Declaring Variables | 179 |
| Assigning Variables | 179 |
| Statement Blocks | 180 |
| Conditional Processing | 181 |
| Repeated Statement Execution (Looping) | 181 |
| GOTO Statement | 182 |
| PRINT Statement | 182 |
| Returning from Stored Procedures | 182 |
| Raising Program Errors | 183 |
| Implementing Cursors | 184 |
| Cursor Syntax | 184 |
| Declaring a Cursor | 185 |
| Opening a Cursor | 186 |
| Fetching Data | 186 |
| CURRENT OF Clause | 187 |
| Closing a Cursor | 187 |
| Cursor Example | 187 |
| Tuning Transact–SQL Statements | 188 |
| Using XML | 190 |
| Using ODBC | 190 |
| Recommended Conversion Strategy | 191 |
| ODBC Architecture | 191 |
| Forward-Only Cursors | 192 |
| Server Cursors | 193 |
| Scrollable Cursors | 194 |
| Strategies for Using SQL Server Default Result Sets and Server Cursors | 195 |
| Multiple Active Statements (hstmt) per Connection | 196 |
| Data Type Mappings | 196 |
| ODBC Extended SQL | 198 |
| Outer Joins | 198 |
| Date, Time, and Timestamp Values | 199 |
| Calling Stored Procedures | 199 |
| Native SQL Translation | 200 |
| Manual Commit Mode | 200 |
| Developing and Administering Database Replication | 201 |
| ODBC, OLE/DB, and Replication | 202 |
| Migrating Your Data and Applications | 203 |
| Data Migration Using DTS | 203 |
| Oracle Call Interface (OCI) | 204 |
| Embedded SQL | 205 |
| Developer 2000 and Third-Party Applications | 208 |
| Internet Applications | 209 |
| PART 3 DATABASE ADMINISTRATION | 211 |
| CHAPTER 8 Managing Database Change | 213 |
| Preparing for a Changing Environment | 213 |
| Conflicting Goals | 214 |
| Managing the Development Environment | 215 |
| Development Database Process | 215 |
| Control: Helping or Hindering? | 216 |
| Duplication of the Production Database | 219 |
| Security | 219 |
| Using Command Line Scripts for Implementation | 220 |
| Expecting the Unexpected During Implementation | 224 |
| Managing the QA Environment | 225 |
| Implementing in QA | 225 |
| QA Administration | 226 |
| Managing Production Implementations | 227 |
| Owning the Change: Production vs. DBA | 228 |
| When a Good Plan Comes Together | 229 |
| Conclusion | 231 |
| Further Reading | 231 |
| CHAPTER 9 Storage Engine Enhancements | 233 |
| Storage Engine Enhancements | 234 |
| Interacting with Data | 237 |
| Reading Data More Effectively | 238 |
| Concurrency | 239 |
| Tables and Indexes | 241 |
| In-Row Text | 241 |
| New Data Types | 242 |
| Indexes | 242 |
| Logging and Recovery | 244 |
| Recovery Models | 246 |
| Administrative Improvements | 249 |
| Dynamic Tuning | 251 |
| Data Storage Components | 252 |
| Files, Filegroups, and Disks | 253 |
| Innovation and Evolution | 254 |
| CHAPTER 10 Implementing Security | 255 |
| Introduction | 255 |
| New Security Features | 255 |
| Secure Setup | 255 |
| C2 Security Evaluation Completed | 256 |
| Kerberos and Delegation in Windows 2000 Environments | 256 |
| Security Auditing | 257 |
| Elimination of the SQLAgentCmdExec Proxy Account | 258 |
| Server Role Enhancements | 259 |
| Encryption | 259 |
| Network Encryption Using SSL/TLS | 259 |
| Encrypted File System Support on Windows 2000 | 260 |
| Server-Based Encryption Enhanced | 260 |
| DTS Package Encryption | 261 |
| Password Protection | 261 |
| Backups and Backup Media Sets | 261 |
| SQL Server Enterprise Manager | 261 |
| Service Account Changes Using SQL Server Enterprise Manager | 261 |
| SUID Column | 261 |
| Security Model | 262 |
| Authentication Modes | 263 |
| Using SIDs Internally | 263 |
| Roles | 264 |
| Public Role | 264 |
| Predefined Roles | 264 |
| User-Defined Roles | 266 |
| Application Roles | 266 |
| Securing Access to the Server | 269 |
| Securing Access to the Database | 273 |
| User-Defined Database Roles | 274 |
| Permissions System | 276 |
| Granting and Denying Permissions to Users and Roles | 276 |
| Ownership Chains | 279 |
| Implementation of Server-Level Security | 280 |
| Use of SIDs | 280 |
| Elimination of SUIDs | 280 |
| Generation of GUIDs for Non-Trusted Users | 281 |
| Renaming Windows User or Group Accounts | 281 |
| sysxlogins System Table | 281 |
| Implementation of Object-Level Security | 284 |
| How Permissions Are Checked | 284 |
| Cost of Changing Permissions | 285 |
| Changes to Windows User or Group Account Names | 285 |
| sysprocedures System Table Removed | 286 |
| WITH GRANT OPTION | 286 |
| sysusers System Table | 286 |
| sysmembers System Table | 287 |
| syspermissions System Table | 287 |
| sysprotects System Table | 288 |
| Named Pipes and Multiprotocol Permissions | 288 |
| Upgrading from SQL Server 7.0 | 289 |
| Upgrading from SQL Server 6.5 | 289 |
| Upgrade Process | 289 |
| Analyzing the Upgrade Output | 290 |
| Preparing the SQL Server 6.5 Security Environment | 291 |
| Setting Up a Secure SQL Server 2000 Installation | 292 |
| Service Accounts | 293 |
| File System | 295 |
| Registry | 296 |
| Auditing | 296 |
| Profiling for Auditing | 297 |
| Backup and Restore | 298 |
| Security of Backup Files and Media | 298 |
| Restoring to Another Server | 298 |
| Attaching and Detaching Database Files | 300 |
| General Windows Security Configurations | 300 |
| Additional Resources | 301 |
| CHAPTER 11 Using BLOBs | 303 |
| Designing BLOBs | 304 |
| BLOB Storage in SQL Server | 304 |
| Learning from the TerraServer Design and Implementation | 312 |
| BLOBs in Special Operations | 315 |
| Implementing BLOBs | 316 |
| BLOBs on the Server | 318 |
| BLOBs on the Client | 325 |
| Working with BLOBs in SQL Server | 336 |
