| |
| |
| About This Book | |
| |
| |
| |
| Overview of SQL Server 2000 | |
| |
| |
| |
| What Is SQL Server 2000? | |
| |
| |
| The SQL Server 2000 Environment | |
| |
| |
| SQL Server 2000 Components | |
| |
| |
| SQL Server 2000 Relational Database Engine | |
| |
| |
| SQL Server 2000 Analysis Services | |
| |
| |
| Application Support | |
| |
| |
| Additional Components | |
| |
| |
| SQL Server 2000 Editions | |
| |
| |
| Integration with Windows 2000 and Windows NT 4.0 | |
| |
| |
| |
| What Are the SQL Server 2000 Components? | |
| |
| |
| Server Components | |
| |
| |
| Client-Based Administration Tools and Utilities | |
| |
| |
| Client Communication Components | |
| |
| |
| SQL Server Books Online | |
| |
| |
| |
| What Is the Relational Database Architecture? | |
| |
| |
| System and User Databases | |
| |
| |
| Physical Structure of a Database | |
| |
| |
| Logical Structure of a Database | |
| |
| |
| |
| What Is the Security Architecture? | |
| |
| |
| Authentication | |
| |
| |
| Authorization | |
| |
| |
| |
| Installing SQL Server 2000 | |
| |
| |
| |
| Planning to Install SQL Server 2000 | |
| |
| |
| What Is the Minimum Hardware Required? | |
| |
| |
| Exceeding Minimum Computer Hardware Requirements | |
| |
| |
| |
| Deciding SQL Server 2000 Setup Configuration Options | |
| |
| |
| Determining the Appropriate User Account for the SQL Server and SQL Server Agent Services | |
| |
| |
| Practice: Creating a Dedicated Windows 2000 User Account | |
| |
| |
| Choosing an Authentication Mode | |
| |
| |
| Determining Collation Settings | |
| |
| |
| Practice: Determining Your Windows Collation | |
| |
| |
| Selecting Network Libraries | |
| |
| |
| Deciding on a Client Licensing Mode | |
| |
| |
| |
| Running the SQL Server 2000 Setup Program | |
| |
| |
| Running the SQL Server 2000 Setup Program | |
| |
| |
| Understanding Installation Types | |
| |
| |
| Selecting a Setup Type | |
| |
| |
| Practice: Installing a Default Instance of SQL Server 2000 | |
| |
| |
| |
| Using Default, Named, and Multiple Instances of SQL Server 2000 | |
| |
| |
| Installing Multiple Instances of SQL Server 2000 | |
| |
| |
| Using Multiple Instances of SQL Server 2000 Effectively and Appropriately | |
| |
| |
| Understanding Shared Components Between Instances | |
| |
| |
| Understanding Unique Components Between Instances | |
| |
| |
| Working with Default and Named Instances of SQL Server 2000 | |
| |
| |
| |
| Performing Unattended and Remote Installations of SQL Server 2000 | |
| |
| |
| Performing an Unattended Installation of SQL Server 2000 | |
| |
| |
| Creating Setup Initialization Files for SQL Server 2000 | |
| |
| |
| Practice: Performing an Unattended Installation of a Named Instance of SQL Server 2000 | |
| |
| |
| Performing a Remote Installation of SQL Server 2000 | |
| |
| |
| |
| Troubleshooting a SQL Server 2000 Installation | |
| |
| |
| Reviewing SQL Server 2000 Setup Program Log Files | |
| |
| |
| Accessing SQL Server 2000 Troubleshooting Information Online | |
| |
| |
| Reviewing the SQL Server Error Log and the Windows Application Log | |
| |
| |
| Practice: Reviewing the SQL Server Error Log and the Windows Application Log | |
| |
| |
| |
| Preparing to Use SQL Server 2000 | |
| |
| |
| |
| Reviewing the Results of Installation | |
| |
| |
| What Files and Folders Were Added? | |
| |
| |
| What Permissions Were Set in the NTFS File System | |
| |
| |
| Practice: Reviewing the Files and Folders That Were Created | |
| |
| |
| What Registry Keys Were Added | |
| |
| |
| What Permissions Were Set on Registry Keys | |
| |
| |
| Practice: Reviewing Permissions on Registry Keys | |
| |
| |
| What Programs Were Added to the Start Menu | |
| |
| |
| |
| Starting, Stopping, Pausing, and Modifying SQL Server 2000 Services | |
| |
| |
| What Is the Default Configuration for Each SQL Server Service? | |
| |
| |
| Starting, Stopping, and Pausing SQL Server 2000 Services | |
| |
| |
| Practice: Starting SQL Server Services | |
| |
| |
| Changing the SQL Server or SQL Server Agent Service Account After Setup | |
| |
| |
| |
| Working with Osql, SQL Query Analyzer, and SQL Server Enterprise Manager | |
| |
| |
| Working with Osql | |
| |
| |
| Practice: Using Osql to Query SQL Server 2000 Instances | |
| |
| |
| Working with SQL Query Analyzer | |
| |
| |
| Practice: Using SQL Query Analyzer to Query SQL Server 2000 Instances | |
| |
| |
| Working with SQL Server Enterprise Manager | |
| |
| |
| Practice: Working with the SQL Server Enterprise Manager MMC Console | |
| |
| |
| |
| Upgrading to SQL Server 2000 | |
| |
| |
| |
| Preparing to Upgrade | |
| |
| |
| Working with Multiple Versions of SQL Server on the Same Computer | |
| |
| |
| Choosing the Appropriate Upgrade Process and Method | |
| |
| |
| Determining Hardware and Software Requirements | |
| |
| |
| Preparing for the Actual Upgrade | |
| |
| |
| |
| Performing a Version Upgrade from SQL Server 7.0 | |
| |
| |
| Performing a Version Upgrade | |
| |
| |
| Performing Post-Upgrade Tasks | |
| |
| |
| Manually Upgrading Meta Data Services Tables and the Repository Database | |
| |
| |
| |
| Performing an Online Database Upgrade from SQL Server 7.0 | |
| |
| |
| Performing an Online Database Upgrade | |
| |
| |
| Performing Post-Upgrade Tasks | |
| |
| |
| |
| Performing a Version Upgrade from SQL Server 6.5 | |
| |
| |
| Performing a Version Upgrade | |
| |
| |
| Troubleshooting a SQL Server 6.5 Upgrade | |
| |
| |
| Specifying a Backward Compatibility Level for Upgraded Databases | |
| |
| |
| |
| Understanding System and User Databases | |
| |
| |
| |
| Understanding the Database Architecture | |
| |
| |
| Introducing Data Files | |
| |
| |
| Practice: Viewing the Properties of a Data File | |
| |
| |
| Allocating Space for Tables and Indexes | |
| |
| |
| Storing Index and Data Pages | |
| |
| |
| |
| Understanding the Transaction Log Architecture | |
| |
| |
| Introducing Transaction Log Files | |
| |
| |
| How the Transaction Log Works | |
| |
| |
| Introducing Recovery Models | |
| |
| |
| Practice: Viewing the Properties of a Transaction Log and a Database | |
| |
| |
| |
| Understanding and Querying System and Database Catalogs | |
| |
| |
| Introducing System Tables | |
| |
| |
| Retrieving System Information | |
| |
| |
| Practice: Querying System Tables Directly | |
| |
| |
| Practice: Querying System Tables Using System Stored Procedures | |
| |
| |
| Practice: Querying System Tables Using System Functions | |
| |
| |
| Practice: Querying System Tables Using Information Schema Views | |
| |
| |
| |
| Creating and Configuring User Databases | |
| |
| |
| |
| Creating a User Database | |
| |
| |
| Creating a User Database | |
| |
| |
| Using SQL Server Enterprise Manager to Create a User Database | |
| |
| |
| Practice: Creating a Database Using the Create Database Wizard in SQL Server Enterprise Manager | |
| |
| |
| Practice: Creating a Database Directly Using SQL Server Enterprise Manager | |
| |
| |
| Using the CREATE DATABASE Transact-SQL Statement to Create a User Database | |
| |
| |
| Practice: Creating a Database in SQL Query Analyzer Using the CREATE DATABASE Transact-SQL Statement | |
| |
| |
| Scripting Databases and Database Objects Using SQL Server Enterprise Manager | |
| |
| |
| Practice: Generating a Transact-SQL Script to Re-create the TSQLDB Database | |
| |
| |
| |
| Setting Database Options | |
| |
| |
| Introducing Database Options | |
| |
| |
| Viewing Database Option Settings | |
| |
| |
| Modifying Database Options | |
| |
| |
| |
| Managing User Database Size Using Automatic File Growth Appropriately | |
| |
| |
| Practice: Configuring Automatic Filegrowth Settings Using SQL Server Enterprise Manager | |
| |
| |
| Using Automatic File Shrinkage Appropriately | |
| |
| |
| Controlling Data File Size Manually | |
| |
| |
| Practice: Modifying Data File Size Using SQL Server Enterprise Manager | |
| |
| |
| Controlling Transaction Log File Size Manually | |
| |
| |
| Creating Additional Data and Transaction Log Files | |
| |
| |
| |
| Placing Database Files on Multiple Disks | |
| |
| |
| Introducing RAID | |
| |
| |
| Introducing Filegroups | |
| |
| |
| Configuring Your SQL Server 2000 Disk Subsystem for Performance, Fault Tolerance, and Recoverability | |
| |
| |
| Moving Data and Transaction Log Files | |
| |
| |
| |
| Populating a Database | |
| |
| |
| |
| Transferring and Transforming Data | |
| |
| |
| Importing Data | |
| |
| |
| DTS Data Transformations | |
| |
| |
| Introducing the Data Transfer Tools | |
| |
| |
| |
| Introducing Microsoft Data Transformation Services (DTS) | |
| |
| |
| Understanding a DTS Package | |
| |
| |
| DTS Package Storage | |
| |
| |
| Introducing DTS Tools | |
| |
| |
| |
| Transferring and Transforming Data with DTS Graphical Tools | |
| |
| |
| Using the DTS Import/Export Wizard | |
| |
| |
| Practice: Transferring Tables and Data from the Northwind Database Using the DTS Import/Export Wizard | |
| |
| |
| Using DTS Designer | |
| |
| |
| Practice: Creating a Data Transfer and Transform Package Using DTS Designer | |
| |
| |
| Extending DTS Package Functionality | |
| |
| |
| |
| Working with DTS Packages | |
| |
| |
| Understanding DTS Package Storage Options | |
| |
| |
| Using DTS Package Execution Utilities | |
| |
| |
| Using DTS Package Logs and Exception Files | |
| |
| |
| Performing Disconnected Edits | |
| |
| |
| |
| Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement | |
| |
| |
| Copying Data in Bulk Using Text Files | |
| |
| |
| Using Bcp | |
| |
| |
| Practice: Importing Data Using Bcp | |
| |
| |
| Using the BULK INSERT Transact-SQL Statement | |
| |
| |
| Optimizing Bulk Copy Operations | |
| |
| |
| |
| Developing a Data Restoration Strategy | |
| |
| |
| |
| Understanding Data Restoration Issues | |
| |
| |
| |
| Understanding the Types of Database Backups | |
| |
| |
| |
| Understanding the Restoration Process | |
| |
| |
| |
| Backing Up and Restoring SQL Server | |
| |
| |
| |
| Understanding Backup Terms, Media, and Devices | |
| |
| |
| Defining Terms | |
| |
| |
| Selecting Backup Media | |
| |
| |
| Creating Permanent Backup Devices | |
| |
| |
| Practice: Creating Backup Devices Using Transact-SQL | |
| |
| |
| |
| Backing Up Databases, Files, Filegroups, and Transaction Logs | |
| |
| |
| Perform Backups Using SQL Server Enterprise Manager | |
| |
| |
| Practice: Backing Up the master Database Using the Create Database Backup Wizard | |
| |
| |
| Practice: Backing Up the msdb Database Directly Using SQL Server Enterprise Manager | |
| |
| |
| Perform Backups Using Transact-SQL | |
| |
| |
| Practice: Performing Backups Using Transact-SQL | |
| |
| |
| |
| Restoring a User Database | |
| |
| |
| Determining the Data Restoration Sequence | |
| |
| |
| Practice: Retrieving Backup Media Information | |
| |
| |
| Performing Restorations Using SQL Server Enterprise Manager | |
| |
| |
| Practice: Performing a Complete Database Restoration | |
| |
| |
| Practice: Performing a Database Restoration to a Specified Point in Time | |
| |
| |
| Performing Restorations Using Transact-SQL | |
| |
| |
| Practice: Performing Restorations Using Transact-SQL | |
| |
| |
| |
| Restoring and Rebuilding System Databases | |
| |
| |
| Restoring the Master Database | |
| |
| |
| Rebuilding the System Databases | |
| |
| |
| |
| Managing Access to SQL Server 2000 | |
| |
| |
| |
| Understanding the Authentication Process | |
| |
| |
| Understanding the SQL Server 2000 Authentication Process | |
| |
| |
| Comparing Windows Authentication and SQL Server Authentication | |
| |
| |
| Selecting a SQL Server 2000 Authentication Mode | |
| |
| |
| Practice: Switching SQL Server 2000 Authentication Modes | |
| |
| |
| Understanding Security Account Delegation | |
| |
| |
| |
| Understanding the Authorization Process | |
| |
| |
| Understanding Server-Wide Permissions | |
| |
| |
| Understanding Database-Specific Permissions | |
| |
| |
| |
| Creating and Managing Logins | |
| |
| |
| Granting Access Using SQL Server Enterprise Manager | |
| |
| |
| Practice: Creating a Login Using the Create Login Wizard | |
| |
| |
| Practice: Creating a Login Directly Using SQL Server Enterprise Manager | |
| |
| |
| Granting Access Using Transact-SQL Statements | |
| |
| |
| Practice: Granting SQL Server 2000 and Database Access Using Transact-SQL | |
| |
| |
| Viewing Access Information | |
| |
| |
| Practice: Viewing SQL Server 2000 Access Information | |
| |
| |
| |
| Managing SQL Server Permissions | |
| |
| |
| |
| Granting Database-Specific Permissions | |
| |
| |
| Implementing Permissions | |
| |
| |
| Practice: Creating and Testing Permission Conflicts | |
| |
| |
| Managing Statement Permissions | |
| |
| |
| Practice: Granting and Testing Statement Permissions | |
| |
| |
| Managing Object Permissions | |
| |
| |
| Viewing Permissions | |
| |
| |
| Practice: Granting and Testing Object Permissions | |
| |
| |
| |
| Using Application Roles | |
| |
| |
| Understanding Application Roles | |
| |
| |
| Creating Application Roles | |
| |
| |
| Activating and Using Application Roles | |
| |
| |
| Practice: Creating and Testing Application Roles | |
| |
| |
| |
| Designing an Access and Permissions Strategy | |
| |
| |
| Permitting Administrator Access | |
| |
| |
| Using Windows Groups and SQL Server 2000 Server Roles | |
| |
| |
| Providing SQL Server 2000 Access | |
| |
| |
| Providing Database Access | |
| |
| |
| Using Fixed Database Roles for Administrative Access | |
| |
| |
| Providing Data Access | |
| |
| |
| |
| Performing Administrative Tasks | |
| |
| |
| |
| Performing Configuration Tasks | |
| |
| |
| Configuring Windows 2000 (and Windows NT 4.0) | |
| |
| |
| Configuring the SQL Server Service | |
| |
| |
| Configuring the Service Account | |
| |
| |
| Configuring the SQL Server Agent Service | |
| |
| |
| Registering SQL Server 2000 Instances with SQL Server Enterprise Manager | |
| |
| |
| Sharing Registration Information | |
| |
| |
| |
| Setting Up Additional Features | |
| |
| |
| Setting Up SQL Mail and SQLAgentMail | |
| |
| |
| Setting Up Linked Servers | |
| |
| |
| Practice: Setting Up and Testing a Linked Server Configuration | |
| |
| |
| Creating an ODBC SQL Server Data Source | |
| |
| |
| Configuring SQL Server XML Support in IIS | |
| |
| |
| Practice: Creating an IIS Virtual Directory | |
| |
| |
| |
| Performing Maintenance Tasks | |
| |
| |
| Updating Distribution Statistics | |
| |
| |
| Maintaining Indexes | |
| |
| |
| Maintaining Full-Text Indexes | |
| |
| |
| |
| Automating Administrative Tasks | |
| |
| |
| |
| Defining Operators | |
| |
| |
| Methods of Notification | |
| |
| |
| Fail-Safe Operators | |
| |
| |
| Creating Operators | |
| |
| |
| Creating a Fail-Safe Operator | |
| |
| |
| Practice: Creating Operators and Setting a Fail-Safe Operator | |
| |
| |
| |
| Creating Jobs | |
| |
| |
| Implementing Jobs | |
| |
| |
| Creating Jobs | |
| |
| |
| Practice: Creating a Job Using the Create Job Wizard | |
| |
| |
| Practice: Creating a Job Using SQL Server Enterprise Manager Directly | |
| |
| |
| Using Transact-SQL | |
| |
| |
| Reviewing Jobs and Job History | |
| |
| |
| Practice: Reviewing and Modifying a Job and Viewing Job History | |
| |
| |
| |
| Configuring Alerts | |
| |
| |
| Defining Alerts | |
| |
| |
| Configuring Alerts | |
| |
| |
| Practice: Creating an Event Alert Using the Create Alert Wizard | |
| |
| |
| Practice: Creating a Performance Condition Alert Using SQL Server Enterprise Manager Directly | |
| |
| |
| Reviewing Alerts and Alert History | |
| |
| |
| |
| Creating a Database Maintenance Plan | |
| |
| |
| Using the Database Maintenance Plan Wizard | |
| |
| |
| Viewing and Modifying Database Maintenance Plans | |
| |
| |
| Using the Sqlmaint Utility | |
| |
| |
| |
| Creating Multiserver Jobs | |
| |
| |
| Creating Master and Target Servers | |
| |
| |
| Creating Jobs for Target Servers | |
| |
| |
| Monitoring Jobs on Target Servers | |
| |
| |
| Practice: Creating a Multiserver Job | |
| |
| |
| |
| Monitoring SQL Server Performance and Activity | |
| |
| |
| |
| Developing a Performance Monitoring Methodology | |
| |
| |
| Establishing Monitoring Goals | |
| |
| |
| Determining Resources and Activities to Monitor | |
| |
| |
| |
| Choosing Among Monitoring Tools | |
| |
| |
| Using System Monitor | |
| |
| |
| Using Task Manager | |
| |
| |
| Using SQL Profiler | |
| |
| |
| Using SQL Query Analyzer | |
| |
| |
| Using the SQL Server Enterprise Manager Current Activity Window | |
| |
| |
| Using Transact-SQL | |
| |
| |
| Using SNMP | |
| |
| |
| |
| Performing Monitoring Tasks | |
| |
| |
| Monitoring Resource Usage | |
| |
| |
| Practice: Monitoring System Resources Using System Monitor and Task Manager | |
| |
| |
| Monitoring Stored Procedures, Transact SQL Batches, and User Activity | |
| |
| |
| Practice: Monitoring SQL Batches and Stored Procedures | |
| |
| |
| Monitoring Current Locking and User Activity | |
| |
| |
| Practice: Monitoring Blocking Problems Using the Current Activity Window and System Stored Procedures | |
| |
| |
| |
| Using SQL Server Replication | |
| |
| |
| |
| Introducing Replication | |
| |
| |
| Describing Replication | |
| |
| |
| Understanding the Types of Replication | |
| |
| |
| Selecting a Physical Replication Model | |
| |
| |
| Choosing Replication Implementation Tools | |
| |
| |
| |
| Planning for Replication | |
| |
| |
| Planning for Replication Security | |
| |
| |
| Filtering Published Data | |
| |
| |
| Choosing Initial Snapshot Options | |
| |
| |
| |
| Implementing Replication | |
| |
| |
| Configuring Distributor and Publisher Properties | |
| |
| |
| Practice: Configuring a Distributor | |
| |
| |
| Creating a Publication | |
| |
| |
| Practice: Creating a Transactional Publication | |
| |
| |
| Practice: Creating a Merge Publication | |
| |
| |
| Configuring Push Subscriptions | |
| |
| |
| Practice: Creating a Push Subscription | |
| |
| |
| Configuring a Pull Subscription | |
| |
| |
| Practice: Creating a Pull Subscription | |
| |
| |
| |
| Monitoring and Administering Replication | |
| |
| |
| Monitoring with Replication Monitor | |
| |
| |
| Practice: Monitoring Replication | |
| |
| |
| Reviewing and Modifying Distributor Properties | |
| |
| |
| Reviewing and Modifying Publication Properties | |
| |
| |
| Reviewing and Modifying Replication Agent Profile | |
| |
| |
| Reviewing and Modifying Subscription Properties | |
| |
| |
| Practice: Resolving Conflicts Interactively Using Windows Synchronization Manager | |
| |
| |
| |
| Maintaining High Availability | |
| |
| |
| |
| Using Standby Servers | |
| |
| |
| Using a Standby Server | |
| |
| |
| Automating Log Shipping | |
| |
| |
| Practice: Creating a Standby Server | |
| |
| |
| Monitoring Log Shipping | |
| |
| |
| Practice: Monitoring Log Shipping | |
| |
| |
| Bringing a Standby Server Online | |
| |
| |
| Practice: Bringing a Standby Server Online as the Primary Server | |
| |
| |
| |
| Using Failover Clustering | |
| |
| |
| Introducing MSCS | |
| |
| |
| Creating a Virtual Server on a Server Cluster | |
| |
| |
| Appendix | |
| |
| |
| Glossary | |
| |
| |
| Index | |