Chapter 5 Working with Disaster Recovery Options 

Ensuring your databases are available after problems occur is high on the list of important DBA tasks. This chapter opens with a good overview of the options SQL Server provides for High Availability (HA) and Disaster Recovery (DR), including: 

• Always On Failover Cluster

• Always On Availability Groups (AOAG)

• Database mirroring

• Log shipping

• Replication 

Each option is discussed only briefly, and some have supporting diagrams – however the diagrams are not explained. The chapter then discusses replication in more detail, providing a step-by-step walkthrough on setting it up. Configuration of database mirroring and log shipping is discussed with useful screenshots. The importance of having a DR plan that is regularly tested is highlighted.

This chapter provides some useful content, but leaves a lot unanswered. Some formulae/terms are given but not explained (e.g. MTBF, failover quorum, witness server).  I would have expected the chapter on AOAG to follow next, since DR and HA do have a degree of similarity, but we have to wait until chapter 9.

Chapter 6 Indexing and Performance 

A significant part of database administration involves solving performance problems. This chapter discusses some underlying system internals, together with tools to monitor, detect, and correct these problems. Additionally, indexes are perhaps the primary means of improving query performance, hence the grouping of these topics into a single chapter.

The chapter opens with a useful overview of SQL Server protocols (i.e. TCP/IP, Shared Memory, and Named Pipes). Next, the chapter looks at the work of the Query Processor, with a useful discussion on the cardinality estimator and how it is used to help create a more accurate execution plan costing. There’s a helpful section on database-scoped settings that were previously set at the server-level (MAXDOP, parameter sniffing, and Query Optimizer fixes), and a useful section on what to look out for when examining the execution plan (e.g. row counts, warning messages) .

The importance of performance monitoring is outlined, and the value of having a performance baseline is noted – although this is not linked to the discussion of having a baseline given in Chapter 3.

The chapter proceeds with a look at some of the common tools used in monitoring performance, including: 

• Activity Monitor

• Performance Monitor

• SQL Profiler and SQL Trace

• Extended Events

• Dynamic Management Views (DMVs)

• Management Data Warehouse (MDW)

• Query Store 

For each tool, a brief overview of its functionality together with how to use it is given. Helpful screenshots and some step-by-step walkthroughs are provided.

Next, the chapter changes track and discusses various aspects of indexes. Indexes are a primary means of improving performance. The chapter discusses the various types of indexes (i.e. heap, clustered, non-clustered, columnstore, and others).  There’s a useful list of index recommendations. The chapter ends with a discussion of common performance problems together with how to go about solving them – generally useful advice.

This chapter contains some useful areas, however, like some other chapters these are offset by poor use of English, muddled thinking, together with unsubstantiated and incorrect assertions.

Some sample concerns are:  

• There are some minor problems with the use of English at times, e.g. “Dynamic management consists of a huge set of views and table-valued functions intended for the querying.”

• Various terms are used but not defined (e.g. LOB pages, OPTION (RECOMPILE))

• Section incorrectly entitled “How to use dynamic management” (it should read “How to use Dynamic Management Views”)

• Incorrectly states “SQL Server enables the creation of up to 499 non-clustered indexes on one table…”

• Weak/incorrect description of parameter sniffing and covered indexes

• Incorrectly says “Heap is not a complicated structure but it is absolutely useless for tables bigger than several rows.” (They are useful for fast loading of staging tables, writing log rows that are rarely queried, also temp tables are heaps!)

• Although performance monitor is mentioned, the use of Performance Analysis for Logs (PAL) to aid the analysis of the output is not mentioned 

• The chapter should link to the chapter on automation, defrag indexes etc

• The chapter says “There's a considerable difference between the estimated and actual execution plans.”, I would say this is largely untrue, the two are often or mostly the same 

This chapter is of mixed usefulness, it contains some useful detail, but is marred by slack language etc. I think this chapter would work better as two separate chapters, one concerning performance/troubleshooting, and another chapter about index performance – as in the first edition of this book.

Chapter 7 Planning Migration and Upgrade 

This chapter opens with various reasons why you might want to upgrade your SQL Servers, including using the latest functionality and the end of mainstream support. It helpfully notes that many SQL Server Enterprise features (e.g. in-memory OLTP) are now including in SQL Server 2016 Standard Edition – if you have service pack 1 or higher.

Next, the various types of upgrade methods are outlined (i.e. in-place upgrade, side-by-side migration, rolling upgrade), together with their advantages and disadvantages. There’s a helpful section on the use of the Data Migration Assistant to help you highlight any potential problems with your migration. Similarly, the System Configuration Checker is used to check any Windows related blockers. Also included is a useful discussion of the SQL Server Migration Assistant, helping your migration from other databases (e.g. Oracle, MS Access) to various versions of SQL Server.

There’s some useful step-by-step walkthroughs for the SQL Server migration process itself, and for upgrading to Reporting Services 2019.

It might have been useful to include a list of steps to perform immediately after any upgrade (e.g. set the Compatibility Level, update the statistics etc) – so you can take advantage of the upgrade! 

I do wonder if this chapter should be an early chapter in the book (perhaps after the installation chapter). After the last chapter, this chapter provides some welcomed normality – easy to read and structurally ‘correct’.

Chapter 8 Automation - Using Tools to Manage and Monitor SQL Server 2019 

DBAs on large systems with many databases tend to take advantage of automation to ensure their tasks are consistent and complete in a timely manner. The major scheduling tool is SQL Server Agent.

This chapter opens with a look at SQL Server Agent, describing with the use of helpful step-by-step walkthroughs the setting up of the Agent service, its properties, and database mail.

Next, it discusses Maintenance Plans, these are existing templates that cover many of the more common DBA tasks (e.g. update statistics). The importance of a backup strategy is emphasized.

The chapter ends with a discussion of the various Agent objects. The use of Operators, Jobs, and Alerts are discussed with walkthroughs. There’s a useful section on monitoring and troubleshooting jobs. There’s a good point about monitoring for a SQL Server error via the severity level rather than the many individual error numbers.

Generally, this is a well-structured and informative chapter. It contains plenty of walkthroughs, and some useful cross-references to other chapters.

This chapter would benefit by having a broader scope. Much more could have been done with PowerShell, there is only one incidental example of PowerShell usage here. There’s a wonderful set of PowerShell database automation functions available in dbatools - these should have been discussed with examples here.