Author: Peter Carter
Publisher: Apress

ISBN: 978-1484207116
Print: 1484207114
Kindle: B0157W4JNE
Rating: 5
Reviewer: Ian Stirk

Part III: Security, Resilience, and Scaling

This part opens with a chapter that looks at SQL Server 2014’s complex security model. Security is examined from the point of view of the domain, local server, instance and database. Having security at various levels provides additional safeguards against intruders. The chapter proceeds with a look at the associated topics of instance and database auditing.

The part continues with a chapter on encryption, starting with a look at the encryption hierarchy. This is followed with a look at Transparent Data Encryption (TDE) which allows the entire database to be encrypted. The chapter ends with a look at cell-level encryption, which provides a granular level of encryption (column or row), the performance impact of this is discussed.

Next, there is a chapter on High Availability (HA) and Disaster Recovery (DR) concepts. The levels of availability are described in relation to the 9s (e.g. 99.99%), and the importance of Service Level Agreements (SLAs) is noted. HA/DR technologies are discussed from an architectural view (detail follows in subsequent chapters), and considers: AlwaysOn Failover Clustering, Database Mirroring, AlwaysOn Availability Groups, and Log Shipping. Combining these technologies is also discussed.

In the next three chapters details are provided on how to implement: AlwaysOn Failover Clustering, AlwaysOn Availability Groups, and Log Shipping respectively. These chapters are detailed, with step-by-step instruction. Database Mirroring is omitted since it’s superseded by AlwaysOn technology.

The next chapter is the most important part of most DBAs work, backups and restores. Backup fundamentals are examined from the viewpoint of recovery models (simple, bulk-logged, full) and backup types (full, differential, and log). Then the various backup strategies are discussed, together with GUI walkthrough and SQL code examples. Finally, various aspects of restores are examined, including SQL code, point-in-time restores, file and page level restores.

The next chapter looks at scaling workloads, this allows more work to be processed without locking and provides additional resources. The chapter concentrates on the use of database snapshots, replication, and AlwaysOn secondaries. I’m surprised no mention was made of PolyBase, HDInsight, and Hadoop, in relation to achieving true scalability.

This part provides very useful detail on security, HA/DR, and the most important part of being a DBA: backups and restores. The part has an odd mix of chapters. I think the chapters on security and scalability need to be in other areas (even have their own parts).

I would have liked to see some reference made to ensuring backups are valid (e.g. regularly restore them and run DBCC CHECKDB on them). I speak from experience, having been involved with a corrupt live database, only to discover 3 missing transaction logs, making a restore impossible.

Additionally, you should have a document that details, step-by-step, with diagrams, the process to follow during a HA/DR problem – you really don’t want to do this in a pressure situation.

Part IV: Monitoring and Maintenance

This part opens with a chapter concerning metadata (e.g. DMVs, system functions). Examples are provided on using Performance Counters, Waits, and finding and tuning the most expensive SQL queries. There’s an interesting section on using metadata to automate some standard DBA practices (e.g. rebuilding fragmented indexes). The corroboration of waits and performance counters could have been explored since problems are sometimes not how they initially appear.

The next chapter looks at locking and blocking. Locking is essential to support data integrity. The chapter explores the various levels of lock granularity (from key to database), and the compatibility of the different types of locks. Deadlocks are then explored, these are circular locks that would never be released, so one of the processes needs to be killed. Various considerations for reducing deadlocks are discussed. The various ACID properties of transactions are discussed with example code, and in relation to transaction isolation levels – together with the problems they may involve. In-memory OLTP uses an optimistic model, this obviates the need for locks and latches, and this is discussed in relation to retry logic. Some useful code is provided showing: long running transactions, locking, and deadlocks. Perhaps SQL code should have been provided to decode the deadlock information given in the system health Extended Event session?

The next chapter discusses Extended Events (XEs), a lightweight monitoring framework, and preferred to the deprecated SQL Trace/Profiler. Various XE concepts are explained before creating a XE session via the GUI and SQL code. Perhaps more useful examples could have been given (e.g. long running SQL queries, blocking).

The next chapter looks Distributed Replay utility, allowing you to replay traces, ideal for testing software updates, performance tuning and consolidation testing. It’s recommended that traces are captured via XE sessions (SQL Profiler can also be used). The chapter explains how to configure the utility, and shows an example of its usage. Potentially, this is an exceedingly useful utility.

The next chapter looks at SQL Server’s facilities that allow automation of maintenance tasks. Firstly, SQL Agent is examined, a job is created (e.g. delete old backups) and scheduled. An example alert is also created, idea for proactive monitoring. The chapter next looks at multiserver jobs, allowing servers to be grouped together for processing purposes, configuration details are explained, and example jobs given. The chapter ends with a look at maintenance plans, these use a SSIS GUI to automate common DBA maintenance tasks, and their usage is explained in a step-by-step walkthrough. The use of SSIS to expose further automated tasks is discussed.

The next chapter looks at Policy-Based Management (PBM), a tool for enforcing policies on your servers/databases (e.g. ensure backups are not on same drive as data files). The chapter discusses the PBM concepts (facets, conditions, targets, and policies), before creating and executing some example policies.

The next chapter looks at the Resource Governor, this allows resources (CPU, memory, IO) to be restricted by use of a Classifier Function (e.g. users running reports may have less resources than OLTP users). The concepts (resource pool, workload group, classifier function) are explained before creating examples and monitoring them.

The last chapter in this part relates to triggers, these are inbuilt routines that run in response to certain events. The 3 types of triggers are explained: DDL triggers (changes to objects), Logon triggers, and DML triggers (changes to data). DDL and logon triggers are explained in detail since they are commonly used in admin work.

This part described the many features that can be used in monitoring and maintenance of database systems. The content is easy to read, comprehensive, and full of useful examples and tips Again, I’m uncertain of the grouping of monitoring together with maintenance, are they really related features?

Part V: Managing a Hybrid Cloud Environment

This part opens with a chapter that looks at using the cloud for backups and restores. The cloud has advantages of High Availability and cheapness. The chapter provides step-by-step walkthroughs of the tasks needed to create backups on Windows Azure, scheduled automation of backups, together with the simple restore process.

The next chapter looks at storing the SQL Server data and log files in Windows Azure, while hosting the node on your own premises. Additionally, some data files can be hosted locally, providing a hybrid setup – very useful for having recent data locally and historical data in the cloud. Similar to the previous chapter, the prerequisites for cloud storage are described, together with the steps to create the database in Windows Azure.

This part ends with a look at migrating to the cloud, sure to be of increasing importance. Databases can be hosted using either Windows SQL Server VM which offers Infrastructure as a Service (IaaS) or Azure SQL Database which offers Database as a Service (DaaS) – the former offers greater SQL Server functionality, the latter means Microsoft takes care of the hardware platform, OS etc. The chapter discusses how to create and migrate databases to both options.

This section of the book is missing from many other SQL Server admin books, but is becoming an increasingly important area of businesses focus. Some very useful step-by-step setups are given.

Conclusion

This is a big, wide-ranging book, containing plenty of real-life advice and experience with sensible and practical guidance. The book is generally easy to read, with useful explanations and discussions, step-by-step walkthroughs, and plenty of relevant screenshots. Useful code, tips, and solutions are provided throughout. Each chapter ends with a helpful summary. Having a single author certainly helps provide a consistent level of explanation, and removes unnecessary repetition.

Although not stated, the book is aimed at existing DBAs, and perhaps curious developers, that want to know the capabilities of SQL Server 2014. Additionally, junior DBAs will learn a lot from this book.

The book should certainly have “2014” added to its title, its absence only causes confusion. Some areas are perhaps a bit too prescriptive (e.g. memory to leave for the OS). That said, if you do follow the advice you won’t go far wrong. Some of the groupings of chapters in the parts seem odd (e.g. including Security with Scaling). Sometimes, terms are used before being explained (e.g. DMVs, SAN snapshot). It might have been useful to have a section on where to go to get further information (newsletters, blogs, websites etc). I did wonder, with the imminent release of SQL Server 2016, if this book’s release is badly timed.

Any misgivings or criticisms I have of the book are certainly minor in terms of the book’s overall content. I’ve reviewed more than 30 SQL Server books over the last three years, and this is the best SQL Server administration book I’ve read. The author is to be congratulated on his diligence.