Microsoft® SQL Server™ 2005 Administrator's Companion

Microsoft® SQL Server™ 2005 Administrator's Companion

by Edward Whalen, Marcilina Garcia, Burzin Patel, Stacia Misner, Victor Isakov
Released November 2006
Publisher(s): Microsoft Press
ISBN: 9780735621985

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

Get comprehensive information to plan, deploy, administer, and support Microsoft SQL Server 2005 in organizations of any size. With the ADMINISTRATOR’S COMPANION, you get mission-critical information in a single volume—straight from the experts.

Table of contents

  1. Microsoft® SQL Server™ 2005 Administrator’s Companion
    1. SPECIAL OFFER: Upgrade this ebook with O’Reilly
    2. A Note Regarding Supplemental Files
    3. Acknowledgments
      1. Contributing Authors
    4. Introduction
      1. How to Use this Book
      2. What’s in This Book
      3. About the CD
      4. Computer System Requirements
      5. Support
      6. Talk to Us
    5. I. Introduction to Microsoft SQL Server 2005
      1. 1. What’s New in Microsoft SQL Server
        1. New Hardware Support
          1. Native 64-Bit Support
          2. NUMA Support
        2. Data Availability
          1. Online Restore
          2. Online Index Operations
          3. Database Snapshot
          4. Fast Recovery
          5. Mirrored Backups
          6. Database Mirroring
          7. Read Committed Snapshot and Snapshot Isolation
        3. Performance
          1. Data Partitioning
          2. Plan Guides
          3. Forced Parameterization
          4. Dynamic Management Views
        4. Enhancements to Existing Features
          1. SNAC
          2. Failover Clustering
          3. Replication
          4. Indexes
          5. Full-Text Search
        5. Tools and Utilities
          1. SQL Server Management Studio
          2. Query Editor
          3. SQL Configuration Manager
          4. Surface Area Configuration
          5. SQL Server Profiler
          6. Database Engine Tuning Advisor
          7. SQL Server Upgrade Advisor
          8. sqlcmd Utility
          9. tablediff Utility
        6. Business Intelligence Features
          1. Business Intelligence Development Studio
          2. Integration Services
          3. Analysis Services
          4. Reporting Services
          5. Notification and Broker Services
        7. Summary
      2. 2. Microsoft SQL Server 2005 Editions, Capacity Limits, and Licensing
        1. SQL Server 2005 Editions
          1. Mobile Edition
          2. Express Edition
          3. Workgroup Edition
          4. Standard Edition
          5. Enterprise Edition
          6. Developer Edition
        2. Understanding Windows Platform Support
        3. Understanding Processors and Memory Limits
        4. Factoring in Head-Room
        5. Comparing SQL Server 2005 Editions
          1. Database Engine Features
          2. Analysis Services
          3. Reporting Services
          4. Notification Services
          5. Integration Services
          6. Replication
        6. SQL Server 2005 Capacity Limits
        7. Understanding SQL Server 2005 Licensing
          1. User Client Access Licensing
          2. Device Client Access Licensing
          3. Processor Licensing
        8. Licensing Considerations for High-Availability Environments
        9. SQL Server 2005 Pricing
        10. Summary
      3. 3. Roles and Responsibilities of the Microsoft SQL Server DBA
        1. Different Kinds of DBA
          1. Production DBA
          2. Development DBA
          3. Architect DBA
          4. ETL DBA
          5. OLAP DBA
        2. Basic Duties of a DBA
          1. Installation and Configuration
            1. Software Installation
            2. Hardware and Software Configuration
            3. Service Packs and Security Updates
          2. Security
            1. User Accounts and Permissions
            2. Server Security
            3. Security Auditing
          3. Operations
            1. Backup and Restore
            2. Change Management
          4. Service Levels
          5. System Monitoring
          6. Performance Tuning
          7. Routine Maintenance
          8. Reliability
          9. Disaster Recovery
            1. Clustering
            2. Log Shipping
            3. Database Mirroring
            4. Replication
          10. Planning and Scheduling Downtime
          11. Capacity Planning
          12. Documentation
            1. Configuration Documentation
            2. Design Documents
            3. Operational Information
          13. Development and Design
            1. Database Design
            2. Data Modeling
            3. Procedure and SSIS Development
          14. Scalability
          15. Replication
          16. Named Instances
        3. DBA Tips, Guidelines, and Advice
          1. Know Your Operating System
          2. Help Desk
          3. Purchasing Input
          4. Know Your Versions
          5. Don’t Panic
        4. Summary
    6. II. System Design and Architecture
      1. 4. I/O Subsystem Planning and RAID Configuration
        1. I/O Fundamentals
          1. Disk Drive Basics
          2. Disk Drive Performance Characteristics
            1. Rotational Latency
            2. Disk Seeks
            3. Track-to-Track Seeks
            4. Average Seek Time
            5. Transfer Time
          3. Disk Drive Specifications
          4. Disk Drive Performance
            1. Sequential I/O
            2. Random I/O
          5. Solutions to the Disk Performance Limitation Problem
        2. Redundant Array of Independent Disks (RAID)
          1. RAID Basics
            1. Striping
            2. Redundancy
          2. RAID Levels
            1. RAID-0
              1. RAID-0 Recommendations
            2. RAID-1 and RAID-10
            3. RAID-1 and RAID-10 Recommendations
            4. RAID-5
              1. RAID-5 Parity
                1. Creating the Parity
              2. RAID-5 Recommendations
          3. RAID Performance
            1. Read Performance
            2. Write Performance
          4. Disk Calculations
            1. RAID-0
            2. RAID-1
            3. RAID-10
            4. RAID-5
          5. RAID Comparison
          6. Which RAID Level Is Right for You?
        3. SQL Server I/O Overview
          1. SQL Server Reads
          2. SQL Server Writes
          3. Transaction Log
          4. Backup and Recovery
        4. Planning the SQL Server Disk Layout
          1. Determine I/O Requirements
            1. Space
            2. Performance
          2. Plan the Disk Layout
            1. Planning the Log
            2. Planning the Data Files
          3. Implement the Configuration
        5. Summary
      2. 5. 32-Bit Versus 64-Bit Platforms and Microsoft SQL Server 2005
        1. CPU Basics
          1. 64-Bit Versus 32-Bit Addressing
            1. Virtual Memory
            2. Physical Memory
          2. Hardware Platforms
            1. x86
            2. x64
            3. Itanium
        2. Windows Versions
          1. Windows 2000
          2. Windows Server 2003
          3. Windows Server 2003 64-Bit editions
          4. Windows Comparison
        3. SQL Server 2005 Options
          1. SQL Server 32-Bit Edition
            1. Running 32-Bit SQL Server 2005 on 32-Bit Windows 2003 Server
            2. 32-Bit SQL Server on 64-Bit Windows
          2. SQL Server 64-Bit
        4. Taking Advantage of 64-Bit SQL Server
          1. Utilizing Large Memory with the 32-Bit Version of SQL Server 2005
          2. Utilizing Large Memory with the 64-Bit Version of SQL Server 2005
        5. Summary
      3. 6. Capacity Planning
        1. Principles of Capacity Planning
          1. Capacity Planning Versus Sizing
          2. Service Level Agreements
          3. Mathematics of Capacity Planning
        2. CPU Capacity Planning
          1. Sizing CPUs
          2. Monitoring CPU Usage
        3. Memory Capacity Planning
          1. Sizing Memory
          2. Monitoring Memory
        4. I/O Capacity Planning
          1. Sizing the I/O Subsystem
          2. Monitoring the I/O Subsystem
        5. Network Capacity Planning
          1. Sizing the Network
          2. Monitoring the Network
        6. Growth Considerations
          1. Calculating Growth
          2. Planning for Future Growth
        7. Benchmarking and Load Testing
          1. Load Testing the Application
        8. Benchmarking the I/O Subsystem
          1. Getting Iometer
          2. Using Iometer
          3. Benchmarking the Network
            1. Using TTCP
        9. Using MOM for Capacity Planning
        10. Summary
      4. 7. Choosing a Storage System for Microsoft SQL Server 2005
        1. Interconnect and Protocol Technologies
          1. Understanding Data Transfer: Block Form Versus File Format
          2. SCSI Protocol over Parallel SCSI Interconnect
            1. Clustering with SCSI
            2. Advantages of SCSI
            3. Disadvantages of SCSI
          3. Ethernet Interconnect
            1. Advantages of Ethernet
            2. Disadvantages of Ethernet
          4. iSCSI
            1. Advantages of iSCSI
            2. Disadvantages of iSCSI
          5. Fibre Channel (FC) Interconnect
            1. Advantages of Fibre Channel
            2. Disadvantages of Fibre Channel
          6. Interconnect Bandwidth Comparison
        2. Storage Systems
          1. DAS
          2. SAN
            1. FC SAN
            2. iSCSI SAN (or IP SAN)
            3. iSCSI Bridge to FC SAN
          3. NAS
            1. When to Use NAS
            2. NAS Gateway to FC SAN
        3. Storage Considerations for SQL Server 2005
        4. Summary
      5. 8. Installing and Upgrading Microsoft SQL Server 2005
        1. Preinstallation Planning
          1. Minimum Hardware Requirements
          2. Selecting the Processor Architecture
          3. Installing Internet Information Services
          4. Components to Be Installed
          5. Service Accounts
          6. Multiple Instances and Side-by-Side Installation
          7. Licensing Mode
          8. Collation
          9. Authentication Modes
          10. Security Considerations
        2. Installing SQL Server 2005
          1. Installing SQL Server 2005 Using the Installation Wizard
          2. Installing SNAC Using the Installation Wizard
          3. Installing SQL Server 2005 Using the Command Prompt
            1. Installing a Default Instance
            2. Installing a Named Instance with Mixed Authentication
            3. Adding Components to an Existing Instance
            4. Installing Using a Settings (.ini) File
        3. Upgrading to SQL Server 2005
          1. SQL Server Upgrade Advisor
            1. Installing SQL Server Upgrade Advisor
            2. Using SQL Server Upgrade Advisor
          2. Upgrade Procedure
          3. Post-Upgrade Steps
        4. Reading the SQL Server 2005 Setup Log Files
        5. Uninstalling SQL Server 2005
          1. Uninstalling SQL Server 2005 Using the Uninstall Wizard
          2. Uninstalling SQL Server 2005 Using the Command Prompt
        6. Using SQL Server Surface Area Configuration
          1. sac Utility
        7. Summary
      6. 9. Configuring Microsoft SQL Server 2005 on the Network
        1. Understanding the SQL Server Network Services
          1. SQL Server APIs
            1. ODBC Connectivity
            2. OLE DB
            3. JDBC
            4. Other APIs
          2. SQL Server Network Libraries
            1. Named Pipes
            2. Shared Memory
            3. TCP/IP
            4. VIA
          3. Selecting a Network Library
        2. SQL Native Client (SNAC)
          1. Using SQL Native Client
          2. Tracing and Debugging
        3. Configuring Network Protocols
          1. Configuring Server and Client Protocols
            1. SQL Server 2005 Services
            2. SQL Server 2005 Network Protocols
            3. SQL Native Client Configuration
        4. Using ODBC Data Source Names (DSN)
          1. Creating an ODBC DSN
          2. Using Aliases
        5. SQL Server Browser Service
          1. SQL Browser Working
          2. Hiding a SQL Server 2005 Instance
        6. Network Components and Performance
          1. The Software Layer
          2. The Hardware Layer
        7. Network Monitoring
          1. Monitoring Network Performance
          2. Finding Solutions to Network Problems
        8. Summary
    7. III. Microsoft SQL Server 2005 Administration
      1. 10. Creating Databases and Database Snapshots
        1. Understanding the Database Structure
          1. Database Files
            1. Primary Data File
            2. Transaction Log File
            3. Secondary Data File
            4. Naming Database Files
          2. Database Filegroups
        2. Understanding System Databases
          1. master
          2. model
          3. msdb
          4. resource
          5. tempdb
          6. AdventureWorks and AdventureWorksDW
        3. Creating User Databases
          1. Creating a Database
            1. Creating a Database Using SQL Server Management Studio
            2. Creating a Database Using the T-SQL Command
          2. Setting Database Options
            1. Collation (COLLATE)
            2. Recovery Model (RECOVERY)
            3. Compatibility Level
            4. Auto Close (AUTO_CLOSE)
            5. Auto Create Statistics (AUTO_CREATE_STATISTICS)
            6. Auto Shrink (AUTO_SHRINK)
            7. Auto Update Statistics (AUTO_UPDATE_STATISTICS)
            8. Auto Update Statistics Asynchronously (AUTO_UPDATE_STATISTICS_ASYNC)
            9. Close Cursor on Commit Enabled (CURSOR_CLOSE_ON_COMMIT)
            10. Default Cursor (CURSOR_DEFAULT)
            11. ANSI NULL Default (ANSI_NULL_DEFAULT)
            12. ANSI NULL Enabled (ANSI_NULLS)
            13. ANSI Padding Enabled (ANSI_PADDING)
            14. ANSI Warnings Enabled (ANSI_WARNINGS)
            15. Arithmetic Abort Enabled (ARITHABORT)
            16. Concatenate Null Yields Null (CONCAT_NULL_YIELDS_NULL)
            17. Cross-Database Ownership Chaining Enabled (DB_CHAINING)
            18. Date Correlation Optimization Enabled (date_correlation_optimization_option)
            19. Numeric Round-Abort (NUMERIC_ROUNDABORT)
            20. Parameterization (PARAMETERIZATION)
            21. Quoted Identifier Enabled (QUOTED_IDENTIFIER)
            22. Recursive Triggers Enabled (RECURSIVE_TRIGGERS)
            23. Trustworthy (TRUSTWORTHY)
            24. Page Verify (PAGE_VERIFY)
            25. Database Read-Only (READ_ONLY or READ_WRITE)
            26. Database State (DB_STATE_OPTION)
            27. Restrict Access (DB_USER_ACCESS_OPTION)
        4. Viewing Database Details
          1. Viewing Database Details with SQL Server Management Studio
          2. Viewing Database Details with the sp_helpdb Command
        5. Deleting a Database
          1. Deleting a Database Using SQL Server Management Studio
          2. Deleting a Database Using the DROP DATABASE Command
        6. Real-World Database Layouts
          1. Simple Application Workload
          2. Moderately Complex Application Workload
          3. Complex Application Workload
        7. Using Database Snapshots
          1. How Database Snapshots Work
          2. Managing Database Snapshots
            1. Creating Database Snapshots
            2. Viewing Database Snapshots
            3. Deleting a Database Snapshot
            4. Reverting a Database
          3. Common Uses
            1. Database Snapshot Performance
          4. Database Snapshots Limitations
        8. Summary
      2. 11. Creating Tables and Views
        1. Table Fundamentals
          1. Data Types
            1. System Data Types
            2. Aliases and Common Language Runtime User-Defined Data Types
              1. Creating Alias Data Types
              2. Creating CLR User-Defined Data Types
              3. Dropping User-Defined Data Types
          2. Nulls
          3. IDENTITY Column
        2. Creating, Modifying, and Dropping Tables
          1. Creating Tables
          2. Modifying Tables
          3. Dropping Tables
        3. Views
          1. Advantages of Views
          2. Data Security with Views
          3. Creating, Modifying, and Dropping Views
          4. View Source
          5. Modifying Views
          6. Dropping Views
        4. System Views
        5. Summary
      3. 12. Creating Indexes for Performance
        1. Index Fundamentals
        2. How to Optimally Take Advantage of Indexes
        3. Index Types
          1. Clustered Index
          2. Nonclustered Index
          3. Included Columns Index
          4. Indexed Views
          5. Full-Text Index
          6. XML Index
        4. Designing Indexes
          1. Index Best Practices
          2. Index Restrictions
          3. Using the Index Fill Factor
          4. Partitioned Indexes
        5. Creating Indexes
          1. Index Creation Examples
          2. Normal Index Creation Logging
          3. Minimally Logged Operations
        6. Index Maintenance and Tuning
          1. Monitoring Indexes
          2. Rebuilding Indexes
          3. Disabling Indexes
          4. Tuning Indexes
          5. Online Index Operations
        7. Summary
      4. 13. Enforcing Data Integrity
        1. What Is Data Integrity?
        2. Enforcing Integrity with Constraints
          1. PRIMARY KEY Constraints
          2. UNIQUE Constraints
          3. FOREIGN KEY Constraints
          4. CHECK Constraints
          5. NULL and NOT NULL Constraints
          6. DEFAULT Definitions
        3. Summary
      5. 14. Backup Fundamentals
        1. Why Perform Backups with a Highly Available System?
        2. System Failures That Require Backups
          1. Hardware Failures
          2. Software Failures
        3. Purpose of the Transaction Log
        4. Microsoft SQL Server Automatic Recovery
        5. Recovery Models and Logging
          1. Simple Recovery Model
          2. Full Recovery Model
          3. Bulk-Logged Recovery Model
          4. Viewing and Changing the Recovery Model
        6. Types of Backups
          1. Data Backups
            1. Full Database Backup
            2. Partial Backup
            3. File and Filegroup Backup
          2. Differential Backups
          3. Log Backups
          4. Copy-Only Backups
          5. Full-Text Catalog Backups
        7. Backup and Media Fundamentals
          1. Understanding Backup Devices and Media Sets
          2. Mirrored Media Sets
          3. Overview of Backup History Tables
          4. Viewing Backup Sets in Management Studio
        8. Backup Strategy
        9. Backing Up System Databases
        10. Summary
      6. 15. Restoring Data
        1. Practicing and Documenting Restore Procedures
        2. Restore and Recovery Concepts
        3. Restoring Data from Backups
          1. Complete Database, Differential Database, and Log Restores
          2. Point-in-Time Restore
          3. File and Filegroup Restore
          4. Page Restore
          5. Partial and Piecemeal Restore
          6. Revert to Database Snapshot
          7. Onine Restore
          8. Fast Recovery
        4. Summary
      7. 16. User and Security Management
        1. Principals
          1. Logins
            1. Windows Authentication
            2. Mixed Mode Authentication
            3. Creating Logins
          2. Users
          3. Roles
            1. Fixed Database Roles
        2. Securables
          1. Schemas
        3. Permissions
          1. Server Permissions
          2. Database Object Permissions
            1. Using SQL Server Management Studio to Assign Object Permissions
            2. Using SQL to Assign Object Permissions
            3. Using SQL to Revoke Object Permissions
          3. Statement Permissions
            1. Using SQL Server Management Studio to Assign Statement Permissions
            2. Using SQL to Assign Statement Permissions
            3. Using SQL to Revoke Statement Permissions
        4. Summary
    8. IV. Microsoft SQL Server 2005 Architecture and Features
      1. 17. Transactions and Locking
        1. What Is a Transaction?
        2. ACID Properties
          1. Atomicity
          2. Consistency
          3. Isolation
          4. Durability
        3. Committing Transactions
          1. Transaction Commit Modes
            1. Autocommit Mode
            2. Explicit Mode
            3. Using Explicit Transactions
            4. @@TRANCOUNT Variable
            5. Creating Nested Transactions
            6. Implicit Mode
          2. Transaction Performance
        4. Transaction Rollbacks
          1. Automatic Rollbacks
          2. Programmed Rollbacks
          3. Using Savepoints
        5. Transaction Locking
          1. Locking Management Features
          2. Lockable Resources
          3. Lock Modes
            1. Shared
            2. Update
            3. Exclusive
            4. Intent
            5. Schema
            6. Bulk Update
            7. Key-Range
        6. Viewing Locks
        7. Locking Hints
        8. Blocking and Deadlocks
        9. Isolation Levels
          1. Concurrent Transaction Behavior
          2. Row Versioning
            1. Read Committed Snapshot
              1. Advantages of Read Committed Snapshot
              2. Disadvantages of Read Committed Snapshot
            2. Snapshot Isolation Level
              1. Advantages of Snapshot Isolation
              2. Disadvantages of Snapshot Isolation
            3. Viewing Snapshot Database Options
        10. Summary
      2. 18. Microsoft SQL Server 2005 Memory Configuration
        1. Buffer Cache
          1. Lazy Writer Process
          2. Checkpoint Process
            1. Checkpoint Duration
            2. Recovery Interval
        2. SQL Server Memory Allocation
          1. Dynamic Memory Allocation
          2. Static Memory Allocation
          3. Setting Max and Min Server Memory
        3. Summary
      3. 19. Data Partitioning
        1. Partitioning Fundamentals
          1. Data Partitioning Basics
          2. Partitioning Benefits
            1. Partitioning for Data Manageability
            2. Partitioning for Storage Resource Utilization
          3. Performance Benefits of Partitioning
            1. Partitions Versus Indexes
            2. Partitions Versus Bad Indexes
        2. Designing Partitions
          1. Partitioning Design Fundamentals
        3. Creating Partitions
          1. Create the Partition Function
          2. Create the Partition Scheme
          3. Create the Partitioned Table
          4. Create the Partitioned Index
        4. Viewing Partition Information
          1. Viewing Partition Information with SQL Statements
          2. Viewing Partition Information with SQL Server Management Studio
        5. Maintaining Partitions
          1. Adding Partitions
          2. Archiving Partitions
          3. Deleting Partitions
          4. Repartitioning Tables
          5. Partitioning a Nonpartitioned Table
          6. Unpartitioning a Table
          7. Dropping Partition Functions and Schemes
        6. Using Partitions
          1. Inserting Data into Partitioned Tables
          2. Selecting Data from Partitioned Tables
          3. Selecting Data from a Specific Partition
        7. Partitioning Scenarios
          1. Scenario 1: Partitioning Historical Data
          2. Scenario 2: Storage Partitioning
          3. Scenario 3: Partitioning for Maintenance Optimization
          4. Scenario 4: Spatial Partitioning
          5. Scenario 5: Account Partitioning
          6. Scenario 6: Join Partitioning
          7. Scenario Summary
        8. Summary
    9. V. Microsoft SQL Server 2005 Business Intelligence
      1. 20. Replication
        1. Replication Fundamentals
        2. Uses of Replication
          1. Scaling Out Applications
          2. Data Warehousing
          3. Distributing and Consolidating Data
          4. Offloading Report Processing
        3. Replication Concepts
          1. Replication Components
            1. Publishers
              1. Publications
              2. Articles
            2. Distributors
            3. Subscribers
              1. Subscription
        4. Types of Replication
          1. Snapshot Replication
          2. Transactional Replication
          3. Merge Replication
        5. Components of Replication
          1. Replication Data
            1. Articles
            2. Publications
          2. Push and Pull Subscriptions
            1. Push Subscriptions
            2. Pull Subscriptions
          3. Replication Agents
            1. The Snapshot Agent
            2. The Log Reader Agent
            3. The Distribution Agent
            4. The Merge Agent
            5. The Queue Reader Agent
        6. Configuring Replication
          1. Configure the Distributor
          2. Configure Publications
        7. Creating a Publication with SQL Statements
          1. Configure Subscribers
        8. Creating a Subscription with SQL Statements
        9. Configuring an Oracle Publication
        10. Managing Replication
          1. Publisher Properties
          2. Distributor Properties
          3. Disable Publishing and Distribution
          4. Launch Replication Monitor
          5. Generate Scripts
          6. Update Replication Passwords
          7. New
          8. Refresh
        11. Monitoring and Tuning Replication
          1. Monitoring Replication with perfmon
          2. Monitoring Replication with the Replication Monitor
          3. Tuning for Snapshot Replication
            1. Monitoring the Snapshot System
            2. Tuning the Snapshot System
          4. Tuning the Distributor
            1. Configuring the Distributor
            2. Tuning the Distributor
          5. Tuning for Transactional Replication
            1. Attributes of Transactional Replication
            2. Configuring for Transactional Replication
              1. Configure Sufficient I/O
              2. Configure the I/O Subsystem on the Publisher
                1. Configuring the I/O Subsystem on the Distributor
                2. Configuring the I/O Subsystem on the Subscriber
              3. Increase the Commit Batch Size on the Distributor
            3. Tune the Log Reader
            4. Monitoring the Transactional Replication System
            5. Tuning the Transactional Replication System
            6. Tuning Transactional Replication with Updating Subscriptions
          6. Monitoring and Tuning the Merge Replication System
            1. Attributes of Merge Replication
            2. Configuring for Merge Replication
              1. Configure Sufficient I/O
                1. Configuring the I/O Subsystem on the Publisher
                2. Configuring the I/O Subsystem on the Distributor
                3. Configuring the I/O Subsystem on the Subscriber
              2. Configure the Merge Batch Size
          7. Monitoring the Merge Replication System
          8. Tuning the Merge Replication System
        12. Summary
      2. 21. Integration Services
        1. What Is Integration Services?
          1. Integration Services Versus Data Transformation Services
            1. Some Important Enhancements
            2. Migration from DTS to Integration Services
          2. Integration Services Fundamentals
          3. Integration Services Components Overview
        2. Designing Packages
          1. The Development Environment
            1. Starting an Integration Services Project
            2. Using Data Sources
            3. Using Data Source Views
            4. Using the SQL Server Import and Export Wizard
            5. Adding Documentation to a Package
            6. Executing a Package in Visual Studio
          2. Control Flow Components
            1. Tasks
            2. Containers
            3. Precedence Constraints
          3. Connection Managers
          4. Data Flow Components
            1. Data Sources and Data Destinations
            2. Transformations
            3. Error Output Configuration
          5. Debugging Tools
            1. Control Flow Breakpoints
            2. Data Flow Data Viewers
          6. Logging
          7. Advanced Integration Services Features
            1. Variables
            2. Event Handlers
            3. Checkpoints
        3. Deploying Packages
          1. Package Configuration
          2. Package Deployment
          3. Package Security
            1. Protecting Data
            2. Using Digital Signatures
            3. Protecting Packages
          4. Package Execution
          5. Package Management
          6. Monitoring Packages
        4. Summary
      3. 22. Analysis Services
        1. What Is Analysis Services?
          1. Analysis Services 2005 Versus Analysis Services 2000
            1. A Selective Look at New Features
            2. Migration from Analysis Services 2000
          2. Analysis Services Fundamentals
          3. Integration with SQL Server 2005 Components
          4. Analysis Services Components Overview
        2. Designing Analysis Services Projects
          1. Data Preparation
          2. Starting an Analysis Services Project
            1. Using Data Sources
            2. Using Data Source Views
          3. Dimension Design
            1. Using the Dimension Wizard
            2. Using the Dimension Browser
            3. Building a Server Time Dimension
          4. Cube Design
            1. Using the Cube Wizard
            2. Using the Cube Browser
            3. Reviewing Advanced Cube Design Features
        3. Managing Analysis Services
          1. Analysis Server Configuration
          2. Deployment Options
            1. Using the Analysis Services Deployment Wizard
            2. Using the Synchronize Database Wizard
            3. Backing Up and Restoring a Database
          3. Partitions
            1. Creating a Partition
            2. Specifying a Storage Mode
            3. Designing Aggregations
          4. Processing Data
            1. Processing Options
            2. Proactive Caching
          5. Security Management
          6. Performance Management
          7. SQL Server Profiler
          8. Performance Counters
        4. Summary
      4. 23. Reporting Services
        1. What Is Reporting Services?
          1. Reporting Services 2005 Versus Reporting Services 2000
            1. Reporting Services 2005 Enhancements
            2. Migration from Reporting Services 2000
          2. Reporting Services Fundamentals
          3. Reporting Services Components Overview
        2. Authoring Reports
          1. Enterprise Reports
            1. Starting a Reporting Services Project
            2. Defining Queries
              1. Creating a Data Source
              2. Creating a Dataset
            3. Organizing Data
            4. Adding Report Enhancements
          2. Ad Hoc Reports
            1. Report Model Projects
            2. Report Builder
        3. Managing Reporting Services
          1. Report Server Configuration
            1. Reporting Services Configuration Tool
            2. Reporting Services Configuration Files
          2. Content Management
            1. Publishing Content
            2. Organizing Content
            3. Working with Report Properties
              1. General
              2. Parameters
              3. Data Sources
              4. Execution
          3. Security Management
            1. Adding an Item Role Assignment
            2. Adding a System Role Assignment
          4. Performance Management
            1. Performance Counters
            2. Server Resource Management
            3. Reporting Services Databases
        4. Summary
      5. 24. Notification Services and Service Broker
        1. What Is Notification Services?
          1. Notification Services 2005 Versus Notification Services 2.0
          2. Notification Services Fundamentals
          3. Notification Services Components Overview
        2. Developing Notification Services Applications
          1. Creating an Instance Configuration File
            1. Starting a New Instance Configuration File
            2. Adding an Instance Name
            3. Defining the Host SQL Server Instance
            4. Defining the Instance Database
            5. Defining Hosted Applications
            6. Defining Delivery Channels
            7. Adding Parameter Default Values
            8. Creating an Instance Configuration File by Using Visual Studio
          2. Creating an Application Definition File
            1. Starting a New Application Definition File
            2. Adding an Event Class
            3. Adding a Subscription Class
            4. Adding a Notification Class
            5. Adding an Event Provider
            6. Adding a Generator
            7. Adding a Distributor
            8. Configuring Application Execution Settings
            9. Creating an Application Definition File by Using Visual Studio
          3. Creating an XSLT File
        3. Using Notification Services Applications
          1. Deploying a Notification Services Application
            1. Installing a Notification Services Engine
            2. Creating a Windows Service Account for a Notification Services Instance
            3. Granting Permissions to a Notification Services Instance
            4. Starting a Notification Services Instance
          2. Testing a Notification Services Application
            1. Adding Subscribers
          3. Adding Subscriptions
          4. Submitting Events
          5. Viewing Notifications
        4. What Is Service Broker?
          1. Service Broker Fundamentals
          2. Service Broker Components Overview
        5. Implementing Service Broker Applications
          1. Creating Service Broker Objects
            1. Creating Message Types
            2. Creating a Contract
            3. Creating a Queue
            4. Creating a Service
          2. Managing Conversations
            1. Starting a Conversation
            2. Sending a Message
            3. Receiving a Message
            4. Ending Conversations
        6. Managing Service Broker Applications
          1. Stopping a Service Broker Application
          2. Starting a Service Broker Application
          3. Backing Up and Restoring a Service Broker Application
          4. Querying a Queue
        7. Summary
    10. VI. High Availability
      1. 25. Disaster Recovery Solutions
        1. What Are High Availability and Disaster Recovery?
        2. Fundamentals of Disaster Recovery and Disaster Survival
        3. Microsoft SQL Server Disaster Recovery Solutions
          1. Using Database Backups for Disaster Recovery
          2. Log Shipping
          3. Database Mirroring
          4. Replication
          5. SQL Server Clusters
          6. Overview of High Availability and Disaster Recovery Technologies
        4. Summary
      2. 26. Failover Clustering Installation and Configuration
        1. What Is a Cluster?
        2. Clustering Concepts
        3. Overview of MSCS
          1. Basic Concepts
          2. Cluster Components
            1. MSCS Cluster Management Software
            2. Server Interconnect
            3. Shared Disk System
              1. Internal RAID
              2. External RAID
          3. Cluster Application Types
          4. MSCS Modes
            1. Active/Passive Clusters
            2. Active/Active Clusters
        4. Examples of Clustered Systems
          1. Example 1—High-Availability System with Static Load Balancing
          2. Example 2—Hot Spare System with Maximum Availability
          3. Example 3—Partial Server Cluster
          4. Example 4—Virtual Server Only, with No Failover
        5. Planning Your Configuration
          1. Setting the Recovery Time
          2. Configuring SQL Server for Active/Passive Clusters
          3. Configuring SQL Server for Active/Active Clusters
        6. Installing and Configuring Windows 2003 and SQL Server 2005 Clustering
          1. Creating the Windows Cluster
          2. Creating the SQL Server Cluster
          3. Additional Steps
            1. Modifying the Cluster Configuration
            2. Upgrading the Cluster
            3. Create an Active/Active Cluster
          4. Using a Three-Tier Application
        7. Summary
      3. 27. Log Shipping and Database Mirroring
        1. Types of Data Loss
        2. Log Shipping
          1. Configuring Security for Log Shipping and Database Mirroring
            1. Surface Area Configuration Process
            2. SQL Server Service Account Configuration
          2. Configuring Log Shipping
          3. Monitoring Log Shipping
            1. Running the Transaction Log Shipping Status report
            2. SQL Server Agent Job History
            3. Checking the SQL Server Log
            4. Metadata: Transact-SQL for Database Mirroring Information
          4. Log Shipping Failover
          5. Removing Log Shipping
          6. Tuning Log Shipping: Operations and Considerations
            1. Hardware Considerations
            2. Network Considerations
            3. How Often Should the Transaction Log Be Backed Up?
            4. Log Shipping Job Configuration
          7. Practical Log Shipping Advice
            1. Script Log Shipping
            2. Using Mirroring and Log Shipping Together
            3. Test Failing Over
            4. Backups and Log Shipping
            5. What You Will Not Be Protected From
            6. Backup Log Frequency
            7. Log Shipping Summary
        3. Database Mirroring
          1. Configuring Database Mirroring
          2. Planning and Considerations for Database Mirroring
            1. Major Parts in a Mirror Pair
            2. Operating Modes
            3. Synchronous and Asynchronous Explained
            4. SQL Server Database Mirroring Version Support
          3. Tuning Database Mirroring
            1. Failing Over with Database Mirroring
            2. Highly Available
            3. High Protection
            4. High Performance
            5. Failover Scenario: High Performance
            6. Failover Scenario: High Protection
            7. Failovers: Users, Logins
          4. Configuring Database Mirroring
          5. Monitoring Database Mirroring
            1. SQL Server Management Studio
            2. SQL Server errorlog
            3. Metadata: T-SQL for Database Mirroring Information
            4. Performance Monitor
            5. SQL Server Profiler
            6. Database Mirroring Monitor
          6. Using Mirroring and Snapshots for Reporting Servers
        4. Summary
    11. VII. Performance Tuning and Troubleshooting
      1. 28. Troubleshooting, Problem Solving, and Tuning Methodologies
        1. Troubleshooting and Problem Solving
          1. The Problem Solving Attitude
            1. Don’t Give Up or Get Discouraged
            2. Strive for the Achievable
            3. Success Comes from Enthusiasm
            4. Stay Focused
            5. Take a Break
            6. Ask For Help
            7. Learn Something New
          2. Troubleshooting Techniques
            1. Splitting the Problem
              1. Example 1: Is network an issue?
              2. Example 2: Is I/O an issue?
              3. Example 3: Is blocking an issue?
              4. Splitting the Problem Summary
            2. Finding the Error Logs
            3. Interpreting Error Logs
            4. Help Desk Details
            5. Retracing Your Steps
            6. Test for the Sake of the Problem
          3. The Search for Knowledge
            1. Finding Knowledge Bases
            2. Developing Your Own Knowledge Base
            3. Learn from Others (Find a Mentor)
        2. Performance Tuning and Optimization
          1. Tuning and Optimization Basics
            1. Tuning the Application
            2. Tuning the Instance
        3. Troubleshooting and Tuning Methodology
          1. Developing a Methodology
            1. Step 1: Initial Assessment
            2. Step 2: Monitor the System
            3. Step 3: Analyze Results
            4. Step 4: Create a Hypothesis
            5. Step 5: Propose Solution
            6. Step 6: Implement Change
            7. Step 7: Test Solution
            8. Step 8: Go to Step 2
        4. The Need for Documentation
        5. Summary
      2. 29. Database System Tuning
        1. Monitoring and Tuning Hardware
          1. Tools for Monitoring and Tuning Hardware
            1. System Monitor (PERFMON.EXE)
            2. Performance Logs and Alerts
          2. Determining Hardware Bottlenecks
            1. Processor Subsystem
            2. Memory Subsystem
            3. I/O Subsystem
        2. Monitoring and Tuning SQL Server
          1. Tools for Monitoring and Tuning SQL Server
            1. System Monitor
            2. SQL Server Profiler
            3. SQL Trace
            4. Dynamic Management Views and Functions
          2. Determining SQL Server Performance Bottlenecks
            1. Determining Processor Bottlenecks
              1. Excessive Recompilations
              2. Inefficient Query Plans
              3. Intra-Query Parallelism
            2. Determining Memory Bottlenecks
            3. Determing I/O Subsystem Bottlenecks
            4. Determing tempdb System Database Bottlenecks
          3. Tuning Microsoft SQL Server Configuration Options
            1. The Affinity I/O Mask (Affinity64 I/O Mask) Option
            2. The Affinity Mask (Affinity64 Mask) Option
            3. The Cost Threshold for Parallelism Option
            4. The Lightweight Pooling Option
            5. The Locks Option
            6. The Max Server Memory Option
            7. The Max Degree of Parallelism Option
            8. The Max Worker Threads Option
            9. The Min Memory Per Query Option
            10. The Min Server Memory Option
            11. The Open Objects Option
            12. The Priority Boost Option
            13. The Query Governor Cost Limit Option
            14. The Recovery Internal Option
            15. The Set Working Set Size Option
        3. Tuning the Database Layout
          1. Database Layout
            1. Files
            2. Filegroups
          2. Database Options
            1. AUTO_UPDATE_STATISTICS_ASYNC Database Option
            2. DATE_CORRELATION_OPTIMIZATION Database Option
            3. PARAMETERIZATION Database Option
            4. READ_ONLY Database Option
        4. Tuning the tempdb System Database
        5. Summary
      3. 30. Using Profiler, Management Studio, and Database Engine Tuning Advisor
        1. Overview of SQL Server Tools
          1. Performance Tools
          2. Configuration Tools
          3. External Tools
            1. Microsoft Security Baseline Analyzer
            2. Microsoft SQL Server Best Practices Analyzer
            3. Microsoft SQL Server Management Pack for MOM 2005
            4. Microsoft SQL Server 2005 Upgrade Advisor
        2. Using SQL Server Management Studio
          1. SQL Server Management Studio Environment
          2. Using Object Explorer
          3. Using the Summary Report Pane
          4. Analysing SQL Server Logs
          5. Viewing Current Activity
            1. Using the Activity Monitor
            2. Using System Stored Procedures
            3. Using the sys.dm_tran_locks DMV
          6. Generating SQL Server Agent Alerts
          7. Executing T-SQL Statements
          8. Viewing Execution Plans
        3. Using SQL Server Profiler
          1. Capturing a SQL Server Profile Trace
            1. Correlating a SQL Profiler Trace with Performance Log Data
        4. Using the Database Engine Tuning
        5. Summary
      4. 31. Dynamic Management Views
        1. Understanding Dynamic Management Views
        2. Using Dynamic Management Views
          1. Common Language Runtime–Related DMVs
            1. sys.dm_clr_appdomains
            2. sys.dm_clr_loaded_assemblies
            3. sys.dm_clr_properties
            4. sys.dm_clr_tasks
          2. Database-Related DMVs
            1. sys.dm_db_file_space_usage
            2. sys.dm_db_session_space_usage
            3. sys.dm_db_partition_stats
            4. sys.dm_db_task_space_usage
          3. Database Mirroring-Related DMV
            1. sys.dm_db_mirroring_connections
          4. Execution-Related DMVs and Functions
            1. sys.dm_exec_background_job_queue
            2. sys.dm_exec_background_job_queue_stats
            3. sys.dm_exec_cached_plans
            4. sys.dm_exec_connections
            5. sys.dm_exec_cursors
            6. sys.dm_exec_plan_attributes
            7. sys.dm_exec_query_memory_grants
            8. sys.dm_exec_query_optimizer_info
            9. sys.dm_exec_query_plan
            10. sys.dm_exec_query_resource_semaphores
            11. sys.dm_exec_query_stats
            12. sys.dm_exec_requests
            13. sys.dm_exec_sessions
            14. sys.dm_exec_sql_text
          5. Full-Text Search–Related DMVs
            1. sys.dm_fts_active_catalogs
            2. sys.dm_fts_index_population
            3. sys.dm_fts_memory_buffers
            4. sys.dm_fts_memory_pools
            5. sys.dm_fts_population_ranges
          6. Input/Output Related DMVs and Functions
            1. sys.dm_io_backup_tapes
            2. sys.dm_io_cluster_shared_drives
            3. sys.dm_io_pending_io_requests
            4. sys.dm_io_virtual_file_stats
          7. Index Related DMVs and Functions
            1. sys.dm_db_index_operational_stats
            2. sys.dm_db_index_physical_stats
            3. sys.dm_db_index_usage_stats
            4. sys.dm_db_missing_index_columns
            5. sys.dm_db_missing_index_details
            6. sys.dm_db_missing_index_group_stats
            7. sys.dm_db_missing_index_groups
          8. Query Notifications-Related DMVs
            1. sys.dm_qn_subscriptions
          9. Replication-Related DMVs
            1. sys.dm_repl_articles
            2. sys.dm_repl_schemas
            3. sys.dm_repl_tranhash
            4. sys.dm_repl_traninfo
          10. Service Broker-Related DMVs
            1. sys.dm_broker_activated_tasks
            2. sys.dm_broker_connections
            3. sys.dm_broker_forwarded_messages
            4. sys.dm_broker_queue_monitors
          11. SQL Server Operating System-Related DMVs
            1. sys.dm_os_buffer_descriptors
            2. sys.dm_os_child_instances
            3. sys.dm_os_cluster_nodes
            4. sys.dm_os_hosts
            5. sys.dm_os_latch_stats
            6. sys.dm_os_loaded_modules
            7. sys.dm_os_memory_cache_clock_hands
            8. sys.dm_os_memory_cache_counters
            9. sys.dm_os_memory_cache_entries
            10. sys.dm_os_memory_cache_hash_tables
            11. sys.dm_os_memory_clerks
            12. sys.dm_os_memory_objects
            13. sys.dm_os_memory_pools
            14. sys.dm_os_performance_counters
            15. sys.dm_os_schedulers
            16. sys.dm_os_stacks
            17. sys.dm_os_sys_info
            18. sys.dm_os_tasks
            19. sys.dm_os_threads
            20. sys.dm_os_virtual_address_dump
            21. sys.dm_os_wait_stats
            22. sys.dm_os_waiting_tasks
            23. sys.dm_os_workers
          12. Transaction-Related DMVs and Functions
            1. sys.dm_tran_active_snapshot_database_transactions
            2. sys.dm_tran_active_transactions
            3. sys.dm_tran_current_snapshot
            4. sys.dm_tran_current_transaction
            5. sys.dm_tran_database_transactions
            6. sys.dm_tran_locks
            7. sys.dm_tran_session_transactions
            8. sys.dm_tran_top_version_generators
            9. sys.dm_tran_transactions_snapshot
            10. sys.dm_tran_version_store
        3. Creating a Performance Data Warehouse
        4. Summary
      5. 32. Microsoft SQL Server 2005 Scalability Options
        1. Scalability Options
        2. Scaling Up
          1. Processor Subsystem
            1. Multiple Processors
            2. Multicore Processors
            3. Hyperthreading
          2. Memory Subsystem
          3. I/O Subsystems
        3. Scaling Out
          1. Multiple SQL Server Instances
          2. Clustering
            1. Multiinstance Cluster
            2. N+1 Cluster
            3. N+M Cluster
          3. Database Mirroring
          4. Log Shipping
          5. Replication
            1. Merge Replication
            2. Transactional Replication
            3. Peer-to-Peer Transactional Replication
          6. Shared Scalable Databases
        4. Summary
      6. 33. Tuning Queries Using Hints and Plan Guides
        1. Understanding the Need for Hints
        2. Microsoft SQL Server 2005 Hints
          1. Join Hints
          2. Query Hints
          3. Table Hints
        3. Plan Guides
          1. Creating and Administering Plan Guides
            1. sp_create_plan_guide
            2. sp_control_plan_guide
          2. Creating Template-Based Plan Guides
          3. Best Practices
          4. Verifying Plan Guides Usage
          5. Example Usage Scenarios for Plan Guides
        4. Summary
    12. Glossary
    13. A. About the Authors
    14. Index
    15. SPECIAL OFFER: Upgrade this ebook with O’Reilly

Product information

  • Title: Microsoft® SQL Server™ 2005 Administrator's Companion
  • Author(s): Edward Whalen, Marcilina Garcia, Burzin Patel, Stacia Misner, Victor Isakov
  • Release date: November 2006
  • Publisher(s): Microsoft Press
  • ISBN: 9780735621985