2

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?

0

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?

  • To clarify, you're recommending a three-node cluster with each of the two instances only allocated to two nodes? We'll probably find some uses for read-only secondaries, though the primary server is enough of a monster that it will likely be fine for most of our analytic workloads, when long-lived locks aren't a problem. – db2 Feb 11 at 15:55
  • @db2 Yes correct. I am recommending the 3 server approach. – WadeH Feb 11 at 16:03
-1

Given that you are

planning a new database environment for our ERP and CRM applications

and

also have a pretty mature ESX infrastructure

This:

running two instances of SQL Server:

Is not really a good idea. Sharing a Windows VM and a larger cluster creates unneeded complexity and risk, with basically nothing useful in exchange.

Just create seperate VMs for each of the two instances, and run them on the same ESX host if you want.

For each one you can add a secondary VM with an AG replica for HA.

  • 1
    The primary server is an overbuilt monster (half a TB of RAM), so it makes plenty of sense to throw two instances of SQL Server at it. It's got enough RAM to easily keep the entirety of both application DBs completely in memory, with plenty to spare for SSAS and SSRS. But it's just running bare-metal Windows and SQL Server, so I can't simply provision a couple VMs on it. The VMs on our existing ESX cluster will be the HA. – db2 Feb 12 at 1:55
  • Cool. Then a 3 node cluster makes lots of sense. – David Browne - Microsoft Feb 12 at 4:30

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.