Microsoft SQL Server 2012 Bible

by Adam Jorgensen, Patrick LeBlanc, Jose Chinchilla, Jorge Segarra, and Aaron Nelson

Purchase Hard Copy: Amazon US | Amazon UK
Kindle Edition: Amazon US | Amazon UK

SQL Server is anything but simple. Its products represent a complex ecosystem that requires the participation of an assortment of professionals: architects to analyze and model data, developers to write code and manage data, BI pros to generate reports and analyze data, and administrators to do just about everything else to deliver that data-from installing to implementing to maintaining to optimizing.

On paper, these roles sound like clearly defined pieces in the great SQL Server puzzle. But the real world is rarely that neat, and not uncommonly the boundaries between roles often soften and blur-to the point that they merge or reverse or out-and-out collide. The data architect needs to set up a SQL Server environment, perhaps write a script to prove out a concept or move a project forward. The developer has to modify a database design or configure a complex environment to test an operation. The administrator must analyze and modify queries that cause bottlenecks in a SQL Server installation.

Professionals
who work with
 SQL Server …

Professionals who work with SQL Server in any capacity often wear a number of hats, usually at the same time. Consequently, they must be able to access a wide range of reliable information quickly and easily. And despite the proliferation of Internet resources, weeding through hundreds of Google hits for a comprehensive answer to an obscure question may not be the best solution. For the one doing the searching, a good reference book remains an invaluable resource.

Yet more often than not, books on SQL Server are geared toward a specific role, such as a book on database design or one on T-SQL coding, rarely providing an encompassing overview of the product and its environment (usually for good reason). And those books that do try to tackle such a behemoth system often gloss over essential details in the name of inclusion. As a result, the SQL Server professional must either invest in an extensive library or piece together information from a multitude of sources, many of which are contradictory, incomprehensible, or worse still, flat out wrong.

But there’s a way out: the Microsoft SQL Server 2012 Bible, a comprehensive resource that covers everything from database design to database mirroring, from T-SQL programming to Analysis Services cubes, from managing clusters to executing distributed queries. The book’s authors and contributors (10 in all) leave few stones unturned when it comes to giving you insight into just about everything SQL Server has to offer.

..in any capacity…

Pulling such an all-inclusive resource into one package is no easy task, given SQL Server’s extensive and fluid nature. And targeting a book to different professional types only adds to the challenge. Yet the writers have done just that, providing an authoritative resource that well earns the term Bible. Not only do they cover a wide range of topics, but they do so in a way that makes the information easy to understand and to apply, often breaking down complex information into simple concepts and instructions. Take, for example, the chapter on change tracking. “All Change Tracking does is say to the world: ‘This row changed; here’s the PK.’ Clean and simple, no fuss, no muss.” This kind of information, anyone can understand.

Yet the book bills itself as being geared toward the intermediate user. And true enough, assumptions are made about the reader’s basic knowledge. For example, the book jumps into the topic of data types without explaining what a data type is, the assumption being that most readers have enough basic knowledge to understand such concepts. Even so, most chapters offer enough information to give the beginner a foothold into the subject at hand, such as when the book describes how the database engine processes a T-SQL query. At the same time, the authors willingly provide more advanced information when the topic warrants a deeper understanding.

..often wear…

No doubt, you can attribute the book’s depth of coverage and breadth of information to the authors and contributors themselves, each one a SQL Server authority, many Microsoft Certified Professionals or SQL Server MVPs-a litany of experts that includes data architects, database developers, BI experts, and DB administrators. They model databases and fine-tune performance and write code and build reports and move data and, among them, do just about everything that database professionals do. Together they have more years experience than SQL Server has been in existence. Certainly, this was no shabby lot who pulled together this book.

And unlike other references I’ve encountered, especially online, the Bible is much more than a remix of SQL Server Books Online. In addition to providing accurate and precise information, the authors are not afraid to give advice or offer opinions. When discussing policy-based management, for example, they call this feature “the absolute gift of power,” then explain why they hold such a position. Or when they discuss set-based queries, they state quite emphatically that iterative T-SQL, the sort that acts upon one row at a time, forces the database engine to perform “wasteful single-row operations, instead of handling the problem in one larger, more efficient set.” The authors then explain the differences in performance and why a set-based approach is critical to efficient data access.

..a number of hats.

And the Bible doesn’t limit itself to the features and functions built into SQL Server. For example, SQL Server does not inherently support row-level security. As the authors point out, you can use views to provide a hard-coded form of this service. However, such a solution can create a nightmare for anyone trying to develop or maintain a database of any significant size. To address this situation, the book describes how to build a custom solution into the abstraction layer to provide the necessary security. The discussion includes an explanation of each of the solution’s components, including all the necessary tables, procedures, and triggers, along with plenty of sample T-SQL code.

There is, in fact, no shortage of useful information from beginning to end. A good example of this is the chapter on installation. As you would expect, the authors provide a comprehensive step-by-step guide to a standard setup, complete with screenshots and careful explanations of the many options you’re presented. However, before the chapter gets into that information, it provides details about hardware and software requirements, various SQL Server editions, and different licensing plans. And after the chapter walks you through the setup process, it describes what post-installation steps you should take, as related to patches, monitoring, TCP/IP ports, and the Best Practices Analyzer. On top of all this, the authors provide good, solid information about command-line and PowerShell installations.

And it’s not just administrative tasks that garner such attention. You’ll find that all 59 chapters are chock full of steps and descriptions and examples and careful explanations on such topics as indexing, database design, procedural flow, hierarchical tables, recovery models, data mining algorithms, and availability testing, to name a few. Plus, the authors provide a careful account of the new features specific to SQL Server 2012. And the book is rich in helpful tidbits that you rarely find elsewhere, such as advice about agile data modeling, SQL Server’s version of PowerShell, or accessing remote data sources in Reporting Services. Many of the chapters also include small Best Practices callouts that address everything from performing bulk inserts to coding mathematical expressions to using the common language runtime (CLR).

Of course, no single book can provide a complete picture of every facet of SQL Server, and the Bible is no exception. Complex features and concepts are often relegated to a single part or chapter in the book. Yet topics such as replication, backup and recovery, database modeling, and business intelligence often demand entire volumes. As a result, you might find yourself reaching for sources outside of the book to supplement the information. The data architect, for example, would not want to rely on this book alone to learn about data modeling, the principles of relational databases, and the various normal forms. But the database developer and administrator would have much to gain from this information, just as the data architect could learn a lot from this book about how SQL Server processes queries and uses indexes to improve performance. Given the book’s scope and what it has set out to accomplish, each chapter contains a remarkable level of detail that can benefit just about any SQL Server professional.

If there’s one area in which the book falls short its in some of its organization. For instance, there’s no obvious introduction to data definition language (DDL) elements. You’ll find examples of creating such objects as databases and tables strewn about, but no cohesive organization. And although there’s a chapter on creating views, it’s been dropped into the part on T-SQL queries, squeezed between a chapter on aggregated data and one on modifying data. And I could find nothing about SQL Server Agent or XML indexes in the book. The information might be there, but if it is, it’s buried in someplace unexpected.

In addition, discussions of database design are covered in multiple chapters spread throughout the book. The second of these chapters, which covers relational theory, is sandwiched between a chapter on query flow and a chapter on expressions and scalar functions. In fact, the theory chapter includes a note pointing out how design concepts are dispersed throughout the book, which left me with the sense that organizational changes (that were perhaps not completely thought out) had been implemented at the last minute and this was their way to cover it up.

But I don’t want to belabor this point. To do so would be to miss much of the value that the book offers. Besides, despite some of its odd construction, you can usually find what you’re looking for-most of the time quite easily. And in all fairness, pulling together the breadth of information that this book does is a remarkable feat in itself.

If it can be said that the devil is in the details, then the Bible is full of the devil and would be a welcomed addition to the libraries of most SQL Server professionals. It might not be the only SQL Server 2012 resource you end up with, but it could easily be the one you use the most.