Author: Robert Pearl
Publisher: Apress
Pages: 408
ISBN: 978-1430267737
Audience: DBAs and developers
Rating: 4.5
Reviewer: Ian Stirk

Chapter 9 High Availability and Disaster Recovery

High Availability (HA) and Disaster Recovery (DR) are integral to ensuring your SQL Servers are healthy. The chapter opens with a bit of history concerning the evolution of HA/DR with SQL Server. The importance of business continuity and its planning are discussed, with reference to the business stakeholders, recovery time objective (RTO) and recovery point objective (RPO).

The chapter next looks at the various types of backups and recovery models. The importance of verifying your backups are good is emphasised. The various types of recovery model are described (simple, bulk_logged, and full). There’s a script that shows useful details on backups (e.g. when last backed up). Each of HA/DR solution has its own concerns, and these are discussed (e.g. mirroring allows only 1 failover target). The author rightly states that any type of HA/DR is better than none.

The chapter next looks in greater detail at backup and recovery. Backups are the first line of defence, so there should be extensive monitoring and notification around it. The backup and restore process should be fully documented in the runbook. Next, the importance of point-in-time restores are discussed, with a step-by-step walkthrough provided. The section ends with a look at database corruption, how it can be detected (DBCC CHECKDB/suspect_pages) and how to recover from it – the importance of having good valid backups is again emphasised.

The chapter then looks at how to configure and deploy the various HA/DR solutions, namely: 

• Log Shipping – well-established

• Mirroring – allows fast automatic failover

• Clustering – a Windows technology

• Availability Groups – a combination of mirroring and clusteringThe chapter ends with a look at the runbook in the context of HA/DR. It should contain details of what to do in a DR scenario, containing contingency plans, emergency handbook, names, places, addresses, phone numbers, contacts, colo details, VPN, logins, passwords – i.e. all the information to get the company back up and running. There should be up-to-date print and online versions.

This chapter covers a critical aspect of healthy SQL Servers. Without due consideration of HA/DR, one day you may not have a business. There are useful overviews of the various HA/DR technologies, together with implementation details.

The chapter says log shipping needs the recovery mode to be full, this is incorrect, it also works with the bulk_logged recovery model.

Chapter 10 Surviving the Audit

Much of the information given in the previous chapters has been building toward this chapter, which aims to enable you to pass a health check audit. This chapter shows you how to retrieve audit-related information, including using SQL Audit, default trace, DDL triggers, and transaction logs.

The chapter looks at the interesting area of data forensics, which can be used to retrace DML/DDL statements, identify data pre/post transaction, recover deleted rows, and prove data breaches. Additionally, regulatory requirements for some industries require you keep an audit trail (e.g. SOX).

The next section provides guidelines for creating a database compliance policy, all of which will help with an audit. The areas covered include: 

• Control Access to the Database (give people/service accounts minimum rights. Have Access Control List. Review regularly who should have access)

• Separation of Duties (keep dev and prod environments separate. Have approvals process, and test evidence to implement changes. Clear separation of roles)

• Monitoring (follow up schema changes, failed logins etc)

• Backup/Recovery (store written documents outside network, safe and secure in case of disaster. Document testing. Review backup jobs and schedule, ensure current)

• SQL Code (should be managed and versioned in source code repository. Also have deployment doc. Deployable scripts and rollback scripts. Tested and verified by UAT)

• Change Control (controlled and standardized method of implementing changes. Change tickets with tracking) 

Next the transaction log is discussed. It is possible to read the transaction log and reverse the data changes, however using Change Data Capture (CDC) or SQL Audit is typically easier. A step-by-step walkthrough of reading the transaction log is provided.

SQL Audit is discussed briefly, this allows the auditing of instance and database level events, including: SELECT, INSERT, UPDATE, DELETE, and EXECUTE. Step-by-step examples of creating an Audit object via SSMS at both the instance and database levels are given.

The chapter continues with a look at DDL triggers, these are invoked when a DDL change occurs (e.g. alter a table). It is possible to rollback any change from within the trigger. A simple example is given.

The default trace runs in background, and records various important events (e.g. configuration changes). It can prove very useful in determining when an object was dropped, and by whom! Code is provided to periodically capture and store configuration changes. The chapter next looks at Change Data Capture (CDC), which records DML changes. An example of setting up CDC is given.

The chapter ends with a look at Policy-Based Management (PBM), this allows you to define policies and best practices (e.g. backups not on same drive as database data files). An example is provided that implements a SQL password enforcement policy.

This chapter covers several areas that together can provide information to pass a SQL Server audit. Plenty of helpful step-by-step walkthroughs are provided. There’s a useful script concerning SQL Login security (e.g. shows if account is locked etc).

The chapter mentions Backup/Recovery was covered in chapter 8, but it was in chapter 9. Perhaps the use of XEs to record audit information could have been mentioned here too.

Conclusion

The book aims to ensure your SQL Servers are healthy. It certainly covers many tools, techniques, and best practices. There are plenty of step-by-step walkthroughs, relevant code examples, helpful diagrams, and links to useful websites throughout the book. The book should take your level of understanding of SQL Server health, from 2 to 8.

Reading the initial chapters, I had expected the book to provide distinct separate checklists to enable me to check the health of my SQL Servers. I expected the checklist to tell me what data values where acceptable, and details of how to correct bad data values. However, this is not the case. In many ways, the book as a whole becomes the source of your checklists.

I found some of the initial chapters muddled - maybe because there is a lot to say on the subject, and the topics overlap and intertwine. Many tools are introduced and then discussed in more detail later, I would have preferred the tool usage to be centralised in one chapter, and earlier in the book. I found the book’s style too chatty and informal, but this might suit other readers.

A few areas contained obvious spelling mistakes e.g. statiscs, exeucet ,  “dot able scan”, “than0for”. Some of the links given between the chapters were incorrect. The query output is often difficult to read, owing to bad formatting. Sometimes, the language used was a bit lax (e.g. log shipping requires the full recovery model). All these faults should have been caught by the reviewers/editors.  

If you want to ensure your SQL Servers are healthy, with respect to performance, security, stability, audits, and business continuity, I can recommend this wide-ranging book.