SQL Server 2008 Administration in Action
Rod Colledge
  • August 2009
  • ISBN 9781933988726
  • 464 pages
  • printed in black & white

Simply loaded with excellent and immediately useful information.

Kevin Kline, Quest Software

SQL Server 2008 Administration in Action offers over 100 DBA-tested, high-value, best practices that will help you tame the beast and keep it under control. Unlike the many comprehensive SQL Server reference tomes out there that attempt to cover the whole SQL Server feature set, this book drills down on the techniques, procedures, and practices that help you keep your database running like clockwork.

Table of Contents show full




about this book

about the cover illustration

about the author

Part I Planning and installation

1. Chapter 1 The SQL Server landscape

1.1. SQL Server 2008: evolution or revolution?

1.2. Editions and features

1.3. SQL Server tools

1.4. DBA responsibilities

2. Chapter 2 Storage system sizing

2.1. Characterizing I/O workload

2.2. Determining the required number of disks and controllers

2.3. Selecting the appropriate RAID level

2.4. Selecting an appropriate storage system

2.5. SQL Server and SANs

2.6. Solid-state disks

2.7. Best practice considerations: storage system sizing

3. Chapter 3 Physical server design

3.1. Disk configuration

3.2. CPU architecture

3.3. Memory configuration

3.4. Networking components

3.5. Server consolidation and virtualization

3.6. Best practice considerations: physical server design

4. Chapter 4 Installing and upgrading SQL Server 2008

4.1. Preparing for installation

4.2. Installing SQL Server

4.3. Upgrading to SQL Server 2008

4.4. Developing a service pack upgrade strategy

4.5. Best practice considerations: installing and upgrading SQL Server

5. Chapter 5 Failover clustering

5.1. Clustering overview

5.2. Clustering topologies and failover rules

5.3. Installing a clustered SQL Server instance

5.4. Best practice considerations: failover clustering

Part II Configuration

6. Chapter 6 Security

6.1. Authentication mode

6.2. Networking

6.3. Implementing least privilege

6.4. Auditing

6.5. Data encryption

6.6. SQL injection protection

6.7. Best practice considerations: security

7. Chapter 7 Configuring SQL Server

7.1. Memory configuration

7.2. CPU configuration

7.3. Server configuration

7.4. Operating system configuration

7.5. Best practice considerations: configuring SQL Server

8. Chapter 8 Policy-based management

8.1. Server management challenges

8.2. Policy-based management terms

8.3. Policies in action

8.4. Enterprise policy management

8.5. Advanced policy-based management

8.6. Best practice considerations: policy-based management

9. Chapter 9 Data management

9.1. Database file configuration

9.2. Filegroups

9.3. BLOB storage with FileStream

9.4. Data compression

9.5. Best practice considerations: data management

Part III Operations

10. Chapter 10 Backup and recovery

10.1. Backup types

10.2. Recovery models and data loss exposure

10.3. Backup options

10.4. Online piecemeal restores

10.5. Database snapshots

10.6. Backup compression

10.7. Best practice considerations: backup and recovery

11. Chapter 11 High availability with database mirroring

11.1. High-availability options

11.2. Transaction log shipping

11.3. Database mirroring overview

11.4. Mirroring modes

11.5. Failover options

11.6. Mirroring in action

11.7. Best practice considerations: high availability

12. Chapter 12 DBCC validation

12.1. DBCC validation overview

12.2. Preventing and detecting corruption

12.3. Controlling CHECKDB impact

12.4. Removing corruption

12.5. Best practice considerations: DBCC validation

13. Chapter 13 Index design and maintenance

13.1. An introduction to indexes

13.2. Index design

13.3. Index analysis

13.4. Index maintenance

13.5. Managing statistics

13.6. Best practice considerations: index design and maintenance

14. Chapter 14 Monitoring and automation

14.1. Activity Monitor

14.2. SQL Server Profiler

14.3. Performance Monitor

14.4. Task automation and alerts

14.5. Best practice considerations: monitoring and automation

15. Chapter 15 Data Collector and MDW

15.1. Component overview

15.2. Setup and configuration

15.3. Data collection

15.4. Custom collection sets

15.5. Reporting

15.6. Best practice considerations: Data Collector and MDW

16. Chapter 16 Resource Governor

16.1. Resource Governor overview

16.2. Classifier function

16.3. Workload groups

16.4. Resource pools

16.5. Resource Governor in action

16.6. Monitoring resource usage

16.7. Best practice considerations: Resource Governor

17. Chapter 17 Waits and queues: a performance-tuning methodology

17.1. SQLOS schedulers

17.2. Wait analysis

17.3. Common performance problems

17.4. Waits, queues, and DMV cross-reference

17.5. Best practice considerations: performance tuning

Appendix A: Top 25 DBA worst practices

Appendix B: Suggested DBA work plan

Appendix C: Common Performance Monitor counters

Appendix D: Top 10 Management Studio enhancements

Appendix E: Date/time data types in SQL Server 2008


About the Technology

The integrity and security of your database is crucial to your business. SQL Server 2008 is a massive and mature product with a very large feature set. As a SQL Server administrator, you must be equipped to handle myriad day-to-day tasks to keep your database healthy, and you must also be prepared to act quickly when something unexpected happens.

About the book

SQL Server 2008 Administration in Action focuses the production DBA, digging deep into the various tasks specific to that role. Expert author Rod Colledge — check him out at sqlCrunch.com — teaches you best practices that cover the lifecycle of a SQL Server system, including infrastructure design, installation, and operational maintenance. And while many of these techniques will work in any recent version of SQL Server, you'll find full coverage of emerging SQL Server 2008 best practices.

Each technique is presented in a task-driven style, and in the order of the typical life cycle of a SQL Server system. This allows you to easily open the book at the appropriate page and focus on what you need to know for each specific situation.

What's inside

  • Selecting and configuring server components
    • Distributing I/O load over multiple controllers
    • Selecting the appropriate stripe size
    • Configuring storage cache
  • Preparing for SQL Server clustering
    • Configure the private network for cluster use only
    • Configure the cluster network priority
    • Prepare resources for a clustered SQL Server installation
  • Index selection and maintenance
    • Identify and drop unused indexes
    • Identify and create missing indexes
    • Select the appropriate clustered index
  • Database mirroring
    • Prepare the mirror server for failover
    • Select the appropriate failover mode
    • Consider disabling log stream compression for CPU bottlenecked systems
  • Upgrading SQL Server
    • Reduce downtime with T-Log backup/restore and the side-by-side upgrade method
    • Prior to an upgrade, run DBCC checks and take full backups with verification
    • Ensure read only is not enabled prior to the upgrade

About the reader

This book is a DBA's best friend. It covers the most important and often overlooked areas that you need to maintain to keep your databases secure, in-tune, and well-protected in the event of disaster.

About the author

Rod Colledge is an independent SQL Server consultant based in Brisbane, Australia. Since 1996, Rod has specialized in the development and administration of SQL Server database systems. His recent work has included the design and implementation of custom transactional replication and log shipping solutions, performance tuning large, high-volume databases, and developing procedures for operational maintenance of large database systems. In September 2007, Rod founded sqlCrunch.com, a website specializing in summarizing and categorizing links to SQL Server white papers and best practices.

eBook $35.99 pdf + ePub + kindle

FREE domestic shipping on three or more pBooks

I thought I knew SQL Server until I read this book.

Tariq Ahmed, Coauthor of Flex 4 in Action

A refreshing database administration book.

Michael Redman, Microsoft

Required for any MS DBA.

Andrew Siemer, OTX Research

It delivered way beyond my expectations...Packed with useful enterprise-level knowledge.

Darren Neimke, Author of ASP.NET 2.0 Web Parts in Action