Application High Availability and Disaster Recovery for SQL Server, Part 1

IT is all about configuring and maintaining computer hardware and software to support business needs. That means you need to utilize, maximize, and capitalize on the fastest and most powerful hardware and software available. However, Murphy's Law states, "Anything that can go wrong will go wrong." So, to minimize downtime and impact to the business, you need a high availability (HA)/disaster recovery (DR) plan in place for the hardware and software.

Related: 3 Reasons SQL Server is an Excellent Platform for Building Mission-Critical Business Solutions

In my initial series of posts for the SQL Server Pro IdeaXchange, I'll be focusing on high availability/disaster recovery solutions for Microsoft SQL Server. I'll explore each implementation and its pros and cons. Over the following weeks, I'll discuss popular HA/DR solutions for SQL Server, including:

Failover Clustering

Failover clustering solution is built on Windows Server Failover Cluster (WSFC) (or Microsoft Cluster Service (MSCS)) to provide high-availability protection for SQL Server at the instance level. A failover cluster is a combination of one or more servers, either residing in the same subnet or across multiple subnets. An instance of SQL Server is installed on all nodes in the cluster group but only one server can be active at any one time. The active server takes ownership of the single shared copy of the data. If the active server experiences failure, a passive node takes over the ownership of the data and becomes active.

AlwaysOn Availability Groups

Availability Groups is a super-charged database mirroring solution where a set of user databases, known as availability databases, fail over together as a single entity. Availability Groups consist of a primary replica, where the primary databases are available for read-write from the clients; and one to eight secondary replicas, which optionally can be configured for read-only from the clients. Availability Groups requires a Windows Server Failover Clustering (WSFC) cluster and fails over at the level of an availability replica.

Database Mirroring

Database mirroring provides high availability and protection for a single user database. Database mirroring provides database availability by mirroring the transaction streams from the principal server to a mirror server.

Database mirroring operates in the following modes:

Log Shipping

Log shipping is a low-cost solution that operates at the database level. A log shipping configuration includes one primary server and one or more secondary servers. Log shipping does not provide automatic failover, but involves a user-configurable interval between a transaction log backup of the primary server and when the secondary server restores the transaction log backup.

Replication

Replication is a publish-subscribe model. A replication configuration includes a primary server, referred to as the publisher, which distributes data to one or more secondary servers, referred to as subscribers.

Replication supports the following types:

Virtual Machines (Hyper-V, VMWare)

A virtual machine solution is a consolidation/high availability model where the entire operating system is ported from physical to a virtual machine. The OS and all of its applications (SQL Server included) are encapsulated inside one or more disk image files (i.e. *.vdi, *.vmdk, *.vhd/x) on the host machine. High availability for SQL Server can be achieved by backing up and restoring the disk image onto another host machine.

I hope you'll join me for an in-depth look at each of these options over the next few weeks!