Pro SQL Server 2008 Administration

Pro SQL Server 2008 Administration

by Ken Simmons, Sylvester Carstarphen
Released August 2009
Publisher(s): Apress
ISBN: 9781430223733

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

Pro SQL Server 2008 Administration is critical for database administrators seeking in-depth knowledge on administering SQL Server 2008. This book covers the impact of the new features available in SQL Server 2008 specifically targeted for database administrators, along with the tried-and-true advanced techniques required to support and maintain Microsoft SQL Server.

  • Introduces new administration features of SQL Server 2008

  • Shows how to manage a SQL Server 2008 database at professional level

  • Provides guidance on performance optimization

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright
  4. Contents at a Glance
  5. Contents
  6. About the Authors
  7. About the Technical Reviewer
  8. Acknowledgments
  9. Introduction
    1. Who Should Read This Book
    2. How the Book Is Structured
    3. Downloading the Code
    4. Contacting the Authors
  10. PART 1 Introducing Microsoft SQL Server 2008
    1. CHAPTER 1 New Feature Overview
      1. Scalability Enhancements
        1. Filtered Indexes and Statistics
        2. Table and Query Hints
        3. Query Performance and Processing
      2. Manageability Enhancements
        1. Auditing
        2. Change Data Capture
        3. Change Tracking
        4. Backup Compression
        5. Data Collector
        6. Central Management Servers
        7. Policy-Based Management
        8. Resource Governor
        9. PowerShell
      3. Availability Enhancements
        1. Database Mirroring
        2. Clustering
        3. Peer-to-Peer Replication
        4. Hot-Add CPU
      4. Programmability Enhancements
        1. Variables
        2. Transact-SQL Row Constructors
        3. Table-Valued Parameters
        4. MERGE Statement
        5. GROUPING SETS Operator
      5. Security Enhancements
        1. Transparent Data Encryption
        2. Extensible Key Management
      6. Summary
    2. CHAPTER 2 Pre-Installation Considerations
      1. Choosing a SQL Server Edition
        1. Enterprise Edition
        2. Standard Edition
        3. Developer Edition
        4. Workgroup Edition
        5. Web Edition
        6. Express Edition
      2. Determining Hardware Requirements
        1. Determining CPU Needs
        2. Disk Subsystems
        3. Database Sizing
        4. Determining RAID Levels
        5. Final Disk Storage Configuration
        6. Determining Memory Requirements
        7. Choosing the Server
      3. SQL Server Consolidation
      4. Consolidation with Virtual Servers
      5. Summary
    3. CHAPTER 3 Choosing a High-Availability Solution
      1. What Exactly Is High Availability Anyway?
      2. Failover Clustering
        1. Key Terms
        2. Failover Clustering Overview
        3. Implementation
        4. Pros and Cons of Failover Clustering
      3. Database Mirroring
        1. Key Terms
        2. Database Mirroring Overview
        3. High-Safety Mode
        4. High-Performance Mode
        5. Pros and Cons of Database Mirroring
      4. Copying Data with Log Shipping
        1. Key Terms
        2. Log Shipping Overview
        3. Pros and Cons of Log Shipping
      5. Making Data Available Through Replication
        1. Key Terms
        2. Replication Overview
        3. Snapshot Replication
        4. Transactional Replication
        5. Merge Replication
        6. Pros and Cons of Replication
      6. Other High-Availability Techniques
        1. High Availability Feature Comparison
        2. Summary
  11. PART 2 Getting Started
    1. CHAPTER 4 Installing Microsoft SQL Server 2008
      1. User Accounts
      2. Preparation and Prerequisites
      3. SQL Server Installation Center
        1. Planning
        2. Installation
        3. Maintenance
        4. Tools
        5. Resources
        6. Advanced
        7. Options
      4. Installing Your First Instance
        1. Checking System Configuration
        2. Choosing Your Features
        3. Configuring the Instance
        4. Configuring the Database Engine
        5. Allowing for Error and Usage Reporting
        6. Validating and Installing
      5. Installing More Than One Instance
        1. Preparing to Install Another Instance
        2. Getting Your System Ready
        3. Configuring the Instance
      6. Command-Line Installation
        1. Learning the Parameters
        2. Passing Parameters to the Installer
        3. Running a Command-Line Install
      7. Configuration File Installation
      8. Summary
    2. CHAPTER 5 Upgrading to Microsoft SQL Server 2008
      1. Upgrade Tools
        1. Microsoft SQL Server 2008 Upgrade Advisor
        2. Upgrade Assistant
      2. SQL Server Integration Services
        1. Running DTS in SQL Server 2008
        2. DTS Package Migration Wizard
      3. Upgrade Strategies
        1. In-Place Upgrade
        2. Side-by-Side Upgrade
      4. Post-Upgrade Procedures
        1. Changing Compatibility Level
        2. Checking Object Integrity
        3. Correct Row and Page Counts
        4. Setting the Page Verification Method
        5. Updating Statistics
      5. Summary
    3. CHAPTER 6 Post-Installation
      1. SQL Server Configuration Manager
        1. SQL Server Services
        2. SQL Server Network Configuration
        3. SQL Native Client Configuration
      2. Configuring the Instance
        1. Viewing Advanced Options
        2. Viewing Configuration Settings
        3. Specifying Maximum and Minimum Server Memory
        4. Enabling Address Windows Extensions
        5. Specifying the Backup Compression Default
        6. Enabling Login Failure Auditing
        7. Enabling Dedicated Administrator Connections
        8. Disabling Default Trace
        9. Enabling Use of the CLR (Common Language Runtime)
        10. Choosing Lightweight Pooling
        11. Enabling a Query Governor Cost Limit
        12. xp_cmdshell
        13. Miscellaneous Configuration Options
      3. Preproduction Tasks
        1. Server Tasks
        2. Database Tasks
        3. Maintenance and Monitoring
      4. Summary
  12. PART 3 Administering Microsoft SQL Server 2008
    1. CHAPTER 7 Multi-Server Administration
      1. Policy-Based Management
        1. Manually Creating a Policy
        2. Exporting Current State As Policy
        3. Importing a Predefined Policy
      2. Central Management Servers
        1. Creating a Central Management Server
        2. Running Multi-Server Queries
        3. Configuring Multi-Server Query Options
        4. Evaluating Policies
      3. SQL Dependency Reporting
      4. Summary
    2. CHAPTER 8 Managing Security Within the Database Engine
      1. Security Language
        1. Principals
        2. Securables
        3. Schemas
      2. Creating SQL Server Principals
        1. Creating Logins for SQL Server
        2. Creating SQL Server Logins for Windows Principals
        3. Creating SQL Server–Authenticated Logins
        4. Associating Logins with Certificates and Asymmetric Keys
        5. Linking Credentials to Logins
        6. SQL Server–Level Roles
      3. Database Security
        1. Creating Database Users
        2. Database Roles
        3. Application Roles
      4. Securables
        1. Managing Server Securables
        2. Managing Database Securables
        3. Understanding Schema Securables
      5. Permissions
        1. Types of Permissions
        2. Permission Grouping
        3. Managing Permissions
      6. GUI Security Administration
        1. Creating a User
        2. Creating Roles
        3. Creating Schemas
      7. Encryption
        1. Encrypting Data Using Certificate Encryption
        2. Encrypting Data Using Asymmetric Keys
        3. Encrypting Data Using Symmetric Keys
        4. Extensible Key Management
        5. Transparent Data Encryption
      8. Summary
    3. CHAPTER 9 Administering Database Objects
      1. Database 101
      2. Working with System Databases
      3. Working with Database Snapshots
        1. Creating a Database Snapshot
        2. Viewing and Querying a Snapshot
        3. Reverting a Database to a Database Snapshot
      4. Working with Tables
        1. Default Constraints
        2. Primary Key Constraints
        3. Unique Constraints
        4. Foreign Key Constraints
        5. Check Constraints
        6. Sparse Columns
        7. Compression
        8. Partitions
        9. Temporary Tables
      5. Working with Views
        1. Partitioned Views
        2. Updateable Views
        3. Indexed Views
      6. Working with Synonyms
      7. Working with Stored Procedures
      8. Working with Functions
        1. Scalar-Valued Functions
        2. Table-Valued Functions
      9. Working with Triggers
        1. DML Triggers
        2. DDL Triggers
        3. Logon Triggers
      10. Working with the Common Language Runtime
      11. Summary
    4. CHAPTER 10 Indexing for Performance
      1. Index Vocabulary, Structure, and Concepts
        1. Heaps
        2. Clustered Indexes
        3. Nonclustered Indexes
        4. Structure of Indexes and the Heap
        5. Indexes Created by Constraints
        6. Other Ways to Categorize Indexes
        7. Other Index Concepts and Terminology
      2. Creating Indexes via T-SQL
        1. Creating Clustered and Nonclustered Indexes
        2. Creating Unique and Primary Key Indexes
        3. Creating Filtered Indexes
        4. Creating XML Indexes
      3. Creating Indexes Using the GUI
        1. Creating an Example Table
        2. Creating a Clustered Index via the GUI
        3. Creating a Nonclustered Index via the GUI
      4. Post-Index Creation
        1. Performance of Insert, Update, and Delete Statements
        2. Useful Dynamic Management Views
      5. Putting It All Together
        1. Setting Up the Scenario
        2. Table Scans
        3. Clustered Index Seeks
        4. Covering Your Queries
        5. Indexing JOIN Criteria
        6. Filtered Indexes
      6. Summary
    5. CHAPTER 11 Managing Backups
      1. Recovery Models
      2. Backup Architecture
      3. Backup Types
        1. Full Backups
        2. Differential Backups
        3. Transaction Log Backups
        4. Partial Backups
        5. File Backups
        6. Copy-Only Backups
      4. Backup Compression
      5. Logical Backup Devices
      6. The GUI Backup Interface
      7. Backups from T-SQL
      8. Using Encryption
      9. Backing Up the System Databases
      10. Backup History
      11. Summary
    6. CHAPTER 12 Restore and Recovery Strategies
      1. Restore Vocabulary
      2. The Importance of Recovery Models
      3. Practicing Your Recovery Strategy
      4. Types of Restores
        1. Restoring Full Backups
        2. Restoring Transaction Logs
        3. Restoring Differential Backups
        4. Restoring Files and Filegroups
        5. Restoring Pages
        6. Piecemeal Restores
        7. Restoring Snapshots
        8. RESTORE HEADERONLY
        9. RESTORE FILELISTONLY
        10. RESTORE VERIFYONLY
      5. Online Recoveries
      6. Restore Considerations
        1. Utilizing Filegroups for Faster Restores
        2. Preparing for Hardware Failures
        3. Recovering from Scratch
      7. Recovery Examples
        1. Example 1: The Piecemeal Restore
        2. Backing Up the Tail End of the Transaction Log
        3. Example 2: Restoring a Compressed and Encrypted Database
        4. Example 3: Restoring System Databases
      8. Summary
    7. CHAPTER 13 Automating Routine Maintenance
      1. Database Mail
        1. Database Mail Configuration Wizard
        2. Configuring Database Mail Using T-SQL
        3. Sending Database Mail
        4. Database Mail Cleanup Procedures
      2. SQL Server Agent
        1. Operators
        2. Enabling SQL Server Agent Notifications
        3. Alerts
        4. Jobs
        5. Proxies
      3. Maintenance Plans
        1. Maintenance Plan Wizard
        2. Maintenance Plan Design Tab
        3. Maintenance Plan Tasks
        4. Exporting and Importing Maintenance Plans
      4. Summary
  13. PART 4 Troubleshooting and Tuning
    1. CHAPTER 14 Monitoring Your Server
      1. Baseline Your Server
      2. Familiarizing Yourself with the Performance Monitor
      3. Monitoring Your CPU
        1. Windows Counters That Indicate CPU Utilization
        2. SQL Server Counters That Impact CPU
        3. DMVs for Monitoring CPU
      4. Monitoring Your Memory
        1. Memory Usage in SQL Server
        2. Counters to Watch
        3. Memory with Windows Counters
        4. Memory with SQL Server Counters
        5. Memory with DMVs and DBCC Commands
      5. Monitoring Disk IO
        1. Disk IO with Windows Counters
        2. Disk IO with SQL Server Counters
      6. Monitoring Miscellaneous Counters and DMVs
      7. Capturing SQL Server PerfMon Counters Using DMVs
      8. Leveraging the SQL Server Profiler
      9. Using Server-Side Tracing
      10. Automating Your Monitoring
      11. Summary
    2. CHAPTER 15 Auditing SQL Server
      1. Choosing Your Audit Type
      2. Creating SQL Server Audits with T-SQL
      3. Creating Server Audit Specifications
        1. Server-Level Action Groups
        2. Testing Your Server Audit Specification
      4. Creating Database Audit Specifications
        1. Database-Level Audit Action Groups
        2. Database-Level Audit Actions
        3. Testing Your Database Audit Specification
      5. Creating SQL Server Audits Using the GUI
        1. Creating a Database Audit Specification Using the GUI
        2. Reviewing Audit Files Using SQL Server Management Studio
      6. Audit-Related Startup and Shutdown Problems
        1. Failure to Start
        2. Forced Shutdowns
      7. Useful SQL Server Audit Objects
      8. Summary
    3. CHAPTER 16 Managing Query Performance
      1. Correlating Profiler and the Performance Monitor
      2. Finding Similar Queries
      3. Running the Standard Performance Reports
      4. Optimizing for Specific Parameter Values
      5. Forcing Index Seeks
      6. Forcing a Specific Execution Plan
      7. Adding Hints Through Plan Guides
        1. Creating a Plan Guide
        2. Validating a Plan Guide
      8. Managing Resources with the Resource Governor
        1. Resource Pools
        2. Workload Groups
        3. Classifier Function
      9. Monitoring Resource Governor
        1. Performance Counters
        2. Trace Events
        3. Views
      10. Summary
  14. PART 5 Conclusion
    1. CHAPTER 17 Secrets to Excelling As a Professional DBA
      1. Be Good at What You Do
        1. Face Reality
        2. Build Your Reputation
      2. Go Above and Beyond
      3. Communicate Effectively
        1. Talk to Your Target Audience
        2. Know When They Don't Care
        3. Be a Good Listener
      4. Remain Calm Under Pressure
        1. Prepare for Pressure Situations
        2. Deal with Pressure Situations
        3. Wrap Up the Pressure Situation
      5. Be an Effective Leader
        1. Make Tough Decisions
        2. Make Others Around You Better
      6. Summary
    2. CHAPTER 18 What's Next?
      1. Ongoing Experimentation and Learning
      2. Podcasts
      3. Free Training Events
      4. SQL Support Options
        1. Free Support
        2. Paid Support
        3. Advisory Services
      5. Web Sites Dedicated to SQL Server
      6. Apress Titles for Specialized Topics
      7. Summary
  15. Index

Product information

  • Title: Pro SQL Server 2008 Administration
  • Author(s): Ken Simmons, Sylvester Carstarphen
  • Release date: August 2009
  • Publisher(s): Apress
  • ISBN: 9781430223733