Windows Failover Cluster With SQL Server
We are moving away from using VMWare over to Windows Failover Cluster (version 2019) to host our company services. I have successfully configured WFC and have a few Virtual-Machine roles running along with the File-Server role and DHCP-Server role. Next is to get our SQL server instances (also version 2019) setup and I am posed with an architectural question.
Is it acceptable to install SQL server clustered role directly onto the bare-metal WFC hypervisors? Let me take a step back..
We want SQL server to be highly-available, just like anything else in the cluster. Following articles such as https://www.mssqltips.com/sqlservertip/6586/clustering-sql-server-2019-step-by-step/ it shows how to setup an always online SQL server instance, which uses WFC as a foundation to facilitate failover between the SQL server instances.
My thought is, why waste resources by firing up two new virtual machines, turning these two machines into a WFC, then installing SQL AO into that WFC when it can just be done in the parent instance? It seems overly redundant, the sql servers can failover within the virtual WFC and each vm can failover within the parent WFC. It seems like this was designed to be installed directly on the main WFC instance alongside other roles.
What is best practice here?
SQL Server running on top of VM in the cluster will be highly available. Failover Cluster with Hyper-V will handle VMs Failover. MS best practices states that Hyper-V should be the only role installed on a host.
https://docs.microsoft.com/en-us/windows-server/virtualization/hyper-v/best-practices-analyzer/hyper-v-should-be-the-only-enabled-role
You can deploy nested failover cluster though. Shared VHDX could be used to configure SQL Server Cluster.
https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-r2-and-2012/dn265980(v=ws.11)
Check the following article to improve SQL FCI performance.
https://www.hyper-v.io/can-sql-server-failover-cluster-instance-run-twice-fast-sql-server-basic-availability-groups-2-node-cluster-part-2-studying-fci-performance/