| SQL Server AlwaysOn Revealed |
|
Author: Peter Carter This slim book is devoted to AlwaysOn, SQL Server’s High Availability and Disaster Recovery solution and aims to show you how to use this technology to reduce or eliminate system downtime. The back cover says the book: “...is adapted from Peter Carter’s Pro SQL Server Administration...” This is something of an understatement, since the text is lifted straight from that book. Its six chapters of this book are Chapters 11, 12 and 13 of Pro SQL Server Administration. This book is also from Apress and when I reviewed it I awarded a 5-star rating concluding: I’ve reviewed more than 30 SQL Server books over the last three years, and this is the best SQL Server administration book I’ve read. The book is aimed at DBAs, and perhaps curious developers. To get the most from this book you need some existing awareness of High Availability (HA), and Disaster Recovery (DR). Below is a chapter-by-chapter exploration of the topics covered. Chapter 1 High Availability and Disaster Recovery Concepts The book opens with a look at the levels of availability (i.e. uptime), measured with reference to the number of nines (e.g. 5 nines is 99.999% availability). The importance of Service-Level Agreements is noted, with their specified availabilities and financial penalties. The standard HA terms Recovery Point Objective (RPO – how much data loss is acceptable) and Recovery Time Objective (RTO – how long the application can be unavailable) are examined. The chapter continues with a look at the cost of downtime, explaining it’s often easier to negotiate an acceptable level of downtime when its costs are specified. Tangible and intangible costs are briefly discussed. The chapter ends with a brief look at the types of standby servers (i.e. Hot, Warm, and Cold). This chapter provides a helpful introduction to High Availability and Disaster Recovery concepts. There’s an interesting table showing the cost of downtime against the cost of the solution, for the various levels of availability – a useful logical approach to help determine your availability solution. Two of the savings calculations seem to be wrong, the value $525,474 should read $525,074, and the value $520,334 should read $520,344. The chapter is easy to read, very well written, with useful discussions, diagrams, code, tables, inter-chapter references, and chapter summary. These traits apply to the whole of the book.
Chapter 2 Understanding High Availability and Disaster Recovery Technologies Historically, as newer versions of SQL Server have been released, more capable HA and DR functionality has been provided. The chapter looks at AlwaysOn Failover Clustering, which consists of a group of servers (nodes) with shared storage, if one server in the group fails, another takes over the instance - this is especially suitable for large databases having a high number of writes. Examples are discussed of Active/Passive, Active/Active and Three-Plus node configurations, helpful diagrams support the text. The importance of a quorum for automatic failover is discussed. The chapter briefly looks at Database Mirroring, before moving on to AlwaysOn Availability Groups (AOAG) - which supersedes mirroring. AOAG is especially suitable for smaller databases with fewer writes. The secondary servers in AOAG, unlike older HA/DR technologies, can be used for reporting, backups, and extractions – relieving pressure on the primary server. AOAG allows a group of databases to failover together. The ability of AOAG to automatic repair pages is discussed. Log shipping is briefly discussed as a method of implementing DR. The chapter ends with a look at combining some of the technologies discussed previously (e.g. clustering with AOAG). This chapter provides a useful overview of the various common HA/DR technologies. There’s a very useful point about resource considerations for active/active cluster, you really don’t want to end up with resource problems in the event of a failover, so testing is of paramount importance.
Chapter 3 Implementing a Cluster Server engineers typically build clusters, which can have many configuration options. Since DBAs need to be very familiar with clustering (and may sometimes build the cluster), the chapter opens by discussing how to build a cluster. The Failover Cluster Feature is installed on each node in the cluster, via the Add Roles And Features Wizard, this is discussed step-by-step. Next, the various steps in creating the cluster are given, again in a step-by-step manner. The Cluster Validation Wizard ensures the environment is ‘correct’. Having built the cluster, the chapter next discusses how to configure it, for some of the more common configurations, including:
This chapter provides very practical instruction on how to build and configure a cluster. All changes are given in a step-by-step manner, with plenty of screenshots, and often with equivalent PowerShell code.
Chapter 4 Implementing an AlwaysOn Failover Clustered Instance This chapter discusses installing the AlwaysOn failover cluster instance of SQL Server, via the New SQL Server Failover Cluster Installation option, on the Installation tab of the SQL Server Installation Center. The various installation screens are examined in a step-by-step manner (i.e. instance configuration, cluster resource group, cluster disk selection, cluster network configuration, and service accounts). The Failover Cluster Manager is then used to show that the instance has been correctly installed. Code is also provided to install the instance using PowerShell. The chapter ends with a look at adding a second node to the cluster. This is achieved by logging onto the passive cluster node and selecting Add Node To SQL Server Failover Cluster from the Installation tab of the SQL Server Installation Center. Each of the subsequent screens is described in a step-by-step manner. Code is also provided to add a node using PowerShell. This chapter provides useful practical instruction on installing AlwaysOn failover cluster instance, and adding a second node.
Chapter 5 Implementing AlwaysOn Availability Groups AOAG cater for HA, DR, and use of the secondary for reporting, extracts, and backups. Code is provided to create 3 databases, each containing a single table, for use in the chapter’s examples. The chapter opens with a look at configuring SQL Server for HA, firstly by enabling it on the SQL Server service, and then following the steps of the New Availability Group Wizard – all steps are discussed with plenty of supporting detail. A script version of the changes is also given. The section ends with a brief discussion on the performance considerations of using Synchronous Commit Mode – and code provided to benchmark this. Next, the chapter looks at DR for the created databases, here another server is built on another site with a stand-alone version of SQL Server. The cluster now spans 2 sites, so is configured as a multi-subnet cluster, additionally the quorum is reconfigured to remove the shared storage dependency. The instance on the new node is then added to the Availability Group. Detailed instructions are provided for all these steps. The chapter ends with a brief discussion on using AOAG secondary servers for read-only tasks (i.e. reports, extracts, backups). Code is provided that makes the secondary replica read-only. This chapter provides a useful overview on implementing AOAG, and how it supports HA, DR, and very useful read-only functionality.
Chapter 6 Administering AlwaysOn Here we first look at cluster maintenance, which generally provides significantly reduced downtime. Common maintenance tasks include:
The chapter then looks at managing AOAG, including:
In each case, the relevant steps to perform the action are given. This chapter provides an overview of some of the more common AlwaysOn admin tasks. Having spent time previously debugging various AlwaysOn problems, I wished the section on monitoring was larger, and provided useful diagnostic code (e.g. what is current state of servers, what was the cause of last failover etc).
Conclusion This book aims to explain AlwaysOn, SQL Server’s High Availability and Disaster Recovery solution, and certainly succeeds. The book is easy to read, with useful explanations and discussions, step-by-step walkthroughs, and plenty of relevant screenshots. In many ways it reads like listening to a true expert discuss the technology, providing many of the tips he’s collected along the way. Providing some troubleshooting scripts would have been useful for investigating problems. Although not stated, this book is based on SQL Server 2014 - with SQL Server 2016’s imminent release (next month?!) I wonder if this book’s release is badly timed. For value-for-money, buy the author’s Pro SQL Server Administration book. If you’re interested only in AlwaysOn, then get this book. Highly recommended. 
|
|||
| Last Updated ( Tuesday, 12 April 2016 ) |