Microsoft® SQL Server® 2008 Administrator's Pocket Consultant, 2nd Edition

Table of Contents

1. Microsoft(R) SQL Server(R) 2008 Administrator's Pocket Consultant
2. Acknowledgments
3. Introduction
   1. Who Is This Book For?
   2. How Is This Book Organized?
   3. Conventions Used in This Book
   4. Other Resources
   5. Support for This Book
   6. We Want to Hear from You
4. 1. SQL Server 2008 Administration Overview
   1. SQL Server 2008 and Your Hardware
   2. SQL Server 2008 Editions
   3. SQL Server and Windows
      1. Services for SQL Server
      2. SQL Server Logins and Authentication
      3. Service Accounts for SQL Server
   4. Using the Graphical Administration Tools
   5. Using the Command-Line Tools
      1. BCP
      2. SQLCMD
      3. Other Command-Line Tools
   6. Using SQL Server PowerShell
      1. Running and Using Cmdlets
      2. Running and Using SQL Server PowerShell
      3. Working with SQL Server Cmdlets
5. 2. Deploying SQL Server 2008
   1. SQL Server Integration Roles
      1. Using SQL Server Integration Services
      2. Using SQL Server 2008 for Relational Data Warehousing
      3. Using SQL Server 2008 for Multidimensional Databases and Data Mining
      4. Using SQL Server 2008 for Managed Reporting
   2. Planning for Your SQL Server 2008 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
      5. Managing SQL Server Configuration and Security
   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. Repairing a SQL Server 2008 Installation
      4. Upgrading Your Edition of SQL Server 2008
      5. Uninstalling SQL Server
6. 3. Managing the Surface Security, Access, and Network Configuration
   1. Managing SQL Server Component Feature Access
   2. Configuring SQL Server Services
      1. Managing the Services Configuration
      2. Managing Service State and Start Mode
      3. Setting the Startup Service Account
      4. Configuring File Streaming
      5. Configuring Service Dump Directories, Error Reporting, and Customer Feedback Reporting
   3. Managing the Network and SQL Server Native Client Configuration
      1. Managing the Connections Configuration
      2. Specifying the Shared Memory Network Configuration
      3. Specifying the Named Pipes Network Configuration
      4. Specifying the TCP/IP Network Configuration
      5. Disabling, Enabling, and Configuring TCP/IP
         1. Using Static TCP/IP Network Configurations
         2. Using Dynamic TCP/IP Network Configurations
      6. Configuring Security for Native Client Configurations
      7. Configuring the Native Client Protocol Order
      8. Configuring the Shared Memory Native Client Configuration
      9. Configuring the Named Pipes Native Client Configuration
      10. Configuring the TCP/IP Native Client Configuration
7. 4. Configuring and Tuning SQL Server 2008
   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 ALTER DATABASE
8. 5. Managing the Enterprise
   1. 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
   2. 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
   3. Managing Servers
      1. Registering a Connected Server
      2. Registering a New Server in the 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
   4. Using Windows PowerShell for SQL Server Management
   5. Starting, Stopping, and Configuring SQL Server Agent
   6. Starting, Stopping, and Configuring Microsoft Distributed Transaction Coordinator
   7. 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
   8. Managing Server Activity
      1. Examining Process Information
      2. Tracking Resource Waits and Blocks
      3. Troubleshooting Deadlocks and Blocking Connections
      4. Tracking Command Execution in SQL Server
      5. Killing Server Processes
9. 6. Implementing Policy-Based Management
   1. Introducing Policy-Based Management
   2. Working with Policy-Based Management
   3. Managing Policies Throughout the Enterprise
      1. Importing and Exporting Policies
      2. Configuring Central Management Servers
         1. Registering Central Management Servers
         2. Registering Subordinate Servers
         3. Registering Subordinate Server Groups
         4. Moving Subordinate Servers and Server Groups
         5. Deleting Subordinate Servers and Server Groups
      3. Executing Statements Against Multiple Servers
      4. Configuring and Managing Policy Facets
      5. Creating and Managing Policy Conditions
      6. Creating and Managing Policies
      7. Managing Policy Categories and Mandating Policies
      8. Evaluating Policies
      9. Troubleshooting Policy-Based Management Policies
10. 7. Configuring SQL Server with SQL Server Management Studio
    1. SQL Server Management Studio Essentials
       1. Managing the Configuration with SQL Server Management Studio
       2. Determining System and Server Information
       3. Configuring Utility Control Points
          1. Creating a Control Point
          2. Enrolling an Instance
          3. Deploying Data-Tier Applications
          4. Performing Utility Administration
    2. Configuring Authentication and Auditing
       1. Setting the Authentication Mode
       2. Setting the Auditing Level
       3. Enabling or Disabling C2 Audit Logging
       4. Enabling or Disabling Common Criteria Compliance
    3. 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
    4. Configuring Processors and Parallel Processing
       1. Optimizing CPU Usage
       2. Setting Parallel Processing
    5. Configuring Threading, Priority, and Fibers
    6. Configuring User and Remote Connections
       1. Setting Maximum User Connections
       2. Setting Default Connection Options
       3. Configuring Remote Server Connections
    7. Managing Server Settings
       1. Enabling or Disabling File Streaming Support
       2. Setting the Default Language for SQL Server
       3. Allowing and Disallowing Nested Triggers
       4. Controlling Query Execution
       5. Configuring Year 2000 Support
    8. 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 the Cache with Checkpoints
       5. Compressing the Backup Media
    9. Adding and Removing Active Directory Information
    10. Troubleshooting Configuration Problems
        1. Recovering from a Bad Configuration
        2. Changing Collation and Rebuilding the master Database
11. 8. 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 Parameterization
       6. Configuring Cursor Options
       7. Controlling User Access and Database State
       8. Setting Online, Offline, or Emergency Mode
       9. Managing Cross-Database Chaining and External Access Options
       10. Configuring Recovery, Logging, and Disk I/O Error Checking Options
       11. 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
       8. Configuring Database Encryption
12. 9. Managing SQL Server 2008 Security
    1. Overview of SQL Server 2008 Security
       1. Working with Security Principals and Securables
       2. Understanding Permissions of Securables
       3. Examining Permissions Granted to Securables
          1. Examining Built-in Permissions
          2. Examining Effective Permissions
    2. SQL Server 2008 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
    5. Statement Permissions
       1. Implied Permissions
    6. Roles
       1. Server Roles
       2. Database Roles
    7. 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
    8. Configuring Server Roles
       1. Assigning Roles by Login
       2. Assigning Roles to Multiple Logins
       3. Revoking Access Rights and Roles by Server Login
    9. 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. T-SQL Commands for Managing Access and Roles
    10. Managing Database Permissions
        1. Assigning Database Permissions for Statements
        2. Object Permissions by Login
        3. Object Permissions for Multiple Logins
13. 10. 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 Sparse Columns
       7. Using Identities and Globally Unique Identifiers
       8. Using User-Defined Table Types
          1. Understanding User-Defined Table Types
          2. Creating User-Defined Table Types
          3. Managing User-Defined Table Types
    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. Using Filtered Indexes
       6. Determining Which Columns Should Be Indexed
       7. Indexing Computed Columns and Views
       8. Viewing Index Properties
       9. Creating Indexes
       10. Managing Indexes
       11. 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
    9. Creating Partitioned Tables and Indexes
       1. Creating Partition Functions
       2. Creating Partition Schemes
       3. Creating Partitions
       4. Viewing and Managing Partitions
    10. Compressing Tables, Indexes, and Partitions
        1. Using Row and Page Compression
        2. Setting or Changing Compression Settings
14. 11. 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
15. 12. 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
16. 13. 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
17. 14. Profiling and Monitoring SQL Server 2008
    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 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. Performance Logging
          1. Creating and Managing Data Collector Sets
          2. Collecting Performance Counter Data
          3. Collecting Performance Trace Data
          4. Collecting Configuration Data
       3. Viewing Data Collector Reports
       4. Configuring Performance Counter Alerts
    5. Configuring a Management Data Warehouse
       1. Understanding Management Data Warehouses
       2. Creating the Management Data Warehouse
       3. Setting Up Data Collection
       4. Managing Collection and Generating Reports
    6. 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
18. 15. Backing Up and Recovering SQL Server 2008
    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
       5. Planning for Backup Compression
    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
    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
    7. Restoring the master Database
19. 16. 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 User Accounts, 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 Checklists
          1. Daily
          2. Weekly
          3. Monthly
          4. As Needed
       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 CHECKIDENT
          6. Using DBCC CHECKFILEGROUP
          7. Using DBCC UPDATEUSAGE
20. 17. Managing Log Shipping and Database Mirroring
    1. Log Shipping
       1. Log Shipping: How It Works
       2. Preparing for Log Shipping
       3. Upgrading SQL Server 2000 Log Shipping to SQL Server 2008 Log Shipping
       4. Enabling Log Shipping on the Primary Database
       5. Adding Log Shipping Secondary Databases
       6. Changing the Transaction Log Backup Interval
       7. Changing the Copy and Restore Intervals
       8. Monitoring Log Shipping
       9. Failing Over to a Secondary Database
       10. Disabling and Removing Log Shipping
    2. Database Mirroring
       1. Database Mirroring Essentials
       2. Configuring Database Mirroring
       3. Managing and Monitoring Mirroring
       4. Recovering by Using Failover
       5. Removing Database Mirroring
    3. Using Mirroring and Log Shipping
21. Index
22. About the Author
23. Copyright