SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple AG groups in one Windows failover cluster?


Multiple AG groups in one Windows failover cluster?

Author
Message
Blue Mancunian
Blue Mancunian
Mr or Mrs. 500
Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)

Group: General Forum Members
Points: 573 Visits: 550
In an organisation I found the following setup:
Six VMs SQL1 to SQL6 in the same domain, each host two instances. They are in four AG groups, each AG contains 10-20 databases.
AG1:
SQL1\INS1, SQL3\INS1, SQL5\INS1
AG2:
SQL1\INS2, SQL3\INS2, SQL5\INS2
AG3:
SQL2\INS3, SQL4\INS3, SQL6\INS3
AG4:
SQL2\INS4, SQL4\INS4, SQL6\INS4

However, all the AG groups and all the servers are in one Windows failover cluster CLUS1. SQL5 and SQL6 are in DR data center so they don't have quorum vote. Quorum Modes is Node and File Share Majority.

I would like to hear your opinions and pros/cons to have all AG groups and servers/nodes in one cluster (license cost, perfromance, high availability and etc.)

* They don't use Failover Cluster Instance of SQL Server. Only AG groups and Windows Server Failover Cluster.

Many thanks!
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210624 Visits: 18558
Blue Mancunian - Tuesday, December 12, 2017 4:50 AM
In an organisation I found the following setup:
Six VMs SQL1 to SQL6 in the same domain, each host two instances. They are in four AG groups, each AG contains 10-20 databases.
AG1:
SQL1\INS1, SQL3\INS1, SQL5\INS1
AG2:
SQL1\INS2, SQL3\INS2, SQL5\INS2
AG3:
SQL2\INS3, SQL4\INS3, SQL6\INS3
AG4:
SQL2\INS4, SQL4\INS4, SQL6\INS4

However, all the AG groups and all the servers are in one Windows failover cluster CLUS1. SQL5 and SQL6 are in DR data center so they don't have quorum vote. Quorum Modes is Node and File Share Majority.

I would like to hear your opinions and pros/cons to have all AG groups and servers/nodes in one cluster (license cost, perfromance, high availability and etc.)

* They don't use Failover Cluster Instance of SQL Server. Only AG groups and Windows Server Failover Cluster.

Many thanks!


What is the host operating system of the cluster nodes?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Blue Mancunian
Blue Mancunian
Mr or Mrs. 500
Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)Mr or Mrs. 500 (573 reputation)

Group: General Forum Members
Points: 573 Visits: 550
Perry Whittle - Wednesday, December 13, 2017 9:48 AM
Blue Mancunian - Tuesday, December 12, 2017 4:50 AM
In an organisation I found the following setup:
Six VMs SQL1 to SQL6 in the same domain, each host two instances. They are in four AG groups, each AG contains 10-20 databases.
AG1:
SQL1\INS1, SQL3\INS1, SQL5\INS1
AG2:
SQL1\INS2, SQL3\INS2, SQL5\INS2
AG3:
SQL2\INS3, SQL4\INS3, SQL6\INS3
AG4:
SQL2\INS4, SQL4\INS4, SQL6\INS4

However, all the AG groups and all the servers are in one Windows failover cluster CLUS1. SQL5 and SQL6 are in DR data center so they don't have quorum vote. Quorum Modes is Node and File Share Majority.

I would like to hear your opinions and pros/cons to have all AG groups and servers/nodes in one cluster (license cost, perfromance, high availability and etc.)

* They don't use Failover Cluster Instance of SQL Server. Only AG groups and Windows Server Failover Cluster.

Many thanks!


What is the host operating system of the cluster nodes?

Hi Perry, OS is Windows 2012 R2 DataCenter

Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210624 Visits: 18558
Blue Mancunian - Thursday, December 14, 2017 2:50 AM
Perry Whittle - Wednesday, December 13, 2017 9:48 AM
Blue Mancunian - Tuesday, December 12, 2017 4:50 AM
In an organisation I found the following setup:
Six VMs SQL1 to SQL6 in the same domain, each host two instances. They are in four AG groups, each AG contains 10-20 databases.
AG1:
SQL1\INS1, SQL3\INS1, SQL5\INS1
AG2:
SQL1\INS2, SQL3\INS2, SQL5\INS2
AG3:
SQL2\INS3, SQL4\INS3, SQL6\INS3
AG4:
SQL2\INS4, SQL4\INS4, SQL6\INS4

However, all the AG groups and all the servers are in one Windows failover cluster CLUS1. SQL5 and SQL6 are in DR data center so they don't have quorum vote. Quorum Modes is Node and File Share Majority.

I would like to hear your opinions and pros/cons to have all AG groups and servers/nodes in one cluster (license cost, perfromance, high availability and etc.)

* They don't use Failover Cluster Instance of SQL Server. Only AG groups and Windows Server Failover Cluster.

Many thanks!


What is the host operating system of the cluster nodes?

Hi Perry, OS is Windows 2012 R2 DataCenter


Then all nodes must be a part of the same WSFC to support an Availability Group configuration

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Super Cat
Super Cat
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5882 Visits: 3110
Best practise indicates that the port for the LISTENERS on the non default instances should not be set to 1433.
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210624 Visits: 18558
Multiple listeners can all listen on TCP port 1433 and this doesnt require the port number to be specified at connection time.
The combination of VIP and port number must be unique which they will be for multiple AGs each with a listener.
Default and named instances can all participate in an AG and they should not be configured to listen on the same port number

See more at this link

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Super Cat
Super Cat
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5882 Visits: 3110
I think that's what I was trying to say Perry. I'm not a man of many words. Thanks for padding it out.Smile
AlexSQLForums
AlexSQLForums
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12499 Visits: 2870
Blue Mancunian - Tuesday, December 12, 2017 4:50 AM
In an organisation I found the following setup:
Six VMs SQL1 to SQL6 in the same domain, each host two instances. They are in four AG groups, each AG contains 10-20 databases.
AG1:
SQL1\INS1, SQL3\INS1, SQL5\INS1
AG2:
SQL1\INS2, SQL3\INS2, SQL5\INS2
AG3:
SQL2\INS3, SQL4\INS3, SQL6\INS3
AG4:
SQL2\INS4, SQL4\INS4, SQL6\INS4

However, all the AG groups and all the servers are in one Windows failover cluster CLUS1. SQL5 and SQL6 are in DR data center so they don't have quorum vote. Quorum Modes is Node and File Share Majority.

I would like to hear your opinions and pros/cons to have all AG groups and servers/nodes in one cluster (license cost, perfromance, high availability and etc.)

* They don't use Failover Cluster Instance of SQL Server. Only AG groups and Windows Server Failover Cluster.

Many thanks!

Just a few questions before pro's/cons:

Is each AG in synchronous mode in the same Data Center and asynchronous mode for DR?
VM's or physical? Hyper-V or VMware?
What kind of storage are you using?
How are your disks setup per box?
Are all data/log files on the same drive but different folders?
What's the average size and usage of databases?
Are there listeners setup? if yes which ports are you using for each listener?


Alex S
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210624 Visits: 18558
AlexSQLForums - Tuesday, January 2, 2018 2:25 PM
Is each AG in synchronous mode in the same Data Center and asynchronous mode for DR?

Is this what you want, it all depends on your requirements

AlexSQLForums - Tuesday, January 2, 2018 2:25 PM
VM's or physical? Hyper-V or VMware?

What's your requirement here?


AlexSQLForums - Tuesday, January 2, 2018 2:25 PM
What kind of storage are you using?

all depends on the above, physical or virtual


AlexSQLForums - Tuesday, January 2, 2018 2:25 PM
How are your disks setup per box?

as above


AlexSQLForums - Tuesday, January 2, 2018 2:25 PM
Are all data/log files on the same drive but different folders?

see above


AlexSQLForums - Tuesday, January 2, 2018 2:25 PM
Are there listeners setup? if yes which ports are you using for each listener?

do you require read only routing?
If so, which port do you want to use, using 1433 is the best option


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search