Page 2 of 2
Author: Randolph West et al
Publisher: Microsoft Press
Audience: DBAs and developers
Reviewer: Ian Stirk
Part III. SQL Server management
This section covers the more traditional aspect of database administration, monitoring, automation, recovery, High Availability (HA) and Disaster Recovery (DR).
The section opens with a look at the detection and prevention database corruption. As usual, some very insightful tips are provided. I often feel the DBCC option REPAIR_ALLOW_DATA_LOSS
should be called something more dramatic (REPAIR_BUT_YOU MAY_SERIOUSLY_LOSS_YOUR_DATA?!), there’s a useful link to Paul Randel’s ideas on this setting. The importance of valid backups is emphasised - this is probably the DBAs most important task.
The importance of index fragmentation and up-to-date statistics is discussed in relation to performance. Monitoring usage via DMOs (DMVs/DMFs) and Extended Events (XEs) are discussed with simple examples. Useful methods for collecting monitoring data are outlined. New software upgrades and patches are always of interest to DBAs, in the latest versions of SQL Server service packs (SPs) are retired and replaced by scheduled cumulative updates (CUs) – you just need to know when is a good time to test then apply them to your live servers.
With a greater focus on saving money, there has been increased movement towards task automation, which additionally often provides improved performance and reduces human error. Various automation components are discussed, including SQL Agent, Database Mail, Maintenance Plans, Master/Target servers, and PowerShell. There’s a useful section on troubleshooting database mail, with helpful code. There’s a very useful tip about using dbatools to further enhance your use of PowerShell for SQL Server - I cannot recommend this tool highly enough.
The next chapter covers what is undoubtedly the most important area for DBAs, backup and recovery. All relevant topics are covered with expert insight. This leads naturally to a discussion of the related topics of High Availability (HA) and Disaster Recovery (DR). Configuration, design, and administration of the various options is discussed. There’s a useful table comparing the capabilities of the various HA/DR technologies.
In many ways, this section covers the more traditional and most important SQL Server administration areas. As always, rather than step-by-step implementation walkthroughs, the authors outline a topic and then discuss its salient points, together with some expert tips.
Part IV. Security
With data breaches increasingly being reported, the importance of security cannot be overemphasised. This section opens with details on how SQL Server implements security via authorization. Topics covered include: authentication modes (including Azure AD), server and database principals, and moving logins and permissions. There’s a very helpful section covering common security problems together with their solutions (e.g. fixing orphaned SIDs).
Closely aligned to permissions, the next chapter looks at the privacy aspect of security via encryption, and auditing. Various types and layers of security are discussed in relation to thwarting attacks. There’s a very useful background primer on protocols to get you up to speed.
The information in this section should be read by all DBAs, it contains an array of features and tips to ensure only authenticated logins have authorized access to appropriately protected SQL Server data.
Part V. Performance
Performance is at the heart of many day-to-day DBA activities. The first chapter in this section covers all the usual suspects, including: concurrency, isolation levels, execution plans, parallelism, and query store. Any one of these areas could lead to substantial query performance (or equally degradation).
The second chapter in this section concentrates on indexes, these are probably the primary means of improving query performance. Topics covered include clustered, rowstore, columnstore, filtered, and missing indexes. The importance and use of statistics is noted. The chapter ends with an overview of the other indexes i.e. full-text, special, and XML. There’s an interesting discussion on using heaps or rather not to use them. There’s also a useful section on gathering metrics on index usage.
Part VI. Cloud
Increasingly there is a movement away from on-premiss databases to cloud hosted databases. I have a friend at Microsoft who said their biggest problem was trying to keep up with the demand for cloud solutions.
This section opens with a background explaining the various ideas and terms behind the cloud. This progresses into separate chapters on provisioning Azure SQL databases, both the vanilla and managed types. To help with the move to the cloud, there’s a very helpful chapter on migrating SQL Server solutions to Azure. The section on the Azure Data Migration Assistant is particularly useful (also excellent for migrating to non-Azure databases too).
While the use of on-premiss SQL Servers will continue into the near future, the move towards cloud usage continues unabated – definitely a topic to invest your future career growth in.
Part VII. Big Data and Machine Learning
In addition to the move towards the cloud, another recent directional change has been the use of Big Data and Machine Learning (ML). With the growth of data in recent times, relational databases have been unable to process the data in a timely manner, instead, big data systems are able to distribute the processing over many server, then collect and reduce the results.
Various Microsoft technologies have been able to plug into these big data systems, providing a familiar (and more user friendly?) interface to users.
The first part of the chapter discusses Big Data Clusters and PolyBase – what they are and how to use them. The latter part of the chapter provides a brief look at ML – but I’m not sure how useful it will be to the reader (there is much more to know).
Although ML can work with large volumes of data, I don’t think there was a need to present these two topics within the same chapter.
This book aims to update your DBA skills to cover SQL Server 2019, and succeeds. It is generally easy to read and filled with useful incidental tips that come from experienced DBAs. Sometimes it feels like overhearing experienced DBAs talk about their subject. Of course, no book can be comprehensive, but it is both wide-ranging and has a depth of detail.
Typically, Database Administration books are filled with step-by-step instruction to perform various tasks. This book takes a different approach, it assumes you know how to perform many tasks (or provides an overview for you), and instead concentrates on areas that contain pitfalls and best practices.
In many ways, it’s a perfect follow-on book to read after you’ve read a more standard admin book. I would suggest you first read Peter Carter’s Pro SQL Server 2019 Administration, see its details in Book Watch, and then read this current book. Disclaimer: I was the technical reviewer for “Pro SQL Server 2019 Administration”, but not for the first edition, which I did review for I Programmer.
Verdict: Overall, an excellent SQL Server 2019 catch-up book for seasoned DBAs. Highly recommended.
For more recommendations of SQL Server books see Pick Of The Shelf - SQL Server in our Programmer's Bookshelf section.