| 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 |
