SQL SERVER high availability on VM devices

Question

Hello.

I'm considering how to maintain the high availability of SQL SERVER in a virtual machine.

First, we are considering using always On and MSCS, which are the high availability features of SQL SERVER

Second, we are considering whether high availability can be secured through VM redundancy.

As a second method, application and OS-level failures through VM redundancy and shared storage can secure some high availability through RAID configuration.

However, it is judged that related references are insufficient, so I want to check whether it is possible to secure high availability through VM redundancy.

Please share or guide related content.

ps. Various opinions were given in other articles, but since the opinions were based on Azure, I would like to ask an additional question whether it is possible to do this with SQL SERVER itself.

Answer 1

Hey,

SQL Server has different options to achieve high availability. Always On Availability Groups is one of the options. The configuration will be similar on your on-premises VMs to Azure VMs. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-overview?view=azuresql

Your hosts will need to have proper network connectivity and storage amount.

FCI could also be used in VMs. Configuration will differ depending on hypervisor. As an example:
https://core.vmware.com/resource/microsoft-sql-server-failover-cluster-instance-vmware-vsan-stretched-cluster

https://dba.stackexchange.com/questions/332157/where-to-store-db-files-for-sql-failover-cluster-instance-on-hyper-v

SQL FCI requires shared storage. If you need shared storage, you can look at StarWind VSAN as an option.

Cheers,

Alex Bykovskyi

StarWind Software

Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

Answer 2

That's a broad question, and I think it is a possible to give a definitive answer here. There are so many "it depends" here.

Before I go on, I should say that I am by no means an HA/DR expert. I have some knowledge, but I mainly work in a developer role, not as a DBA.

I would say that generally Availability Groups are considered as the "best" solution for SQL Server HA. But it is a complex technology, and I commonly see posts where people had problems because they had an AG, and they did not understand the technology well.

So if your staff are that good at Windows clustering and even less SQL Server Availability Groups, but they have good understanding of VM mirroring, the latter may be a better option for you.