How to get SQL SERVER high availability in VMs

Question

ello.
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.

Answer 1

Hello 고민재

Welcome to Microsoft Q&A Platform, thanks for posting your query here.

Regarding your first option, Always On and MSCS are both great options for achieving high availability for SQL Server. Always On Availability Groups provide a high-availability and disaster-recovery solution that can support multiple databases, while MSCS (Microsoft Cluster Service) provides high availability for a single instance of SQL Server.

As for your second option, VM redundancy can also provide some level of high availability. By using VM redundancy, you can ensure that if one VM fails, another VM can take over its workload. However, it's important to note that VM redundancy alone may not be enough to provide complete high availability.

In terms of securing high availability through VM redundancy and shared storage, RAID configuration can definitely help. RAID can provide redundancy and improve the reliability of your storage system. However, it's important to note that RAID alone may not be enough to provide complete high availability.

I would recommend taking a look at the following resources for more information on achieving high availability for SQL Server in a virtual machine:

https://learn.microsoft.com/en-us/azure/virtual-machines/availability

https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-overview?view=azuresql

https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/failover-cluster-instance-overview?view=azuresql

https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/business-continuity-high-availability-disaster-recovery-hadr-overview?view=azuresql

Hope this helps.

Answer 2

Hi @고민재,

Always On availability groups on Azure Virtual Machines are similar to Always On availability groups on-premises, and rely on the underlying Windows Server Failover Cluster. However, since the virtual machines are hosted in Azure, there are a few additional considerations as well, such as VM redundancy, and routing traffic on the Azure network.

The following diagram illustrates an availability group for SQL Server on Azure VMs.

Always On availability group on SQL Server on Azure VMs

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hey,

As mentioned, you can configure Always On HA on Azure VMs. You can also configure it (either Availability Groups or FCI) on-premises. If you want VMs to be highly available as well, it might be an overkill. VMs will handle the HA of you Databases/Instances, additional HA (on the host level) might lead to issues.
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/prereqs-restrictions-recommendations-always-on-availability?view=sql-server-ver16

You can also deploy multiple replicas to have additional redundancy. Might be helpful: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver16

As another option, you can deploy HADR scenario.
https://www.starwindsoftware.com/resource-library/starwind-virtual-san-sql-server-hadr-using-availability-groups-and-failover-cluster-instance/

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 4

In my case we have Azure SQL VMs configured as Shared Storage Failover Clusters (2 VMs using shared disks) combined with Availability Groups. That configuration works very well and we have HA/DR configured this way

Regards

Javier

Answer 5

Thank you all. However, I did not plan to use the Azure service, so I was researching how to use VM for LOCAL SQL SERVER operation. We will check the information you shared and conduct additional research. Thank you very much.