May 25, 2017 at 2:55 pm
My company is getting ready to make a major architecture change and one of the things we are trying to do is reduce our actual virtual server footprint.
Currently we are running 22 separate availability groups each with their own windows cluster across 44 servers (SQL Server 2014, Windows Server 2012R2 VMs).
We want to move to 6 servers to hold the 22 AGs, yes I know it doesn't divide evenly.
For example a given server (Windows Server 2016 VM, 20 vCPU, 64GB RAM) would be 4 SQL Server 2016 instances, each instance would be an availability group and have its own listener.
So something like:
NODE2: SQLINST1, SQLINST2, SQLINST3, SQLINST4
AGs: SQLINST1, SQLINST2, SQLINST3, SQLINST4
Listeners: SQLINST1Lis, SQLINST2Lis, SQLINST3Lis, SQLINST4Lis
1 WSFC across both nodes
My questions are:
Do I require a new HADR endpoint/ new port for each AG
Do I require a new port for each Listener
Is there a preferred port range for multiple endpoints/AG listeners
And perhaps more importantly, everyone I have asked about this so far has been fairly pessimistic on the configuration so, multiple instances on virtualization: How bad an idea is this?
May 25, 2017 at 3:05 pm
We have a similar setup.
The one challenge so far was load balancing unexpected workloads on instances, because you are now sharing one resource pool between all Instances.
Other than that, it's fairly stable, simple and no issues.
To answer your question;
Yes, each listener needs it's own unique endpoint, and unique port when on the same server.
List1 = 5022, List2 = 5033 etc..
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 26, 2017 at 4:47 am
tdroche - Thursday, May 25, 2017 2:55 PMMy company is getting ready to make a major architecture change and one of the things we are trying to do is reduce our actual virtual server footprint.Currently we are running 22 separate availability groups each with their own windows cluster across 44 servers (SQL Server 2014, Windows Server 2012R2 VMs).
We want to move to 6 servers to hold the 22 AGs, yes I know it doesn't divide evenly.
Sounds sensible to me, it's not an issue that the numbers do not divide, just ensure the number of nodes ahve the resource to cope with the workload. Less nodes in the cluster mean more instances per node.
tdroche - Thursday, May 25, 2017 2:55 PM
For example a given server (Windows Server 2016 VM, 20 vCPU, 64GB RAM) would be 4 SQL Server 2016 instances, each instance would be an availability group and have its own listener.So something like:
- NODE1: SQLINST1, SQLINST2, SQLINST3, SQLINST4
NODE2: SQLINST1, SQLINST2, SQLINST3, SQLINST4
AGs: SQLINST1, SQLINST2, SQLINST3, SQLINST4
Listeners: SQLINST1Lis, SQLINST2Lis, SQLINST3Lis, SQLINST4Lis
1 WSFC across both nodes
Is 64GB RAM enough to cope with the number of instances you will be deploying, i would suspect not
tdroche - Thursday, May 25, 2017 2:55 PM
My questions are:
Do I require a new HADR endpoint/ new port for each AG
Do I require a new port for each Listener
Is there a preferred port range for multiple endpoints/AG listeners
And perhaps more importantly, everyone I have asked about this so far has been fairly pessimistic on the configuration so, multiple instances on virtualization: How bad an idea is this?
You can re use the listener VCOs and VIPs but you'll need to take them down on the source system first, at the end of the day they're computernames and IP addresses.
If the port is not in use for that IP on the new system then it may be re used. It's the IP address and port number as a combination that need to be unique. A computer can listen on the same port over multiple IP addresses if it has them configured
There is a default that MS use but it's up to you
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 30, 2017 at 4:58 pm
Thank you all for your answers. It makes me far more confident in this process. One final question regarding service accounts. My first instinct is to install so each instance is a separate domain service account, so that they have no access to each others files. I can't seem to find anything specifying yay or nay on best practices around that.
Is there a specific best practice doc that I am missing around service accounts and multiple instances or, perhaps far more importantly, am I going to break something by going down that path?
May 31, 2017 at 8:05 am
tdroche - Tuesday, May 30, 2017 4:58 PMThank you all for your answers. It makes me far more confident in this process. One final question regarding service accounts. My first instinct is to install so each instance is a separate domain service account, so that they have no access to each others files. I can't seem to find anything specifying yay or nay on best practices around that.Is there a specific best practice doc that I am missing around service accounts and multiple instances or, perhaps far more importantly, am I going to break something by going down that path?
Any instances that will participate in an Availability Group and use a Listener will need to use the same domain account and not a separate account, otherwise you will have issues with the Listener VCO resgistering the SPN during a failover
Please read my satirway to AlwaysOn starting at this link
http://www.sqlservercentral.com/stairway/112556/
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 31, 2017 at 12:44 pm
I didnt even know there was an Always On stairway /facepalm. I will read that immediately!
Just for clarity what I was proposing was:
node 1 and 2 are in a cluster
Are you saying that it should be all instances on the cluster installed with the same service account i.e.
SQLINST1 on both nodes is installed with SQL-SVCACT
SQLINST2 on both nodes is installed with SQL-SVCACT etc
June 2, 2017 at 3:24 am
I think I read a while back about instance stacking and the default port for the LISTENER, I'm sure best practise states that the default port for the Listener on multi instance servers should not be 1433 on all instances. Please double check this as it was a couple of years ago that I read this. I will google it and see if I can find the article.
June 2, 2017 at 3:37 am
June 2, 2017 at 7:03 am
tdroche - Wednesday, May 31, 2017 12:44 PMI didnt even know there was an Always On stairway /facepalm. I will read that immediately!Just for clarity what I was proposing was:
node 1 and 2 are in a cluster
- NODE1: SQLINST1, SQLINST2, SQLINST3, SQLINST4
- NODE2: SQLINST1, SQLINST2, SQLINST3, SQLINST4
SQLINST1 on both nodes is installed with SQL-SVCACT1
SQLINST2 on both nodes is installed with SQL-SVCACT2 etcAre you saying that it should be all instances on the cluster installed with the same service account i.e.
SQLINST1 on both nodes is installed with SQL-SVCACTSQLINST2 on both nodes is installed with SQL-SVCACT etc
Yes
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 2, 2017 at 7:06 am
Talib123 - Friday, June 2, 2017 3:37 AM
That link quite rightly states that multiple instances of sql server should not listen on the same TCP port 1433. Multiple listeners can when using a unique VIP
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 2, 2017 at 11:13 am
Perry Whittle - Friday, June 2, 2017 7:03 AMtdroche - Wednesday, May 31, 2017 12:44 PMI didnt even know there was an Always On stairway /facepalm. I will read that immediately!Just for clarity what I was proposing was:
node 1 and 2 are in a cluster
- NODE1: SQLINST1, SQLINST2, SQLINST3, SQLINST4
- NODE2: SQLINST1, SQLINST2, SQLINST3, SQLINST4
SQLINST1 on both nodes is installed with SQL-SVCACT1
SQLINST2 on both nodes is installed with SQL-SVCACT2 etcAre you saying that it should be all instances on the cluster installed with the same service account i.e.
SQLINST1 on both nodes is installed with SQL-SVCACTSQLINST2 on both nodes is installed with SQL-SVCACT etc
Yes
Gotta love a straightforward answer. Thank you very much.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply