SQL Server provides many features that you can use for high availability. Two of the primary availability features are branded as AlwaysOn features: AlwaysOn Failover Clustering Instances (FCI) and AlwaysOn Availability Groups (AG). These features use multiple copies of compute, storage, or both to increase availability for SQL Server applications.

Failover Cluster Instances

A SQL Server AlwaysOn Failover Cluster Instance (FCI) relies on Windows Server Failover Clustering (WSFC) and is the basis of many enterprise SQL Server deployments. FCI operates at the SQL Server instance level to protect all databases and resources within a SQL Server instance. A basic SQL Server FCI runs on two or more Windows Servers configured as a WSFC and requires shared storage between the servers. If an issue is detected on the server running the SQL Server instance, that SQL Server instance (leveraging WSFC functionality) will be restarted on another server in the WSFC and the SQL Server instance will bring all related databases online. Complete information can be found on the Microsoft Always On Failover Cluster Instances page.

Figure 2.   Basic WSFC storage configuration

Availability groups

Always On Availability groups (AG) provide a replicated environment for one or more SQL Server databases. Databases that need to failover together are placed in the same AG. When using Availability Groups, the main database or databases are known as the primary and data is replicated to one or more copies known as secondaries.

Figure 3.   Basic AG storage configuration

An important distinction between FCI and AG is that FCI protects an entire SQL Server instance and all of the databases it contains. By contrast, AGs are configured at the database level and protect a single database or a group of databases. Another important difference is that shared storage is used with FCI and storage for AGs are agnostic. There are many different ways AGs can be configured. Complete information can be found in the Microsoft article What is an Always On availability group.

Limitations

The availability features within SQL Server offer various ways to minimize disruption to business applications, but if a failover is required between SQL Server instances, it often results in a disruption of service. The following sections describe a few of the reasons and the possible results of these disruptions.

Dropped database connections

One of the main limitations of features in the Always On architecture is that when a failover occurs, active database connections are terminated and need to be reestablished. When using FCI, the active instance is terminated, and the SQL Server instance is started on another cluster node. With AGs, a role change occurs in which a secondary database assumes the primary role, and database connections reconnect to the SQL Server instance hosting the primary. Depending on the method used, whether they are read-only or read/write, and how the application connects to SQL Server will determine whether the application experiences a disruption of service. Graceful handling of a failover scenario requires careful planning and application changes, which may not be possible when using third-party applications. For business continuity, it is best to avoid a failover whenever possible, especially unplanned failovers.

Failover and recovery time

When a SQL Server instance is started or restarted, crash recovery is performed on every database in the SQL Server instance. Depending on the database recovery model, the amount of activity in the database, and several other factors, database recovery time can take seconds to hours in extreme cases. Accelerated Database Recovery was a feature introduced in SQL Server 2019 to accelerate this process and improve the experience. However, the best way to maximize uptime is to avoid the failover event if possible.

External application data

Another limitation is that there is often critical application data that lives outside of the database. This could be configuration files, large image files (such as FILESTREAM), and so on. This critical application data that is stored outside of the database needs to be protected, but it is not included with SQL Server HA features; another method must be used.

Resynchronization and failback

Following a failover event, the data needs to be synchronized to the original primary and possible a failback event is required to the original primary and secondary roles. SQL Server availability features do not provide automatic data resynchronization. This needs to be done manually or through custom scripts.