Options for SQL server Failover Clustering?

Question

Due to some requirements, restrictions and app incompatibility I am being forced into using SQL on VM in azure instead of Azure SQL and Managed Instance.

What is the best way to go about failover clustering on Azure VM's for SQL. 2016 or 2019 SQL Standard Licensing, Microsoft Server 2016 or 19. Need to be able to backup the databases/servers.

I tried backing up a non sql cluster using azure backup and a Storage Spaces Direct Cluster, the backups didn't work. So is my only option SQL Always on or use a 3rd party backup software and use S2D again?

I just need a direction and some options, I can figure out the configuration.

Answer 1

Hi, I had to do something similar having hit a functional issue with Azure SQL.
I followed this document
https://learn.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-create-failover-cluster?sa=X&ved=2ahUKEwjI8YX4odznAhWD34UKHe7nBoAQ9QF6BAgIEAI

It’s a little out of date but the approach seem to work well.

On the backup side you’ve got a couple of options;

Azure backup for SQL server
SQL backup on board the cluster but send the files to a mounted Azure file store

We’ve used Azure backup for SQL but also pulled a copy of the nodes themselves via ASR due to the criticality of the dataset..

Hope this helps a little.

Answer 2

For the high availability for SQL VM I would recommend for always on high availability. Always On availability groups do not require deployment of a Failover Cluster Instance or use of symmetric shared storage (SAN or SMB) that can save on hardware investments.

Availability Groups, a license is required to run on the primary server. The standby server is only covered by a license if it comes with Software Assurance.