Title:
MCSE training kit : Microsoft SQL Server 2000 system administration
Personal Author:
Publication Information:
Redmond, Wash : Microsoft Press, 2001
Physical Description:
xxviii, 689 p. : ill. ; 23 cm.
ISBN:
9780735612471
General Note:
"Exam 70-228."
Includes index
Available:*
Library | Item Barcode | Call Number | Material Type | Item Category 1 | Status |
|---|---|---|---|---|---|
Searching... | 30000010310212 | QA76.3 R32 2001 | Open Access Book | Gift Book | Searching... |
On Order
Summary
Summary
Designed for the Microsoft Certified Professional (MCP) exam, this instruction book presents lessons and exercises on installing, configuring, and troubleshooting SQL Server 2000. Attention is given to topics like database options, Data Transformation Services, replication services, security, back-
Table of Contents
| About This Book | p. xvii |
| Chapter 1 Overview of SQL Server 2000 | p. 1 |
| Lesson 1 What Is SQL Server 2000? | p. 1 |
| The SQL Server 2000 Environment | p. 2 |
| SQL Server 2000 Components | p. 2 |
| SQL Server 2000 Relational Database Engine | p. 4 |
| SQL Server 2000 Analysis Services | p. 4 |
| Application Support | p. 4 |
| Additional Components | p. 5 |
| SQL Server 2000 Editions | p. 7 |
| Integration with Windows 2000 and Windows NT 4.0 | p. 10 |
| Lesson 2 What Are the SQL Server 2000 Components? | p. 12 |
| Server Components | p. 13 |
| Client-Based Administration Tools and Utilities | p. 14 |
| Client Communication Components | p. 14 |
| SQL Server Books Online | p. 18 |
| Lesson 3 What Is the Relational Database Architecture? | p. 19 |
| System and User Databases | p. 19 |
| Physical Structure of a Database | p. 20 |
| Logical Structure of a Database | p. 21 |
| Lesson 4 What Is the Security Architecture? | p. 23 |
| Authentication | p. 23 |
| Authorization | p. 25 |
| Chapter 2 Installing SQL Server 2000 | p. 27 |
| Lesson 1 Planning to Install SQL Server 2000 | p. 28 |
| What Is the Minimum Hardware Required? | p. 28 |
| Exceeding Minimum Computer Hardware Requirements | p. 31 |
| Lesson 2 Deciding SQL Server 2000 Setup Configuration Options | p. 32 |
| Determining the Appropriate User Account for the SQL Server and SQL Server Agent Services | p. 32 |
| Practice: Creating a Dedicated Windows 2000 User Account | p. 34 |
| Choosing an Authentication Mode | p. 34 |
| Determining Collation Settings | p. 35 |
| Practice: Determining Your Windows Collation | p. 37 |
| Selecting Network Libraries | p. 37 |
| Deciding on a Client Licensing Mode | p. 38 |
| Lesson 3 Running the SQL Server 2000 Setup Program | p. 39 |
| Running the SQL Server 2000 Setup Program | p. 40 |
| Understanding Installation Types | p. 41 |
| Selecting a Setup Type | p. 42 |
| Practice: Installing a Default Instance of SQL Server 2000 | p. 48 |
| Lesson 4 Using Default, Named, and Multiple Instances of SQL Server 2000 | p. 49 |
| Installing Multiple Instances of SQL Server 2000 | p. 50 |
| Using Multiple Instances of SQL Server 2000 Effectively and Appropriately | p. 51 |
| Understanding Shared Components Between Instances | p. 51 |
| Understanding Unique Components Between Instances | p. 51 |
| Working with Default and Named Instances of SQL Server 2000 | p. 52 |
| Lesson 5 Performing Unattended and Remote Installations of SQL Server 2000 | p. 53 |
| Performing an Unattended Installation of SQL Server 2000 | p. 53 |
| Creating Setup Initialization Files for SQL Server 2000 | p. 53 |
| Practice: Performing an Unattended Installation of a Named Instance of SQL Server 2000 | p. 55 |
| Performing a Remote Installation of SQL Server 2000 | p. 57 |
| Lesson 6 Troubleshooting a SQL Server 2000 Installation | p. 58 |
| Reviewing SQL Server 2000 Setup Program Log Files | p. 59 |
| Accessing SQL Server 2000 Troubleshooting Information Online | p. 59 |
| Reviewing the SQL Server Error Log and the Windows Application Log | p. 60 |
| Practice: Reviewing the SQL Server Error Log and the Windows Application Log | p. 60 |
| Chapter 3 Preparing to Use SQL Server 2000 | p. 63 |
| Lesson 1 Reviewing the Results of Installation | p. 64 |
| What Files and Folders Were Added? | p. 64 |
| What Permissions Were Set in the NTFS File System | p. 67 |
| Practice: Reviewing the Files and Folders That Were Created | p. 68 |
| What Registry Keys Were Added | p. 70 |
| What Permissions Were Set on Registry Keys | p. 71 |
| Practice: Reviewing Permissions on Registry Keys | p. 73 |
| What Programs Were Added to the Start Menu | p. 74 |
| Lesson 2 Starting, Stopping, Pausing, and Modifying SQL Server 2000 Services | p. 75 |
| What Is the Default Configuration for Each SQL Server Service? | p. 75 |
| Starting, Stopping, and Pausing SQL Server 2000 Services | p. 76 |
| Practice: Starting SQL Server Services | p. 79 |
| Changing the SQL Server or SQL Server Agent Service Account After Setup | p. 80 |
| Lesson 3 Working with Osql, SQL Query Analyzer, and SQL Server Enterprise Manager | p. 82 |
| Working with Osql | p. 82 |
| Practice: Using Osql to Query SQL Server 2000 Instances | p. 84 |
| Working with SQL Query Analyzer | p. 85 |
| Practice: Using SQL Query Analyzer to Query SQL Server 2000 Instances | p. 87 |
| Working with SQL Server Enterprise Manager | p. 89 |
| Practice: Working with the SQL Server Enterprise Manager MMC Console | p. 91 |
| Chapter 4 Upgrading to SQL Server 2000 | p. 95 |
| Lesson 1 Preparing to Upgrade | p. 96 |
| Working with Multiple Versions of SQL Server on the Same Computer | p. 96 |
| Choosing the Appropriate Upgrade Process and Method | p. 97 |
| Determining Hardware and Software Requirements | p. 99 |
| Preparing for the Actual Upgrade | p. 100 |
| Lesson 2 Performing a Version Upgrade from SQL Server 7.0 | p. 101 |
| Performing a Version Upgrade | p. 101 |
| Performing Post-Upgrade Tasks | p. 103 |
| Manually Upgrading Meta Data Services Tables and the Repository Database | p. 103 |
| Lesson 3 Performing an Online Database Upgrade from SQL Server 7.0 | p. 104 |
| Performing an Online Database Upgrade | p. 104 |
| Performing Post-Upgrade Tasks | p. 110 |
| Lesson 4 Performing a Version Upgrade from SQL Server 6.5 | p. 111 |
| Performing a Version Upgrade | p. 111 |
| Troubleshooting a SQL Server 6.5 Upgrade | p. 118 |
| Specifying a Backward Compatibility Level for Upgraded Databases | p. 119 |
| Chapter 5 Understanding System and User Databases | p. 121 |
| Lesson 1 Understanding the Database Architecture | p. 122 |
| Introducing Data Files | p. 122 |
| Practice: Viewing the Properties of a Data File | p. 123 |
| Allocating Space for Tables and Indexes | p. 124 |
| Storing Index and Data Pages | p. 125 |
| Lesson 2 Understanding the Transaction Log Architecture | p. 127 |
| Introducing Transaction Log Files | p. 128 |
| How the Transaction Log Works | p. 129 |
| Introducing Recovery Models | p. 132 |
| Practice: Viewing the Properties of a Transaction Log and a Database | p. 133 |
| Lesson 3 Understanding and Querying System and Database Catalogs | p. 134 |
| Introducing System Tables | p. 134 |
| Retrieving System Information | p. 136 |
| Practice: Querying System Tables Directly | p. 136 |
| Practice: Querying System Tables Using System Stored Procedures | p. 137 |
| Practice: Querying System Tables Using System Functions | p. 140 |
| Practice: Querying System Tables Using Information Schema Views | p. 141 |
| Chapter 6 Creating and Configuring User Databases | p. 143 |
| Lesson 1 Creating a User Database | p. 144 |
| Creating a User Database | p. 144 |
| Using SQL Server Enterprise Manager to Create a User Database | p. 147 |
| Practice: Creating a Database Using the Create Database Wizard in SQL Server Enterprise Manager | p. 151 |
| Practice: Creating a Database Directly Using SQL Server Enterprise Manager | p. 154 |
| Using the CREATE DATABASE Transact-SQL Statement to Create a User Database | p. 155 |
| Practice: Creating a Database in SQL Query Analyzer Using the CREATE DATABASE Transact-SQL Statement | p. 157 |
| Scripting Databases and Database Objects Using SQL Server Enterprise Manager | p. 158 |
| Practice: Generating a Transact-SQL Script to Re-create the TSQLDB Database | p. 159 |
| Lesson 2 Setting Database Options | p. 161 |
| Introducing Database Options | p. 161 |
| Viewing Database Option Settings | p. 161 |
| Modifying Database Options | p. 162 |
| Lesson 3 Managing User Database Size Using Automatic File Growth Appropriately | p. 163 |
| Practice: Configuring Automatic Filegrowth Settings Using SQL Server Enterprise Manager | p. 164 |
| Using Automatic File Shrinkage Appropriately | p. 165 |
| Controlling Data File Size Manually | p. 165 |
| Practice: Modifying Data File Size Using SQL Server Enterprise Manager | p. 166 |
| Controlling Transaction Log File Size Manually | p. 167 |
| Creating Additional Data and Transaction Log Files | p. 168 |
| Lesson 4 Placing Database Files on Multiple Disks | p. 170 |
| Introducing RAID | p. 170 |
| Introducing Filegroups | p. 170 |
| Configuring Your SQL Server 2000 Disk Subsystem for Performance, Fault Tolerance, and Recoverability | p. 172 |
| Moving Data and Transaction Log Files | p. 174 |
| Chapter 7 Populating a Database | p. 179 |
| Lesson 1 Transferring and Transforming Data | p. 180 |
| Importing Data | p. 180 |
| DTS Data Transformations | p. 181 |
| Introducing the Data Transfer Tools | p. 182 |
| Lesson 2 Introducing Microsoft Data Transformation Services (DTS) | p. 183 |
| Understanding a DTS Package | p. 183 |
| DTS Package Storage | p. 188 |
| Introducing DTS Tools | p. 188 |
| Lesson 3 Transferring and Transforming Data with DTS Graphical Tools | p. 190 |
| Using the DTS Import/Export Wizard | p. 190 |
| Practice: Transferring Tables and Data from the Northwind Database Using the DTS Import/Export Wizard | p. 198 |
| Using DTS Designer | p. 202 |
| Practice: Creating a Data Transfer and Transform Package Using DTS Designer | p. 208 |
| Extending DTS Package Functionality | p. 213 |
| Lesson 4 Working with DTS Packages | p. 215 |
| Understanding DTS Package Storage Options | p. 215 |
| Using DTS Package Execution Utilities | p. 217 |
| Using DTS Package Logs and Exception Files | p. 217 |
| Performing Disconnected Edits | p. 218 |
| Lesson 5 Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement | p. 218 |
| Copying Data in Bulk Using Text Files | p. 219 |
| Using Bcp | p. 219 |
| Practice: Importing Data Using Bcp | p. 220 |
| Using the BULK INSERT Transact-SQL Statement | p. 222 |
| Optimizing Bulk Copy Operations | p. 222 |
| Chapter 8 Developing a Data Restoration Strategy | p. 225 |
| Lesson 1 Understanding Data Restoration Issues | p. 226 |
| Lesson 2 Understanding the Types of Database Backups | p. 231 |
| Lesson 3 Understanding the Restoration Process | p. 235 |
| Chapter 9 Backing Up and Restoring SQL Server | p. 243 |
| Lesson 1 Understanding Backup Terms, Media, and Devices | p. 244 |
| Defining Terms | p. 244 |
| Selecting Backup Media | p. 244 |
| Creating Permanent Backup Devices | p. 246 |
| Practice: Creating Backup Devices Using Transact-SQL | p. 248 |
| Lesson 2 Backing Up Databases, Files, Filegroups, and Transaction Logs | p. 249 |
| Perform Backups Using SQL Server Enterprise Manager | p. 249 |
| Practice: Backing Up the master Database Using the Create Database Backup Wizard | p. 254 |
| Practice: Backing Up the msdb Database Directly Using SQL Server Enterprise Manager | p. 259 |
| Perform Backups Using Transact-SQL | p. 260 |
| Practice: Performing Backups Using Transact-SQL | p. 262 |
| Lesson 3 Restoring a User Database | p. 264 |
| Determining the Data Restoration Sequence | p. 265 |
| Practice: Retrieving Backup Media Information | p. 267 |
| Performing Restorations Using SQL Server Enterprise Manager | p. 268 |
| Practice: Performing a Complete Database Restoration | p. 273 |
| Practice: Performing a Database Restoration to a Specified Point in Time | p. 278 |
| Performing Restorations Using Transact-SQL | p. 281 |
| Practice: Performing Restorations Using Transact-SQL | p. 283 |
| Lesson 4 Restoring and Rebuilding System Databases | p. 285 |
| Restoring the Master Database | p. 285 |
| Rebuilding the System Databases | p. 287 |
| Chapter 10 Managing Access to SQL Server 2000 | p. 289 |
| Lesson 1 Understanding the Authentication Process | p. 290 |
| Understanding the SQL Server 2000 Authentication Process | p. 290 |
| Comparing Windows Authentication and SQL Server Authentication | p. 291 |
| Selecting a SQL Server 2000 Authentication Mode | p. 293 |
| Practice: Switching SQL Server 2000 Authentication Modes | p. 295 |
| Understanding Security Account Delegation | p. 295 |
| Lesson 2 Understanding the Authorization Process | p. 298 |
| Understanding Server-Wide Permissions | p. 298 |
| Understanding Database-Specific Permissions | p. 299 |
| Lesson 3 Creating and Managing Logins | p. 302 |
| Granting Access Using SQL Server Enterprise Manager | p. 302 |
| Practice: Creating a Login Using the Create Login Wizard | p. 306 |
| Practice: Creating a Login Directly Using SQL Server Enterprise Manager | p. 311 |
| Granting Access Using Transact-SQL Statements | p. 314 |
| Practice: Granting SQL Server 2000 and Database Access Using Transact-SQL | p. 318 |
| Viewing Access Information | p. 319 |
| Practice: Viewing SQL Server 2000 Access Information | p. 324 |
| Chapter 11 Managing SQL Server Permissions | p. 329 |
| Lesson 1 Granting Database-Specific Permissions | p. 330 |
| Implementing Permissions | p. 330 |
| Practice: Creating and Testing Permission Conflicts | p. 332 |
| Managing Statement Permissions | p. 333 |
| Practice: Granting and Testing Statement Permissions | p. 337 |
| Managing Object Permissions | p. 340 |
| Viewing Permissions | p. 346 |
| Practice: Granting and Testing Object Permissions | p. 346 |
| Lesson 2 Using Application Roles | p. 351 |
| Understanding Application Roles | p. 351 |
| Creating Application Roles | p. 352 |
| Activating and Using Application Roles | p. 353 |
| Practice: Creating and Testing Application Roles | p. 353 |
| Lesson 3 Designing an Access and Permissions Strategy | p. 355 |
| Permitting Administrator Access | p. 355 |
| Using Windows Groups and SQL Server 2000 Server Roles | p. 356 |
| Providing SQL Server 2000 Access | p. 356 |
| Providing Database Access | p. 357 |
| Using Fixed Database Roles for Administrative Access | p. 357 |
| Providing Data Access | p. 357 |
| Chapter 12 Performing Administrative Tasks | p. 361 |
| Lesson 1 Performing Configuration Tasks | p. 362 |
| Configuring Windows 2000 (and Windows NT 4.0) | p. 362 |
| Configuring the SQL Server Service | p. 365 |
| Configuring the Service Account | p. 374 |
| Configuring the SQL Server Agent Service | p. 375 |
| Registering SQL Server 2000 Instances with SQL Server Enterprise Manager | p. 378 |
| Sharing Registration Information | p. 383 |
| Lesson 2 Setting Up Additional Features | p. 384 |
| Setting Up SQL Mail and SQLAgentMail | p. 385 |
| Setting Up Linked Servers | p. 389 |
| Practice: Setting Up and Testing a Linked Server Configuration | p. 393 |
| Creating an ODBC SQL Server Data Source | p. 396 |
| Configuring SQL Server XML Support in IIS | p. 399 |
| Practice: Creating an IIS Virtual Directory | p. 404 |
| Lesson 3 Performing Maintenance Tasks | p. 406 |
| Updating Distribution Statistics | p. 406 |
| Maintaining Indexes | p. 407 |
| Maintaining Full-Text Indexes | p. 407 |
| Chapter 13 Automating Administrative Tasks | p. 411 |
| Lesson 1 Defining Operators | p. 412 |
| Methods of Notification | p. 412 |
| Fail-Safe Operators | p. 413 |
| Creating Operators | p. 413 |
| Creating a Fail-Safe Operator | p. 415 |
| Practice: Creating Operators and Setting a Fail-Safe Operator | p. 416 |
| Lesson 2 Creating Jobs | p. 418 |
| Implementing Jobs | p. 418 |
| Creating Jobs | p. 422 |
| Practice: Creating a Job Using the Create Job Wizard | p. 426 |
| Practice: Creating a Job Using SQL Server Enterprise Manager Directly | p. 431 |
| Using Transact-SQL | p. 433 |
| Reviewing Jobs and Job History | p. 434 |
| Practice: Reviewing and Modifying a Job and Viewing Job History | p. 437 |
| Lesson 3 Configuring Alerts | p. 439 |
| Defining Alerts | p. 440 |
| Configuring Alerts | p. 442 |
| Practice: Creating an Event Alert Using the Create Alert Wizard | p. 446 |
| Practice: Creating a Performance Condition Alert Using SQL Server Enterprise Manager Directly | p. 450 |
| Reviewing Alerts and Alert History | p. 452 |
| Lesson 4 Creating a Database Maintenance Plan | p. 453 |
| Using the Database Maintenance Plan Wizard | p. 454 |
| Viewing and Modifying Database Maintenance Plans | p. 460 |
| Using the Sqlmaint Utility | p. 462 |
| Lesson 5 Creating Multiserver Jobs | p. 462 |
| Creating Master and Target Servers | p. 463 |
| Creating Jobs for Target Servers | p. 466 |
| Monitoring Jobs on Target Servers | p. 467 |
| Practice: Creating a Multiserver Job | p. 470 |
| Chapter 14 Monitoring SQL Server Performance and Activity | p. 475 |
| Lesson 1 Developing a Performance Monitoring Methodology | p. 476 |
| Establishing Monitoring Goals | p. 476 |
| Determining Resources and Activities to Monitor | p. 478 |
| Lesson 2 Choosing Among Monitoring Tools | p. 479 |
| Using System Monitor | p. 480 |
| Using Task Manager | p. 481 |
| Using SQL Profiler | p. 482 |
| Using SQL Query Analyzer | p. 484 |
| Using the SQL Server Enterprise Manager Current Activity Window | p. 485 |
| Using Transact-SQL | p. 485 |
| Using SNMP | p. 488 |
| Lesson 3 Performing Monitoring Tasks | p. 489 |
| Monitoring Resource Usage | p. 489 |
| Practice: Monitoring System Resources Using System Monitor and Task Manager | p. 498 |
| Monitoring Stored Procedures, Transact SQL Batches, and User Activity | p. 499 |
| Practice: Monitoring SQL Batches and Stored Procedures | p. 503 |
| Monitoring Current Locking and User Activity | p. 504 |
| Practice: Monitoring Blocking Problems Using the Current Activity Window and System Stored Procedures | p. 509 |
| Chapter 15 Using SQL Server Replication | p. 513 |
| Lesson 1 Introducing Replication | p. 514 |
| Describing Replication | p. 514 |
| Understanding the Types of Replication | p. 517 |
| Selecting a Physical Replication Model | p. 521 |
| Choosing Replication Implementation Tools | p. 522 |
| Lesson 2 Planning for Replication | p. 523 |
| Planning for Replication Security | p. 523 |
| Filtering Published Data | p. 524 |
| Choosing Initial Snapshot Options | p. 525 |
| Lesson 3 Implementing Replication | p. 526 |
| Configuring Distributor and Publisher Properties | p. 526 |
| Practice: Configuring a Distributor | p. 534 |
| Creating a Publication | p. 535 |
| Practice: Creating a Transactional Publication | p. 545 |
| Practice: Creating a Merge Publication | p. 553 |
| Configuring Push Subscriptions | p. 556 |
| Practice: Creating a Push Subscription | p. 563 |
| Configuring a Pull Subscription | p. 565 |
| Practice: Creating a Pull Subscription | p. 571 |
| Lesson 4 Monitoring and Administering Replication | p. 573 |
| Monitoring with Replication Monitor | p. 573 |
| Practice: Monitoring Replication | p. 577 |
| Reviewing and Modifying Distributor Properties | p. 578 |
| Reviewing and Modifying Publication Properties | p. 579 |
| Reviewing and Modifying Replication Agent Profile | p. 580 |
| Reviewing and Modifying Subscription Properties | p. 582 |
| Practice: Resolving Conflicts Interactively Using Windows Synchronization Manager | p. 589 |
| Chapter 16 Maintaining High Availability | p. 593 |
| Lesson 1 Using Standby Servers | p. 594 |
| Using a Standby Server | p. 594 |
| Automating Log Shipping | p. 596 |
| Practice: Creating a Standby Server | p. 604 |
| Monitoring Log Shipping | p. 607 |
| Practice: Monitoring Log Shipping | p. 611 |
| Bringing a Standby Server Online | p. 612 |
| Practice: Bringing a Standby Server Online as the Primary Server | p. 613 |
| Lesson 2 Using Failover Clustering | p. 615 |
| Introducing MSCS | p. 616 |
| Creating a Virtual Server on a Server Cluster | p. 617 |
| Appendix | p. 621 |
| Glossary | p. 637 |
| Index | p. 667 |
