Professional SQL Server™ 2005 Administration

Book description

SQL Server 2005 is the largest leap forward for SQL Server since its inception. With this update comes new features that will challenge even the most experienced SQL Server DBAs. Written by a team of some of the best SQL Server experts in the industry, this comprehensive tutorial shows you how to navigate the vastly changed landscape of the SQL Server administration.

Drawing on their own first-hand experiences to offer you best practices, unique tips and tricks, and useful workarounds, the authors help you handle even the most difficult SQL Server 2005 administration issues, including blocking and locking. You'll learn how to fine-tune queries you've already written, automate redundant monitoring and maintenance tasks, and use hidden tools so that you can quickly get over the learning curve of how to configure and administer SQL Server 2005.

What you will learn from this book

  • How to use some of the more advanced concepts of installation

  • Techniques for properly administering development features such as SQL CLR

  • Ways to secure your SQL Server from common threats

  • How to choose the right hardware configuration

  • Best practices for backing up and recovering your database

  • Step-by-step guidelines for clustering your SQL Server

Who this book is for

This book is for experienced developers and database administrators who plan to administer or are already administering an SQL Server 2005 system and its business intelligence features.

Wrox Professional guides are planned and written by working technologists to meet the real-world needs of programmers, developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.

Table of contents

  1. Copyright
  2. About the Authors
  3. Credits
  4. Acknowledgments
  5. Introduction
    1. Who This Book Is For
    2. How This Book Is Structured
    3. What You Need to Use This Book
    4. Conventions
    5. Source Code
    6. Errata
    7. p2p.wrox.com
  6. 1. SQL Server 2005 Architecture
    1. 1.1. Growing Role of a DBA
      1. 1.1.1. Production DBA
      2. 1.1.2. Development DBA
      3. 1.1.3. Business Intelligence DBA
      4. 1.1.4. Hybrid DBA
      5. 1.1.5. Industry Trends
    2. 1.2. SQL Server Architecture
      1. 1.2.1. Transaction Log and Database Files
      2. 1.2.2. SQL Native Client
      3. 1.2.3. System Databases
        1. 1.2.3.1. The Resource Database
        2. 1.2.3.2. The Master Database
        3. 1.2.3.3. Tempdb Database
        4. 1.2.3.4. Model Database
        5. 1.2.3.5. msdb Database
      4. 1.2.4. Schemas
      5. 1.2.5. Synonyms
      6. 1.2.6. Dynamic Management Views
      7. 1.2.7. SQL Server 2005 Data Types
        1. 1.2.7.1. Character Data Types
        2. 1.2.7.2. Numeric Data Types
        3. 1.2.7.3. Binary Data Types
      8. 1.2.8. XML
        1. 1.2.8.1. Date Time
        2. 1.2.8.2. CLR Integration
    3. 1.3. Editions of SQL Server
      1. 1.3.1. SQL Express
      2. 1.3.2. Workgroup and Standard Editions
      3. 1.3.3. Enterprise, Evaluation, and Developer Editions
      4. 1.3.4. Operating System
      5. 1.3.5. Maximum Capacity of SQL Server
      6. 1.3.6. Database Features by Edition
        1. 1.3.6.1. Developer Features by Edition
        2. 1.3.6.2. Business Intelligence Features by Edition
        3. 1.3.6.3. DBA Features by Edition
      7. 1.3.7. Licensing
        1. 1.3.7.1. Modern Processor Issues
        2. 1.3.7.2. Scaling and High Availability Licensing Issues
    4. 1.4. Summary
  7. 2. SQL Server 2005 Installation Best Practices
    1. 2.1. Planning the System
      1. 2.1.1. Hardware Choices
        1. 2.1.1.1. Processors
        2. 2.1.1.2. Disk Configuration
          1. 2.1.1.2.1. Disk Throughput
          2. 2.1.1.2.2. Best Practices
          3. 2.1.1.2.3. Disk Controllers
      2. 2.1.2. Software and Install Choices
        1. 2.1.2.1. Collation
          1. 2.1.2.1.1. SQL Server Collations
          2. 2.1.2.1.2. Windows Collations
          3. 2.1.2.1.3. Case Sensitivity
        2. 2.1.2.2. Sort Order
          1. 2.1.2.2.1. Collation Best Practices and Warnings
        3. 2.1.2.3. System Files Location
        4. 2.1.2.4. Disk Setup
        5. 2.1.2.5. Security Considerations
    2. 2.2. Installing SQL Server
      1. 2.2.1. Side by Side, Upgrade, and New Install
      2. 2.2.2. Scripted Installation
      3. 2.2.3. Remote Installation
      4. 2.2.4. Where is AdventureWorks?
      5. 2.2.5. Where Are Pubs/Northwind?
    3. 2.3. Installing Analysis Services
    4. 2.4. Installing Reporting Services
    5. 2.5. Burning in the System
    6. 2.6. Post-Install Configuration
      1. 2.6.1. Surface Area Configuration
      2. 2.6.2. SQL Server Configuration Manager
      3. 2.6.3. SQL Server Management Studio
      4. 2.6.4. Tempdb
      5. 2.6.5. Back it up
    7. 2.7. Uninstalling SQL Server
      1. 2.7.1. Uninstalling Reporting Services
        1. 2.7.1.1. Delete the ReportServer Application Pool
        2. 2.7.1.2. Drop the ReportServer and ReportServerTempDB Databases
        3. 2.7.1.3. Drop the Remaining Directories
        4. 2.7.1.4. Drop the File Association
      2. 2.7.2. Uninstalling Analysis Services
      3. 2.7.3. Uninstalling SQL Server Engine
    8. 2.8. Help from Microsoft Product Support Services
      1. 2.8.1. Installing Client Tools from a Network Share
      2. 2.8.2. Admin Tools Not Installed by Default on Remote Cluster Nodes
      3. 2.8.3. Minimum Configuration Warning
    9. 2.9. Troubleshooting a Failed Install
    10. 2.10. Summary
  8. 3. Upgrading SQL Server 2005 Best Practices
    1. 3.1. Why Upgrade to SQL Server 2005
      1. 3.1.1. Risk Mitigation — The Microsoft Contribution
      2. 3.1.2. Independent Software Vendors (ISV) & SQL Community Contributions
    2. 3.2. Upgrading to SQL Server 2005
      1. 3.2.1. Upgrade in Place
      2. 3.2.2. Side-by-Side Upgrade
      3. 3.2.3. Upgrade in Place versus Side-By-Side Upgrade Considerations
    3. 3.3. Pre-Upgrade Checks
      1. 3.3.1. SQL Server Upgrade Advisor
      2. 3.3.2. Installing the SQL Server 2005 Upgrade Advisor
      3. 3.3.3. Using the Upgrade Advisor
      4. 3.3.4. Scripting the Upgrade Advisor
      5. 3.3.5. Resolving Upgrade Issues
    4. 3.4. SQL Server Upgrade Assistant
      1. 3.4.1. Capturing the Environment
      2. 3.4.2. Setting up the Baseline Server
      3. 3.4.3. Running the Trace
      4. 3.4.4. Upgrading to SQL Server 2005
      5. 3.4.5. Final Analysis
    5. 3.5. Backward Compatibility
      1. 3.5.1. Unsupported and Discontinued Features
      2. 3.5.2. SQL Server 2005 Deprecated Database Features
      3. 3.5.3. Other SQL 2005 impacting Behavior Changes
    6. 3.6. SQL Server Component Considerations
      1. 3.6.1. Upgrading Full-Text Catalog to SQL Server 2005
      2. 3.6.2. Upgrading DTS to SQL Server 2005 Integration Services (SSIS)
      3. 3.6.3. Log Shipping
      4. 3.6.4. Failover Clustering
      5. 3.6.5. Upgrading to 64-bit
    7. 3.7. Post Upgrade Checks
    8. 3.8. Poor Query Performance After Upgrade
    9. 3.9. Surface Area Configuration Tool
      1. 3.9.1. Surface Area Configuration for Services and Connections tool
      2. 3.9.2. Surface Area Configuration Features tool
    10. 3.10. Update Usage Counters
    11. 3.11. Summary
  9. 4. Managing and Troubleshooting the Database Engine
    1. 4.1. Configuration Tools
      1. 4.1.1. SQL Server Configuration Manager
      2. 4.1.2. SQL Server Surface Area Configuration
      3. 4.1.3. Startup Parameters
      4. 4.1.4. Startup Stored Procedures
      5. 4.1.5. Rebuilding the System Databases
    2. 4.2. Management Studio
      1. 4.2.1. Reports
        1. 4.2.1.1. Server Reports
        2. 4.2.1.2. Database Reports
      2. 4.2.2. Configuring SQL Server
      3. 4.2.3. Filtering Objects
      4. 4.2.4. Error Logs
      5. 4.2.5. Activity Monitor
      6. 4.2.6. Monitoring Processes in T-SQL
        1. 4.2.6.1. sp_who and sp_who2
        2. 4.2.6.2. sys.dm_exec_connections
      7. 4.2.7. DBCC INPUTBUFFER
        1. 4.2.7.1. Sys.dm_exec_sql_text
    3. 4.3. Trace Flags
    4. 4.4. Getting Help from Support
      1. 4.4.1. SQLDumper.exe
      2. 4.4.2. SQLDiag.exe
    5. 4.5. Summary
  10. 5. Automating SQL Server
    1. 5.1. Automation Components
      1. 5.1.1. Jobs
        1. 5.1.1.1. Job Step Logging
        2. 5.1.1.2. Job Notifications
      2. 5.1.2. Schedules
      3. 5.1.3. Operators
        1. 5.1.3.1. Scheduling Notifications
        2. 5.1.3.2. Failsafe Operator
      4. 5.1.4. Alerts
        1. 5.1.4.1. Alert Event Types
          1. 5.1.4.1.1. SQL Server Event
          2. 5.1.4.1.2. SQL Server Performance Condition
        2. 5.1.4.2. Alert Responses
    2. 5.2. SQL Agent Security
      1. 5.2.1. Service Account
      2. 5.2.2. Access to SQL Agent
        1. 5.2.2.1. SQLAgentUserRole
        2. 5.2.2.2. SQLAgentReaderRole
        3. 5.2.2.3. SQLAgentOperatorRole
      3. 5.2.3. Proxy Accounts
        1. 5.2.3.1. SQL Agent Subsystems
        2. 5.2.3.2. Proxies
          1. 5.2.3.2.1. Credentials
          2. 5.2.3.2.2. Create Proxy
          3. 5.2.3.2.3. Using Proxies
    3. 5.3. Configuring SQL Server Agent
    4. 5.4. Database Mail
      1. 5.4.1. Architecture
      2. 5.4.2. Security
      3. 5.4.3. Configuration
        1. 5.4.3.1. Database Mail Account
        2. 5.4.3.2. Setup
      4. 5.4.4. Archiving
    5. 5.5. Multiserver Administration
      1. 5.5.1. Using Token Replacement
      2. 5.5.2. Event Forwarding
      3. 5.5.3. Using WMI
      4. 5.5.4. Multiserver Administration — Using Master and Target Servers
    6. 5.6. Problem Resolution
      1. 5.6.1. Job Failure Error 3621
      2. 5.6.2. Database Mail Message Failed
      3. 5.6.3. Some ISPs block Email from Unknown SMTP Servers
      4. 5.6.4. SQL Agent Jobs Fail after Installing Service Pack 1
    7. 5.7. Summary
  11. 6. Integration Services Administration and Performance Tuning
    1. 6.1. Tour of Integration Services
      1. 6.1.1. Integration Services Uses
      2. 6.1.2. Four Main Parts of Integration Services
        1. 6.1.2.1. Integration Services Service
        2. 6.1.2.2. Integration Services Runtime Engine and Runtime Components
        3. 6.1.2.3. Integration Services Object Model
        4. 6.1.2.4. Integration Services Dataflow Engine and Dataflow Components
      3. 6.1.3. Project Management and Change Control
    2. 6.2. Administration of the Integration Services Service
      1. 6.2.1. Overview
      2. 6.2.2. Configuration
        1. 6.2.2.1. XML Configuration File
        2. 6.2.2.2. Setting Service Properties Using the Windows Services Snap-in
        3. 6.2.2.3. Setting Service Properties Using SQL Server Configuration Manager
        4. 6.2.2.4. Configuring Windows Firewall for Access
      3. 6.2.3. Event Logs
      4. 6.2.4. Monitoring Activity
    3. 6.3. Administration of Integration Services Packages
      1. 6.3.1. Overview
      2. 6.3.2. Creating Packages
        1. 6.3.2.1. Using the Import and Export Wizard to Create Packages
        2. 6.3.2.2. Creating and Using Package Templates
      3. 6.3.3. Management
        1. 6.3.3.1. Using Management Studio for Package Management
        2. 6.3.3.2. Using DTUtil Package Management Utility?
        3. 6.3.3.3. Importing and Exporting Packages
      4. 6.3.4. Deployment
        1. 6.3.4.1. Creating a Package Deployment Utility
        2. 6.3.4.2. Using the Package Installer Wizard
        3. 6.3.4.3. Manual Package Deployment
          1. 6.3.4.3.1. Import or Export Packages Deployment
          2. 6.3.4.3.2. File Save/Move Package Deployment
          3. 6.3.4.3.3. DTUtil Package Deployment
      5. 6.3.5. Execution and Scheduling
        1. 6.3.5.1. Running Packages in Business Intelligence Development Studio
        2. 6.3.5.2. Running Packages with SQL Server Import and Export Wizard
        3. 6.3.5.3. Running Packages with DTExec
        4. 6.3.5.4. Running Packages with DTExecUI
        5. 6.3.5.5. Scheduling Execution with SQL Server Agent
    4. 6.4. Applying Security to Integration Services
      1. 6.4.1. Overview
      2. 6.4.2. Securing Packages
        1. 6.4.2.1. Package Protection Levels
        2. 6.4.2.2. Database Integration Services Roles
      3. 6.4.3. Saving Packages
      4. 6.4.4. Running Packages
      5. 6.4.5. Package Resources
      6. 6.4.6. Digital Signatures
    5. 6.5. Summary
  12. 7. Analysis Services Administration and Performance Tuning
    1. 7.1. Tour of Analysis Services
      1. 7.1.1. Unified Dimensional Model Components
        1. 7.1.1.1. Data Source View
        2. 7.1.1.2. Dimensional Model
        3. 7.1.1.3. Calculations (Expressions)
        4. 7.1.1.4. Familiar and Abstracted Model
        5. 7.1.1.5. Administrative Configuration
      2. 7.1.2. Analysis Services Architectural Components
        1. 7.1.2.1. Query Processor
      3. 7.1.3. XMLA Listener
      4. 7.1.4. XML for Analysis
    2. 7.2. Administering Analysis Services Server
      1. 7.2.1. Server Configuration Settings
        1. 7.2.1.1. Review and Adjust Server Properties
        2. 7.2.1.2. Lock Manager Properties
        3. 7.2.1.3. Log Properties
        4. 7.2.1.4. Memory Properties
        5. 7.2.1.5. Network Properties
        6. 7.2.1.6. OLAP Properties
        7. 7.2.1.7. Security Properties
      2. 7.2.2. Required Services
      3. 7.2.3. Analysis Services Scripting Language
    3. 7.3. Administering Analysis Services Databases
      1. 7.3.1. Deploying Analysis Services Databases
        1. 7.3.1.1. Processing Analysis Services Objects
        2. 7.3.1.2. Processing Dimensions
        3. 7.3.1.3. Processing Cubes
        4. 7.3.1.4. Processing Partitions
        5. 7.3.1.5. Reprocessing
        6. 7.3.1.6. Performing Processing
      2. 7.3.2. Backing Up and Restoring Analysis Services Databases
      3. 7.3.3. Synchronizing Analysis Services Databases
    4. 7.4. Analysis Services Performance Monitoring and Tuning
      1. 7.4.1. Monitoring Analysis Services Events Using SQL Profiler
      2. 7.4.2. Creating Traces for Replay
      3. 7.4.3. Using Flight Recorder for After the Fact Analysis
    5. 7.5. Management of Analysis Services Storage
      1. 7.5.1. Storage Modes
        1. 7.5.1.1. Multidimensional OLAP
        2. 7.5.1.2. Relational OLAP
        3. 7.5.1.3. Hybrid OLAP
      2. 7.5.2. Partition Configuration
      3. 7.5.3. Designing Aggregations
    6. 7.6. Applying Security to Analysis Services
      1. 7.6.1. Server and Database Roles
        1. 7.6.1.1. Server Role
        2. 7.6.1.2. Database Role
        3. 7.6.1.3. Database Role Permissions
    7. 7.7. Summary
  13. 8. Administering the Development Features
    1. 8.1. The Service Broker
      1. 8.1.1. Service Broker Architecture
        1. 8.1.1.1. Meassage Type
        2. 8.1.1.2. Contracts
        3. 8.1.1.3. Queue
        4. 8.1.1.4. Services
      2. 8.1.2. Service Broker Examples
      3. 8.1.3. Activation
      4. 8.1.4. Conversation Groups
    2. 8.2. Security Considerations for the Service Broker
      1. 8.2.1. Transport Security
        1. 8.2.1.1. Windows Authentication
        2. 8.2.1.2. Certificate-Based Authentication
        3. 8.2.1.3. Encryption
      2. 8.2.2. Routing
      3. 8.2.3. Dialog Security
    3. 8.3. Administering the Service Broker
      1. 8.3.1. Installing the Service Broker Application
        1. 8.3.1.1. Preparing the Databases
        2. 8.3.1.2. Running the Scripts to Install the Application
      2. 8.3.2. Setting Permissions on Service Broker Objects
        1. 8.3.2.1. CONNECT Permission
        2. 8.3.2.2. SEND Permission
        3. 8.3.2.3. RECEIVE Permission
        4. 8.3.2.4. EXECUTE Permission
      3. 8.3.3. Managing Service Broker Queues
      4. 8.3.4. Poison Message Handling
      5. 8.3.5. Moving Service Broker Applications
      6. 8.3.6. Copying Service Broker Applications
      7. 8.3.7. Replacing Expired Certificates
      8. 8.3.8. Troubleshooting Service Broker Applications
        1. 8.3.8.1. Design and Code Review
          1. 8.3.8.1.1. Not Handling All Message Types
          2. 8.3.8.1.2. Not Dealing with Poison Messages
          3. 8.3.8.1.3. Not Ending the Conversation
          4. 8.3.8.1.4. Service Broker Commands Outside of an Explicit Transaction
        2. 8.3.8.2. Using Catalog Views for Service Broker Metadata
        3. 8.3.8.3. Using Catalog Views for Service Broker Data
        4. 8.3.8.4. Dynamic Management Views
        5. 8.3.8.5. Using SQL Profiler to View Service Broker Activities
        6. 8.3.8.6. No Return Route
        7. 8.3.8.7. Dropped Messages
        8. 8.3.8.8. Conversation Endpoint is Not in a Valid State for SEND
        9. 8.3.8.9. Queue Disabled
        10. 8.3.8.10. Service Broker Is Disabled in a Database
        11. 8.3.8.11. Could Not Obtain Information about Windows NT Group/User
        12. 8.3.8.12. Duplicate Routes
        13. 8.3.8.13. Activation Stored Procedure Is Not Being Activated
      9. 8.3.9. Performance
    4. 8.4. Introduction to CLR Integration
      1. 8.4.1. SQL Server as .NET Runtime Host
      2. 8.4.2. Application Domains
      3. 8.4.3. T-SQL versus CLR
      4. 8.4.4. Extended Stored Procedure versus CLR
        1. 8.4.4.1. Enabling CLR Integration
        2. 8.4.4.2. Creating the CLR Assembly
      5. 8.4.5. Deploying the Assembly
        1. 8.4.5.1. Security Notes
        2. 8.4.5.2. Registering the Assembly
      6. 8.4.6. ALTER ASSEMBLY
      7. 8.4.7. DROP ASSEMBLY
      8. 8.4.8. Cataloging Objects
        1. 8.4.8.1. Assemblies
        2. 8.4.8.2. CLR Stored Procedures
        3. 8.4.8.3. CLR Trigger Metadata
        4. 8.4.8.4. CLR Scalar Function
        5. 8.4.8.5. CLR Table Valued Function
        6. 8.4.8.6. CLR User-Defined Aggregates
        7. 8.4.8.7. CLR User Defined Types
      9. 8.4.9. Application Domains
      10. 8.4.10. Performance Monitoring
        1. 8.4.10.1. System Monitor
        2. 8.4.10.2. SQL Profiler
        3. 8.4.10.3. DMVs
          1. 8.4.10.3.1. SQL CLR Memory Usage
          2. 8.4.10.3.2. Loaded Assemblies
          3. 8.4.10.3.3. CLR Request Status
          4. 8.4.10.3.4. Time Spent in SQL CLR By a Query
          5. 8.4.10.3.5. Finding the .NET Framework Version in SQL Server
    5. 8.5. Summary
  14. 9. Securing the Database Engine
    1. 9.1. Logins
      1. 9.1.1. Windows vs. SQL Server Authentication
      2. 9.1.2. Creating the Login
        1. 9.1.2.1. Defining Server Roles
        2. 9.1.2.2. Granting Database Rights
        3. 9.1.2.3. Defining Access to Objects
        4. 9.1.2.4. The Status Page
        5. 9.1.2.5. Logins with T-SQL
        6. 9.1.2.6. The Security Hierarchy
        7. 9.1.2.7. The Guest Account
      3. 9.1.3. BUILTIN\Administrators Login
      4. 9.1.4. Granting Profiler Access
    2. 9.2. Credentials
    3. 9.3. Users
      1. 9.3.1. Granular Permissions
      2. 9.3.2. Roles
      3. 9.3.3. Creating Users with T-SQL
    4. 9.4. Schemas
      1. 9.4.1. Schema Example
      2. 9.4.2. Changing Ownership
    5. 9.5. Permission Context
      1. 9.5.1. EXECUTE AS Command
      2. 9.5.2. Troubleshooting Permission
    6. 9.6. Encryption
      1. 9.6.1. Creating the Certificate and Key
      2. 9.6.2. Encrypting the Data
      3. 9.6.3. Creating a Helper Function Example
    7. 9.7. Column-Level Permissions
    8. 9.8. Summary
  15. 10. Change Management
    1. 10.1. Creating Projects
      1. 10.1.1. Creating a Connection
      2. 10.1.2. Creating a Project Query
    2. 10.2. DDL Triggers
      1. 10.2.1. DDL Trigger Syntax
      2. 10.2.2. Database Triggers
      3. 10.2.3. Server Triggers
      4. 10.2.4. Trigger Views
    3. 10.3. SQLCMD
      1. 10.3.1. Sqlcmd from the Command Prompt
      2. 10.3.2. Sqlcmd from Management Studio
    4. 10.4. Creating Change Scripts
    5. 10.5. Version Tables
    6. 10.6. Summary
  16. 11. Configuring the Server for Optimal Performance
    1. 11.1. What Every DBA Needs to Know about Performance
      1. 11.1.1. The Performance Tuning Cycle
      2. 11.1.2. Defining Good Performance
      3. 11.1.3. Focus on What's Most Important
    2. 11.2. What the Developer DBA Needs to Know about Performance
      1. 11.2.1. Users
      2. 11.2.2. Queries
      3. 11.2.3. Data
      4. 11.2.4. Robust Schema
    3. 11.3. What the Production DBA Needs to Know about Performance
    4. 11.4. Optimizing the Server
    5. 11.5. Configuring Server Hardware
      1. 11.5.1. Windows Server System Reference Architecture
      2. 11.5.2. Windows Server Catalog
    6. 11.6. CPU
      1. 11.6.1. 32-bit X86 Processors
      2. 11.6.2. 64-bit
      3. 11.6.3. X64 or IA64
      4. 11.6.4. Hyperthreading
      5. 11.6.5. Cache
      6. 11.6.6. Multi-Core
      7. 11.6.7. System Architecture
        1. 11.6.7.1. Symmetric Multi-Processing
        2. 11.6.7.2. NUMA
    7. 11.7. Memory
      1. 11.7.1. Physical Memory
      2. 11.7.2. Physical Address Space
      3. 11.7.3. Virtual Memory Manager
      4. 11.7.4. The Page File
      5. 11.7.5. Page Faults
        1. 11.7.5.1. Soft Page Faults
        2. 11.7.5.2. Hard Page Faults
        3. 11.7.5.3. Why Page Faults Are Bad
      6. 11.7.6. Virtual Address Space
      7. 11.7.7. 32-Bit System Memory Configuration
        1. 11.7.7.1. Physical Address Extensions
        2. 11.7.7.2. 3GB
        3. 11.7.7.3. AWE
        4. 11.7.7.4. /3GB or AWE?
      8. 11.7.8. 64-bit Systems
      9. 11.7.9. Memory Configuration Scenarios
    8. 11.8. I/O
      1. 11.8.1. Network
      2. 11.8.2. Disks
      3. 11.8.3. Storage Design
        1. 11.8.3.1. No Silver Bullet in Storage Design
        2. 11.8.3.2. Use the Vendors' Expertise
        3. 11.8.3.3. Every System Is Different
        4. 11.8.3.4. Simple Is Better
        5. 11.8.3.5. More Disks
        6. 11.8.3.6. Faster Disks
        7. 11.8.3.7. Test
        8. 11.8.3.8. Monitor
      4. 11.8.4. Designing a Storage System
        1. 11.8.4.1. Space
        2. 11.8.4.2. How Many Disks
        3. 11.8.4.3. Cost
        4. 11.8.4.4. Desired I/O Characteristics
        5. 11.8.4.5. RAID
          1. 11.8.4.5.1. Mirror with Stripe
          2. 11.8.4.5.2. Striping with Parity
          3. 11.8.4.5.3. RAID-Level Recommendations
        6. 11.8.4.6. Isolation
          1. 11.8.4.6.1. Separate SQL Data from the Log
          2. 11.8.4.6.2. Group Similar I/O Workloads
          3. 11.8.4.6.3. Using tempdb
      5. 11.8.5. Large Storage System Considerations: SAN Systems
        1. 11.8.5.1. Disk Virtualization
        2. 11.8.5.2. LUNs
          1. 11.8.5.2.1. LUN Size
          2. 11.8.5.2.2. Number of LUNs
      6. 11.8.6. Server Configuration
        1. 11.8.6.1. Disk Adapters
          1. 11.8.6.1.1. Number of Adapters
          2. 11.8.6.1.2. Placement
          3. 11.8.6.1.3. Firmware
          4. 11.8.6.1.4. Drivers
          5. 11.8.6.1.5. Configuration
        2. 11.8.6.2. Partitioning
        3. 11.8.6.3. Mounting Volumes
        4. 11.8.6.4. Start Sector Alignment
        5. 11.8.6.5. File Systems—NTFS versus FAT
        6. 11.8.6.6. NTFS Allocation Unit Size
      7. 11.8.7. Fragmentation
    9. 11.9. Summary
  17. 12. Optimizing SQL Server 2005
    1. 12.1. Application Optimization
      1. 12.1.1. Defining a Workload
      2. 12.1.2. System Harmony is the Goal
    2. 12.2. The Silent Killer: I/O Problems
      1. 12.2.1. SQL Server I/O Process Model
      2. 12.2.2. Database File Placement
      3. 12.2.3. Tempdb considerations
    3. 12.3. Table and Index Partitioning
      1. 12.3.1. Why Consider Partitioning?
      2. 12.3.2. Implementing Partitioning
      3. 12.3.3. Creating a partition function
      4. 12.3.4. Creating Filegroups
      5. 12.3.5. Creating a Partition Scheme
      6. 12.3.6. Create Tables and Indexes
        1. 12.3.6.1. Index Alignment
        2. 12.3.6.2. Storage alignment
    4. 12.4. Memory Considerations AND Enhancements
      1. 12.4.1. Tuning SQL Server Memory
      2. 12.4.2. 64-bit Version of SQL Server 2005
      3. 12.4.3. Configuring SQL 2005 for Dynamic Memory on an X86 platform
      4. 12.4.4. Memory-Friendly Applications
    5. 12.5. CPU Considerations
      1. 12.5.1. Data Locality
      2. 12.5.2. Cache Coherency
      3. 12.5.3. Affinity mask
        1. 12.5.3.1. SQL Server Processor Affinity Mask
        2. 12.5.3.2. Default SQL Server Work Scheduling
        3. 12.5.3.3. SQL Server Work Scheduling using Affinity Mask
      4. 12.5.4. Context switching
        1. 12.5.4.1. Privileged mode
        2. 12.5.4.2. User mode
        3. 12.5.4.3. Priority Boost
        4. 12.5.4.4. SQL Server Lightweight Pooling
      5. 12.5.5. Max degree of parallelism (MAXDOP)
      6. 12.5.6. Affinity I/O mask
      7. 12.5.7. Max Server Memory
      8. 12.5.8. Index Creation Memory Option
      9. 12.5.9. Min Memory per Query
    6. 12.6. Summary
  18. 13. Monitoring Your SQL Server
    1. 13.1. Why Should You Monitor?
      1. 13.1.1. Determining Your Monitoring Goals
      2. 13.1.2. Choosing the Appropriate Monitoring Tools
      3. 13.1.3. Evaluating Performance
    2. 13.2. Performance Monitor
      1. 13.2.1. Processor Usage
        1. 13.2.1.1. Object: Processor - Counter: % Processor Time
        2. 13.2.1.2. Object: System - Counter: % Total Processor Time
        3. 13.2.1.3. Object: System - Counter: Processor Queue Length
        4. 13.2.1.4. Object: Processor - Counter: % Privileged Time
        5. 13.2.1.5. Object: Processor - Counter: % User Time
        6. 13.2.1.6. Object: Processor - Counter: Interrupts/Sec
        7. 13.2.1.7. Isolating Processor Activity Created by SQL Server
          1. 13.2.1.7.1. Object: Process - Counter: % Processor Time
          2. 13.2.1.7.2. Object: Process - Counter: % User Time
        8. 13.2.1.8. Resolving Processor Bottlenecks
          1. 13.2.1.8.1. Analyze Your Application
          2. 13.2.1.8.2. Server Configuration
      2. 13.2.2. Disk Activity
        1. 13.2.2.1. Object: Physical Disk - Counter: % Avg. Disk Sec/Transfer
        2. 13.2.2.2. Object: Physical Disk - Counter: Avg. Disk Sec/Read
        3. 13.2.2.3. Object: Physical Disk - Counter: Avg. Disk Sec/Write
        4. 13.2.2.4. Object: Physical Disk - Counter: % Disk Time
        5. 13.2.2.5. Object: Physical Disk - Counter: Avg. Disk Queue Length
        6. 13.2.2.6. Object: Physical Disk - Counter: Current Disk Queue Length
        7. 13.2.2.7. Object: Physical Disk - Counter: % Disk Read Time
        8. 13.2.2.8. Object: Physical Disk - Counter: % Disk Write Time
        9. 13.2.2.9. Object: Physical Disk - Counter: % Idle Time
        10. 13.2.2.10. Object: Physical Disk - Counter: Avg. Disk Bytes/Transfer
        11. 13.2.2.11. Object: Physical Disk - Counter: Disk Bytes/Sec
        12. 13.2.2.12. Isolating Disk Activity Created by SQL Server
        13. 13.2.2.13. Is Disk Performance the Bottleneck?
        14. 13.2.2.14. Data Analysis
        15. 13.2.2.15. Resolving Disk Bottlenecks
          1. 13.2.2.15.1. Analyze Your Application
          2. 13.2.2.15.2. Disk Configuration and File Layout
          3. 13.2.2.15.3. Increase I/O Bandwidth
        16. 13.2.2.16. Disk Performance Counters
      3. 13.2.3. Memory Usage
        1. 13.2.3.1. Object: Memory - Counter: Available Mbytes
        2. 13.2.3.2. Object: Memory - Counter: Pages/Sec
        3. 13.2.3.3. Object: Memory - Counter: Committed Bytes
        4. 13.2.3.4. Object: Memory - Counter: Committed Limit
        5. 13.2.3.5. Isolating Memory Used by SQL Server
          1. 13.2.3.5.1. Object: Process - Counter: Working Set
          2. 13.2.3.5.2. Object: Process - Counter: Private Bytes
          3. 13.2.3.5.3. Object: Process - Counter: Virtual Bytes
          4. 13.2.3.5.4. Object: SQL Server: Buffer Manager - Counter: Buffer Cache Hit Ratio
          5. 13.2.3.5.5. Object: SQL Server: Buffer Manager - Counter: Total Pages
          6. 13.2.3.5.6. Object: SQL Server: Memory Manager - Counter: Total Server Memory (KB) and Target Server Memory (KB)
        6. 13.2.3.6. Resolving Memory Bottlenecks
    3. 13.3. Monitoring Events
      1. 13.3.1. SQL Trace
        1. 13.3.1.1. SQL Trace Architecture
        2. 13.3.1.2. Creating a Server-Side Trace Using T-SQL Stored Procedures
          1. 13.3.1.2.1. Retrieving the Trace Metadata
          2. 13.3.1.2.2. Retrieving Data from the Trace File
        3. 13.3.1.3. SQL Server Profiler
          1. 13.3.1.3.1. Showplan XML
          2. 13.3.1.3.2. Correlating a Trace with Windows Performance Log Data
          3. 13.3.1.3.3. Replaying a Trace
        4. 13.3.1.4. Performance Considerations When Using Trace
      2. 13.3.2. Event Notification
    4. 13.4. Monitoring with Dynamic Management Views and Functions
      1. 13.4.1. Viewing the Locking Information
      2. 13.4.2. Viewing Blocking Information
      3. 13.4.3. Index Usage in a Database
      4. 13.4.4. View Queries Waiting for Memory Grants
      5. 13.4.5. Connected User Information
      6. 13.4.6. Query Plan and Query Text for Currently Running Queries
      7. 13.4.7. Memory Usage
    5. 13.5. Summary
  19. 14. Performance Tuning T-SQL
    1. 14.1. Physical Query Processing
      1. 14.1.1. Compilation
        1. 14.1.1.1. Correctness-Related Reasons
          1. 14.1.1.1.1. Schemas of Objects
          2. 14.1.1.1.2. SET Options
        2. 14.1.1.2. Plan Optimality-Related Reasons
      2. 14.1.2. Tools and Commands for Recompilation Scenarios
        1. 14.1.2.1. SQL Profiler
        2. 14.1.2.2. Sys.syscacheobjects Virtual Table
        3. 14.1.2.3. DBCC FREEPROCCACHE
        4. 14.1.2.4. DBCC FLUSHPROCINDB (db_id)
      3. 14.1.3. Parser and Algebrizer
        1. 14.1.3.1. Name Resolution
        2. 14.1.3.2. Type Derivation
        3. 14.1.3.3. Aggregate Binding
        4. 14.1.3.4. Grouping Binding
      4. 14.1.4. Optimization
        1. 14.1.4.1. Optimization Flow
        2. 14.1.4.2. Simplification
        3. 14.1.4.3. Exploration
        4. 14.1.4.4. Implementation
    2. 14.2. Tuning Process
      1. 14.2.1. Database I/O Information
      2. 14.2.2. Working with the Query Plan
        1. 14.2.2.1. Estimated Execution Plan
          1. 14.2.2.1.1. SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL
          2. 14.2.2.1.2. SET SHOWPLAN_XML
          3. 14.2.2.1.3. Graphical Estimated Showplan
        2. 14.2.2.2. Actual Execution Plan
          1. 14.2.2.2.1. SET STATISTICS XML ON|OFF
          2. 14.2.2.2.2. SET STATISTICS PROFILE ON|OFF
          3. 14.2.2.2.3. Graphical Actual Execution Plan
      3. 14.2.3. Join Algorithms
        1. 14.2.3.1. Nested Loop or Loop Join
        2. 14.2.3.2. Hash Join
        3. 14.2.3.3. Merge Join
      4. 14.2.4. Index Access Methods
        1. 14.2.4.1. Table Scan
          1. 14.2.4.1.1. SET STATISTICS IO ON|OFF
          2. 14.2.4.1.2. Clearing Caches
        2. 14.2.4.2. Clustered Index Scan (Unordered)
        3. 14.2.4.3. Covering Nonclustered Index Scan (Unordered)
        4. 14.2.4.4. Clustered Index Scan (Ordered)
        5. 14.2.4.5. Covering Nonclustered Index Scan (Ordered)
        6. 14.2.4.6. Nonclustered Index Seek with Ordered Partial Scan and Lookups
        7. 14.2.4.7. Clustered Index Seek with Ordered Partial Scan
        8. 14.2.4.8. Fragmentation
        9. 14.2.4.9. Statistics
      5. 14.2.5. Data Modification Query Plan
      6. 14.2.6. Partitioned Table Query Plan
      7. 14.2.7. Gathering Query Plans for Analysis with SQL Trace
    3. 14.3. Summary
  20. 15. Indexing Your Database
    1. 15.1. What's New for Indexes in SQL Server 2005
      1. 15.1.1. Partition Tables and Indexes
      2. 15.1.2. Online Index Operations
      3. 15.1.3. Parallel Index Operations
      4. 15.1.4. Asynchronous Statistics Update
      5. 15.1.5. Full Text Indexes
      6. 15.1.6. Non-Key Columns in Nonclustred Indexes
      7. 15.1.7. Index Lock Granularity Changes
      8. 15.1.8. Indexes on XML Columns
      9. 15.1.9. Dropping and Rebuilding Large Indexes
      10. 15.1.10. Indexed View Enhancements
      11. 15.1.11. Version Store
      12. 15.1.12. Database Tuning Advisor
    2. 15.2. Sample Database
    3. 15.3. Partition Tables and Indexes
      1. 15.3.1. Why Use Partition Tables and indexes?
        1. 15.3.1.1. Faster and Easier Data Loading
        2. 15.3.1.2. Faster and Easier Data Deletion or Archival
        3. 15.3.1.3. Faster Queries
        4. 15.3.1.4. Sliding Windows
      2. 15.3.2. Prerequisites for Partitioning
      3. 15.3.3. Creating Partition Tables
        1. 15.3.3.1. Creating a Partition Table from an Existing Table
        2. 15.3.3.2. Adding New Data to the Partition Table
        3. 15.3.3.3. Deleting Data from the Partition Table
        4. 15.3.3.4. Partitioning a Production System
        5. 15.3.3.5. partitioning and dta
    4. 15.4. Index Maintenance
      1. 15.4.1. Monitoring Index Fragmentation
      2. 15.4.2. Cleaning Up Indexes
    5. 15.5. Database Tuning Advisor
      1. 15.5.1. Using DTA to Tune Individual Queries
        1. 15.5.1.1. Indexes for Updates
        2. 15.5.1.2. Reassessing Inserts after Adding Update Indexes
      2. 15.5.2. Too Many Indexes?
      3. 15.5.3. Tuning a Workload
    6. 15.6. Summary
  21. 16. Replication
    1. 16.1. Replication Overview
      1. 16.1.1. Replication types
        1. 16.1.1.1. Snapshot Replication
        2. 16.1.1.2. Transactional Replication
        3. 16.1.1.3. Merge Replication
      2. 16.1.2. Replication Components
    2. 16.2. Implementing Replication
      1. 16.2.1. The Setup
      2. 16.2.2. Setting up Distribution
      3. 16.2.3. Implementing Snapshot Replication
        1. 16.2.3.1. Setting Up Snapshot Publication
        2. 16.2.3.2. Setting up Subscription to the Snapshot Publication
        3. 16.2.3.3. Verifying Snapshot Replication
      4. 16.2.4. Implementing Transactional and Merge Replication
    3. 16.3. Peer-to-Peer Replication
    4. 16.4. Replication Monitoring
    5. 16.5. Summary
  22. 17. Database Mirroring
    1. 17.1. Overview of Database Mirroring
      1. 17.1.1. Operating Modes of Database Mirroring
      2. 17.1.2. Database Mirroring Example
        1. 17.1.2.1. Preparing the Endpoints
        2. 17.1.2.2. Preparing the Database for Mirroring
        3. 17.1.2.3. Initial Synchronization between Principal and Mirror
        4. 17.1.2.4. Establishing the Mirroring Session
        5. 17.1.2.5. High-Safety Operating Mode with Automatic Failover
          1. 17.1.2.5.1. Witness Server
          2. 17.1.2.5.2. The Quorum
        6. 17.1.2.6. High-Safety Operating Mode without Automatic Failover
        7. 17.1.2.7. High-Performance Operating Mode
      3. 17.1.3. Database Mirroring and SQL Server 2005 Editions
      4. 17.1.4. Database Mirroring Catalog Views
        1. 17.1.4.1. sys.database_mirroring
        2. 17.1.4.2. sys.database_mirroring_witnesses
        3. 17.1.4.3. sys.database_mirroring_endpoints
      5. 17.1.5. Database Mirroring Role Change
        1. 17.1.5.1. Automatic Failover
        2. 17.1.5.2. Manual Failover
        3. 17.1.5.3. Forced Service Failover
      6. 17.1.6. Database Availability Scenarios
        1. 17.1.6.1. Principal is Lost
          1. 17.1.6.1.1. Scenario 1: Safety FULL with a Witness
          2. 17.1.6.1.2. Scenario 2: Safety FULL without a Witness
          3. 17.1.6.1.3. Scenario 3: SAFETY OFF
        2. 17.1.6.2. Mirror is Lost
        3. 17.1.6.3. Witness Is Lost
        4. 17.1.6.4. Mirror and Witness Are Lost
    2. 17.2. Monitoring Database Mirroring
      1. 17.2.1. Monitoring Using System Monitor
        1. 17.2.1.1. Counters for the Principal
        2. 17.2.1.2. Counters for Mirror
      2. 17.2.2. Monitoring Using Database Mirroring Monitor
        1. 17.2.2.1. Principal Log: Unsent Log
        2. 17.2.2.2. Principal Log: Oldest Unsent Transaction
        3. 17.2.2.3. Principal Log: Time to Send Log (Estimated)
        4. 17.2.2.4. Principal Log: Current Send Rate
        5. 17.2.2.5. Principal Log: Current Rate of New Transaction
        6. 17.2.2.6. Mirror Log: Unrestored Log
        7. 17.2.2.7. Mirror Log: Time to Restore Log
        8. 17.2.2.8. Mirror Log: Current Restore Rate
        9. 17.2.2.9. Mirror Committed Overhead
        10. 17.2.2.10. Time to send and restore all current Log (Estimated)
        11. 17.2.2.11. Witness Address
        12. 17.2.2.12. Operating Mode
        13. 17.2.2.13. Setting Threshold on Counters and Sending Alerts
      3. 17.2.3. Monitoring using SQL Profiler
    3. 17.3. Troubleshooting Database Mirroring
      1. 17.3.1. Troubleshooting Setup Errors
      2. 17.3.2. Troubleshooting Runtime Errors
    4. 17.4. Preparing the Mirror server for Failover
      1. 17.4.1. Hardware, Software, and Server Configuration
      2. 17.4.2. Database Availability During Planned Downtime
        1. 17.4.2.1. SAFETY FULL
        2. 17.4.2.2. SAFETY OFF
      3. 17.4.3. SQL Job Configuration on the Mirror
      4. 17.4.4. Database TRUSTWORTHY Bit on the Mirror
      5. 17.4.5. Client Redirection to the Mirror
    5. 17.5. Mirroring Multiple Databases
    6. 17.6. Database Mirroring and Other High-Availability Solutions
      1. 17.6.1. Database Mirroring and Clustering
      2. 17.6.2. Database Mirroring and Transactional Replication
      3. 17.6.3. Database Mirroring and Log Shipping
    7. 17.7. Mirroring Event Listener Setup
    8. 17.8. Database Snapshots
    9. 17.9. Summary
  23. 18. Backup and Recovery
    1. 18.1. Types of Failure
      1. 18.1.1. Hardware Failure
      2. 18.1.2. User Error
      3. 18.1.3. Application Failure
      4. 18.1.4. Software Failure
      5. 18.1.5. Too Much Privilege
      6. 18.1.6. Local Disasters
    2. 18.2. Overview of Backup and Restore
      1. 18.2.1. How Backup Works
      2. 18.2.2. How Restore Works
      3. 18.2.3. Recovery Models
      4. 18.2.4. Choosing a Model
      5. 18.2.5. Switching Recovery Models
      6. 18.2.6. Verifying the Backup Images
      7. 18.2.7. Backup History Tables
      8. 18.2.8. Permissions required for backup and restore
      9. 18.2.9. Backup System Databases
        1. 18.2.9.1. Master
        2. 18.2.9.2. MSDB
        3. 18.2.9.3. Tempdb
        4. 18.2.9.4. Model
      10. 18.2.10. Full-text Backup
    3. 18.3. Planning for Recovery
      1. 18.3.1. Recovery Requirements
      2. 18.3.2. Data Usage Patterns
      3. 18.3.3. Maintenance Time Window
      4. 18.3.4. Other High-Availability Solutions
    4. 18.4. Developing and Executing a Backup Plan
      1. 18.4.1. SQL Server 2005 Management Studio
      2. 18.4.2. Transact-SQL Backup Command
    5. 18.5. Managing Backups
    6. 18.6. Backup and Restore Performance
    7. 18.7. Performing Recovery
      1. 18.7.1. Restore Process
        1. 18.7.1.1. Instant File Initialization
        2. 18.7.1.2. Full Database Restore
        3. 18.7.1.3. Partial database Restore
        4. 18.7.1.4. Transaction-Log Restore
        5. 18.7.1.5. File/Filegroup Restore
        6. 18.7.1.6. Database Snapshot Restore
        7. 18.7.1.7. Full-Text Restore
        8. 18.7.1.8. History Tables Restore
      2. 18.7.2. SQL Server Management Studio Restore
      3. 18.7.3. T-SQL Restore Command
      4. 18.7.4. Restoring System Databases
    8. 18.8. Archiving Data
      1. 18.8.1. SQL Server 2005 table partitioning
      2. 18.8.2. Partition View
    9. 18.9. Disaster Recovery Planning
    10. 18.10. Summary
  24. 19. SQL Server 2005 Log Shipping
    1. 19.1. Log Shipping Deployment Scenarios
      1. 19.1.1. Log Shipping as a Warm Standby Server
      2. 19.1.2. Log Shipping as a Disaster Recovery Solution
      3. 19.1.3. Log Shipping as a Report Database Solution
    2. 19.2. Log-Shipping Architecture
      1. 19.2.1. Primary Server
      2. 19.2.2. Monitor Server
    3. 19.3. Log Shipping Process
    4. 19.4. System Requirements
      1. 19.4.1. Network
      2. 19.4.2. Identical Capacity Servers
      3. 19.4.3. Storage
      4. 19.4.4. Monitor Server
      5. 19.4.5. Software
    5. 19.5. Deploying Log Shipping
      1. 19.5.1. Initial Configuration
      2. 19.5.2. Deploying with Management Studio
      3. 19.5.3. Deploying with T-SQL commands
    6. 19.6. Monitoring and Troubleshooting
      1. 19.6.1. Monitoring with Management Studio
      2. 19.6.2. Monitoring with Stored Procedures
      3. 19.6.3. Troubleshooting Approach
    7. 19.7. Managing Changing Roles
      1. 19.7.1. Synchronizing Dependencies
        1. 19.7.1.1. Login and Database Users
        2. 19.7.1.2. SQL Agent Jobs
        3. 19.7.1.3. Other Database Dependencies
      2. 19.7.2. Switching Roles from the Primary to Secondary Servers
        1. 19.7.2.1. Planned Failover
        2. 19.7.2.2. Unplanned Role Change
      3. 19.7.3. Switching Between Primary and Secondary Roles
      4. 19.7.4. Redirecting Clients to Connect to the Secondary Server
        1. 19.7.4.1. Application Coding
        2. 19.7.4.2. Network Load Balancing
        3. 19.7.4.3. Domain Name Service (DNS)
        4. 19.7.4.4. SQL Client Aliasing
    8. 19.8. Database Backup Plan
    9. 19.9. Integrating Log Shipping with other High Availability Solutions
      1. 19.9.1. SQL Server 2005 Data Mirroring
      2. 19.9.2. Windows Failover Clustering
      3. 19.9.3. SQL Server 2005 Replication
    10. 19.10. Removing Log Shipping
      1. 19.10.1. Removing Log Shipping with Management Studio
      2. 19.10.2. Removing Log Shipping with T-SQL Commands
    11. 19.11. Log-Shipping Performance
    12. 19.12. Upgrading to SQL Server 2005 Log Shipping
      1. 19.12.1. Minimum Downtime Approach
      2. 19.12.2. With Downtime Approach
      3. 19.12.3. Deploy Log Shipping Approach
      4. 19.12.4. Removing SQL Server 2000 Log-Shipping Tables and Jobs
    13. 19.13. Summary
  25. 20. Clustering SQL Server 2005
    1. 20.1. Clustering and Your Organization
      1. 20.1.1. What Clustering Can Do
      2. 20.1.2. What Clustering Cannot Do
      3. 20.1.3. Choosing SQL Server 2005 Clustering for the Right Reasons
      4. 20.1.4. Alternatives to Clustering
        1. 20.1.4.1. Warm Backup Server
        2. 20.1.4.2. Hot Backup Server
        3. 20.1.4.3. Log Shipping
        4. 20.1.4.4. Replication
        5. 20.1.4.5. Database Mirroring
        6. 20.1.4.6. Third-Party Clustering Solutions
        7. 20.1.4.7. What to Do
    2. 20.2. Clustering: The Big Picture
      1. 20.2.1. How Clustering Works
        1. 20.2.1.1. Active vs. Passive Nodes
        2. 20.2.1.2. Shared Disk Array
        3. 20.2.1.3. The Quorum
        4. 20.2.1.4. Public and Private Networks
        5. 20.2.1.5. The Virtual Server
        6. 20.2.1.6. How a Failover Works
      2. 20.2.2. Clustering Options?
        1. 20.2.2.1. Active/Passive vs. Active/Active
        2. 20.2.2.2. Multinodes Clusters
          1. 20.2.2.2.1. Purchase the Right Software
          2. 20.2.2.2.2. Number of Nodes to Use?
        3. 20.2.2.3. Clustering Multiple Instances of SQL Server on the Same Server
    3. 20.3. Upgrading SQL Server Clustering
      1. 20.3.1. Don't Upgrade
      2. 20.3.2. Upgrading Your SQL Server 2005 Cluster In-Place
      3. 20.3.3. Rebuilding Your Cluster from Scratch
      4. 20.3.4. Backout Plan
      5. 20.3.5. Which Upgrade Option is Best?
    4. 20.4. Getting Prepared for Clustering
      1. 20.4.1. Preparing the Infrastructure
      2. 20.4.2. Preparing the Hardware
        1. 20.4.2.1. Finding Your Way Through the Hardware Jungle
        2. 20.4.2.2. The Hardware Compatibility List
        3. 20.4.2.3. Preparing the Hardware
    5. 20.5. Clustering Windows Server 2003
      1. 20.5.1. Before Installing Windows 2003 Clustering
      2. 20.5.2. Installing Windows Server 2003 Clustering
        1. 20.5.2.1. Installing the First Cluster Node
        2. 20.5.2.2. Installing the Second Node of Your Cluster
        3. 20.5.2.3. Verifying the Nodes with Cluster Administrator
      3. 20.5.3. Configuring Windows Server 2003 for Clustering
      4. 20.5.4. Test, Test, and Test Again
        1. 20.5.4.1. Preparing for the Tests
        2. 20.5.4.2. Move Groups Between Nodes
        3. 20.5.4.3. Manually Initiate a Failover in Cluster Administrator
        4. 20.5.4.4. Manually Failover Nodes by Turning Them Off
        5. 20.5.4.5. Manually Failover Nodes by Breaking the Public Network Connections
        6. 20.5.4.6. Manually Failover Nodes by Breaking the Shared Array Connection
    6. 20.6. Configuring the Microsoft Distributed Transaction Coordinator
    7. 20.7. Clustering SQL Server 2005
      1. 20.7.1. Clustering SQL Server
      2. 20.7.2. Clustering Analysis Services
      3. 20.7.3. Installing the Service Pack and Hot Fixes
      4. 20.7.4. Checking the SQL Server 2005 Installation From Cluster Administrator
      5. 20.7.5. Installing Clustering on Multiple Nodes
      6. 20.7.6. Test, Test, and Test Again
        1. 20.7.6.1. Preparing for the Testing
        2. 20.7.6.2. Move Groups Between Nodes
        3. 20.7.6.3. Manually Initiate a Failover in Cluster Administrator
        4. 20.7.6.4. Manually Fail Over Nodes by Turning Them Off
        5. 20.7.6.5. Manually Fail Over Nodes by Breaking the Public Network Connections
        6. 20.7.6.6. Manually Fail Over Nodes by Breaking the Shared Array Connection
    8. 20.8. Maintaining the Cluster
      1. 20.8.1. Learn to Love Cluster Administrator
      2. 20.8.2. Doing the Routine
        1. 20.8.2.1. Checking the Cluster
        2. 20.8.2.2. Reviewing Logs
      3. 20.8.3. Dealing with Cluster Failovers
        1. 20.8.3.1. Removing the Failed Node
        2. 20.8.3.2. Re-Adding the New Node
        3. 20.8.3.3. What Happens if My Disk Array Dies?
      4. 20.8.4. Installing Patches and Service Packs
        1. 20.8.4.1. Operating System Patches and Service Packs
        2. 20.8.4.2. SQL Server 2005 Service Packs and Hot Fixes
      5. 20.8.5. Test, Test, and Test Regularly
    9. 20.9. Troubleshooting Cluster Problems
      1. 20.9.1. How to Approach Clustering Troubleshooting
      2. 20.9.2. Do It Right the First Time
      3. 20.9.3. Gathering Information
      4. 20.9.4. Resolving Problems
      5. 20.9.5. Working with Microsoft
    10. 20.10. Summary
  26. A. Raymond James Lab Report
    1. A.1. Summary

Product information

  • Title: Professional SQL Server™ 2005 Administration
  • Author(s): Brian Knight, Ketan Patel, Wayne Snyder, Jean-Claude Armand, Ross LoForte, Brad McGehee, Steven Wort, Joe Salvatore, Haidong Ji
  • Release date: December 2006
  • Publisher(s): Wrox
  • ISBN: 9780470055205