SQL Server Failover strategies

In my infrastructure I have a SQL Server 2014 Standard standalone server.

As databases and applications which are using SQL Server are growing up, I want to upgrade to SQL Server 2016 Standard and install two nodes to get an highly available SQL Server infrastructure.

I know I could make a SQL Server Failover Cluster, but I need some shared storages for this, which could not be a problem because I have a production NAS/SAN available but it's very slow as it is for data purposes and not for application purposes.

As my virtualization nodes have some fast SSD local storage, I wish to use them to host SQL Server data, so I am wondering if there is a way to build a SQL Server HA cluster without a shared storage, but making a master-slave replication between two nodes on their own local storage, something like what I am already doing for MariaDB with MariaDB Galera Cluster.

I also have an HAProxy available.

Thanks!!

Solution 1:

In SQL Server 2014 Standard Edition, your only built-in option for automatic failover without shared storage would be database mirroring.

If you upgraded versions to SQL Server 2016 Standard Edition, or if you upgraded editions to 2014 Enterprise Edition, you'd get Always On Availability Groups, which is like database mirroring but for groups of databases that can fail over together.

There are lots of other options that don't give you automatic failover (my personal favorite is log shipping, for example) but since you said a failover cluster, I assumed you want automatic failover with zero data loss - in that case, mirroring is all you've got for 2014 Standard.

Solution 2:

If you upgrade to SQL Server 2016 Standard Edition, you will be able to use the Basic Availability Group which is a limit version of Always On Availability Group of SQL Server Enterprise Edition. It support failover and doesn't require shared storage.