SQL Server 2008 Administration in Action by Rod Colledge
April 9, 2009
Today's technical book market is flooded with labels featuring tantalizing phrases promising easy access to insider information on even the most complex topics. Unfortunately, such promises are frequently misleading in regard to depth of presented information or the level of knowledge possessed by their authors. In such an environment, it is refreshing to find "SQL Server 2008 Administration in Action" by Rod College, which rather terse title contrasts with breadth and exceptional quality of its content. The purpose of our article is to offer its overview.
With over a decade of working experience with the SQL Server product line as a developer and administrator, as well as the founder and the primary contributor to the sqlCrunch.com web site, which provides a knowledge-sharing forum to a crowd of database management enthusiasts, Rod certainly has qualifications to write authoritatively on the subject. Acquired skills and professional insight translate in this case into thorough and reliable coverage of topics most relevant from the administration perspective. Ability to present them in a clear and concise, yet comprehensive manner constitutes one of the biggest strengths of the book. Another of its significant values lies in a wide range of pragmatic advice, including "the best practice considerations" (supplemented by cleverly summarized "worst practices") backed up by examples and easy to reproduce sample test results. The author also exposes some of the myths and misconceptions widely popularized in DBA circles, pointing out their faults and weaknesses.
The book deals with tasks most commonly handled by database administrators supporting Online Transaction Processing environments, focusing on planning, implementation, and maintenance. While this somewhat confined approach resulted in exclusion of subjects falling into Business Intelligence (Integration Services, Analysis Services, and Reporting Services) and development (T-SQL and .NET programming, locking, or transaction isolation levels) areas (surprisingly, replication and full text search functionality have been excluded as well), its presentation of core management responsibilities is superb.
The planning section begins with extensive coverage of all relevant server hardware components, including disks (with analysis of different storage types in the context of high availability, performance, utilization, and pricing, as well as recommendations regarding their configuration settings, such as track alignment, allocation unit size, controller caching, or redundancy), CPUs (caching characteristics, Non-Uniform Memory Access capabilities) and NICs (e.g. teaming). This serves as an entry point to discussion about design that takes into consideration projected usage patterns (OLAP vs. OLTP), capacity planning, and required service levels. You will also find here guidance in consolidation efforts and an overview of tools that assist with planning and performance optimization (such as SQLIO or SQLIOSIM).
Installation section follows, outlining the steps necessary to perform trouble-free setup and upgrade (including comparison between in-place and side-by-side approaches to the latter). Post-installation configuration related topics consist of security hardening (selecting authentication mode, network protocols, and port assignment mechanism, leveraging auditing enhancements introduced in SQL Server 2008, protecting sensitive information with Transparent Data Encryption), tuning memory (e.g. /3GB, Physical Address Extensions and Address Windowing Extensions) and processor (priority boosting, adjusting Worker Thread limits, lightweight pooling, CPU affinity, maximum degree of parallelism) usage characteristics, as well as adjusting a variety of other server and database level parameters (e.g. recovery interval, fill factor, locks, query wait, user connections, or Query Governor cost limit).
One of book's highlights is an insightful overview of Policy Based Management (combined with Central Management Servers and PowerShell functionality) as a mechanism to manage large numbers of dissimilarly configured SQL Server installations. The chapter dedicated to this subject contains a presentation of its concepts, deployment guidelines, and summary of potential obstacles that could surface during its implementation (along with fairly ingenious workarounds that allow you to mitigate their impact). This information is supplemented by a description of architectural components that facilitate centralized management, as well as analysis of pre-defined and custom policies (leveraging Windows Management Instrumentation-based data) and their evaluation methods.
The author does not neglect to cover the area of data management, pointing out the relevance of proper placement, separation, and sizing of database files and filegroups, with particular emphasis on some of the default behavior (such as Autogrow settings or initial tempdb size), which suitability should be carefully considered, as well as requirements necessary to take advantage of the instant initialization functionality introduced in SQL Server 2008. He also discusses the new Filestream feature, allowing the storage of binary large objects (pointing out some of its important drawbacks) and describes page and row compression functionality that help maximize storage utilization (along with its performance implications).
You will likely find helpful an outline of decision making process leading to the selection of backup strategy, which presents available options, corresponding recovery models, backup enhancements (e.g. checksum verification, mirroring, coordinated backups based on transaction log marks, online piecemeal restores, database snapshots, and backup compression), a range of criteria that should be taken into consideration, pros and cons of disk and tape backups (along with the benefits of a hybrid approach), as well as backup retention and verification recommendations.
The high availability chapter delivers a thorough overview of failover clustering, log shipping, and mirroring, analyzing their relative strengths and weaknesses, in regard to such criteria as transparent failover capability and client redirection, the ability to provide redundancy on the database and server level, and replication delay. It presents several scenarios where log shipping delivers significant benefits, as well as provides an example illustrating its sample implementation, including steps involved in failover and failback. There is also a common sense discussion of database mirroring, introducing its concepts and restrictions. Built-in (automatic, manual, forced service) and custom failover options available in each mode are identified based on their dependency on recovery objectives, presence of the Witness server, and environmental factors (with primary emphasis placed on network latency, server topology, transactional safety, as well as server performance and capacity). You will find here tips assisting with monitoring of mirroring implementations, suspending and resuming of mirroring sessions, and caveats that come into play whenever multiple mirrored databases are hosted on the same SQL Server instance.
The maintenance section starts with an overview of tasks that verify the integrity
of arbitrary database objects by leveraging
The book includes straightforward explanation of indexes, factors affecting their size and performance, implications of their creation strategy, mutual dependencies between clustered and non-clustered indexes, significance of bookmark lookups and statistics on the index selection process, and criteria that help you make the most efficient indexing choices. It also describes methods that allow you to improve index efficiency and flexibility, such as covering indexes, included columns, indexed views, and filtered indexes. In addition, there are also valuable tips regarding index analysis and maintenance.
The Monitoring and Automation chapter covers Activity Monitor introduced in SQL Server 2008 (providing you a quick, convenient method to evaluate processor utlization, spot an unusually high number of tasks awaiting execution, or identify I/O bottlenecks), SQL Server Profiler and SQL Trace, Data Collector and Management Data Warehouse (facilitating baseline analysis, capacity planning, and long term trending of database utilization), and a few Microsoft Product Support Services utilities. It also provides an overview of different ways of automating tasks and alerts, including maintenance plans, SQL Server agent jobs, and Microsoft System Center Operations Manager.
The book also presents the Resource Governor functionality, introduced in SQL Server 2008, which goes beyond the traditional set of performance tuning tools, by allowing granular control of resource utilization on per-connection source basis. A description of its architecture consisting of resource pools, workload groups, and classifier functions, as well as their mutual interaction can also be found here. Understanding these concepts is considerably easier thanks to a sample implementation documented by the author. Another interesting subject covered in the book that deals with performance is the methodology known as Waits and Queues. Its principle is based on characteristics of a scheduling component of SQL Server Operating System (SQLOS) and a mix of dynamic management views, stored procedures, and extended events, which give you insight into its behavior. The author also discusses the most common performance problems (procedure cache bloat, high volume of signal waits, index-related memory issues, disk bottlenecks, and blocking) as well as ways to identify and resolve them.
I hope that this overview gave you a good understanding of the book's purpose, content, and intended audience. If you are a production DBA facing the challenges of managing a SQL Server environment and you aspire to improve its efficiency, you should consider adding this title to your personal library.