February 10, 2014 at 12:04 pm
we are testing always on feature on our test servers. I kind of stuck at quorum settings. If the quorum file share location is placed on a server which reboots every night, what happens to the cluster. I was referring to the following link where it states that -
http://technet.microsoft.com/en-us/library/hh270280.aspx
The presence of a quorum means that the cluster is healthy and able to provide node-level fault tolerance.The absence of a quorum indicates that the cluster is not healthy. Overall WSFC cluster health must be maintained in order to ensure that healthy secondary nodes are available for primary nodes to fail over to.
If the quorum vote fails, the WSFC cluster will be set offline as a precautionary measure. This will also cause all SQL Server instances registered with the cluster to be stopped.
By above statement if quorum is not available cluster will go offline and also the sql instances?? or if cluster is not available on two node scenario, cluster doesn't vote and the two nodes will work as it is? Which one is true?
February 10, 2014 at 2:48 pm
do we need to have quorum for using always on feature??
February 11, 2014 at 7:53 am
Yes, You need a Quorum for your Windows cluster to be functional. No Quorum => No Cluster to keep it very simple.
Please see below post where Perry explained Quorum models very clearly.
http://www.sqlservercentral.com/Forums/Topic1395316-2799-1.aspx
February 11, 2014 at 10:59 am
muthyala_51 (2/10/2014)
we are testing always on feature on our test servers. I kind of stuck at quorum settings. If the quorum file share location is placed on a server which reboots every night, what happens to the cluster.
As long as the nodes don't suffer an outage nothing will happen, same as losing a disk based quorum witness. If the witness goes offline when the cluster nodes are suffering outages then your cluster could go offline completely, depending on the healthy node votes that are functional.
muthyala_51 (2/10/2014)
The presence of a quorum means that the cluster is healthy and able to provide node-level fault tolerance.
Not exactly, in some configurations you don't need a disk or file share witness, if the cluster has an odd number of votes from configured nodes then no other witness is required. Quorum modes are
The cluster quorum models are as follows
Can sustain failures of half the nodes (rounding up) minus one. For example, a seven node cluster can sustain three node failures.
Can sustain failures of half the nodes (rounding up) if the disk witness remains online. For example, a six node cluster in which the disk witness is online could sustain three node failures.
Can sustain failures of half the nodes (rounding up) minus one if the disk witness goes offline or fails. For example, a six node cluster with a failed disk witness could sustain two (3-1=2) node failures.
Works in a similar way to Node and Disk Majority, but instead of a disk witness, this cluster uses a file share witness.
Note that if you use Node and File Share Majority, at least one of the available cluster nodes must contain a current copy of the cluster configuration before you can start the cluster. Otherwise, you must force the starting of the cluster through a particular node. For more information, see "Additional considerations" in Start or Stop the Cluster Service on a Cluster Node.
Can sustain failures of all nodes except one (if the disk is online). However, this configuration is not recommended because the disk might be a single point of failure.
muthyala_51 (2/10/2014)
The absence of a quorum indicates that the cluster is not healthy.
No, see above
muthyala_51 (2/10/2014)
If the quorum vote fails, the WSFC cluster will be set offline as a precautionary measure. This will also cause all SQL Server instances registered with the cluster to be stopped.By above statement if quorum is not available cluster will go offline and also the sql instances?? or if cluster is not available on two node scenario, cluster doesn't vote and the two nodes will work as it is? Which one is true?
No, you would need to lose over half the votes for the cluster to go offline completely. With dynamic node weight in Windows 2012 it is possible to lose multiple votes and still keep the cluster online.
muthyala_51 (2/10/2014)
do we need to have quorum for using always on feature??
It depends on your node\vote configuration in the cluster.
sreekanth bandarla (2/11/2014)
Yes, You need a Quorum for your Windows cluster to be functional. No Quorum => No Cluster to keep it very simple.
You need a majority node set, if you have even number of voting notes either remove votes from some nodes to make count uneven or introduce a disk or fileshare witness.
See my latest article at this link[/url], keep an eye out for part 2 where i discuss the cluster and quorum at great length 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 12, 2014 at 5:26 pm
Thanks a lot Perry !! I am also having trouble to understand what exactly the alwayson availability group listener is ? What this is used for? Does it acts as a virtual name to connect to the sql nodes? I was searching for more info on it which I could not find. Can you please highlight this topic in your second document ? If possible can you provide some info over here, so that I can move forward with my testing. Appreciate your help.
February 13, 2014 at 6:30 am
muthyala_51 (2/12/2014)
Thanks a lot Perry !! I am also having trouble to understand what exactly the alwayson availability group listener is ? What this is used for? Does it acts as a virtual name to connect to the sql nodes?
Yes, that's essentially what it does do. It's a virtual client access point into the AlwaysOn group. Connecting via an appropriately configured listener with appropriately configured AlwaysOn group replicas could resulty in different connections being routed to different replicas within the AO group. For instance all read only connections might be routed to a secondary, away from the Primary replica and its database.
However, it's a cluster resource and is subject to the WSFC control.
muthyala_51 (2/12/2014)
ICan you please highlight this topic in your second document
Part 2 of my guide will be hitting the cluster and quorum configuration quite hard, keep an eye out for it. Have you read part 1 yet?
muthyala_51 (2/12/2014)
If possible can you provide some info over here, so that I can move forward with my testing. Appreciate your help.
Since you aksed so nicely!!
When you first create the AO group you are informed of the option of creating a listener, selecting this will require the following
Once the listener has been created i urge you to go to the Failover Cluster Manager and look at the cluster applications\roles. You'll see a role with the same name as the AlwaysOn group that you created. Within the role will be 3 resources,
The role tracks the primary replica when failing over as a direct result of an AlwaysOn group level failover. To see an example, assume the following configuration
Windows Cluster 192.168.0.165
Nodes are
Nodename Node IP
ClusterNode1 192.168.0.166
ClusterNode2 192.168.0.167
ClusterNode3 192.168.0.168
ClusterNode4 192.168.0.169
The cluster nodes have the following instances installed. AOINST3\A3 is a failover cluster instance of SQL Server across nodes 3 and 4 only
Nodename Instancename Instance IP address
ClusterNode1 AOINST1\A1 192.168.0.171
ClusterNode2 AOINST2\A2 192.168.0.172
ClusterNode3 AOINST3\A3 192.168.0.173
ClusterNode4 AOINST3\A3 192.168.0.173
An AlwaysOn group "MyHA" has been created across the 3 instances. During the creation wizard a listener was configured with the following detail
Virtual Networkname Virtual IP Address
MyHAListener 192.168.0.175
Given this we will look at 3 scenarios
Primary failover from AOINST1\A1 to AOINST2\A2
Looking at the Listener clustered role ownership before failover will list ClusterNode1 as the role owner. Once the failover has been completed within the AO group, the new owner of the Listener clustered role will be ClusterNode2
Primary failover from AOINST3\A3 to AOINST\A1 (where AOINST3\A3 is active on ClusterNode3)
Looking at the Listener clustered role ownership before failover will list ClusterNode3 as the role owner. Once the failover has been completed within the AO group, the new owner of the Listener clustered role will be ClusterNode1
Primary failover from AOINST2\A2 to AOINST\A3 (where AOINST3\A3 is active on ClusterNode4)
Looking at the Listener clustered role ownership before failover will list ClusterNode2 as the role owner. Once the failover has been completed within the AO group, the new owner of the Listener clustered role will be ClusterNode4
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2014 at 4:56 pm
Thanks a lot Perry !!
February 13, 2014 at 5:00 pm
You're welcome 😎
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 14, 2014 at 11:01 am
I am facing the following issues while setting up alwayson.
1) Not able to set the listener to the alwayson group. Error--
The WSFC cluster could not bring the Network Name resource with DNS name 'MyAGListener' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, Error: 19471)
2) Also we are planning to have 3 replicas with one server acts as principal and the other two replicas one synchronous and other asynchronous setup. We kind of planning to achieve both HA + DR by using alwayson feature. But we cannot replicate system databases. Is there any way to achive this?
3) to setup transactional replication can we use listener virtual name or it asks for physical name? If it accepts physical server name we can't go with servername as it causes issues when failover happens.
4) Also I have a table with identity column on in a database which is in alwayson group. For some reason the values jumped from 1,2,3,2002,2003? What could be the reason behind this?
February 20, 2014 at 7:40 am
any suggestions?
February 20, 2014 at 8:08 am
muthyala_51 (2/14/2014)
I am facing the following issues while setting up alwayson.1) Not able to set the listener to the alwayson group. Error--
The WSFC cluster could not bring the Network Name resource with DNS name 'MyAGListener' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, Error: 19471)
This is pretty descriptive, the virtual networkname and \or IP address supplied for the listener are not unique. Supply a unique name and IP
muthyala_51 (2/14/2014)
2) Also we are planning to have 3 replicas with one server acts as principal and the other two replicas one synchronous and other asynchronous setup. We kind of planning to achieve both HA + DR by using alwayson feature. But we cannot replicate system databases. Is there any way to achive this?
No, this requires a failover cluster instance.
muthyala_51 (2/14/2014)
3) to setup transactional replication can we use listener virtual name or it asks for physical name? If it accepts physical server name we can't go with servername as it causes issues when failover happens.
Never set it up myself.
Section 2 of this link details
MSDN-SQL Server
Create a DNS listener for the availability group that will be used by the replication agents to connect to the current primary. The listener name that is specified will be used as the target of redirection for the original publisher/published database pair. For example, if you are using DDL to configure the availability group, the following code example can be used to specify an availability group listener for an existing availability group named MyAG:[Code="SQL"]
ALTER AVAILABILITY GROUP 'MyAG'
ADD LISTENER 'MyAGListenerName' (WITH IP (('10.120.19.155', '255.255.254.0')));[/code]
muthyala_51 (2/14/2014)
4) Also I have a table with identity column on in a database which is in alwayson group. For some reason the values jumped from 1,2,3,2002,2003? What could be the reason behind this?
The only possible explanation is that the seed value changed, can't see how though
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 20, 2014 at 8:27 am
About the identity column, I just truncated the table on pricipal and inserted few rows and failed over to the other server. When i started inserting the rows again the value jumped into 1000s. Everytime i failover to the other it's incrementing to 1000's.
fidfname
6pears
7pears
1002pears---failed over
1003pears
1010pears
2002pears--failed over
2003pears
2004pears
February 20, 2014 at 9:08 am
yep that's what i'm seeing, jumps by 1000 each failover
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 20, 2014 at 9:18 am
Perry Whittle (2/20/2014)
muthyala_51 (2/14/2014)
I am facing the following issues while setting up alwayson.1) Not able to set the listener to the alwayson group. Error--
The WSFC cluster could not bring the Network Name resource with DNS name 'MyAGListener' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, Error: 19471)
This is pretty descriptive, the virtual networkname and \or IP address supplied for the listener are not unique. Supply a unique name and IP
muthyala_51 (2/14/2014)
2) Also we are planning to have 3 replicas with one server acts as principal and the other two replicas one synchronous and other asynchronous setup. We kind of planning to achieve both HA + DR by using alwayson feature. But we cannot replicate system databases. Is there any way to achive this?No, this requires a failover cluster instance.
muthyala_51 (2/14/2014)
3) to setup transactional replication can we use listener virtual name or it asks for physical name? If it accepts physical server name we can't go with servername as it causes issues when failover happens.Never set it up myself.
Section 2 of this link details
MSDN-SQL Server
Create a DNS listener for the availability group that will be used by the replication agents to connect to the current primary. The listener name that is specified will be used as the target of redirection for the original publisher/published database pair. For example, if you are using DDL to configure the availability group, the following code example can be used to specify an availability group listener for an existing availability group named MyAG:[Code="SQL"]
ALTER AVAILABILITY GROUP 'MyAG'
ADD LISTENER 'MyAGListenerName' (WITH IP (('10.120.19.155', '255.255.254.0')));[/code]
muthyala_51 (2/14/2014)
4) Also I have a table with identity column on in a database which is in alwayson group. For some reason the values jumped from 1,2,3,2002,2003? What could be the reason behind this?The only possible explanation is that the seed value changed, can't see how though
Unfortunately This(restarting SQL Server(or Failover of AG) forces reseed of Identity) behavior is by design as per microsoft...:crazy:
see below connect items for more information.
http://connect.microsoft.com/SQLServer/feedback/details/743300/identity-column-jumps-by-seed-value
February 21, 2014 at 10:08 am
after checking there is a trace flag
-T272
This will revert to the behaviour in previous versions of the product
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply