Professional Microsoft® SQL Server® 2008 Administration

Book description

SQL Server 2008 represents a sizable jump forward in scalability, performance, and usability for the DBA, developer, and business intelligence (BI) developer. It is no longer unheard of to have 20-terabyte databases running on a SQL Server. SQL Server administration used to just be the job of a database administrator (DBA), but as SQL Server proliferates throughout smaller companies, many developers have begun to act as administrators as well. Additionally, some of the new features in SQL Server are more developer-centric, and poor configuration of these features can result in poor performance. SQL Server now enables you to manage the policies on hundreds of SQL Servers in your environment as if you were managing a single instance. We've provided a comprehensive, tutorial-based book to get you over the learning curve of how to configure and administer SQL Server 2008.

Whether you're an administrator or developer using SQL Server, you can't avoid wearing a DBA hat at some point. Developers often have SQL Server on their own workstations and must provide guidance to the administrator about how they'd like the production configured. Oftentimes, they're responsible for creating the database tables and indexes. Administrators or DBAs support the production servers and often inherit the database from the developer.

This book is intended for developers, DBAs, and casual users who hope to administer or may already be administering a SQL Server 2008 system and its business intelligence features, such as Integration Services. This book is a professional book, meaning the authors assume that you know the basics about how to query a SQL Server and have some rudimentary concepts of SQL Server already. For example, this book does not show you how to create a database or walk you through the installation of SQL Server using the wizard. Instead, the author of the installation chapter may provide insight into how to use some of the more advanced concepts of the installation. Although this book does not cover how to query a SQL Server database, it does cover how to tune the queries you've already written.

The first ten chapters of the book are about administering the various areas of SQL Server, including the developer and business intelligence features. Chapter 1 briefly covers the architecture of SQL Server and the changing role of the DBA. Chapters 2 and 3 dive into best practices on installing and upgrading to SQL Server 2008. Managing your SQL Server database instance is talked about in Chapter 4. This chapter also describes some of the hidden tools you may not even know you have.

Once you know how to manage your SQL Server, you can learn in Chapter 5 how to automate many of the redundant monitoring and maintenance tasks. This chapter also discusses best practices on configuring SQL Server Agent. Chapters 6 and 7 cover how to properly administer and automate many tasks inside of the Microsoft business intelligence products, such as Integration Services and Analysis Services. Developers will find that Chapter 8 is very useful, as it covers how to administer the development features, such as SQL CLR. Chapter 9 explains how to secure your SQL Server from many common threats and how to create logins and users. Chapter 10 covers how to create a SQL Server project and do proper change management in promoting your scripts through the various environments. It also covers the Policy-Based Management framework in SQL Server.

Chapters 11 through 15 make up the performance tuning part of the book. Chapter 11 discusses how to choose the right hardware configuration for your SQL Server in order to achieve optimal performance. After the hardware and operating system is configured, Chapter 12 shows you how to optimize your SQL Server instance for the best performance. Chapter 13 describes how to monitor your SQL Server instance for problematic issues such as blocking and locking. Chapters 14 and 15 discuss how to optimize the T-SQL that accesses your tables and then how to index your tables appropriately.

Chapters 16 through 20 consist of the high-availability chapters of the book. Chapter 16 covers how to use the various forms of replication, while database mirroring is covered in Chapter 17. Classic issues and best practices with backing up and recovering your database are discussed in Chapter 18. Chapter 19 dives deeply into the role of log shipping in your high-availability strategy, and Chapter 20 presents a step-by-step guide to clustering your SQL Server and Windows 2008 server.

This edition of the book covers all the same great information we covered in the last book, and we've added loads of new content for SQL Server 2008, which adds numerous new features to improve the DBA's life. In short, the new version of SQL Server focuses on improving your efficiency, the scale of your server, and the performance of your environment, so you can do more in much less time, and with fewer resources and people. This means you can manage many servers at one time using Policy-Based Management, scale your I/O load using compression, and collect valuable information about your environment using data collectors, to name just a few key new features.

To follow the examples in this book, you will need to have SQL Server 2008 installed. If you wish to learn how to administer the business intelligence features, you need to have Analysis Services and the Integration Services components installed. You need a machine that can support the minimum hardware requirements to run SQL Server 2008; and you also need the AdventureWorks2008 and AdventureWorksDW2008 databases installed. Instructions for accessing these databases can be found in the ReadMe file on this book's Web site.

Some features in this book (especially in the high-availability part) require the Enterprise or Developer Edition of SQL Server. If you do not have this edition, you will still be able to follow through some of the examples in the chapter with Standard Edition.

Table of contents

  1. Copyright
  2. About the Authors
  3. Credits
  4. Acknowledgments
  5. Introduction
  6. SQL Server 2008 Architecture
    1. The Expanding Role of a DBA
    2. SQL Server Architecture
    3. Editions of SQL Server
    4. Summary
  7. SQL Server 2008 Installation Best Practices
    1. Planning the System
    2. Installing SQL Server
    3. Installing Analysis Services
    4. Burning in the System
    5. Post-Install Configuration
    6. Uninstalling SQL Server
    7. Common Installation Issues
    8. Troubleshooting a Failed Install
    9. Summary
  8. Upgrading SQL Server 2008 Best Practices
    1. Why Upgrade to SQL Server 2008?
    2. Upgrading to SQL Server 2008
    3. Pre-Upgrade Checks
    4. Backward Compatibility
    5. SQL Server Component Considerations
    6. Post-Upgrade Checks
    7. Summary
  9. Managing and Troubleshooting the Database Engine
    1. Configuration Tools
    2. Management Studio
    3. Trace Flags
    4. Getting Help from Support
    5. Summary
  10. Automating SQL Server
    1. Maintenance Plans
    2. SQL Server Agent
    3. Summary
  11. Integration Services Administration and Performance Tuning
    1. A Tour of Integration Services
    2. Administration of the Integration Services Service
    3. Administration of Integration Services Packages
    4. Applying Security to Integration Services
    5. Summary
  12. Analysis Services Administration and Performance Tuning
    1. Tour of Analysis Services
    2. Administering Analysis Services Server
    3. Administering Analysis Services Databases
    4. Analysis Services Performance Monitoring and Tuning
    5. Management of Analysis Services Storage
    6. Applying Security to Analysis Services
    7. Summary
  13. Administering the Development Features
    1. Service Broker
    2. Security Considerations for Service Broker
    3. Conversation Priorities
    4. Administering Service Broker
    5. Introduction to CLR Integration
    6. Summary
  14. Securing the Database Engine
    1. Security Principles
    2. Creating a Secure Configuration
    3. Surface Area Configuration
    4. Endpoints
    5. Identity and Access Control
    6. Schemas
    7. Securables and Object Permissions
    8. Encryption
    9. SQL Server Audit
    10. Summary
  15. Change Management
    1. Creating Projects
    2. Policy-Based Management
    3. Trigger Views
    4. sqlcmd
    5. Creating Change Scripts
    6. Summary
  16. Configuring the Server for Optimal Performance
    1. What Every DBA Needs to Know about Performance
    2. What the Developer DBA Needs to Know about Performance
    3. What the Production DBA Needs to Know about Performance
    4. Optimizing the Server
    5. Configuring Server Hardware
    6. CPU
    7. Memory
    8. I/O
    9. Summary
  17. Optimizing SQL Server 2008
    1. Application Optimization
    2. The Silent Killer: I/O Problems
    3. Table and Index Partitioning
    4. Data Compression
    5. Memory Considerations and Enhancements
    6. CPU Considerations
    7. Summary
  18. Monitoring Your SQL Server
    1. The Goal of Monitoring
    2. What's New in Monitoring for SQL Server 2008
    3. Choosing the Appropriate Monitoring Tools
    4. Performance Monitor
    5. Monitoring Events
    6. Monitoring with Dynamic Management Views and Functions
    7. Monitoring Logs
    8. Management Data Warehouse
    9. Summary
  19. Performance Tuning T-SQL
    1. Physical Query Processing
    2. Tuning Process
    3. Summary
  20. Indexing Your Database
    1. Noteworthy Index-Related Features in SQL Server
    2. Sample Database
    3. Partitioned Tables and Indexes
    4. Filtered Indexes and Filtered Statistics
    5. Index Maintenance
    6. Database Tuning Advisor
    7. Summary
  21. Replication
    1. Replication Overview
    2. Replication Models
    3. Implementing Replication
    4. Peer-to-Peer Replication
    5. Scripting Replication
    6. Monitoring Replication
    7. Summary
  22. Database Mirroring
    1. Overview of Database Mirroring
    2. Monitoring Database Mirroring
    3. Troubleshooting Database Mirroring
    4. Preparing the Mirror Server for Failover
    5. Mirroring Multiple Databases
    6. Database Mirroring and Other High-Availability Solutions
    7. Mirroring Event Listener Setup
    8. Database Snapshots
    9. Summary
  23. Backup and Recovery
    1. Types of Failure
    2. Making Plans
    3. Overview of Backup and Restore
    4. Planning for Recovery
    5. Developing and Executing a Backup Plan
    6. Managing Backups
    7. Backup and Restore Performance
    8. Performing Recovery
    9. Archiving Data
    10. Disaster Recovery Planning
    11. Summary
  24. SQL Server 2008 Log Shipping
    1. Log Shipping Deployment Scenarios
    2. Log-Shipping Architecture
    3. Log Shipping Process
    4. System Requirements
    5. Deploying Log Shipping
    6. Monitoring and Troubleshooting
    7. Managing Changing Roles
    8. Database Backup Plan
    9. Integrating Log Shipping with Other High-Availability Solutions
    10. Removing Log Shipping
    11. Log-Shipping Performance
    12. Upgrading to SQL Server 2008 Log Shipping
    13. Summary
  25. Clustering SQL Server 2008
    1. Clustering and Your Organization
    2. Clustering: The Big Picture
    3. Upgrading SQL Server Clustering
    4. Getting Prepared for Clustering
    5. Clustering Windows Server 2008
    6. Clustering SQL Server 2008
    7. Maintaining the Cluster
    8. Troubleshooting Cluster Problems
    9. Summary
  26. Index

Product information

  • Title: Professional Microsoft® SQL Server® 2008 Administration
  • Author(s): Brian Knight, Ketan Patel, Wayne Snyder, Ross LoForte, Steven Wort
  • Release date: November 2008
  • Publisher(s): Wrox
  • ISBN: 9780470247969