Page 3 of 3
Author: Adam Jorgensen et al
Audience: DBAs and SQL Developers
Reviewer: Ian Stirk
Chapter 16 Clustering SQL Server 2012
Clustering is another High Availability (HA) option. The chapter opens with what clustering can and cannot accomplish, closely followed with a discussion of other HA options (cold/warm backup server, log shipping, replication, mirroring, and AlwaysOn). A theoretical discussion of clustering follows. Then comes a more practical section, preparing for clustering, and a step-by-step walkthrough of installing clustering (both Windows and SQL Server), with plenty of relevant screenshots. This is followed by a troubleshooting section, which is probably inadequate (the bottom line seems to be ‘…work with Microsoft to resolve your problems’).
The practical step-by-step installation example should prove very useful. This chapter contained a good discussion of the various HA options, but I think these are relevant to all the HA options (there are chapters on each option), so perhaps having them in one place would have been better.
Chapter 17 Backup and Recovery
Backup and recovery are the most important tasks for a production DBA, downtime can be both costly and unacceptable, thus it is critical to have an in-depth understanding of the topic.
The chapter starts with an overview of the different types of failure (hardware, data modification, application, elevated privilege, software, and local disasters). This is followed by a discussion of the importance of making plans (backup/recovery, disaster recovery, and testing) and the need to maintain these plans.
There is a detailed section on the various types of data backup (full, partial, file/filegroup, differential, partial differential, file differential, and copy-only), and transaction log backup (pure, bulk, and tail). Methods of copying databases are discussed (detach/attach, BCP, and scripting wizard) with plenty of screenshots and example T-SQL provided. Recovery models (full, bulk logged, and simple) are compared and contrasted. The importance of verifying backups is also discussed.
There is a practical step-by-step example of creating and executing a backup plan, using both SSMS (plenty of screenshots), and T-SQL. This is followed by a corresponding example of a restore. The importance of archiving and partitioning data is explained.
This chapter is quite wordy in parts, but I feel in this case it is necessary. The majority of backup/restore work is about planning and preparation (and please, test you plans regularly!). I cannot understand why this backup and restore chapter is wedged between the HA chapters.
Chapter 18 SQL Server 2012 LOG Shipping
Log shipping is a long established method of recovery/HA. It is simple, efficient, and low cost. The chapter starts with a discussion of the various scenarios that can use log shipping (warm standby server, DR solution, and reporting database). There is a step-by-step walkthrough of setting up log shipping, using SSMS, and a brief (and inadequate) example using T-SQL commands.
Monitoring and troubleshooting are only briefly discussed. A planned failover from the primary to the secondary server is discussed.
This chapter provides a good overview of what log shipping is, how to set it up, how to test it, and how to monitor it. Again the other HA options are discussed.
Chapter 19 Database Mirroring
Mirroring is yet another Microsoft HA option. The chapter starts with an overview of what database mirroring is, and its operating modes (high performance, high safety with and without automatic failover). There’s a step-by-step walkthrough of setting up mirroring, using a combination of SSMS and T-SQL.
There is a discussion on monitoring via the use of catalog views, also a little later in the chapter, using system monitor (PerfMon), and the Database Mirroring Monitor. Manual and forced failover are explained with example code.
This chapter provides a good overview of what mirroring is, how to set it up, how to test it, and how to monitor it. I do not understand why the different monitoring tools where not explained together in one place. Yet again other HA options are discussed.
Chapter 20 Integration Services Administration and Performance Tuning
SQL Server Integration Services (SSIS) is a method of getting data in various formats, from source to target, and performing any necessary data transformations along the way.
The chapter starts with an overview of what SSIS is, then expands into how to deploy and configure packages, and how to secure and administer SSIS. Finally it discusses common problems and their solutions.
There’s a nice link to the corresponding Wrox publishing’s advanced SSIS book. I had expected to see an example package (e.g. how to export a SQL table to an Excel spreadsheet), but no example was given. I know this book is about administration, but an example would have allowed easier follow-through of the later sections, and also put things into context.
Chapter 21 Analysis Services Administration and Performance Tuning
SQL Server Analysis Services (SSAS) is a method for discovering facts about data (e.g. what products sell most on Tuesdays). The chapter starts with an overview of what SSAS is, then expands into how to administer, deploy, monitor, performance tune, and secure SSAS.
This chapter provides a good introduction to SSAS. There’s a nice link to the corresponding Wrox publishing’s advanced SSAS book. There is an error in the opening sentence “Now that you’ve learned how to administer SSAS…”, but of course we’ve only just started the chapter, the editor meant to say SSIS instead of SSAS. Again, no example SSAS was created.
Chapter 22 SQL Server Reporting Services Administration
SQL Server Reporting Services (SSRS) is Microsoft’s reporting solution. The chapter opens with how to configure your SSRS server, then expands with setting properties for the server, then uses Report Builder to create useful DBA reports, and finally shows how to manage and execute reports using the Report Manager.
This chapter provides a good introduction to SSRS, how to configure, manage, deploy SSRS solutions. Here there is an example creation of a report. Oddly, there’s no link to the corresponding Wrox publishing’s advanced SSRS book (which seems to have been published before the SSAS book).
Chapter 23 SQL Server 2012 SharePoint 2010 Integration
This chapter discusses the advanced reporting capabilities of SharePoint 2010, including PowerPivot, reporting services, and Power View. This book seems to have been published too early to incorporate the later version of SharePoint (SharePoint 2013 seems to have been released in October 2012), so it might be worthwhile looking for an update.
Chapter 24 SQL Azure Administration and Configuration
SQL Azure is Microsoft’s cloud database, providing elastic resource usage for users, so you don’t need to worry about installation, hardware maintenance, DR/HA and resource exhaustion. The chapter starts with an overview of SQL Azure architecture, continues with configuring and administration. It should be noted that several features are missing from SQL Azure compared with standard databases. SQL Azure is a fast changing area, so while the chapter does explain the basics, be sure to look for later updates elsewhere.
Chapter 25 Alwayson Availability Groups
AlwaysOn Availability Groups is Microsoft’s latest HA solution. AlwaysOn allows grouping of multiple databases into groups, for HA purposes. It combines the best of clustering and mirroring. The chapter starts with a discussion of the architecture involved, and then continues to an example availability group. Primary and replica databases are discussed, together with manual and automatic failover. Read-only access of secondary replicas provides useful additional functionality (e.g. DR, reporting, backup from the secondary).
Monitoring is provided via a dashboard, additionally, DMVs and system views can be used. There’s a very detailed worked example. I’ve no idea why this HA related chapter is separate from the other HA chapters.
This book has a very wide scope. It is generally easy to read, with a good balance between theory and step-by-step example walkthroughs, together with good use of screenshots. Most chapters have sufficient detail to take you from level 3 expertise to level 7 or 8 expertise (based on a 1 to 10 scale).
Although many topics are covered, it some ways the book felt lightweight - this is not really a fault of the book, rather that SQL Server 2012 is a big product (each chapter could probably be expanded to a whole book itself).
The physical grouping of chapters could have been better, with all related chapters being placed together (e.g. why is AlwaysOn not adjacent to the other High Availability chapters?). Additionally, an overview of the content of each group of chapters would have put the content into context, and prevented some repetition.
The links between the chapters could have been better, there are several areas where cross referencing of information would have been useful. This might have occurred because different authors were used to write the different chapters, but better editing should have compensated for this.
Although written for 2012, much is applicable to 2008 and even 2005. If you want a good, wide ranging, general SQL Server 2012 administration book, I can certainly recommend this book.