| About the Contributors | v |
| Acknowledgments | vii |
| Preface | xv |
| Introduction | xvii |
| 1 | Installing SQL Server 2000 | 1 |
| Installing SQL Server | 2 |
| Questions | 3 |
| Failover Clustering | 4 |
| Questions | 6 |
| Default Collation | 8 |
| Questions | 10 |
| Number of Instances | 11 |
| Questions | 12 |
| File Location | 13 |
| Questions | 14 |
| Service Account | 15 |
| Questions | 17 |
| Performing a Custom Upgrade to SQL Server 2000 | 18 |
| Questions | 18 |
| Upgrading to SQL Server 2000 from SQL Server 6.5 | 19 |
| Questions | 20 |
| Upgrading to SQL Server 2000 from SQL Server 7.0 | 21 |
| Questions | 22 |
| Lab Question | 24 |
| Quick Answer Key | 25 |
| In-Depth Answers | 26 |
| Lab Answer | 36 |
| 2 | Configuring SQL Server 2000 | 39 |
| Creating a Linked Server | 40 |
| Questions | 44 |
| Configuring SQL Mail and SQL Agent Mail | 45 |
| Questions | 47 |
| Configuring Network Libraries | 49 |
| Questions | 51 |
| Troubleshooting Failed Installations | 52 |
| Questions | 55 |
| Lab Question | 57 |
| Quick Answer Key | 59 |
| In-Depth Answers | 60 |
| Lab Answer | 69 |
| 3 | Creating SQL 2000 Databases | 71 |
| Creating and Altering Databases | 72 |
| Questions | 76 |
| Setting Database Options | 78 |
| Questions | 81 |
| Adding/Configuring Filegroups | 83 |
| Questions | 86 |
| Expanding and Shrinking Databases | 87 |
| Questions | 89 |
| Sizing and Placing the Transaction Log | 91 |
| Questions | 92 |
| Configuring Database Options for Performance | 95 |
| Questions | 97 |
| Attaching and Detaching Databases | 99 |
| Questions | 101 |
| Lab Question | 103 |
| Quick Answer Key | 105 |
| In-Depth Answers | 106 |
| Lab Answer | 116 |
| 4 | Creating and Managing Database Objects | 119 |
| Creating and Managing Basic Database Objects | 120 |
| Creating and Managing Constraints | 122 |
| Questions | 125 |
| Creating and Managing Indexes | 126 |
| Questions | 131 |
| Creating and Managing Stored Procedures | 132 |
| Questions | 134 |
| Creating and Managing Triggers | 135 |
| Questions | 136 |
| Creating and Managing Views | 137 |
| Questions | 139 |
| Lab Question | 141 |
| Quick Answer Key | 143 |
| In-Depth Answers | 144 |
| Lab Answer | 149 |
| 5 | Managing, Monitoring, and Troubleshooting SQL Server 2000 Databases | 151 |
| Optimizing Database Performance | 152 |
| Questions | 159 |
| Optimizing Data Storage | 162 |
| Questions | 164 |
| Managing Database Schema | 165 |
| Questions | 168 |
| Lab Question | 170 |
| Quick Answer Key | 171 |
| In-Depth Answers | 172 |
| Lab Answer | 175 |
| 6 | Backing Up, Restoring, and Recovering SQL 2000 Databases | 177 |
| Recovering System State | 178 |
| Questions | 181 |
| Backing Up User Databases | 182 |
| Questions | 187 |
| Backing Up System Databases | 189 |
| Questions | 191 |
| Restoring User Databases | 192 |
| Questions | 199 |
| Restoring System Databases | 202 |
| Questions | 204 |
| Configuring, Maintaining, and Troubleshooting Log Shipping | 205 |
| Questions | 207 |
| Performing Integrity Checks | 208 |
| Questions | 212 |
| Troubleshooting Transactions and Locking | 212 |
| Questions | 215 |
| Lab Question | 216 |
| Quick Answer Key | 217 |
| In-Depth Answers | 218 |
| Lab Answer | 227 |
| 7 | Extracting and Transforming Data with SQL Server 2000 | 229 |
| Using XML Support | 230 |
| Questions | 234 |
| Importing and Exporting Data with Data Transformation Services | 235 |
| Questions | 238 |
| Developing and Managing DTS Packages | 240 |
| Questions | 242 |
| Managing Linked Servers | 244 |
| Questions | 246 |
| Converting Data Types | 247 |
| Questions | 249 |
| Configuring, Maintaining, and Troubleshooting Replication Services | 251 |
| Questions | 254 |
| Lab Question | 255 |
| Quick Answer Key | 256 |
| In-Depth Answers | 257 |
| Lab Answer | 262 |
| 8 | Managing and Monitoring SQL Server 2000 Security | 263 |
| Configuring Security Modes | 264 |
| Questions | 266 |
| Creating and Managing Logons | 267 |
| Questions | 271 |
| Creating and Managing Database User Accounts | 273 |
| Questions | 274 |
| Adding and Removing Users from Fixed Roles | 276 |
| Questions | 280 |
| Creating Roles to Manage Database Security | 281 |
| Questions | 284 |
| Setting Permissions in a Database | 286 |
| Questions | 289 |
| Enforcing and Managing Security by Using Stored Procedures, Triggers, Views, and User-Defined Functions | 291 |
| Questions | 294 |
| Managing Security Auditing | 296 |
| Questions | 298 |
| Lab Question | 300 |
| Quick Answer Key | 302 |
| In-Depth Answers | 303 |
| Lab Answer | 311 |
| 9 | Managing SQL Server 2000 Automation | 315 |
| Creating, Managing, and Troubleshooting SQL Server Agent Jobs | 316 |
| Questions | 321 |
| Configuring Alerts Using SQL Server Agent | 322 |
| Questions | 326 |
| Creating and Using User-Defined Event Messages | 328 |
| Questions | 330 |
| Configuring Operators | 332 |
| Questions | 333 |
| Lab Question | 335 |
| Quick Answer Key | 336 |
| In-Depth Answers | 337 |
| Lab Answer | 342 |
| 10 | Managing, Monitoring, and Troubleshooting SQL Server 2000 | 343 |
| Optimizing Hardware Resources | 344 |
| Questions | 347 |
| Monitoring Hardware Using System Monitor | 350 |
| Questions | 352 |
| Resolving System Bottlenecks by Using System Monitor | 354 |
| Questions | 356 |
| Optimizing and Troubleshooting SQL Server System Activity Using the Profiler | 357 |
| Questions | 359 |
| Lab Question | 361 |
| Quick Answer Key | 362 |
| In-Depth Answers | 363 |
| Lab Answer | 368 |
| Practice Exam | 369 |
| Questions | 370 |
| Answers | 395 |
| Glossary | 417 |
