Shawn Meyers (@1dizzygoose), Principal Architect

In this post I will discuss High Availability (HA) and list native SQL Server types along with their pros and cons. This is a brief overview and while there is a lot more to each of these topics, I will be covering the basics.

Overview of High Availability (HA)

High Availability is basically uptime, but most SQL Server native tools are fast recovery and not true HA. True HA involves removing single points of failure to ensure the workload always works.

Native SQL Server HA Methods Reviewed

Log Shipping

Log shipping is a method for automating the backup and restore of transaction logs on a second server. It tends to be used a lot more for DR than for HA, but it can allow for a delay that protects against corruption. However, you will need to be careful with log backups. Log shipping owns the backup chain, so any other log backup will cause chain issues. You will also need to consider that only one replica can exist and log shipping also involves a per database set up.

Mirroring

While mirroring is depreciated, it is not going to be removed any time soon. Mirroring involves two methods: synchronous (high safety), which is available in all editions and asynchronous (high speed), which is available only with Enterprise edition.

Factors to consider with Mirroring:

• Synchronous can slow down production if the latency between the mirrors is too slow
• This involves a per database setup
• Users and jobs are not mirrored and you will need to replicate these yourself
• Automatic failover requires a witness, which is another SQL Server instance (SQL Express can be used)

SQL Server Clustering (official name is FCI)

Clustering requires a quorum, an odd number of votes that can include a shared drive, another SQL Server instance, or a file share. A SQL Server instance can move between multiple servers, which are interconnected and the entire instance moves all databases and objects – everything.

Factors to consider with Clustering:

• The VIP (virtual IP) concept is introduced, the VIP moves with the instance
• A failover is like a reboot of the SQL Server except the OS is already up
• Storage presentation can be tricky, work closely with your SAN admins
• Maximum of two nodes with Standard edition and 16 nodes with Enterprise edition

Availability Groups

The easiest way to understand Availability Groups is they are a mix between Mirroring and Clustering, and are available only with Enterprise edition. Availability Groups can also be combined with FCI.

Factors to consider with Availability Groups:

• Works on a group of databases
• Users and jobs are not replicated, you need to move these yourself
• Can have synchronous and asynchrony standbys at the same time (total of five instances in 2012 and nine instances in 2014)
• Still requires a quorum
• Synchronous instances can be made read only

Virtual High Availability (HA)

All virtualization platforms have some sort of High Availability (HA) option. All virtual machines are protected in this manner as it is already built in. In the event that the host dies, the process is to reboot the virtual machine.

Factors to consider with Virtual HA:

• Reboot takes about five minutes
• Rolling patching is not possible
• Simplest SQL Server setup

VMware Fault Tolerant

VMware Fault Tolerant offers virtually no down time. One server replicates all changes to a second server. If one crashes, the other will take over and the transaction will be completed.

Factors to consider with VMware Fault Tolerant:

• Possible FT latency
• Limited to only four vCPU, not for large workloads
• Been around for a while, but was not usable until vSphere 6.0, which was new in March 2015

Conclusion

You can virtualize your SQL Server clusters. But first you need to review your business rules to see if you can simplify your environment with no clustering. When planning your architecture, consider atomicity, agility, performance, and scalability. If you follow these guidelines, your cluster virtualization initiative is well on its way to be successful.