Author: Kellyn Gorman et al
Publisher: Packt Publishing
Pages: 488
ISBN: 978-1838826215
Print: 1838826211
Kindle: B088BNMRQ4
Audience: SQL Server DBAs and Devs
Rating: 3
Reviewer: Ian Stirk

Each new version of SQL Server introduces new features and enhances others. This book aims to discuss some of these features, how does it fare?

The book is aimed at DBAs and developers wanting to know more about SQL Server 2019. Many of the authors are well known experts, and some have worked directly on the creation of SQL Server. A brief glance at the table of contents might suggest this is no typical introductory book, with many of the subtopics being intermediate to advanced level.

Below is a chapter-by-chapter exploration of the topics covered.

Chapter 1. Optimizing for performance, scalability and real‑time insights

The book opens with an interesting comment, that perhaps with recent performance advances (especially in-memory OLTP and columnstore indexes), it may be feasible to query live data rather than build and query a data warehouse.

Columnstore indexes are briefly discussed, including their potential order of magnitude performance improvement. Next, in-memory OLTP is discussed, this technology often boosts performance by 2 to 30 times. There’s a useful section on using the Transaction Performance Analysis Overview report to identify which tables would benefit most from being moved from disk to in-memory. To get the most from in-memory tables, you need to write Natively Compiled Stored Procedures (which produces complied code, unlike traditional interpreted SQL code). 

The chapter next moves to looking at tempdb, which might be thought the most important database since it’s shared by all the databases on the instance, and is used by SQL Server itself. The new ability to move tempdb’s metadata to in-memory tables to give better performance is discussed (there’s no blocking etc).

Starting with SQL Server 2017, the term Adaptive Query Processing was used to group together various features that improved performance (e.g. changing the query’s join type dynamically), this has been extended in SQL Server 2019, and renamed as Intelligent Query Processing. Hmmm…. Microsoft do we really need a new name for essentially the same feature? 

Over the years, Microsoft has provided tools to help you monitor performance (e.g. Management Data Warehouse, DMVs etc). The latest addition is Query Store, this essentially persists performance details relating to your queries, for given time periods. Example usage is discussed. Although not as comprehensive as paid-for tools, it is a welcomed addition to help in the identification and solving of performance problems. You can read more about Query Store in my recent review of Query Store for SQL Server 2019.

Increasingly, Microsoft is looking at the automatic tuning of SQL Server. This section focuses on automatic plan correction, with a useful example – I’m not sure why automatic index management was ignored.

Next, another useful enhanced monitoring tool is discussed, the Lightweight Query Profiler. This uses significantly less resources than the standard query profiler. When Lightweight Query Profiler is enabled, you can view the live execution plan via Activity Monitor – a very useful feature. I do wonder why Extended Events (with corresponding definitions for traces) are not discussed.

The chapter ends with a miscellany of features: 

• DBCC CLONEDATABASE - clones database without data, has statistics so is useful for examining execution plans etc

• Estimating compression saving for columnstore indexes (new feature to existing routine)

• Troubleshooting page resource waits 

This chapter provides details of some very useful features. It is generally easy to read, has good explanations, diagrams, links, and code examples. The array of topics, are linked together under the theme of performance. While each subtopic (e.g. Query Store) is generally self-contained, the flow between subtopics is often abrupt with no overriding narrative. Similarly, there is little cross-referencing between chapters. These traits apply to the whole of the book

Chapter 2. Enterprise Security

Security is an increasingly important topic, with breaches leading to loss of reputation and business. It seems almost every release of SQL Server adds some new security feature. This chapter provides a useful overview of what’s on offer, including: 

• Data Discovery and Classification – allows you to identify, classify and tag your data. These can then have policies applied and be reported on

• Vulnerability Assessment – identifies current security concerns. Any problems are highlighted, and solutions proposed

• Transparent Data Encryption – encryption at rest. Prevents stolen backups/files being restored/queried

• Always Encrypted – encrypts sensitive information, which can only be read if client has key

• Dynamic Data Masking – limits data exposed to non-privileged users

• Row-Level Security – provides granular control over which rows a user/role can see

• Auditing – typically needed for regulatory reporting 

In each case, the feature is discussed with step-by-step walkthroughs and examples.

The chapter ends with a look at security in Azure SQL Database, covering firewalls, Azure active directory authentication, and Advanced threat detection. In each case, just a brief overview is provided – perhaps suitable links for more information should have been provided.  

This chapter provides a very useful, self-contained, overview of the many security features SQL Server provides. Always a useful reminder for all DBAs.

Chapter 3. High Availability and Disaster Recovery

Undoubtedly the most important part of a DBA’s job is ensuring data is available and correct. High Availability (HA) refers to recovering from local problems, and Disaster Recovery (DR) is similar but typically involves bigger problems e.g. loss of datacenter. The aim of both HA and DR is to get your systems available quickly after a problem. Planning is an integral part of HA/DR.

The chapter opens with a brief look SQL Server’s availability features, covering: backup and restore, Always On (i.e. Availability Groups and Failover Cluster Instances), and Log Shipping. The sections on backups and Log Shipping are minimal, just one page each. Next, the chapter look at a gamut of availability improvements in SQL Server 2019, including: 

• Accelerated Database Recovery – can significantly reduce recovery time, but may need more database storage

• Clustered columnstore index online rebuild – useful admin feature, if you have the memory

• Increased number of synchronous replicas in the Enterprise edition – useful for scalability and reporting

• Read-only routing configuration in SQL Server Management Studio – very useful for automatically taking load off the primary instance 

In each case, a brief description of the feature is provided. The section and code for Online builds or rebuilds for Clustered Columnstore Indexes is listed twice.

The chapter ends with a similar look at availability enhancements in Windows Server 2019, however some of the features described are not currently available to SQL Server, but may be in the future. Whilst SQL Server takes advantage of Window’s features, I do wonder if this section is off-topic.

This was a mixed chapter. Maybe the chapter/book needs to specify who the book is aimed at? If it is introductory (as suggested by the book’s title), then much more should be said about backup and restore. I’m not sure if the section on at availability enhancements in Windows Server 2019 was needed (especially if some of the features are not available to SQL Server).