Microsoft® SQL Server™ 2005 Administrator's Pocket Consultant

Microsoft® SQL Server™ 2005 Administrator's Pocket Consultant

Released November 2005
Publisher(s): Microsoft Press
ISBN: 9780735621077

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Here’s the utterly practical, pocket-sized reference for IT professionals who need to administer, optimize, and maintain Microsoft SQL Server 2005 in their organization. This unique guide provides essential details for using Microsoft SQL Server 2005 to help protect and manage your company’s data—whether automating tasks; creating indexes and views; performing backups and recovery; replicating transactions; tuning performance; managing server activity; importing and exporting data; or performing other key tasks. Featuring quick-reference tables, lists, and step-by-step instructions, this handy, one-stop guide provides fast, accurate answers on the spot—whether you’re at your desk or in the field!

Table of contents

  1. Microsoft® SQL Server™ 2005 Administrator’s Pocket Consultant
    1. Acknowledgments
    2. Introduction
      1. Who Is This Book For?
      2. How Is This Book Organized?
      3. Conventions Used in This Book
      4. Support
        1. Postal Mail:
        2. E-mail:
    3. I. Microsoft SQL Server 2005 Administration Fundamentals
      1. 1. Microsoft SQL Server 2005 Administration Overview
        1. SQL Server 2005 and Your Hardware
        2. Microsoft SQL Server 2005 Editions
        3. SQL Server and Windows
          1. Services for SQL Server
          2. SQL Server Authentication
          3. Service Accounts for SQL Server
        4. Using the Graphical Administration Tools
        5. Using the Command-line Tools
          1. SQLCMD
          2. BCP
          3. Other Command-Line Tools
      2. 2. Deploying Microsoft SQL Server 2005
        1. SQL Server Integration Roles
          1. Using SQL Server Integration Services
          2. Using SQL Server 2005 for Relational Data Warehousing
          3. Using SQL Server 2005 for Multidimensional Databases and Data Mining
          4. Using SQL Server 2005 for Managed Reporting
        2. Planning for Your SQL Server 2005 Deployment
          1. Building the Server System for Performance
          2. Configuring the I/O Subsystem
          3. Ensuring Availability and Scalability
          4. Ensuring Connectivity and Data Access
        3. Running and Modifying SQL Server Setup
          1. Creating New Instances of SQL Server
            1. Understanding SQL Server Instances
            2. Installing a SQL Server Instance
          2. Adding Components and Instances
          3. Maintaining Installed Components
          4. Uninstalling SQL Server
      3. 3. Managing the Surface Security, Access, and Network Configuration
        1. Getting Started with the Configuration Tools
        2. Using SQL Server 2005 Surface Area Configuration
          1. Connecting to a Remote SQL Server Implementation
          2. Managing the Services Configuration
          3. Managing the Connections Configuration
          4. Managing SQL Server Component Feature Access
        3. Configuring SQL Server Services
          1. Managing Service State and Start Mode
          2. Setting the Startup Service Account
          3. Configuring Service Dump Directories, Error Reporting, and Customer Feedback Reporting
        4. Managing the Network and SQL Native Client Configuration
          1. Configuring the Shared Memory Network Configuration
          2. Configuring the Named Pipes Network Configuration
          3. Configuring the TCP/IP Network Configuration
          4. Configuring the Native Client Protocol Order
          5. Configuring the Shared Memory Native Client Configuration
          6. Configuring the TCP/IP Native Client Configuration
          7. Configuring the Named Pipes Native Client Configuration
      4. 4. Configuring and Tuning Microsoft SQL Server
        1. Accessing SQL Server Configuration Data
          1. Working with the System Catalog and Catalog Views
          2. Working with System Stored Procedures
        2. Techniques for Managing SQL Server Configuration Options
          1. Setting Configuration Options
          2. Working with SET Options
          3. Working with Server Options
          4. Working with Database Options
          5. Managing Database Compatibility
        3. Configuring SQL Server with Stored Procedures
          1. Using SQL Server Management Studio for Queries
          2. Executing Queries and Changing Settings
          3. Checking and Setting Configuration Parameters
          4. Changing Settings with sp_dboption
    4. II. Microsoft SQL Server 2005 Administration
      1. 5. Managing the Enterprise
        1. Managing SQL Server Startup
          1. Enabling or Preventing Automatic SQL Server Startup
          2. Setting Database Engine Startup Parameters
            1. Adding Startup Parameters
            2. Removing Startup Parameters
            3. Common Startup Parameters
          3. Managing Services from the Command Line
          4. Managing the SQL Server Command-Line Executable File
        2. Using SQL Server Management Studio
          1. Getting Started with SQL Server Management Studio
          2. Connecting to a Specific Server Instance
          3. Connecting to a Specific Database
        3. Managing SQL Server Groups
          1. Introducing SQL Server Groups
          2. Creating a Server Group
          3. Deleting a Server Group
          4. Editing and Moving Server Groups
          5. Adding SQL Servers to a Group
        4. Managing Servers
          1. Registering a Connected Server
          2. Registering a New Server in Registered Servers View
          3. Registering Previously Registered SQL Server 2000 Servers
          4. Updating Registration for Local Servers
          5. Copying Server Groups and Registration Details from One Computer to Another
          6. Editing Registration Properties
          7. Connecting to a Server
          8. Disconnecting from a Server
          9. Moving a Server to a New Group
          10. Deleting a Server Registration
        5. Starting, Stopping, and Configuring SQL Server Agent
        6. Starting, Stopping, and Configuring the Microsoft Distributed Transaction Coordinator
        7. Starting, Stopping, and Configuring the Microsoft Search Service
        8. Working with Full-Text Search
        9. Managing Full-Text Catalogs
          1. Viewing Catalog Properties
          2. Creating Catalogs
          3. Enabling Indexing of Tables and Views
          4. Editing Indexing of Tables and Views
          5. Disabling and Removing Full-Text Indexing from Tables and Views
          6. Populating Full-Text Catalogs
            1. Populating Catalogs Manually for All Selected Tables and Views
            2. Using Scheduled Jobs to Populate Catalogs for All Selected Tables and Views
            3. Populating Catalogs Manually for a Specific Table or View
            4. Using Scheduled Jobs to Populate Catalogs for a Specific Table or View
          7. Rebuilding Current Catalogs
          8. Cleaning Up Old Catalogs
          9. Removing Catalogs
        10. Managing Server Activity
          1. Examining Process Information
          2. Tracking Locks by Process ID and Object
          3. Troubleshooting Deadlocks and Blocking Connections
          4. Tracking Command Execution in SQL Server
          5. Killing Server Processes
      2. 6. Configuring SQL Server with SQL Server Management Studio
        1. Managing the Configuration with SQL Server Management Studio
        2. Determining System and Server Information
        3. Configuring Authentication and Auditing
          1. Setting Authentication Mode
          2. Setting Auditing Level
        4. Tuning Memory Usage
          1. Working with Dynamically Configured Memory
          2. Using Fixed Memory
          3. Enabling AWE Memory Support
          4. Optimizing Memory for Indexing
          5. Allocating Memory for Queries
        5. Configuring Processors and Parallel Processing
          1. Optimizing CPU Usage
          2. Setting Parallel Processing
        6. Configuring Threading, Priority, and Fibers
        7. Configuring User and Remote Connections
          1. Setting Maximum User Connections
          2. Setting Default Connection Options
          3. Configuring Remote Server Connections
        8. Managing Server Settings
          1. Setting Default Language for SQL Server
          2. Allowing and Disallowing System Updates
          3. Allowing and Disallowing Nested Triggers
          4. Controlling Query Execution
          5. Configuring Year 2000 Support
        9. Managing Database Settings
          1. Setting the Index Fill
          2. Configuring Backup and Restore Time-Out Options
          3. Configuring Backup and Restore Retention Options
          4. Flushing Cache with Checkpoints
        10. Adding and Removing Active Directory Information
        11. Troubleshooting Configuration Problems
          1. Recovering from a Bad Configuration
          2. Changing Collation and Rebuilding the Master Database
      3. 7. Core Database Administration
        1. Database Files and Logs
        2. Database Administration Basics
          1. Viewing Database Information in SQL Server Management Studio
          2. Viewing Database Information Using T-SQL
          3. Checking System and Sample Databases
          4. Examining Database Objects
        3. Creating Databases
          1. Creating Databases in SQL Server Management Studio
          2. Creating Databases Using T-SQL
        4. Altering Databases and Their Options
          1. Setting Database Options in SQL Server Management Studio
          2. Modifying Databases Using ALTER DATABASE
          3. Configuring Automatic Options
          4. Controlling ANSI Compliance at the Database Level
          5. Configuring Cursor Options
          6. Controlling User Access and Database State
          7. Setting Online, Offline, or Emergency Mode
          8. Managing Cross-Database Chaining and External Access Options
          9. Configuring Recovery, Logging, and Disk I/O Error-Checking Options
          10. Viewing, Changing, and Overriding Database Options
        5. Managing Database and Log Size
          1. Configuring SQL Server to Automatically Manage File Size
          2. Expanding Databases and Logs Manually
          3. Compressing and Shrinking a Database Manually
        6. Manipulating Databases
          1. Renaming a Database
          2. Dropping and Deleting a Database
          3. Attaching and Detaching Databases
            1. Detaching a Database
            2. Attaching a Database with Multiple Files
            3. Attaching a Database with Only Data Files
        7. Tips and Techniques
          1. Copying and Moving Databases
          2. Moving Databases
          3. Moving and Resizing tempdb
          4. Creating Secondary Data and Log Files
          5. Preventing Transaction Log Errors
          6. Preventing a Filegroup Is Full Error
          7. Creating a New Database Template
      4. 8. Managing SQL Server 2005 Security
        1. Overview of SQL Server 2005 Security
          1. Working with Security Principals and Securables
          2. Understanding Permissions of Securables
          3. Examining Permissions Granted to Securables
          4. Examining Built-in Permissions
          5. Examining Effective Permissions
        2. SQL Server 2005 Authentication Modes
          1. Windows Authentication
          2. Mixed Security and SQL Server Logins
        3. Special Purpose Logins and Users
          1. Working with the Administrators Group
          2. Working with the Administrator User Account
          3. Working with the sa Login
          4. Working with the NETWORK SERVICE and SYSTEM Logins
          5. Working with the Guest User
          6. Working with the dbo User
          7. Working with the sys and INFORMATION_SCHEMA Users
        4. Permissions
          1. Object Permissions
          2. Statement Permissions
          3. Implied Permissions
        5. Roles
          1. Server Roles
          2. Database Roles
        6. Managing Server Logins
          1. Viewing and Editing Existing Logins
          2. Creating Logins
          3. Editing Logins with T-SQL
          4. Granting or Denying Server Access
          5. Enabling, Disabling, and Unlocking Logins
          6. Removing Logins
          7. Changing Passwords
        7. Configuring Server Roles
          1. Assigning Roles by Login
          2. Assigning Roles to Multiple Logins
          3. Revoking Access Rights and Roles by Server Login
        8. Controlling Database Access and Administration
          1. Assigning Access and Roles by Login
          2. Assigning Roles for Multiple Logins
          3. Creating Standard Database Roles
          4. Creating Application Database Roles
          5. Removing Role Memberships for Database Users
          6. Deleting User-Defined Roles
          7. Transact-SQL Commands for Managing Access and Roles
        9. Managing Database Permissions
          1. Assigning Database Permissions for Statements
          2. Object Permissions by Login
          3. Object Permissions for Multiple Logins
    5. III. Microsoft SQL Server 2005 Data Administration
      1. 9. Manipulating Schemas, Tables, Indexes, and Views
        1. Working with Schemas
          1. Creating Schemas
          2. Modifying Schemas
          3. Moving Objects to a New Schema
          4. Dropping Schemas
        2. Getting Started with Tables
        3. Table Essentials
          1. Understanding Data Pages
          2. Understanding Extents
          3. Understanding Table Partitions
        4. Working with Tables
          1. Creating Tables
          2. Modifying Existing Tables
          3. Viewing Table Row and Size Information
          4. Displaying Table Properties and Permissions
          5. Displaying Current Values in Tables
          6. Copying Tables
          7. Renaming and Deleting Tables
          8. Adding and Removing Columns in a Table
            1. Adding Columns
            2. Modifying Columns
            3. Removing Columns
          9. Scripting Tables
        5. Managing Table Values
          1. Using Native Data Types
          2. Using Fixed-Length, Variable-Length, and Max-Length Fields
          3. Using User-Defined Data Types
            1. Creating User-Defined Data Types
            2. Managing User-Defined Data Types
          4. Allowing and Disallowing Nulls
          5. Using Default Values
          6. Using Identities and Globally Unique Identifiers
        6. Using Views
          1. Working with Views
          2. Creating Views
          3. Modifying Views
          4. Using Updatable Views
          5. Managing Views
        7. Creating and Managing Indexes
          1. Understanding Indexes
          2. Using Clustered Indexes
          3. Using Nonclustered Indexes
          4. Using XML Indexes
          5. Determining Which Columns Should Be Indexed
          6. Indexing Computed Columns and Views
          7. Viewing Index Properties
          8. Creating Indexes
          9. Managing Indexes
          10. Using the Database Engine Tuning Advisor
        8. Column Constraints and Rules
          1. Using Constraints
            1. Setting Unique Constraints
            2. Designating Primary Key Constraints
            3. Using Foreign Key Constraints
            4. Using Check Constraints
            5. Using Not Null Constraints
          2. Using Rules
      2. 10. Importing, Exporting, and Transforming Data
        1. Working with Integration Services
          1. Getting Started with Integration Services
          2. Integration Services Tools
          3. Integration Services and Data Providers
          4. Integration Services Packages
        2. Creating Packages with the SQL Server Import and Export Wizard
          1. Stage 1: Source and Destination Configuration
            1. .NET Framework Data Provider Connections
            2. File-Based Data Connections
            3. Server-Based Connections to Databases Other Than SQL Server
            4. Server-Based Connections to SQL Server
            5. Importing and Exporting Flat Files
          2. Stage 2: Copy or Query
            1. Specifying Tables and Views to Copy
            2. Building a Query
          3. Stage 3: Formatting and Transformation
          4. Stage 4: Save and Execute
        3. Understanding BCP
          1. BCP Basics
          2. BCP Syntax
          3. BCP Permissions and Modes
          4. Importing Data with BCP
          5. Exporting Data with BCP
        4. BCP Scripts
        5. Using the BULK INSERT Command
      3. 11. Linked Servers and Distributed Transactions
        1. Working with Linked Servers and Distributed Data
          1. Using Distributed Queries
          2. Using Distributed Transactions
          3. Running the Distributed Transaction Coordinator Service
        2. Managing Linked Servers
          1. Adding Linked Servers
          2. Configuring Security for Linked Servers
          3. Setting Server Options for Remote and Linked Servers
          4. Deleting Linked Servers
      4. 12. Implementing Snapshot, Merge, and Transactional Replication
        1. An Overview of Replication
          1. Replication Components
          2. Replication Agents and Jobs
          3. Replication Variants
        2. Planning for Replication
          1. Replication Models
          2. Preliminary Replication Tasks
            1. Preparing for Snapshot Replication
            2. Preparing for Transactional Replication
            3. Preparing for Merge Replication
        3. Distributor Administration
          1. Setting Up a New Distributor
          2. Updating Distributors
          3. Creating Distribution Databases
          4. Enabling and Updating Publishers
          5. Enabling Publication Databases
          6. Deleting Distribution Databases
          7. Disabling Publishing and Distribution
        4. Creating and Managing Publications
          1. Creating Publications
            1. Snapshot and Transactional Publications
            2. Merge Publications
          2. Viewing and Updating Publications
          3. Setting Publication Properties
          4. Setting Agent Security and Process Accounts
          5. Controlling Subscription Access to a Publication
          6. Creating a Script for a Publication
          7. Deleting a Publication
        5. Subscribing to a Publication
          1. Subscription Essentials
          2. Creating Subscriptions
          3. Viewing Subscription Properties
          4. Updating, Maintaining, and Deleting Subscriptions
          5. Validating Subscriptions
          6. Reinitializing Subscriptions
    6. IV. Microsoft SQL Server 2005 Optimization and Maintenance
      1. 13. Profiling and Monitoring Microsoft SQL Server 2005
        1. Monitoring Server Performance and Activity
          1. Reasons to Monitor SQL Server
          2. Getting Ready to Monitor
          3. Monitoring Tools and Resources
        2. Working with Replication Monitor
          1. Starting and Using the Replication Monitor
          2. Adding Publishers and Publisher Groups
        3. Working with the Event Logs
          1. Examining the Application Log
          2. Examining the SQL Server Event Logs
          3. Examining the SQL Server Agent Event Logs
        4. Monitoring SQL Server Performance
          1. Choosing Counters to Monitor
          2. Creating and Managing Performance Monitor Logs
            1. Creating and Managing Performance Logging
            2. Creating Counter Logs
            3. Creating Trace Logs
          3. Replaying Performance Logs
          4. Configuring Alerts for Performance Counters
        5. Solving Performance Problems with Profiler
          1. Using Profiler
          2. Creating New Traces
          3. Working with Traces
          4. Saving a Trace
          5. Replaying a Trace
            1. Requirements for Replaying Traces
            2. Replaying Traces on a Different Server
            3. Replaying and Analyzing a Trace
      2. 14. Backing Up and Recovering SQL Server 2005
        1. Creating a Backup and Recovery Plan
          1. Initial Backup and Recovery Planning
          2. Planning for Mirroring and Mirrored Database Backups
          3. Planning for Backups of Replicated Databases
          4. Planning for Backups of Very Large Databases
        2. Selecting Backup Devices and Media
        3. Using Backup Strategies
        4. Creating a Backup Device
        5. Performing Backups
          1. Creating Backups in SQL Server Management Studio
            1. Creating a New Backup Set
            2. Adding to an Existing Backup Set
          2. Using Striped Backups with Multiple Devices
            1. Creating a New Media Set
            2. Adding to an Existing Media Set
          3. Using Transact-SQL Backup
          4. Performing Transaction Log Backups
            1. Options and Commands That Invalidate Log Sequences
            2. Log Truncation Options
          5. Backing Up Full-Text Search Catalogs
        6. Restoring a Database
          1. Database Corruption and Problem Resolution
          2. Restoring a Database from a Normal Backup
          3. Restoring Files and Filegroups
          4. Restoring a Database to a Different Location
          5. Recovering Missing Data
          6. Creating Standby Servers
            1. Creating a Cold Standby
            2. Creating a Warm Standby
          7. Using Transact-SQL Restore Commands
          8. Restoring Full-Text Catalogs
        7. Restoring the Master Database
        8. Mirroring Databases
          1. Configuring Mirroring
          2. Managing Mirroring
          3. Monitoring Mirroring Status and Performance
      3. 15. Database Automation and Maintenance
        1. Overview of Database Automation and Maintenance
        2. Using Database Mail
          1. Performing the Initial Database Mail Configuration
          2. Managing Database Mail Profiles and Accounts
          3. Viewing or Changing Database Mail System Parameters
        3. Using SQL Server Agent
          1. Accessing Alerts, Operators, and Jobs
          2. Configuring the SQL Server Agent Service
          3. Setting the SQL Server Agent Mail Profile
          4. Using SQL Server Agent to Restart Services Automatically
        4. Managing Alerts
          1. Using Default Alerts
          2. Creating Error Message Alerts
          3. Handling Alert Responses
          4. Deleting, Enabling, and Disabling Alerts
        5. Managing Operators
          1. Registering Operators
          2. Deleting and Disabling Notification for Operators
          3. Configuring a Fail-Safe Operator
        6. Scheduling Jobs
          1. Creating Jobs
          2. Assigning or Changing Job Definitions
          3. Setting Steps to Execute
          4. Configuring Job Schedules
          5. Handling Job Alerts
          6. Handling Notification Messages
          7. Managing Existing Jobs
          8. Managing Job Categories
            1. Working with Job Categories
            2. Creating Job Categories
            3. Updating Job Categories
        7. Automating Routine Server-to-Server Administration Tasks
          1. Copying Users, Tables, Views, and Other Objects from One Database to Another
          2. Copying Alerts, Operators, and Scheduled Jobs from One Server to Another
        8. Multiserver Administration
          1. Event Forwarding
          2. Multiserver Job Scheduling
            1. Multiserver Scheduling Requirements
            2. Configuring Master Servers
            3. Configuring Target Servers
        9. Database Maintenance
          1. Database Maintenance Checklist
          2. Using Maintenance Plans
            1. Creating Maintenance Plans
            2. Checking Maintenance Reports and History
            3. Viewing, Editing, Running, and Deleting Maintenance Plans
          3. Checking and Maintaining Database Integrity
            1. Using DBCC CHECKDB
            2. Using DBCC CHECKTABLE
            3. Using DBCC CHECKALLOC
            4. Using DBCC CHECKCATALOG
            5. Using DBCC DBREINDEX
        10. Managing Log Shipping
          1. Log Shipping: How It Works
          2. Preparing for Log Shipping
          3. Upgrading SQL Server 2000 Log Shipping to SQL Server 2005 Log Shipping
          4. Enabling Log Shipping on the Primary Database
          5. Adding Log Shipping Secondaries
          6. Changing the Transaction Log Backup Interval
          7. Changing the Copy and Restore Intervals
          8. Failing Over to a Secondary Database
        11. Using Mirroring and Log Shipping
    7. William R. Stanek
    8. Index

Product information

  • Title: Microsoft® SQL Server™ 2005 Administrator's Pocket Consultant
  • Author(s):
  • Release date: November 2005
  • Publisher(s): Microsoft Press
  • ISBN: 9780735621077