SIOS SANless clusters

SIOS SANless clusters High-availability Machine Learning monitoring

Achieving SQL Server High Availability Disaster Recovery With DataKeeper

by Leave a Comment

Achieve SQL Server High Availability, Disaster Recovery With A Mix Of Always On Availability Groups And SANless SQL Server Failover Cluster Instances

Introduction

The topic of mixing SQL Server Failover Cluster Instances (FCI) with Always On Availability Groups (AG) is pretty well documented. However, most of the available documentation documents configurations that assume the SQL Server FCI portion of the solution utilizes shared storage. What if I want to build a SANless SQL Server FCI using Storage Spaces Direct (S2D), can I still add a SQL Server AG to the mix? Unfortunately, the answer to this question is no. As of today, this combination of S2D based SQL Server FCI and Always On AG is not supported. I previously blogged about this S2D limitation here.

However, the good news is you CAN build a SANless SQL Server FCI with SIOS DataKeeper and still leverage Always On AG for things like readable secondaries. You still have to abide by the same rules that apply when mixing traditional SAN based SQL Server FCI and Always On AGs, but most part to achieve SQL Server high availability is about the same.

DataKeeper Synchronous replication is commonly used between nodes in the same data center or cloud region, but you may want to replicate asynchronously to an additional node in a different region for disaster recovery. In this case, if you ever do have to bring the DR node online after an unexpected failure, you will have to scrap the Always On AG configurations and reconfigure them. This requirement is very similar to to what Microsoft published here in regards to restoring asynchronous snapshots of SQL Server Always On AGs running inside VMs.

Availability Groups

Essentially, a SANLess SQL Server Failover Cluster Instance with DataKeeper looks like a single instance of SQL Server as far as the Always On Availability Group Wizard is concerned. The configuration of the Always On AG is exactly the same as if you were creating just an Always On AG between two Standalone (non-clustered) SQL Server instances.

The real confusion arise in the fact that in this configuration all the servers reside in the same failover cluster. But the SQL Server FCI is only configured to run only on the cluster nodes where SQL Server was installed as a Clustered SQL Server Instance. The other nodes are in the same cluster. However, SQL is installed on those nodes as a Standalone SQL Server Instance, not a Clustered Instance. It’s a bit confusing. Essentially what is happening is that Always On AG’s leverage the WSFC quorum model and listeners. As such all the AG Replicas need to reside in the same WSFC, even though they typically do not run clustered instances of SQL Server. If you are completely confused that is okay, most people are confused when they first try to wrap their head around this hybrid configuration.

The real benefit in a configuration like this is that a SQL Server Failover Cluster Instance can be a better and more cost effective (more on this later*) High Availability solution than Always On AG in many circumstances, but it lacks the ability to offer a readable secondary replica. Adding an Always On AG readable secondary replica becomes a viable option to address this need. And using SIOS DataKeeper eliminates the need for a SAN for the SQL Server FCI, which opens up the possibility of configuring SQL Server FCIs where nodes reside in different data center, which also means support for SQL Server FCI’s that span Availability Zones in both Azure and AWS.

Please note that pictured below is just one possible configuration. Multiple FCI cluster nodes, multiple AGs and multiple Replicas are all supported. You are only limited by the limits imposed by your version of SQL Server.

This article seems to document the setup steps pretty well. Of course, instead of shared storage for the SQL FCI, you will use SIOS DataKeeper to build the FCI as I document here.

Image result for SQL Server FCI with Availability Groups

Basic Availability Groups

As of SQL Server 2016 a scaled down “Basic Availability Groups” became available in SQL Server Standard Edition, making this configuration possible even in SQL Server Standard Edition. Basic AGs are limited to a single database per Availability Group, a Single Replica (2-nodes). However, they do not support a readable secondary replica so their use cases in this hybrid configuration are very limited.

Distributed Availability Groups

Distributed AGs were introduced in SQL Server 2016 are also supported in this hybrid configuration. Distributed AGs are very similar to regular AGs, but the Replicas do not need to reside in the same cluster, or even in the same Windows Domain. Microsoft documents the the main use cases of Distributed Availability Groups as follows:

  • Disaster recovery and easier multi-site configurations
  • Migration to new hardware or configurations, which might include using new hardware or changing the underlying operating systems
  • Increasing the number of readable replicas beyond eight in a single availability group by spanning multiple availability groups
Image result for distributed availability groups

Summary

If you like the idea of SQL Server FCIs for SQL Server high availability, but want the flexibility of read-only secondary replicas, this hybrid solution might just be the thing you are looking for. Traditional SAN based SQL Server FCIs, and even Storage Spaces Direct (S2D) based FCIs, limit you to a single data center. SIOS DataKeeper frees you from the limits of your SAN and enables configurations such as SQL Server FCI that span Availability Zones or Cloud Regions. It also eliminates the reliance on the SAN, allowing you to leverage locally attached high speed storage devices without giving up your SQL Server Failover Cluster Instance.

* How to Save Money

Earlier I promised I would tell you how to save money by doing this all with SQL Server Standard Edition. If you can live with readable replicas that are point in time based snapshots, you can skip Always On AGs completely and just use the SIOS DataKeeper target side snapshot feature to periodically take an application consistent snapshot of the volumes on the target server without impacting ongoing replication or availability. Here’s how…

http://discover.us.sios.com/rs/siostechnology/images/10-Ways-Save-AlwaysOn-vs-Failover-Clustering.pdf

Create a 2-node SQL Server FCI with SQL Server Standard Edition and save a boatload of money on SQL licenses. And yet still replicate the data to a 3rd node outside the cluster for reporting or DR purposes. If you take a snapshot of the volumes on this third server these snapshots are read-right accessible. This way, you can mount those databases from a standalone instance of SQL Server to run month end reports, copy to archives, or you might even want to use those snapshot to quickly and easily update your QA and Test/Dev environments with the latest SQL data.

I hope you found guide to create to achieve SQL Server high availability, disaster recovery with a mix of Always On Availability Groups and SANless SQL Server Failover Cluster Instances useful.

SQL Server on Linux High Availability Features and Considerations

by Leave a Comment

SIOS will be speaking at Microsoft SQLSaturday event

Speaker Jason Aw, Strategic Business Development, SIOS Technology Corp, will be sharing in this one hour session about SQL Server on Linux High Availability Features and Considerations.

With SQL Server on Linux, Microsoft brings SQL Server’s core relational database engine to the growing enterprise Linux ecosystem. High Availability and Disaster Recovery (HADR) are aspects of SQL Server that are critically important and in this session we discuss the features, limitations and options for High Availability and how SANless clustering can ensure proper functionality, availability and reliability for SQL Server on Linux, on-premise and in the Azure cloud.

Jason is a passionate IT leader with over 20 years of experience in technology, infrastructure, cloud environments particularly with high availability and disaster recovery solutions for different architectures including SQL database applications. For more information on SIOS, please contact here.

Track: Track 3

Level: Intermediate

You Are Invited To Join Us At SQLSaturday

SQLSaturday is a free training event for Microsoft Data Platform professionals and those wanting to learn about SQL Server, Business Intelligence and Analytics.

This event will be held on Aug 18 2018 at Microsoft Singapore Operations Pte Ltd, #22-01 One Marina Boulevard, Singapore, Singapore, 018989, Singapore

REGISTER NOW

About The Event

Welcome to SQLSaturday

We are proud to host SQLSaturday for the 3rd consecutive year in Singapore. SQLSaturday is a training event for SQL Server and data professionals who want to learn how to elevate their careers to the next level.

Admittance to this event is free, all costs are covered by donations and sponsorship. Please register soon as seating is limited, and let friends and colleagues know about the event.

It is a great opportunity to learn from Microsoft Product Team, Microsoft MVPs, SQL Server authors, and professionals who have been through and can relate to the obstacles you have day to day. This one day training event is unlike any other providing free food, training, networking, and also provide opportunity to win a few prizes!

Date & Time: Saturday, August 18th, 2018 – 8:30 AM to 5:30 PM

Cost: Free

Venue: #21-01 One Marina Boulevard, Singapore – 018989

Webinar – Five Options For SQL Server High Availability On VMware

by Leave a Comment

Webinar – Clustering 101 – Five Options For SQL Server High Availability On VMware

As part of my Clustering 101 Webinar Series I take a look at five options for providing high availability for SQL Server running on VMware. The webinar was recorded and for those interested, you can watch it here here.

http://discover.us.sios.com/asset-reg-webinar-clustering-101-vmware-sql-server.html

Reproduced with permission from https://clusteringformeremortals.com/2015/01/30/clustering-101-five-options-for-sql-server-high-availability-on-vmware/

Storage Switzerland: SQL Server High Availability at Low-cost Cloud prices

by Leave a Comment

Many organizations are considering the cloud for enhancing SQL Server high availability and business continuity. This is especially true for those small medium-sized businesses that don’t have the budgets to implement redundant data center infrastructure to support high availability for their most critical SQL Server business systems. But the challenge is many application clustering technologies are not capable of integrating with cloud infrastructure. As a result, IT may be left holding the bag by having to separately manage local and remote SQL server high availability clustered application infrastructure.

Click to register for the webinar “HA and DR Must Haves for Cloud Based MS-SQL”
There are no shortage of clustering solutions for MS-SQL. Many of these technologies have been around for years and are proven, however, some of these offerings were not designed to interoperate in a private or hybrid cloud environment. In fact, many of these solutions require the use of local shared storage like SANs, to enable application failover across local hosts. And since cloud storage is not “SAN-like”, it cannot be recognized by these clustering products as an application failover storage target.

To get around this issue, IT administrators need to manage the local failover cluster separately, replicate data to a secondary data center for DR purposes and then migrate VMs to the second site when a failure occurs. Alternatively, they could stand-up a totally redundant “cold stand-by” server infrastructure in their secondary site or rent server CPU resources from a cloud provider. In any of these scenarios, the business is going to incur added costs, added complexity and added business risk.

On the other hand, clustering solutions that can be used in a SAN or SANless environment and support local and remote application failover, regardless of the DR topology, (Private, Public or Hybrid Cloud) give businesses the opportunity to protect their critical systems, simplify operations and avoid overspending on infrastructure.

By taking a software-defined approach to SQL application clustering, IT organizations can use low-cost commodity storage in their primary data center location and replicate their critical data to cost-effective cloud storage resources to lower their DR total cost of ownership. The key, however, is for these solutions to fully automate the application failover process, regardless of the underlying storage infrastructure, to help ensure application uptime and help maintain application service levels.

To learn how to attain enterprise class SQL application availability with low-cost commodity cloud infrastructure, join us for a webinar on October 29th, “HA and DR Must Haves for Cloud Based MS-SQL”.

Click To Register: https://www.brighttalk.com/webcast/5583/129981