Author: Adam Jorgensen et al
Publisher: Wrox
Pages: 960
ISBN: 978-1118106884
Audience: DBAs and SQL Developers
Rating: 4.6
Reviewer: Ian Stirk 

Chapter 6 Service Broker in SQL Server 2012
This chapter provides an overview of the use of Service Broker (SB), what it is and why you would want to use it. In essence SB provides asynchronous processing, using queues. In many ways it supersedes MSMQ technology. It details the major components of SB: message types, contracts, queues, and activation stored procedures. While the content is readable, and code samples are given at various points, it would have been more instructive to have a complete end-to-end working example of Service Broker in action.

Chapter 7 SQL Server CLR Integration
The CLR allows you to write .NET code (e.g. C# or VB.net) and use it within a SQL Server environment. Some tasks, e.g. complex calculation, give better performance when written in .NET. Additionally, .NET provides a rich programming model and development environment. This chapter shows how to create, deploy, and register.NET code (called assemblies), for use within SQL Server. Examples are provided to do this via the command line and from within Visual Studio. Security is discussed, as is performance monitoring via PerfMon, SQL profiler and DMVs.
Again, it would have been better to have produced a useful CLR utility, e.g. using .NET Regular Expression libraries to validate email addresses etc. The example given in the chapter just prints the message “My First SQLCLR Assembly”. I feel this was a missed opportunity.

Chapter 8 Securing the Database Instance
Security is an increasingly noticeable topic. This chapter opens with a discussion of SQL versus Windows authentication types. It then proceeds to cover security at the server, database, object levels, and the use of GRANT, DENY, and REVOKE T-SQL commands. Permission chains and cross database permissions are briefly discussed. Also described is the new 2012 feature of creating user-defined server roles.
I found this chapter too light. Additionally, I wanted a worked example, from start to end, of creating a new user, a new role, assign the user to the role, and assign object permissions to the role.

Chapter 9 Change Management
Various regulatory and compliance measures have made auditing and documentation of changes an important topic. The chapter opens with an overview of Policy Based Management (PBM), which can enforce policies, at various levels. These policies can prevent, allow, or log a change. DDL triggers are discussed with reference to monitoring changes (create, drop, alter of objects). An overview of scripting is provided, with an emphasis on Powershell.  
The chapter contains a useful example which ensures stored procedures follow a naming convention where the name begins with usp. Additionally there’s an example of using a DDL trigger to monitor database changes.
This is generally an interesting chapter, covering aspects of change management, but it is not complete. I would have expected a discussion of Change Data Capture (CDC) which details what data has changed, and Change Tracking, which details what rows have changed but not the data values.  

Chapter 10 Configuring the Server for Optimal Performance
The chapter opens with a discussion of the Performance Tuning Cycle, defining good performance, and focusing on what’s important. This continues with what development DBAs need to know (i.e. who are the users, what SQL is running, data usage patterns and physical database design). There’s a somewhat theoretical discussion of CPU (cores, cache, hyper-threading, and architecture), memory (2TB limit, virtual memory manager, and page faults), and I/O (RAID types, SANs, partitioning, fragmentation etc).
The chapter has a link to another Wrox title I have previously reviewed, Professional SQL Server 2008 Internals and Troubleshooting, which provides further detail. I felt I wanted more from this chapter. However, much of it is inclued in subsequent related chapters.

Chapter 11 Optimizing SQL Server 2012

I/O is often the major bottleneck on many systems. I/O problems often show themselves as a slow system even when the CPUs are not particularly busy. Consideration is given to physical file placement and tempdb (used by all databases, so potentially the most important). Table and index partitioning are discussed as a means of providing better management, with the typical side-benefit of improved performance. The benefits of compression are explained, together with implementation via the GUI and T-SQL.
An insight into CPU architecture, cache coherency, affinity masks, and max degree of parallelism (MAXDOP) is provided, but much of it reads like theory. Memory is discussed in a similar manner. There’s a nice upgrade to the Page Life Expectancy rule (a ‘good’ value used to be 300 seconds, now it is calculated as MaxServerMem x 75!).
There is a small error which says “…SQL Server 2012 64-bit supports 1TB of RAM…”, SQL Server supports 2TB of RAM (else depends on the OS maximum). This should have been caught by the editors, the 2TB limit is mentioned at least twice elsewhere in this book.
The Resource Governor limits the amount of resources for a set of users, this can protect from runaway queries, and provide a fairer distribution of resource usage. Example code is provided to create resource pools for different workload groups.
Again there are some useful links to Microsoft resources for further, deeper information. Overall this is a useful background chapter of things to consider when thinking about SQL Server optimization.

Chapter 12 Monitoring Your SQL Server
The goal of this chapter is for you to know your server, what is running on it, what is normal (the baseline), what is the trend? The aim of this monitoring is to be proactive rather than reactive.  
Many monitoring tools are outlined, these include: Performance Monitor (PerfMon), Extended Events, SQL Profiler, SQL Trace, the Default trace, Activity monitor, DMVs/DMFs, system stored procedures, standard reports and the Best Practice Analyzer. Several of these tools are explained in greater detail later in the chapter. You can see there are plenty to learn about!
This chapter provides a good overview of the tools available for monitoring, and when and where they’re appropriate to use. The content is enough to get you started, but will not make you an expert e.g. the PerfMon section doesn’t mention that you can analyse the logs using Performance Analysis for Logs (PAL).

Chapter 13 Performance Tuning T-SQL
This chapter opens with an overview of how a SQL query is broken down (parsing, algebrization, and optimisation) and executed. Compilation and recompilation are explained in terms of the cached plans and its correctness (i.e. changing the schema etc will cause a recompilation). Recompilation can result is resource usage spikes, so should be monitored. Several ways to monitor this are given.
Various factors that can affect performance are examined, these include tempdb data and log physical placement, index access methods (scan, seek, lookup), fragmentation, statistics, and join algorithms (merge, hash, loop join).
The chapter provides good advice on various considerations for improving SQL performance, in many cases, useful example SQL is provided. While all the information is useful in tuning your SQL, to me it seemed at times a little disjoined. Some of the more traditionally methods of improving your SQL should have been included here.

Chapter 14 Indexing Your Database
Indexes are perhaps the major method of improving the performance of your queries. This chapter opens with a discussion of the new 2012 columnstore index feature, which is especially important in reporting systems that require a large amount of data. Other features discussed include: filtered indexes and statistics, compression, spatial indexes, use of INCLUDE, parallel index operations, version store and the Database tuning Advisor (DTA).
The different types of indexes are explained (clustered, non-clustered, covering, filtered), together with the different ways indexes can be used (seek, scan, lookup). This is followed by a discussion of the advantages of partitioning tables and indexes. Index maintenance is examined, looking at page splits, fill factor, and fragmentation.
Query performance can be improved by implementing missing indexes, removing unused indexes, and defragging any fragmenting indexes. Additionally the DTA can be used to recommend indexes based on a workload. All these methods have drawbacks, so be careful when implementing them.
Overall, this is a useful chapter, some areas felt a bit light (e.g. only one page for the DTA), but is more than ok for introductory/intermediary level. There was no link to the index maintenance section given in the previous chapter.

Chapter 15 Replication
Replication is a means of copying data. It needs to be specified on a per object basis. An interesting overview is provided, with an analogy of replication and the magazine publishing process. The outline includes articles, selection, publishing, and subscribers. Additionally a distributor is used for delivery and monitoring.
Much of replication depends on the use of SQL Server Agent jobs e.g. snapshot agent, log reader, distribution, merge, and queue reader. Again links to previous SQL Agent job usage should have been made.
The main replication types (snapshot, transaction, and merge) are discussed in detail, as are the various topographies (one or more subscribers to one or more publishers). Implementation details are given, together with tools to monitor replication.
This chapter contains a good overview of what replication is, and its various configurations. It might have been worthwhile to explain it in the context of other high availability (HA) / disaster recovery (DR) options.