Table of Contents

Updated : July 19, 2001


William R. Stanek






All Levels





The table of contents of Microsoft SQL Server 2000 Administrator's Pocket Consultant, reprinted with permission from Microsoft Press.

Table of Contents




1 Microsoft SQL Server 2000 Administration Overview
SQL Server 2000 and Your Hardware
SQL Server 2000 and SQL Server Desktop
SQL Server and Windows 2000
Services for SQL Server
Taskbar Extras for SQL Server
Authentication Enhancements
Service Accounts
SQL Server and Active Directory
SQL Server Administration Tools
Graphical Administration Tools
Administration Wizards and the Taskpad
Command-Line Tools
2 Configuring and Tuning Microsoft SQL Server
Running and Modifying SQL Server Setup
Creating New Instances of SQL Server
Adding Components
Restoring the SQL Server Registry
Uninstalling SQL Server
Configuring SQL Server with Enterprise Manager
Determining System and Server Information
Configuring Startup
Setting Startup Parameters
Setting the Startup Service Account
Authentication and Auditing
Tuning Memory Usage
Configuring Processors and Parallel Processing
Configuring Threading, Priority, and Fibers
Configuring User and Remote Connections
Server Settings
Database Settings
Adding and Removing Active Directory Information
Configuring SQL Server with Stored Procedures
Starting Query Analyzer
Changing Settings with sp_configure
Configuration Parameters
Troubleshooting Configuration Problems
Recovering from a Bad Configuration
Changing Collation and Rebuilding the Master Database


3 Managing the Enterprise
Starting, Stopping, and Pausing SQL Server
Managing SQL Server Service with Service Manager
Managing SQL Server Service from the Command Line
Managing the SQL Server Command-Line Executable
Using SQL Server Enterprise Manager
Getting Started with Enterprise Manager
Managing SQL Server Groups
Creating a Server Group
Deleting a Server Group
Adding SQL Servers to a Group
Managing Servers
Registering a Server with a Wizard
Registering a Server Manually
Editing Registration Properties
Moving a Server to a New Group
Deleting a Server Registration
Connecting to a Server
Disconnecting from a Server
Starting, Stopping, and Configuring SQL Server Agent
Starting, Stopping, and Configuring the Microsoft Distributed Transaction Coordinator
Installing and Configuring Full-Text Search
Using Full-Text Searches
Starting, Stopping, and Configuring the Microsoft Search Service
Locating Files Used for Full-Text Searches
Managing Catalogs
Managing SQL Mail and SQL Server Agent Mail
Setting Up SQL Server as a Mail Client
Testing the Mail Installation
Configuring SQL Mail
Configuring SQL Server Agent Mail
Managing Server Activity
Examining Process Information
Tracking Locks by Process ID and Object
Troubleshooting Deadlocks and Blocking Connections
Tracking Command Execution in SQL Server
Sending Messages to Users Based on Connections
Killing Server Processes
4 Core Database Administration
Database Files and Logs
Database Administration Basics
Viewing Database Information in Enterprise Manager
Viewing Database Information Using SQL
Checking System and Sample Databases
Examining Database Objects
Creating Databases
Creating Databases in Enterprise Manager
Creating Databases Using T-SQL
Setting Database Options
Setting Database Options in Enterprise Manager
Database Options for Enterprise Manager and Transact-SQL
Viewing, Changing, and Overriding Database Options
Managing Database and Log Size
Configuring SQL Server to Automatically Manage File Size
Expanding Databases and Logs Manually
Compacting and Shrinking a Database Manually
Manipulating Databases
Temporarily Restricting Database Access
Renaming a Database
Dropping and Deleting a Database
Attaching and Detaching Databases
Copying Databases with Attach
Moving Databases with Detach and Attach
Moving a Database to Another Server
Altering a Database
Tips and Techniques
Moving and Resizing tempdb
Creating Secondary Data and Log Files
Preventing Transaction Log Errors
Resolving a Filegroup Is Full Error
Creating a New Database Template
5 Microsoft SQL Server 2000 Security
SQL Server 2000 Security
SQL Server 2000 Authentication Modes
Server Logins
Managing Server Logins
Viewing and Editing Existing Logins
Creating Logins
Granting or Denying Server Access
Removing Logins
Changing Passwords
Configuring Server Roles
Assigning Roles by Login
Assigning Roles to Multiple Logins
Controlling Database Access and Administration
Assigning Access and Roles by Login
Assigning Roles for Multiple Logins
Creating Standard Database Roles
Creating Application Database Roles
Revoking Access Rights and Roles by Login
Deleting User-Defined Roles
Transact-SQL Commands for Managing Access and Roles
Managing Database Permissions
Assigning Database Permissions for Statements
Object Permissions by Login
Object Permissions for Multiple Logins
6 Manipulating Tables, Indexes, and Views
Table Essentials
Understanding Data Pages
Understanding Extents
Working with Tables
Creating Tables
Modifying Existing Tables
Viewing Table Row and Size Information
Displaying Table Properties and Permissions
Displaying Current Values in Tables
Copying Tables
Renaming and Deleting Tables
Adding and Removing Columns in a Table
Scripting Tables
Managing Table Values
Using Native Data Types
Using Fixed-Length and Variable-Length Fields
Using User-Defined Data Types
Allowing and Disallowing Nulls
Default Values
Using Identities and Globally Unique Identifiers
Using Views
Creating Views
Using Updateable Views
Managing Views
Creating and Managing Indexes
Understanding Indexes
Clustered and Nonclustered Indexes
Which Columns Should Be Indexed?
Indexing Computed Columns and Views
Viewing Indexes
Creating Indexes
Managing Indexes
Using the Index Tuning Wizard
Column Constraints and Rules
Using Constraints
Using Rules
7 Importing and Exporting Data with DTS and BCP
Understanding DTS
DTS and Database Drivers
DTS Packages
Other DTS Tools
Creating Packages with the DTS Import/Export Wizard
Stage 1: Source and Destination Configuration
Stage 2: Copy, Query, or Transfer
Stage 3: Formatting and Transformation
Stage 4: Save, Schedule, and Replicate
Working with DTS Packages
Examining, Running, and Scheduling Packages
Managing Packages Using the DTS Run Utility for Windows
Managing Packages from the Command Line
Understanding BCP
BCP Basics
BCP Syntax
BCP Permissions and Modes
Importing Data with BCP
Exporting Data with BCP
BCP Scripts
8 Linked Servers and Distributed Transactions
Working with Linked Servers and Distributed Data
Using Distributed Queries
Using Distributed Transactions
Running the Distributed Transaction Coordinator Service
Managing Linked Servers
Adding Linked Servers
Configuring Security for Linked Servers
Deleting Linked Servers
Working with Remote Servers
Setting Up Remote Servers in Enterprise Manager
Setting Up Remote Servers Using Stored Procedures
Executing Remote Stored Procedures
9 Configuring Snapshot, Merge, and Transactional Replication
An Overview of Replication
Replication Components
Replication Agents
Replication Variants
Planning for Replication
Replication Models
Preliminary Replication Tasks
Distributor Administration
Setting Up a New Distributor
Updating Distributors
Creating Distribution Databases
Enabling Publishers
Enabling Publication Databases
Enabling Subscribers
Deleting Distribution Databases
Disabling Publishing and Distribution
Creating and Managing Publications
Creating Publications
Updating a Publication
Setting Article Properties
Controlling Subscription Access to a Publication
Creating a Script for a Publication
Deleting a Publication
Subscribing to a Publication
Subscription Essentials
Creating Pull Subscriptions
Updating, Maintaining, and Deleting Pull Subscriptions
Creating Push Subscriptions
Updating, Maintaining, and Deleting Push Subscriptions
Transforming Published Data
Managing Replication Monitors
Configuring Server-Level Monitoring
Configuring Enterprise-Level Monitoring
Setting Agent Profiles
Setting Agent Properties
10 Profiling and Monitoring Microsoft SQL Server 2000
Monitoring Server Performance and Activity
Why Monitor SQL Server?
Getting Ready to Monitor
Monitoring Tools and Resources
Working with the Error Logs
Examining the Application Log
Examining the SQL Server Error Logs
Examining the SQL Server Agent Error Logs
Monitoring SQL Server Performance
Starting Performance Monitor
Choosing Counters to Monitor
Creating and Managing Performance Monitor Logs
Replaying Performance Logs
Configuring Alerts for Performance Counters
Solving Performance Problems with Profiler
Using Profiler
Creating New Traces
Working with Traces
Saving a Trace
Replaying Traces
11 Database Backup and Recovery
Creating a Backup and Recovery Plan
Initial Backup and Recovery Planning
Planning for Backups of Replicated Databases
Planning for Backups of Very Large Databases
Selecting Backup Devices and Media
Using Backup Strategies
Creating a Backup Device
Performing Backups
Creating Backups in Enterprise Manager
Using Striped Backups with Multiple Devices
Using Transact-SQL Backup
Performing Transaction Log Backups
Restoring a Database
Database Corruption and Problem Resolution
Restoring a Database from a Normal Backup
Restoring Files and Filegroups
Restoring a Database from a Device
Restoring a Database to a Different Location
Recovering Missing Data
Creating Standby Servers
Using Transact-SQL Restore
Restoring the Master Database
12 Database Automation and Maintenance
Using SQL Server Agent
Accessing Alerts, Operators, and Jobs
Configuring the SQL Server Agent Service
Configuring SQL Server Agent
Viewing SQL Server Agent Logs
Managing Alerts
Default Alerts
Creating Error Message Alerts
Handling Alert Responses
Deleting, Enabling, and Disabling Alerts
Managing Operators
Registering Operators
Deleting and Disabling Notification for Operators
Configuring a Fail-Safe Operator
Scheduling Jobs
Creating Jobs
Assigning or Changing Job Definitions
Setting Steps to Execute
Configuring Job Schedules
Handling Notification Messages
Managing Existing Jobs
Managing Job Categories
Automating Routine Server-to-Server Administration Tasks
Copying Logins to Another Server
Copying Scheduled Jobs to Another Server
Copying Shared Stored Procedures to Another Server
Copying User-Defined Error Messages to Another Server
Copying or Moving User-Defined Databases to Another Server
Multiserver Administration
Event Forwarding
Multiserver Job Scheduling
Database Maintenance
Database Maintenance Checklist
Using Maintenance Plans
Checking and Maintaining Database Integrity

Click to order