SQL Server 2008 Administration in Action by Rod Colledge

SQL Server 2008 Administration in Action

SQL Server 2008 Administration in Action

MEAP Release: May 2008
Softbound print: September 2009 (est.) | 450 pages

ISBN: 193398872X

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

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 DBCC
validation checks, with primary focus on DBCC
(as well as other commands invoked automatically during
its execution). At the same rate, the author underlines the importance of
preventative actions, through such means as the SQLIOSIM utility or page
checksums, offering several clever ideas that mitigate the harmful overhead of
extra checks. There is also useful advice that lets you determine the extent of
data loss using an undocumented DBCC,
as well as sample recovery procedures that follow the discovery of corruption,
involving the REPAIR_REBUILD
option, page-level restores, and REPAIR_ALLOW_DATA_LOSS.

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.

SQL Server 2008 Administration in Action

SQL Server 2008 Administration in Action

MEAP Release: May 2008
Softbound print: September 2009 (est.) | 450 pages

ISBN: 193398872X


See All Articles by Columnist
Marcin Policht

Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles