2 separate SQL clusters in 1 Windows cluster - possible?

[Mike Leone]

I have this strange request. Basically, I'll need 2 separate SQL servers, but running on 1 Windows cluster. I've been asked:

can we create two independent windows clusters without using named instance? For example, SQL12 and SQL13 but these will live within the same two VMs with their own servers' resources.

I'm thinking that a SQL Cluster Active/Active install (where each cluster node runs its own separately SQL named instance) is what he actually wants, but I haven't been able to track him down to ask (yet).

Is there some other way of doing a SQL install like what he wants - 2 differently named SQL "servers" that aren't actually just named instances of the SQL server in an Active/Active configuration? I don't think so ... (I have no idea what that other way gains us, but hey - I just work here ...)

(I mentioned SQL high availability, I was told to look into this instead. Again, dunno why, possibly licensing, I don't know. But we're going down this road, with SQL cluster(s))

Thanks

--

Mike. Leone, <mailto:tur...@mike-leone.com>

PGP Fingerprint: 0AA8 DC47 CB63 AE3F C739 6BF9 9AB4 1EF6 5AA5 BCDF
Photo Gallery: <http://www.flickr.com/photos/mikeleonephotos>

[Michael B. Smith]

I have literally never done it, but TIL you can have multiple SQL FCI instances on a single WSFC, given sufficient nodes.

 

Looks quite gnarly and it’s way out of the normal. I’d want a really really good justification for it.

 

A single WSFC hosting multiple AoAGs is fairly common.

 

Some references:

https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/install/create-a-new-sql-server-failover-cluster-setup?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-wsfc-with-sql-server?view=sql-server-ver16

--
You received this message because you are subscribed to the Google Groups "ntsysadmin" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ntsysadmin+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ntsysadmin/CAHBr%2B%2BgQvh-Y5eZw_o8YjGEd8DWEdcd5bYYKBMCJHwMJkfT0kw%40mail.gmail.com.

[Solodow, Damien]

When installing MSSQL in a failover cluster, you can set a different network name for each clustered SQL instance.

So say you have two cluster nodes (NODE1 and NODE2), you would create a failover cluster between those two nodes CLUSTER1 that has just the cluster name, IP and quorum.

You could then install a clustered SQL instance (can be default) into that cluster with a new name. That name (and associated IP) would be a cluster resource that the SQL instance depends on.

It’s been a while, but I’ve done it. 😊

Had a SQL cluster that was NODE1, NODE2, and managed as CLUSTER1. It had a couple SQL instances in it: OLTP1, OLAP1, SHAREPOINT

 

Damien​​​​ Solodow IS Senior Systems Engineer Gaylor Electric, Inc. 5750 Castle Creek Pkwy N Drive, Suite 400 Indianapolis ,  IN .  46250 O: 317.815.3103  |  M: 317.506.8521 317.759.0077 emergency IS support

 

From: ntsys...@googlegroups.com <ntsys...@googlegroups.com> On Behalf Of Mike Leone
Sent: Tuesday, October 17, 2023 2:24 PM
To: NTSysAdmin <ntsys...@googlegroups.com>
Subject: [ntsysadmin] 2 separate SQL clusters in 1 Windows cluster - possible?

 

I have this strange request. Basically, I'll need 2 separate SQL servers, but running on 1 Windows cluster. I've been asked:

--

[Mayo, Bill]

In the past, we have done what you describe with an active/active cluster and it worked fine. It was, however, a long time ago and was well before the current version of WSFC. We stopped doing clustering a bit ago and just picked back up on it in a small way, and it all feels quite different to me.

 

From: ntsys...@googlegroups.com <ntsys...@googlegroups.com> On Behalf Of Michael B. Smith
Sent: Tuesday, October 17, 2023 2:39 PM
To: ntsys...@googlegroups.com
Subject: RE: [ntsysadmin] 2 separate SQL clusters in 1 Windows cluster - possible?

 

EXTERNAL EMAIL: This email originated from outside of Pitt County Government. Do not click any links or open any attachments unless you trust the sender and know the content is safe.

To view this discussion on the web visit https://groups.google.com/d/msgid/ntsysadmin/a5a8484011b04442b78fe08624cf240d%40smithcons.com.

[Michael B. Smith]

Yeah but he wants NODE1, NODE2 with CLUSTER1 as a SQL FCI using the default instance. Then NODE3, NODE4 with CLUSTER2 as SQL FCI also using the default instance. All on a single WSFC.

 

IMO that’s uncommon.

 

From: ntsys...@googlegroups.com <ntsys...@googlegroups.com> On Behalf Of Solodow, Damien
Sent: Tuesday, October 17, 2023 2:43 PM
To: ntsys...@googlegroups.com

To view this discussion on the web visit https://groups.google.com/d/msgid/ntsysadmin/CH0PR04MB8129C3A838D15E2655828782BCD6A%40CH0PR04MB8129.namprd04.prod.outlook.com.

[Lance Soller]

I think you can do this with SQL AG's

To view this discussion on the web visit https://groups.google.com/d/msgid/ntsysadmin/cbb0dcff83a54a6b8207e11204c7d6d4%40smithcons.com.

--

Thank You,

 

Lance Soller

 

Tristate Computers, Inc.

718-256-1800 x 205

la...@TristateComputers.net

“An issue without a ticket is an issue that never happened”

[Rogue Platinum]

Multiple SQL instance roles can be set up within a single failover cluster. To specify the node on which a particular instance should be hosted, you can make use of the "Preferred owners" tab in the role's properties.

To view this discussion on the web visit https://groups.google.com/d/msgid/ntsysadmin/CAJ6FWM26PAM1jvPudsOWBgGFd8UX-o-1JFrzRfkA1PGOVuhBKQ%40mail.gmail.com.

[Mike Leone]

For some reason, he doesn't want named instances. By which I think he means he doesn't want to have to enter "SQL01\InstanceName" to connect ... but in an active-active configuration like this, you don't do that. You connect with the SQL network name (i.e., "SQL01").

Trying to explain how active-active worked to him was fun, BTW ... he kept thinking of it as active-passive. By which he meant that SQL01 would be active on NODE1 and passive on NODE2, while (at the same time) SQL02 was active on NODE2 and passive on NODE1. (the same 2 nodes, in both cases). I tried to explain that this meant 1 SQL instance was active at all times on *both* nodes, hence active-active. But I'm not sure it got through ...

What I'm considering now is a 3 node active-active cluster, so that there is always 1 node that is "passive", so that if either node needs to go down (say for patching), there's an empty node for the instance to migrate to, so no one node has to run both instances at once. That way, no node can be swamped by trying to run 2 instances at the same time. There is some cluster feature you can activate to do that, I remember testing it once ... (making each resource use it's own node). As long as 2 of the 3 nodes don't go down at once, of course ....

To view this discussion on the web visit https://groups.google.com/d/msgid/ntsysadmin/CAO4nQNR2knDq4%3DONeHYe5%3DtGhw5UJ8gODtDzK3e_zoFiLzVJEw%40mail.gmail.com.

[Bruce Roberts]

Do you own licenses for SQL Enterprise? If you do then you can build a SQL always on cluster - create an availability group - sync the databases - and establish an always on availability group "listener" for each named instance. This uses Windows Failover Clustering at the base of it. The listener is a virtual SQL host name - and it is treated / referenced / accessed as though it was a stand alone single SQL server. You can have one instance active on SQL1 box you can have the other instance active on SQL2 box. The listener is what you connect to via Management Studio and it is also the hostname you connect to for your application - its not referenced via server/instance - its just that virtual server - and answers on traditional port 1433. You do need SQL Enterprise licenses to build these.

Bruce

[Mike Leone]

On Thu, Oct 19, 2023 at 12:13 PM Bruce Roberts <brucerob...@gmail.com> wrote:

Do you own licenses for SQL Enterprise?

Yes.

 

If you do then you can build a SQL always on cluster - create an availability group -

I was told no availability groups. No, I don't know why.

 

sync the databases - and establish an always on availability group "listener" for each named instance. This uses Windows Failover Clustering at the base of it. The listener is a virtual SQL host name - and it is treated / referenced / accessed as though it was a stand alone single SQL server. You can have one instance active on SQL1 box you can have the other instance active on SQL2 box. The listener is what you connect to via Management Studio and it is also the hostname you connect to for your application - its not referenced via server/instance - its just that virtual server - and answers on traditional port 1433. You do need SQL Enterprise licenses to build these.

I asked about availability groups, and was told no. At which point, I say - hey, I just work here, and go do as I'm asked.

I've got around a half dozen active-passive SQL clusters ....

LOL

[Mike Leone]

On Thu, Oct 19, 2023 at 12:13 PM Bruce Roberts <brucerob...@gmail.com> wrote:

Do you own licenses for SQL Enterprise? If you do then you can build a SQL always on cluster - create an availability group - sync the databases - and establish an always on availability group "listener" for each named instance. This uses Windows Failover Clustering at the base of it. The listener is a virtual SQL host name - and it is treated / referenced / accessed as though it was a stand alone single SQL server. You can have one instance active on SQL1 box you can have the other instance active on SQL2 box. The listener is what you connect to via Management Studio and it is also the hostname you connect to for your application - its not referenced via server/instance - its just that virtual server - and answers on traditional port 1433. You do need SQL Enterprise licenses to build these.

Ok, I will admit I was wrong.  I thought I knew how to do an active-active where each SQL server would be accessed by just SQL1/SQL2. That I had to install 1 SQL cluster on 1 node as SQL1, with a default instance name. And then install another SQL cluster on node 2 as SQL2, also as the default instance name. But I just watched a YouTube video that showed you can't do that (I.e., install a 2nd cluster as default instance, the 2nd cluster has to be a named instance). That would make the 2nd cluster accessible only as SQL2\<instance name> ... Which we don't want, we want too access the 2nd cluster as just SQL2.

So: how do I set it up as you suggest, with availability groups? (Which I've never used) . Do you have a step by step guide link?

 

When installing MSSQL in a failover cluster, you can set a different network name for each clustered SQL instance.

So say you have two cluster nodes (NODE1 and NODE2), you would create a failover cluster between those two nodes CLUSTER1 that has just the cluster name, IP and quorum.

You could then install a clustered SQL instance (can be default) into that cluster with a new name. That name (and associated IP) would be a cluster resource that the SQL instance depends on.

It’s been a while, but I’ve done it. 😊

Had a SQL cluster that was NODE1, NODE2, and managed as CLUSTER1. It had a couple SQL instances in it: OLTP1, OLAP1, SHAREPOINT

That's what I want, I think.