windows cluster quorum setting on windows server 2012

  • 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?

  • do we need to have quorum for using always on feature??

  • 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

  • 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

    • Node Majority (recommended for clusters with an odd number of nodes)

      Can sustain failures of half the nodes (rounding up) minus one. For example, a seven node cluster can sustain three node failures.

    • Node and Disk Majority (recommended for clusters with an even number of nodes)

      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.

    • Node and File Share Majority (for clusters with special configurations)

      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.

    • No Majority: Disk Only (not recommended)

      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" 😉

  • 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.

  • 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

    • unique Virtual IP address
    • unique Virtual networkname
    • a TCP port number

    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,

    • Virtual Networkname resource
    • Virtual IP address resource
    • AlwaysOn listener resource

    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" 😉

  • Thanks a lot Perry !!

  • You're welcome 😎

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • 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?

  • any suggestions?

  • 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" 😉

  • 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

  • yep that's what i'm seeing, jumps by 1000 each failover

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • 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

    https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity

  • 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