Page 1 of 3
Author: Adam Jorgensen et al
Audience: DBAs and SQL Developers
Reviewer: Ian Stirk
Aims to give a professional understanding of a very broad range of administration topics, how does it fare?
This is a big book, with a very broad coverage of topics discussed over its 25 chapters. This fact itself tells you there is much more to SQL Server 2012 than simply T-SQL. As you might expect fiven the ‘professional’ moniker of the title, this is not a book for beginners. Instead it is aimed at experienced DBAs and SQL developers.
Below is a chapter-by-chapter exploration of the topics covered.
Chapter 1 SQL Server 2012 Architecture
The chapter begins with an overview of the different types of admin users (production, development, business intelligent DBAs, and SQL developers) and the new features each may want to investigate. The underlying physical architecture is described in terms of database files, logs, filegroups and pages.
The various system databases are briefly described:
- resource database: contains critical internal system tables, metadata, and routines
- master database: contains metadata about the application databases
- tempdb: contains temporary objects and row versioning information
- model database: used as template when creating new application databases
- msdb database: contains a miscellany of information including SQL Agent job details, backup/restore data, and log shipping information
A miscellany of topics follows. The concept of schemas is discussed, as is synonyms. A long list of the various groups of Dynamic Management Views (DMVs) is given and briefly discussed. Various data types and their ranges/values are detailed. Creating your own data types and routines with the Common Language Runtime (CLR) is outlined. Feature lists for the various editions of SQL Server are given for comparison purposes. Lastly, licensing is featured, the big difference being previously it was socket-based but is now core-based, so potentially more expensive.
This chapter feels awkward, since it contains a wide range of topics, with sufficient depth for an introduction, but they are only loosely tied together. There are some good lists that could be useful for future reference.
Chapter 2 Installing SQL Server 2012 Best Practices
Installation typically involves a large amount of preparation, the time spent planning often pays for itself with a corresponding reduction in problems. The chapter opens with a discussion of various hardware considerations, including processors (type, speed, multi-core etc), memory (cache, RAM, disks etc), and storage (SAN, DAS, RAID). This is followed by software considerations, including collation and service account usage.
The various methods of installing SQL Server, namely new, side-by-side, and upgrade are outlined. Attention is given to both unattended (via a supplied PowerShell script) and attended installations. Some tools for testing/stressing the environment are discussed. Options for configuring SQL Server for security are detailed. Troubleshooting a failed install is discussed.
Overall this is a useful chapter, easy to understand, and with links for further, more detailed, information. There’s a nice list of post-install options to check, which can be easy to overlook.
Chapter 3 Upgrading SQL Server 2012 Best Practices
The previous chapter focused on new installs, this one relates to upgrading SQL Server. Reasons for upgrading are discussed, these include: AlwaysOn, contained databases, built-in encryption abilities, columnstore indexes, better compression, and reduced OS patching. There is an emphasis on the amount of testing the product has received via Microsoft’s daily builds, CTPs, RCs and case studies.
Again preparation is the key to a smooth upgrade. The different types of upgrade (in-place, and side-by-side), are detailed and compared. For each, checklists are provided for pre-upgrade, execution, and post-upgrade steps. To help with upgrading, 2 pre-upgrade tools are discussed, the Upgrade Advisor and the Upgrade Assistant, both should increase your confidence in a problem-free upgrade.
This is a very sensible and easy to follow chapter, containing many pointers of what to look for during upgrading, pre-empting potential problems, with some very good checklists.
Chapter 4 Managing and Troubleshooting the Database Engine
SQL Server comes with a good set of default configuration options (e.g. CLR is disabled), meaning it can typically be run without further changes. However, there are times when configuration changes need to be made.
SQL Server configuration manager is discussed for configuring services, ports, and protocols. The use of various startup parameters, trace flags, and startup routines are discussed in the context of configuration. In terms of troubleshooting, the use of the Dedicated Administrator Connection (DAC) is shown in a step-by-step walkthrough. Further troubleshooting is given via SQL Server Management Studio (SSMS) including: reports (CPU, IO, log space etc), use of sp_configure, error logs, and activity monitor.
Monitoring using T-SQL is shown via: sp_who2, sp_WhoIsActive, sys.dm_exec_connections, and sys.dm_exec_sql_text ().The use of global and local trace flags is outlined. With larger systems, multi-server management becomes increasingly important, it is discussed in the context of Central Management Server (CMS) and the Utility Control Point (UCP). The use of advanced tools including SQLDumper.exe and SQLDiag.exe is outlined.
This chapter is by necessity loosely tied together, this is to be expected since the components of monitoring and troubleshooting are wide ranging. The use of the DAC is well worth reading about – one day it may save your career!
Chapter 5 Automating SQL Server
A lot of SQL Server administration work is repetitive, and as such is ripe for automation. The chapter starts with a look at maintenance plans, which is essentially a GUI on top of a SQL Agent job. Plan wizards exist for DBCC, shrink, index reorgs/rebuilds, update statistics, backup etc. Additionally, for greater control, the maintenance plan designer is outlined.
Automation via the SQL Server Agent is described in depth, including jobs, schedules, operators, and alerts. A useful example of getting notified when the transaction log passes a threshold is given. The configuration of SQL Server agent jobs is given in detail.
Alerts are typically sent via email, as such a detailed discussion of the use of Database Mail is given (architecture, security, configuration, and archiving policy). Extending automation across multiple servers is illustrated with forwarding events to a centralised server, and creating jobs centrally and distributing them.
This is another easy to follow and practical chapter, with many step-by-step examples that will help improve many of your day to day admin tasks.