We're in the early stages of planning a new database environment for our ERP and CRM applications (third-party). The goal is to use Always On Availability Groups for HA, rolling patches, read-only secondaries, etc. We want to use automatic failover, without the client application needing to be aware of the HA.
We've got one physical server that is quite massively specced. It's going to be running two instances of SQL Server: one for the ERP application that requires SQL Server 2016, and one for the CRM application that is better suited by 2017 (and this also allows us to give sysadmin rights to the CRM application vendor's support without affecting the ERP application).
We also have a pretty mature ESX infrastructure, and want to use some VMs as failover nodes. These won't be as decadently built as the primary server, but we will ensure acceptable performance for short outages or maintenance windows on the primary server.
My initial thought was to have two failover VMs: one for the ERP instance and one for CRM. It doesn't appear that a single server can be a member of multiple Windows failover clusters, i.e. I can't make an ERP cluster with the ERP VM and the physical server, plus a CRM cluster with the CRM VM and the same physical server.
If I were to instead build a three-node cluster, and control which nodes each instance can run on, would I be inviting any unpleasant side effects? My main fear is that quorum voting could get a bit weird if we start adding more database servers to the cluster, and we could have an instance go down unexpectedly even if all the nodes that can host that instance are still up. Is that even a risk, and are there reasonable ways to mitigate it? Are there enough possible complications that I should use a single, beefier VM that can handle both instances?
tl;dr:
Would I be shooting myself in the foot by building a three-node cluster where instance 1 can only run on servers A and B, and instance 2 can only run on servers A and C? Should I just build a two-node cluster (plus quorum member) and make sure server B can handle both SQL Server instances?
Best Answer
I cannot see any reasons as to why you would have any issues. Just be sure to add a file share or disk witness to the cluster, as that will ensure there is no "weirdness" in the quorum. As long as you set the AG's up correctly, I cannot see any reasons not to use your suggested approach. The only negative i can think of, is that it will be an additional environment to manage and maintain, but you are already doing that having 2 instances on your primary. Will you be using the VM's for the Read-Only connections?