You are previewing Microsoft SQL Server 2008 R2 Administration Cookbook.
O'Reilly logo
Microsoft SQL Server 2008 R2 Administration Cookbook

Book Description

Over 70 practical recipes for administering a high-performance SQL Server 2008 R2 system

  • Provides Advanced Administration techniques for SQL Server 2008 R2 as a book or eBook

  • Covers the essential Manageability, Programmability, and Security features

  • Emphasizes important High Availability features and implementation

  • Explains how to maintain and manage the SQL Server data platform effectively

In Detail

Microsoft SQL Server is a powerful relational database engine, widely used to store and maintain data in Enterprises of various levels – be they small, medium, or large. SQL Server delivers a rich set of integrated services and reporting features that enable you to do more with your data such as query, search, synchronize, report, and analyze. SQL Server 2008 R2 accelerates the capabilities to scale database operations and is a highly scalable data platform.

This practical cookbook will show you the advanced administration techniques for managing and administering a scalable and high-performance SQL Server 2008 R2 system. It contains over 85 practical, task-based, and immediately useable recipes covering a wide range of advanced administration techniques for administering a high-performance SQL Server 2008 R2 system and solving specific administration problems. It shows how the SQL Server 2008 R2 system can be taken further.

Packed with reusable, real-world recipes, the book starts by providing an overview of the server and specialized editions of SQL Server 2008 R2 technologies, features, and solutions. Next it covers database administration and management principles with real-world examples. The book then dives deep into topics such as administration of core Business Intelligence Services, management of core SQL Server 2008 R2 technologies, availability, security, and programmability, implementing new manageability features and practices, and maintenance and monitoring, amongst others.

Administer a scalable and high-performance SQL Server 2008 R2 system using advanced administration techniques

Table of Contents

  1. Microsoft SQL Server 2008 R2 Administration Cookbook
    1. Microsoft SQL Server 2008 R2 Administration Cookbook
    2. Credits
    3. Foreword
    4. About the Author
    5. Acknowledgment
    6. About the Reviewers
    7. www.PacktPub.com
      1. Support files, eBooks, discount offers and more
        1. Why Subscribe?
        2. Free Access for Packt account holders
        3. Instant Updates on New Packt Books
    8. Preface
      1. What this book covers
      2. SQL Server 2008 R2 Prologue
      3. SQL Server 2008 R2 new enhancements
      4. SQL Server 2008 R2 DataCenter
      5. SQL Server 2008 R2 Parallel Data Warehouse
        1. SQL Server 2008 R2 Core Editions Enhancements
      6. What you need for this book
      7. Who this book is for
      8. Conventions
      9. Reader feedback
      10. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    9. 1. Getting Started with SQL Server 2008 R2
      1. Introduction
      2. Adding SQL Server 2008 R2 Service Pack features using Slipstream technology
        1. Getting Ready
        2. How to do it...
        3. How it works...
        4. There's more...
      3. Designing a best practice approach to upgrading to SQL Server 2008 R2
        1. Getting Ready
        2. How to do it...
        3. How it works...
          1. Backward Compatibility—Deprecate & Discontinued features
      4. There's more.
      5. Working with Data-Tier applications
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      6. Designing and adopting SQL Server 2008 R2 solutions
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      7. Designing applications to use federated servers
        1. Getting ready
        2. How to do it...
        3. How it works...
    10. 2. Administrating the Core Database Engine
      1. Introduction
      2. Designing automated administration practices
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See Also
      3. Implementing Security feature enhancements
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. See Also
      4. Implementing Availability feature enhancements
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See Also
      5. Implementing, Monitoring, and Tuning for performance
        1. Getting ready
        2. How to do it...
        3. How it works...
      6. Administering SQL Server workloads with Resource Governor
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
      7. Designing SQL Server Agent scheduled jobs for ETL processes
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more
      8. Troubleshooting multi-server instances with utility administration
        1. Getting ready
        2. How to do it...
        3. How it works...
      9. Administering SQL Server Replication processes
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. See Also
    11. 3. Managing the Core Database Engine
      1. Introduction
      2. Implementing Central Management feature enhancements
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See Also
      3. Designing Multi-server management from SQL Server 2008 R2
        1. Getting ready
        2. How to do it...
        3. How it works...
      4. Managing the Utility Control Point data warehouse database
        1. How to do it...
        2. How it works...
      5. Implementing Utility & Non-utility collection sets
        1. Getting ready
        2. How to do it...
        3. How it works...
      6. Designing and refreshing a Scalable Shared database
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more…
      7. Managing SQL Server Replication processes
        1. How to do it...
        2. How it works…
      8. Implementing security for SQL Server Agent jobs management
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See Also
      9. Multiserver Management Using Utility Explorer
        1. Getting ready
        2. How to do it...
        3. How it works...
    12. 4. Administering Core Business Intelligence Services
      1. Introduction
      2. Preparing and installing SQL Server Analysis Services
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more…
      3. Implementing Scale-Out of SQL Server Analysis Services
        1. Getting ready
        2. How to do it...
        3. How it works...
      4. Administering SQL Server Reporting Services
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See Also
      5. Implementing Scale-Out deployment of SQL Server Reporting Services
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more…
        5. See Also
      6. Preparing and installing SQL Server Integration Services
        1. Getting ready
        2. How to do it...
        3. How it works...
      7. Managing the ETL process efficiently
        1. How to do it...
        2. How it works...
    13. 5. Managing Core SQL Server 2008 R2 Technologies
      1. Introduction
      2. Planning and implementing Self-Service Business Intelligence services
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more…
      3. Implementing Microsoft StreamInsight Technologies Platform
        1. Getting ready
        2. How to do it...
        3. How it works...
      4. Implementing SQL Azure connectivity features
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more…
      5. Installing and configuring a Master Data Services Solution
        1. Getting ready
        2. How to do it...
        3. How it works...
      6. Designing and deploying framework to use Master Data Services
        1. Getting ready
        2. How to do it...
        3. How it works...
    14. 6. Improving Availability and enhancing Programmability
      1. Introduction
      2. Preparing new Failover Cluster features
        1. Getting ready
        2. How to do it...
        3. How it works...
      3. Installing and configuring Failover Cluster Services
        1. Getting ready
        2. How to do it...
        3. How it works...
      4. Recovering and troubleshooting Failover Cluster Failure
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. See also
      5. Implementing Database Mirroring features and performance enhancements
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. See also
      6. Managing Database Mirroring in a Failover Cluster environment
        1. Getting ready
        2. How to do it...
        3. How it works...
      7. Managing Log Shipping scenarios in a Database Mirroring environment
        1. Getting ready
        2. How to do it...
        3. How it works...
      8. Improving Replication scalability at multiple sites
        1. Getting ready
        2. How to do it...
        3. How it works...
      9. Implementing compressed storage features for tables and indexes
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      10. Designing a storage solution for unstructured data and new collations
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      11. Designing data partitioning solutions for tables and indexes
        1. Getting ready
        2. How to do it...
        3. How it works...
      12. Implementing sparse columns and wide table features
        1. Getting ready
        2. How to do it...
        3. How it works...
      13. Designing spatial data storage methods
        1. Getting ready
        2. How to do it...
        3. How it works...
      14. Deploying and managing data-tier applications
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
    15. 7. Implementing New Manageability Features and Practices
      1. Introduction
      2. Implementing auditing and service level security features
        1. Getting ready
        2. How to do it...
        3. How it works...
      3. Managing server-level securable and database-level permissions
        1. Getting ready
        2. How to do it...
        3. How it works...
      4. Implementing backup compression for a VLDB environment
        1. Getting ready
        2. How to do it...
        3. How it works...
      5. Designing change tracking methodology
        1. Getting ready
        2. How to do it...
        3. How it works...
      6. Implementing Policy-Based Management features
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      7. Implementing and running PowerShell for SQL Server features
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      8. Implementing the SQL Server Best Practices Analyzer
        1. Getting ready
        2. How to do it...
        3. How it works...
      9. Designing Transparent Data Encryption features
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
    16. 8. Maintenance and Monitoring
      1. Introduction
      2. Implementing performance baseline practices
        1. Getting ready
        2. How to do it
        3. How it works...
        4. There's more.
          1. See Also
      3. Monitoring resource usage with Extended Events
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more
      4. Implementing Management Data Warehouse features
        1. Getting ready
        2. How to do it…
        3. How it works...
        4. There's more...
      5. Designing maintenance tasks on a mission-critical environment
        1. Getting ready
        2. How to do it...
        3. How it works.
      6. Implementing Piecemeal restore strategies
        1. Getting ready…
          1. How to do it...
          2. How it works...
          3. There's more...
      7. Planning and designing of a Disaster Recovery environment
        1. Getting ready
        2. How to do it...
        3. How it works...
      8. Implementing sustainable index maintenance practices for a VLDB & 24/7 environment
        1. Getting ready
        2. How to do it...
        3. How it works...
      9. Configuring a manageable database consistency and integrity process for multiple databases and a VLDB environment
        1. Getting ready…
        2. How to do it...
        3. How it works...
      10. Configuring a server storage system for scalability
        1. Getting ready…
        2. How to do it...
        3. How it works...
      11. Configuring SQL server storage architecture for quick recovery
        1. Getting ready
        2. How to do it...
        3. How it works...
      12. Managing a storage system for analyzing bottlenecks
        1. Getting ready
        2. How to do it...
        3. How it works...
    17. 9. Troubleshooting
      1. Introduction
      2. Implementing systematized monitoring methods for data platform optimization
        1. Getting ready
        2. How to do it...
        3. How it works...
      3. Designing a filtered indexes and statistics feature
        1. Getting ready
        2. How to do it...
        3. How it works...
      4. Implementing table hints for advanced query tuning performance
        1. How to do it...
        2. How it works...
      5. Implementing query hints for optimized performance
        1. How to do it...
        2. How it works...
      6. Designing sustainable locking methods to improve concurrency
        1. How to do it...
        2. How it works...
      7. Implementing parallel query processing methods
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      8. Implementing the plan guide to enhance compile-time and run-time execution plans
        1. How to do it...
        2. How it works...
        3. There's more...
      9. Configuring and managing storage systems for optimized defragmentation processes
        1. Getting ready
        2. How to do it...
        3. How it works...
      10. Building best usage processes of Dynamic Management Views
        1. How to do it...
        2. How it works...
      11. Implementing a benchmarking framework on multiple instances
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
    18. 10. Learning the Tricks of the Trade
      1. Introduction
      2. Automated administration across an enterprise
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      3. Administrating resource health policies
        1. Getting ready
        2. How to do it...
        3. How it works...
      4. Administrating multiple Analysis Services using Tools
        1. How to do it...
        2. How it works...
      5. Managing events capture to troubleshoot problems
        1. How to do it...
        2. How it works...
        3. See also
    19. A. More DBA Manageability Best Practices