The TL;DR - beware of Failover Cluster Manager trying to steal your non-shared storage!
--
At a client recently two availability groups on a single Windows cluster went down simultaneously. Apparently the server that was the primary for the AGs (Server1) had mysteriously lost its DATA and LOG drives. By the time the client got us involved they had faked the application into coming up by pointing it directly to the single SQL Server instance that was still up (Server2) directly via the instance name rather than the availability group listeners.
I found that two of the drives on Server1 had gone offline, causing the issues –
sample errors from the Windows System and Application Logs respectively:
--
Log Name: System
Source: Microsoft-Windows-FailoverClustering
Date:
3/31/2015 2:36:25 PM
Event ID: 1635
Task Category: Resource Control Manager
Level: Information
Keywords:
User: SYSTEM
Computer: server1.mydomain.com
Description:
Cluster resource 'Cluster Disk 2' of type 'Physical Disk' in
clustered role 'Available Storage' failed.
--
Log Name: Application
Source: MSSQLSERVER
Date:
3/31/2015 2:36:25 PM
Event ID: 9001
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: server1.mydomain.com
Description:
The log for database 'Database1' is not available.
Check the event log for related error messages. Resolve any errors and restart
the database.
--
Since this is an Availability
Group (AG) I was surprised that there were “Cluster Disk” resources at all –
AG’s do not rely on shared disk (it is one of their many advantages) and most AG
clusters don’t have any shared disk at all (occasionally a quorum drive).
This is what I saw in Failover
Cluster Manager:
Cluster Disk 1 was the Quorum,
but the presence of disks 2-7 did not make sense to me in a regular AG arrangement. The
two disks that were online (Disk 6 and Disk 7) were the two disks that were
currently “live” on Server2, but there was still no reason for them to be in
Failover Cluster Manager.
The service provider assured me that none of
the drives except the Quorum are presented to more than one server from the
back-end storage.
There was one reported event that
happened at 2:36pm, a time that coincided with the failures – the client added a new node Server3 to the cluster (it was evicted 30 minutes
later with no further impact positive or negative).
My best theory at this point was
that when the engineer tried to add Server3 to the Windows cluster they
mistakenly tried to add the disks as Cluster Disk resources – for a traditional
SQL Server Failover Cluster Instance (FCI) this would be correct – for a SQL
FCI almost all disk is shared and all nodes need to have access to all of the
shared disk (although only one node can “own” it at any one time).
A cluster will “lock” disks – if
cluster MySuperHugeAndAmazingCluster01 owns a particular drive then no other server or cluster
can use it – the only way for a server to access it is through the
cluster. I considered that may be the cause of this issue – even though several of the
drives are flagged that “clustered storage is not connected to the node” this
may simply have been because the storage wasn’t presented to the current “owner” of the
Cluster Disk objects Server2.
--
After an application downtime was scheduled, I signed on to the server and after deleting the AGs (first saving their settings for later re-creation) and shutting down SQL I deleted the cluster
disk objects. This, combined with a disk rescan in the Computer Management
console on each server, did indeed return control of the “missing” drives to
the servers. I dropped six of the seven cluster disk objects (all of them
except the Quorum object) which means I needed to rescan disks on all of the
servers. This validated that the only reason things have been working on Server2 was because the cluster thought that Server2 owned the disk objects (my
guess is because the Add Node wizard to add Server3 to the cluster the
other day was probably run from Server2 rather than Server1– more to follow on that).
I recreated the two AGs and as a final step I performed a failover test of each of the two
availability groups from Server2 to Server1 and back again so that at
the end of the process Server2 was the primary for both availability
groups. Making Server2 the primary was necessary because of the
changes the client had made to the front-end applications and processes to get them to
work since they redirected the applications to talk directly to
Server2 rather than to the two availability group names (this works since
the availability group name is really just a redirect to the server name/IP
itself under the hood). A final step for the client was to redirect the
apps to once again talk to the availability group listeners.
I then added the new node (Server3) to the cluster and stepping through the Add Node wizard showed
me the likely cause of the original issue (below).
As of the end of the call, the client was satisfied with the end state – SQL Servers online, availability groups
running, and new cluster node added.
--
Here is what *I* learned today, brought to light through adding the new node and what was almost certainly the cause of the
problem:
As I noticed when adding
Server3 to the cluster, on the Confirmation screen of the Add Node wizard
in Windows Server 2012 there is a check box to “Add all eligible storage to the
cluster” – by default it is *CHECKED*.
As described here
by Clustering MVP David Bermingham, this can really cause problems:
On the
confirmation screen you will see the name and IP address you selected. You will
also see an option which is new with Windows Server 2012 failover
clustering…”Add all eligible storage to the cluster”. Personally I’m not sure
why this is selected by default, as this option can really confuse things. By
default, this selection will add all shared storage (if you have it configured)
to the cluster, but I have also seen it add
just local, non-shared disks, to the cluster as well. I suppose they want to make it easy to support symmetric
storage, but generally any host based or array based replication solutions are
going to have some pretty specific instructions on how to add symmetric storage
to the cluster and generally this option to add all disks to the cluster is
more of a hindrance than a help when it comes to asymmetric storage. For our
case, since I have no shared storage configured and I don’t want the cluster
adding any local disks to the cluster for me automatically I have unchecked the
Add all eligible storage to the cluster option.
(emphasis mine)
Although I have seen a cluster disk object reserve/”lock” a resource so
that the actual servers can’t access it other than through the cluster, but I
haven’t run over this specific situation before (the check box). The above
explanation from David shows the most likely reason *why* this happened in this
case – with the offending box checked by default, whoever was adding the node
probably clicked right past it and when the process to actually add the node
started, it grabbed all of the storage for the cluster, locking everybody
out. This would have impacted Server3 as well, but since it was a new server
with no user databases (or anything else) on its D: and E: drives unless
someone was looking in My Computer and saw the drives disappear, there wouldn’t
be any immediately apparent problem on that server.
The reason why I believe the Add
Node wizard was run from Server2 (not that it is important, just
explanatory) was because the disk objects showed as being owned by Server2. Since Server2 owned the cluster disk objects, it could
still access them, which is why it was able to keep accessing its user
databases on the two drives.
--
At the end of the day, if you are working on a cluster with no shared storage, make sure to uncheck the "Add all eligible storage to the cluster" check box - and even if you do have storage, it may not be a bad practice to uncheck the box - it isn't that hard to add the disks manually afterward, and it makes your cluster creation process consistent.
--
BONUS - I am not a PowerShell-freak myself (I keep telling myself I need to become one since #YouCanDoAnythingWithPowerShell) but if you like PS there is a flag to the relevant command there as well that is functionally equivalent to unchecking the box:
PS C:\> Add-ClusterNode -Name Server3 -NoStorage
--
#TheMoreYouKnow
