Microsoft SQL Server is used in almost every organization worldwide and supports some of the world’s most critical applications. For business-critical applications, high-availability architectures are required to meet stringent business continuity requirements. These high-availability architectures are a combination of both hardware and software. Dell PowerStore and Dell PowerStore metro node complement the high-availability capabilities of Microsoft SQL Server to further increase availability, reduce recovery time, and avoid data loss when using two separate PowerStore or other supported arrays.

In addition to Microsoft SQL Server, Dell PowerStore metro node can be used as a fundamental storage architecture component to increase storage availability and mobility for multiple applications, storage arrays, and operating systems. This eliminates the need to implement and manage different storage availability strategies, applications, and operating systems for each environment.

Dell Technologies and the authors of this document welcome your feedback on this document. Contact the Dell Technologies team by email.

Author: Doug Bernhardt

Note: For links to other documentation for this topic, see the PowerStore Info Hub.

A typical SQL Server storage architecture consists of a single SQL Server host connected through redundant storage paths to a storage appliance such as Dell PowerStore. This SQL Server architecture has many redundant features and works well to provide roughly 99.9%, or “three nines”, of availability which is 8.77 hours of downtime per year. For many organizations, this is perfectly acceptable. The system will be taken offline for short periods of time to apply upgrades and if something happens to the SQL Server host, the database recovery plan is to restore from a backup. Often, this is all achievable within the allotted downtime.

Figure 1.   Basic SQL Server storage configuration

The single points of failure still include the SQL Server host itself or the entire storage appliance. There will also be times when the host or storage appliance may need to be taken offline for an upgrade. If a site disaster occurred, the whole system would need to be reestablished at another site.

SQL Server relies on resilient storage hardware for reliable data storage. If the underlying storage hardware becomes unavailable, SQL Server takes databases offline in order to preserve data integrity. This action results in an outage to the SQL Server application and its users.

Resilient data storage architectures leverage redundant components wherever possible to reduce the possibility of component failure. Dell PowerStore leverages fully redundant hardware and high-availability features to achieve the 99.9999% availability1 design. Complete information about Dell PowerStore availability can be found in the Dell PowerStore: Clustering and High Availability paper.

________________

1 Based on the Dell Technologies specification for Dell EMC PowerStore, April 2020. Actual system availability may vary.

However, there are many more components in the application stack other than PowerStore, such as power, cooling, networking, application servers, and so on. An outage of any single component can impact availability.

Designing additional fault tolerance into an architecture to improve uptime and add “nines” of availability typically involves eliminating single points of failure and adding nondisruptive transitions to redundant components whenever possible.

The next few sections of this paper describe some approaches to improving fault tolerance, using SQL Server features and Dell PowerStore metro node, and how to combine both to improve fault tolerance.

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.

A key principle in achieving continuous availability is reducing single points of failure and ensuring that redundant components complete failover in a nondisruptive fashion. As discussed above, SQL Server failover can be disruptive to applications and interrupt business continuity. Therefore, providing a consistent, resilient storage connection across redundant storage appliances to SQL Server and data external to SQL Server can reduce failover events and increase availability.

Dell PowerStore metro node adds a storage virtualization layer to the storage infrastructure. Once the metro node cluster is in place, storage is presented from one or more underlying arrays. From this pool of storage, virtual volumes are created in metro node. These virtual volumes have their own Logical Unit (LUN ID) and World Wide Name (WWN), which gives them their own identity and makes them unique from a host or server perspective. The virtual volume is then presented to the host and appears like any other volume. The key benefit of a virtual volume is that it provides a consistent identity and connectivity, regardless of the storage appliance on which the data resides. This allows data to move between arrays, and the creation and deletion of mirrored volumes across two arrays, without requiring any outage from an application point of view. The virtual volume can be configured for true active-active synchronous replication either locally or over metro distances with multi-site dual access. This provides zero RPO and RTO for storage.

Virtualizing the storage details behind the PowerStore metro node cluster ensures that SQL Server, or any storage consumer, has a consistent, reliable storage connection across one or more arrays. This includes PowerStore appliances, Dell arrays, or any array type supported by metro node, in one or more locations. This eliminates downtime due to data migrations, storage appliance upgrades, and unplanned failures.

Each metro node cluster consists of two hardware nodes based on the Dell R640 PowerEdge server platform. The dual node architecture provides resiliency within the metro node architecture by providing redundancy of every metro node hardware component.

In addition to resiliency and virtualization, another key component to reducing or eliminating downtime is the time required to make a decision when an event occurs. This is known as the decision time objective (DTO). Other architectures may not support automated failover or resynchronization and therefore require key decisions to be made by business stakeholders, weighing the effort required to failover and then to resynchronize the data and failback. These decisions and approvals take time and increase down time. PowerStore Metro node handles this decision automatically allowing for a zero DTO and therefore reducing or eliminating downtime.

Metro node can also be configured as one or more appliances in either a local or metro configuration. Metro node Local is the term used in a local configuration, such as the same local data center. Metro node Metro is used when multiple appliances are deployed in different locations. A metro node Metro configuration fulfills the storage replication requirement when building SQL Server Multi-Subnet (stretched) clusters. A metro node Metro configuration greatly simplifies the storage configuration of Always On failover cluster instances. When FCIs are configured using metro node, the storage configuration on the host is the same, regardless of the number, type, or the location of the arrays, because the SQL Server hosts are mapped to metro node rather than to the underlying storage appliance. This also allows FCIs to be expanded without disruption.

Metro node is independent of SQL Server features. Therefore, older versions of SQL Server can benefit from increased resiliency. It is not necessary to upgrade to the latest version of SQL Server to benefit from metro node.

Microsoft SQL Server high availability features can be enhanced by adding PowerStore metro node Local. Whether SQL Server is deployed as a standalone instance, a Failover Cluster Instance (FCI), or Always On Availability Group (AG), each deployment model can benefit from added storage resiliency.

A simple architecture diagram illustrates how a basic SQL Server configuration is enhanced with PowerStore metro node Local.

Figure 4.   Basic SQL Server configuration with PowerStore metro node Local

Adding metro node to the SQL Server FCI storage architecture adds additional resiliency and can prevent cluster failover due to storage related events, both planned and unplanned. In this architecture, the shared storage requirement of an FCI is provided by metro node and enhanced with nondisruptive array failover.

Figure 5.   WSFC storage configuration with metro node Local

Always On Availability Group architectures can benefit from metro node as well. Adding PowerStore metro node to the storage architecture allows nondisruptive storage failover to a secondary array to occur without initiating an AG failover that would occur with a single array.

Figure 6.   AG storage configuration with metro node Local

PowerStore metro node Metro enables metro node capabilities over distance. By adding a second metro node cluster in another location, storage resiliency can be stretched between data centers or multiple buildings. This provides an alternative for product-specific data replication mechanisms for extended cluster or geo-cluster applications.

For example, in a metro node Metro configuration, one metro node cluster is placed at Site A and another is placed at Site B as shown in the example below.

Figure 7.   WSFC storage configuration with Metro node Metro

In a metro node Metro configuration, storage network traffic is local to the site and cross-site communication is handled by the Ethernet network. In this example, in the case of a local storage failure at Site A, IO traffic would flow across the ethernet network between metro node clusters and access storage at Site B. At this point an administrator could decide whether to failover the compute to Site B as a planned event, or to wait until the storage issue at Site B is resolved, metro node performs automatic resynchronization, and all IO traffic is returned to normal at Site A.

The same architecture pattern can be applied to SQL Server Always On Availability Groups (AG). In this way, additional protection against site failure is added while maintaining persistent storage connectivity to the applications in the event of a single site storage failure that would otherwise introduce a SQL Server instance failover and likely an unplanned outage.

It is recommended that distance between metro node Metro clusters is less that 100km and the Round Trip Time (RTT) over the inter-cluster link is no more than 10ms. See the Dell Metro Node Product Guide for more information.

Even the most resilient storage appliances are subject to user error, power failures, site failures, periodic maintenance, and hardware refreshes. Adding a redundant array as a replication target, using array-based replication or SQL Server replication, will protect the data and prevent data loss. However, in both scenarios, a failover and interruption of service must take place as the host is remapped to a storage target or the failover cluster instance or availability group instance moves to a different host.

In a typical multi-array environment without metro node, planned or unplanned storage outages would result in a service interruption as the storage volumes are reconnected or cluster failover occurs.

Virtualizing storage volumes from the physical array and maintaining a consistent and reliable storage connection minimizes or eliminates downtime. Metro node allows a virtual volume to be mirrored across supported arrays of the same or different type. Virtual volume mirrors can be created, synchronized, and unmirrored without any disruption to the host. If the mirror is severed, the mirror will self-heal when the connection to the mirror is re-established.

Relocating SQL Server databases due to data center moves and infrastructure changes can be challenging.  If databases are large, establishing, testing, and monitoring a process to move data reliably can be time consuming. SQL Server contains functionality to migrate databases, but often there is data that is required by the application that resides outside of the database, such as configuration files, images, and so on. A separate process is then needed to transfer this data, adding to the complexity. After the data is transferred, typically an outage needs to be scheduled to perform final synchronization tasks and a cutover of the application from the old location to the new location.

Metro node expands the data mobility features of PowerStore and complements the functionality of SQL Server by allowing data to be moved non-disruptively between any supported storage devices. This allows data mobility not only between supported Dell arrays, but between other vendor arrays. Some common uses include data center consolidation, hardware maintenance, moving data and applications over distance, and load balancing between dissimilar storage.

Because metro node is a separate appliance, it can perform mobility tasks at enterprise scale. Metro node supports up to 25 Local and 25 Metro concurrent migration sessions. It also supports queuing additional migration sessions for performing large-scale migration tasks.

PowerStore snapshots are a fast, space efficient way to create backup copies of SQL Server databases, but they are only crash consistent. Dell AppSync is the recommended method of creating snapshot copies of SQL Server databases. AppSync leverages the Microsoft Volume Shadow Copy Service (VSS) to create application consistent and fully aware snapshots of SQL Server databases.

AppSync provides even greater value in PowerStore metro node environments. In a metro node environment, the metro node appliance is registered with AppSync, and AppSync discovers the arrays behind it and performs the same snapshot orchestration operations through the metro node appliance. This saves several steps in the manual snapshot process of not only creating the snapshot but also presenting the volumes to metro node, and then presenting metro node virtual volumes to the host.

When using AppSync with or without metro node, SQL Server database protection and repurposing is the same simplified experience. All the details of storage snapshots and volume presentation are automated by AppSync.

Detailed information about PowerStore and AppSync can be found in the Dell PowerStore: AppSync paper.

Each organization has specific uptime requirements and service levels that need to be met. For those organizations looking to achieve greater than 99.9% availability, Dell PowerStore metro node can be used to build additional resiliency into the storage layer to reduce failover events and increase uptime.

In addition to increasing resiliency, Dell PowerStore metro node mobility features can be used to perform nondisruptive data migrations between Dell or Dell and third-party arrays, reducing planned maintenance events and failovers to further improve business continuity.

The following Dell Technologies documentation provides other information related to this document. Access to these documents depends on your login credentials. If you do not have access to a document, contact your Dell Technologies representative.

• PowerStore Info Hub
• Dell.com/powerstoredocs
• Dell PowerStore metro node documentation
• Dell PowerStore metro node product guide
• Dell PowerStore: AppSync
• Dell PowerStore: Clustering and High Availability

The following Microsoft documentation is referenced in this document:

• Windows Server Failover Clustering with SQL Server
• SQL Server Multi-Subnet Clustering
• What is an Always On availability group
• Always On Failover Cluster Instances