December 12, 2017 at 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!
December 13, 2017 at 9:48 am
Blue Mancunian - Tuesday, December 12, 2017 4:50 AMIn 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\INS4However, 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" 😉
December 14, 2017 at 2:50 am
Perry Whittle - Wednesday, December 13, 2017 9:48 AMBlue Mancunian - Tuesday, December 12, 2017 4:50 AMIn 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\INS4However, 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
December 14, 2017 at 8:55 am
Blue Mancunian - Thursday, December 14, 2017 2:50 AMPerry Whittle - Wednesday, December 13, 2017 9:48 AMBlue Mancunian - Tuesday, December 12, 2017 4:50 AMIn 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\INS4However, 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" 😉
December 15, 2017 at 1:16 am
Best practise indicates that the port for the LISTENERS on the non default instances should not be set to 1433.
December 15, 2017 at 8:55 am
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
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 18, 2017 at 2:59 am
I think that's what I was trying to say Perry. I'm not a man of many words. Thanks for padding it out.🙂
January 2, 2018 at 2:25 pm
Blue Mancunian - Tuesday, December 12, 2017 4:50 AMIn 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\INS4However, 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?
January 3, 2018 at 4:21 am
AlexSQLForums - Tuesday, January 2, 2018 2:25 PMIs 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" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply