Home > Storage > PowerVault > White Papers > Dell PowerVault with Metro Node and Microsoft > SQL Server Always On features
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.
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. For complete information, see the Microsoft Always On Failover Cluster Instances page.
Always On Availability groups (AG) provide a replicated environment for one or more SQL Server databases. Databases that need to fail over together are placed in the same AG. When using Availability Groups, the main database or databases are known as the ‘primary’; data is replicated to one or more copies known as ‘secondaries’.
An important distinction between FCI and AG is that FCI protects an entire SQL Server instance and all the databases it contains. By contrast, AGs are configured at the database level and protect a single database or a group of databases. Another significant difference is that shared storage is used with FCI. Storage for AGs are one or more copies depending on the number of replicas being used. For complete information, see the Microsoft article What is an Always On availability group.
The availability features within SQL Server offer several 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.
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, and whether they are read-only or read/write, 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.
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. However, the best way to maximize uptime is to avoid the failover event if possible.
There is often critical application data that lives outside of the database. This could consist of configuration files, large image files (such as FILESTREAM), and so on. 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.
Following a failover event, the data needs to be synchronized to the original primary and a failback event is required to restore 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.