Several mainline technology offerings from Microsoft allow you to design a tailored high availability solution (solutions in the field of high availability offers another giant - Oracle) that best matches your company’s availability needs. As mentioned earlier, this book focuses on the most viable and production-worthy options, including the following:
Windows Server Failover Clustering (WSFC) - WSFC provides the functionality to provide local high availability at the server instance level, basically allowing multiple nodes (server instances) to be linked together in active/active and active/passive modes that fail over to each other if one node becomes unavailable. A failover cluster instance (FCI) is a single SQL Server instance that is installed across WSFC nodes and possibly across multiple subnets. WSFC allows for up to 64 nodes to be clustered (on Hyper-V), with some restrictions. Typically between 2 and 4 nodes are clustered to provide both the power and availability needed for an application. Each node in the cluster is made aware of each other node in such a way that if one fails, the other takes over its resources (such as its shared disk). An application needs to be “cluster aware” in order to take advantage of this capability. An example of a cluster-aware application is SQL Server. This is considered to be a foundation requirement to many high available solutions, such as SQL clustering and the AlwaysOn availability groups configurations.
SQL clustering - You can define between two and eight SQL Servers running on different machines to act as points of failover if the “active” SQL Server ever fails. Typically run in an active/passive mode (but not limited to that), this cluster-aware capability guarantees, via the creation of a virtual SQL Server, that a client application can connect to and do work on this virtual SQL Server nearly all the time.
AlwaysOn availability groups - Now considered the flagship product configuration for SQL Server high availability and disaster recovery, the AlwaysOn capability builds on FCS across nodes and provides a powerful synchronous or asynchronous option for a database and the instances to be highly available with almost no downtime. Failover times are often measured in seconds rather than minutes or hours. This option can create numerous options for HA and distributed processing. With SQL 2016, as many as eight secondaries can be created as a part of a single availability group.
Database Snapshots - You can use the point-in-time database snapshot capabilities to create milestones when doing production mass updates, inserts, or deletions. This method provides a point to which to recover your data in case there is an issue without having to restore the database or bring it offline, thus maintaining your application’s availability.
Data Replication - SQL Server offers a highly efficient mechanism to distribute data to other locations for the purpose of maximizing availability and mitigating risk of failures. Data Replication identifies a publisher, distributor, and subscriber model to distribute data (replicate it) to another location with a full data integrity guarantee. Thus, a separate location can have an exact image of the primary database to be used as a failover point or as a location to maximize regional availability.
Log Shipping - SQL Server provides a mechanism that allows the transaction log of a primary database to be applied to a secondary copy of that same database. The end result is a hot spare, or at least a reasonably “warm” spare (only as hot as the last transaction log that got applied to it). It is available as a failover point, if needed, or as a place to consider isolating read-only processing away from the primary server for performance and availability factors.
Other hybrid or complete options - Other options are available, such as dynamically stretching warm and cold data to Azure (the cloud) with Stretch Database and availability groups, deploying big data options (on Azure), full Azure SQL database deployments, and a few other HA options in the cloud.
