SQL SERVER ALWAYS ON AG- MULTIPLE STACK ON SINGLE SERVER POSSIBILITY

Question

SQL SERVER ALWAYS ON AG- MULTIPLE STACK ON SINGLE SERVER POSSIBILITY

Vidya 21

We currently have Always ON with AG in 2 nodes Active/passive. We are planning for additing another stack on same server creating one more instance alwaysON 2 nodes which would be opposite of above stack that is passive/Active so that we could utilize passive CPU cores.

stack 1: ( server A with Instance 1 Active and server B with Instance 1 passive)
stack 2: ( server A with Instance 2 passive and server B with Instance 2 Active)

Please let us know the pros and cons of this configuration? and the MS recommended best practices for the requirement mentioned.

Accepted answer

3 additional answers

Your answer

Answer 1

David Browne - msft 3,851

Please let us know the pros and cons of this configuration?

Pros:
The SQL Server Instances are more isolated for memory utilization, patching, and administration

But that's about it.

Cons:
More complicated
Much less efficient use of server memory

and the MS recommended best practices for the requirement mentioned.

Don't use multiple instances.

You should simply add a second Availability Group on the existing instances unless there's some very good reason not to.

If you really need that level of separation between workloads, you should use seperate VMs.

Vidya 21 Reputation points

2020-11-12T10:19:51.25+00:00

Hi David, thanks for the clarification.
I have another question around the same, sorry for the confusion. I have server A and Server B with Always on FCI (shared DB)
And Server C is a DR site connected to server A with Always on AG.

So my question is , I already have a clustered instance created on Server A (which is Active lets assume and server B passive node). Can I have another clustered instance created on Server A (while I make server A as passive mode and server B as active)? will that be feasible ?

My intention is to utilize all the CPU cores from Active and Passive nodes all the time, since its a core-based licensing model.
David Browne - msft 3,851 Reputation points

2020-11-12T14:32:06.76+00:00

If you have a Failover Cluster Instance, that's different.

You can have two FCI's running on A and B. Just make sure that in case a server is down, the remaining server can handle the load.

And both instances can have an AG wit a replica on server C.

In this configuration you should use a Disk Witness with a disk that is mouted on A and B, or if you aren't using shared disks a File Share witness in the same data center as A and B. There is only one witness per cluster.
Manish Kumar Valecha 0 Reputation points

2024-05-09T09:58:09.4633333+00:00

Hi David, I totally agreed with you to create multiple availability group instead of multiple SQL instances on VM. I really appreciated if you can provide Microsoft best practice recommendation document on this so I can also convince to my client.

Answer 2

Shashank Singh 6,251

The configuration is supported. You need to make sure that the secondary replica you were using will not hold primary for other AG and hence it also needs to be fully licensed.

The normal pros and con which applies to AG will be applicable here. One thing to preplan is when lets say a node goes down. The other node will be holding 2 primary replicas so make sure you have enough RAM and CPU to support this scenario.

Vidya 21 Reputation points

2020-11-11T14:25:26.133+00:00

Thank you Shanshank. In that case, will there be 2 Quorum disks defined for each cluster?
Shashank Singh 6,251 Reputation points

2020-11-11T14:55:31.357+00:00

Are you using AG or SQL FCI ? If you have 2 nodes and SQL Server cluster like you are saying then you cannot configure AG on it. You need 3rd server for AG. Can you clarify ?
Vidya 21 Reputation points

2020-11-12T10:18:04.467+00:00

Hey Shashank , sorry for the confusion. I have server A and Server B with Always on FCI .
And Server C is a DR site connected to server A with Always on AG.

So my question is , I already have a clustered instance created on Server A (which is Active lets assume and server B passive node). Can I have another clustered instance created on Server A (while I make server A as passive mode and server B as active)? will that be feasible ?

My intention is to utilize all the CPU cores from Active and Passive node, since its a core-based licensing model.
Shashank Singh 6,251 Reputation points

2020-11-12T11:07:42.867+00:00

@Vidya

Can I have another clustered instance created on Server A (while I make server A as passive mode and server B as active)? will that be feasible ?

Yes you can add another clustered instance to utilize both servers. But like I said you would have to pay for license of passive server which now you want to make as active. You would also have to add new drives for new FCI. Is this recommended confirmation?, ahh lets say if you have powerful machines with lot of CPU and RAM yes you can use such configuration.

My intention is to utilize all the CPU cores from Active and Passive node, since its a core-based licensing model.

You are missing the point if you start using passive instance as active you would have to pay for its license. Like if you have 16 core active and 16 core passive you currently ONLY paying for 16 cores. if you go by this configuration of yours using passive instance as active you would have to pay for 16 more cores. Have you though about that ?
Shashank Singh 6,251 Reputation points

2020-11-12T11:09:39.267+00:00

For your quorum question, same fileshare quorum can be used for both the FCI's
David Browne - msft 3,851 Reputation points

2020-11-12T14:30:34.227+00:00

If you have a Failover Cluster Instance, that's different.

You can have two FCI's running on A and B. Just make sure that in case a server is down, the remaining server can handle the load.

And both instances can have an AG wit a replica on server C.

In this configuration you should use a Disk Witness with a disk that is mouted on A and B, or if you aren't using shared disks a File Share witness in the same data center as A and B. There is only one witness per cluster.

Answer 3

m 4,276

Recommend @Shashank Singh 's reply as Answer.

Answer 4

Tom Phillips 17,771

To SQL Server, these are completely independent and have no interaction. Same as if they were on different physical servers. The only problem is hardware resources need to be large enough to support both instances.

Share via

SQL SERVER ALWAYS ON AG- MULTIPLE STACK ON SINGLE SERVER POSSIBILITY

3 additional answers

Your answer