0

We have two data centers DC1 and DC2

  • The distance between two DC's are about 200 miles.
  • DC1 - has two nodes
  • DC2 - has 1 node with SQL 2012 Always-On AG and windows 2008R2
  • Node majority cluster
  • Two nodes on DC1 are primary.
  • Failovers are always between the two nodes on DC1.
  • DC2 is only for backups.

There is intermittent network issue between two DC1 and DC2, whenever there is network glitch causes the databases on DC2 to restart which kills the full backup job that was running.

One of our DBA suggesting to create a file share witness and remove vote to the node on DC2, then change quorum settings from node majority to node and file share majority.

My Question is..

If we create file share witness and change quorum setting will fix our backup issue? I am not clear how the full backup job continue running when there is network glitch and the all the nodes are in cluster Can any one advise how do i over come from this situation. Network team said they do not have any fix from their end.

I researched on this and found below link which looks similar but i can not correlate with my issue.

SQL Server Always On Node and File Share Majority

2

Regardless of the quorum model, if a SQL Server 2012 secondary becomes disconnected, it will go offline.

This was changed in SQL Server 2014.

When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads.

What's New for SQL Server 2014

  • Thanks David for your reply, If understand this correctly, Since SQL server 2012 secondary becomes disconnected and when it goes offline the backup job will also be killed. The issue remains exist until we upgrade it to latest version or changing backups to run on DC1 nodes – Sunny Aug 20 at 14:10
0

One of our DBA suggesting to create a file share witness and remove vote to the node on DC2, then change quorum settings from node majority to node and file share majority.

This is recommended (source..), the idea of removing quorum votes from DC2 is to let your DC2 node NOT make decision on WSFC resources to go off-line as we need have more than half votes to be active for cluster to be healthy i.e. when total votes=3, more than half means > 1.5, always there must be 2 votes available for cluster to be healthy.

In your case (as per DBA Suggestion), DC1 it-self would have 3 votes (1-Primary, 2-Secondary, 3-File-share).

However, for the backup issue since there is limitation in (SQL 2012) as per Mr. @DavidBrowne, you may want to set backup priority to the secondary replica that is available within Data center (DC1).

  • Thanks Shekar for your reply, As i mentioned we have two nodes on DC1, three AG groups primary on one node, two AG groups are primary on second node and they are very busy servers we can not run backups on any of the servers. – Sunny Aug 20 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.