SQL Server 2019 Administration Inside Out

Article Index
SQL Server 2019 Administration Inside Out
Parts III to VII; Conclusion

Page 1 of 2

Author: Randolph West et al
Publisher: Microsoft Press
Pages: 992
ISBN: 978-0135561089
Print: 0135561086
Kindle: B085P1HSC2
Audience: DBAs and developers
Rating: 4.5
Reviewer: Ian Stirk

This book aims to update your DBA skills to cover SQL Server 2019, how does it fare?

Each version of SQL Server offers both new functionality and builds on existing features. This book aims to help SQL Server DBAs expand and update their skills to include SQL Server 2019. Although the book primarily focuses on SQL Server 2019, much of it is applicable to earlier versions too.
The book is primarily aimed at experienced SQL Server DBAs, although experienced developers might also find it useful. Unlike most admin books, this one typically does not provide step-by-step walkthroughs of tasks, rather it gives an overview of the task and highlights any areas that may need more detail.
Below is a section-by-section exploration of the topics covered (each section contains multiple chapters).

Part I. Introduction
This section is very much an introduction to features that are typically explained in greater detail in subsequent chapters.
The book opens naturally with an overview on SQL Server installation. This is followed with a brief discussion of various useful tools and services (e.g. Machine Learning Services). A helpful outline of the main admin tool, SQL Server Management Studio (SSMS) is provided, together with its developer-focused counterpart, Azure Data Studio.

The section next looks at an overview of various database server components. Starting with memory, CPU, storage, and network – discussing architecture, and salient features. This is followed with an overview of High Availability (HA) concepts, security, and virtualization. I found this chapter to be an odd assortment of disparate features to group together.

The final chapter in this section looks at database infrastructure, discussing data files and files groups, transaction log, partitioning, compression, tempdb, configuration settings (e.g. MAXDOP), and using containers with Kubernetes. For each topic, its main points are discussed, together with advice for the later versions of SQL Server. Only in the discussion of Kubernetes is a step-by-step usage example provided.

Typically, enough detail is provided on a given topic to get started, together with helpful tips on any common problems. Step-by-step walkthrough instructions are not provided, so it expects a certain amount of experience already. A great many incidental tips are provided in each section, especially relating to the newer features. It could be argued this is the core strength of this book, with the advice coming from experienced SQL Server 2019 DBAs.

Overall, the section is easy to read, with lots of helpful tips, together with links to other chapters and websites for further and deeper investigation. In many ways, the book feels like you are having a conversation with experienced SQL Server DBAs, pointing out best practises and common pitfalls. These traits apply to the whole of the book.

Part II. Deployment

This section first takes a deeper look at installing SQL Server. Various prerequisites are discussed (e.g. service accounts, patching, versions, licencing). Next, the process of installing a new instance is outlined, covering Azure, Windows, and Linux. There is a useful section on stand-alone installation, ideal for repeatability and eliminating human error. An overview of installing the various Business Intelligence features (e.g. SSIS) is also included. Finally, a useful post-installation configuration checklist is provided, and items discussed include: max memory, SQL Agent, Power Plan setting, and anti-virus exclusion.

Later versions of SQL Server are available for Linux, and the next chapter discusses installation and configuration on Linux. Since it is a newer feature, detailed step-by-step instructions are provided, and links to further information provided. The Windows/Linux feature sets are not identical, and there is a useful discussion of their differences.

The section progresses with a look at provisioning and configuring SQL Server. Creating, moving, restoring, and upgrading databases are all covered – with outline instructions rather than detailed steps. Again, some interesting tips are provided (e.g. using the LEGACY_CARDINALITY_ESTIMATOR at the database scope). Various useful database settings are outlined, including: Recovery Model, Page Verify Option, and Compatibility Level. The chapter ends with a look at some of the newer features, including Query Store (useful for replacing slow execution plans), and Advanced Database Recovery (making rollbacks much quicker).

Continuing on from database features, the section ends with a chapter on table features. The chapter opens with a review of table design, a topic of critical importance, if design/implementation is done incorrectly, it can have significant impact of many areas (e.g. performance, scalability, security). The various datatypes are examined, and recommendations provided. The importance of constraints is discussed. There is a useful section on memory-optimized tables, extolling their benefits but also warning of their caveats. Table and indexes partitioning are explained with helpful diagrams, and usage guidelines provided.

Overall, this section contains useful introductions to topics, that quickly become more detailed. Again, plenty of tips are provided.

Prev - Next >>

Last Updated ( Saturday, 21 November 2020 )