A few months ago I was paged by a client because their SQL
Server Failover Cluster Instance (FCI) was experiencing multiple failovers when
they performed routine Windows patching.
The client's request which generated the work ticket was this:
At some point recently this clustered node failed
over from the A node to the B node however it seems to be the only instance that
did as INSTANCE_NAME_2 still resides on COMPUTER_NAME. With that, we would like to
open a ticket to investigate the cause/date/time of this failover.
This ticket was generated on 02/11, but
then there was a failover (with file share “flapping” as
described above) on the morning of 02/12, immediately after the request –
it appeared at the time that this was initiated by someone at the
client trying to move the service back from the B node to the A
node (their chosen primary node), which did happen after some flapping.
There is no evidence of
server reboot or other error on 02/12, but someone did RDP to the server
about
ten minutes before the failover, so I assumed it was manually triggered.
--
The failover prior to 02/12 (the one the
client was asking about in their ticket request) happened on 01/14, and
it turned out that there were multiple failover events on 01/14.
The first relevant event on 01/14 was this:
Event
Type: Information
Event Source: USER32
Event
Category:
None
Event
ID:
1074
Date:
1/14/2014
Time:
3:18:18 PM
User:
DOMAIN\LOGIN
Computer:
COMPUTER_NAME
Description:
The process Explorer.EXE has initiated the restart
of computer COMPUTER_NAME on behalf of user DOMAIN\LOGIN for the following
reason: Other (Planned)
Reason Code: 0x85000000
Shutdown Type: restart
Comment: reboot
DOMAIN\LOGIN rebooted the A node at 3:18pm with the comment
of “reboot.”
--
The next relevant event was this:
Event
Type: Information
Event Source: ClusSvc
Event
Category:
Failover Mgr
Event
ID:
1203
Date:
1/14/2014
Time:
5:50:26 PM
User:
N/A
Computer:
COMPUTER_NAME
Description:
The Cluster Service is attempting to offline the
Resource Group "INSTANCE_NAME".
At 5:50pm, INSTANCE_NAME was offlined on the B node, without
a server restart or other error, which probably means it was
user-initiated. This resulted in 10+ minutes of file share “flapping” until
the instance finally ended up on the B node:
--
Event
Type: Information
Event Source: Service Control
Manager
Event
Category:
None
Event
ID:
7036
Date:
1/14/2014
Time:
6:01:35 PM
User:
N/A
Computer:
COMPUTER_NAME
Description:
The SQL Server (INSTANCE_NAME) service entered the
running state.
--
At 10:50pm, the SQL Server cluster resource group was again
failed over, possibly in preparation for the B node reboot that was about to
happen:
Event
Type: Information
Event Source: ClusSvc
Event
Category:
Failover Mgr
Event
ID:
1203
Date:
1/14/2014
Time:
10:50:56 PM
User:
N/A
Computer:
COMPUTER_NAME
Description:
The Cluster Service is attempting to offline the
Resource Group "INSTANCE_NAME".
--
At 10:55pm, the same user rebooted the B node, again with
the comment of “reboot”:
Event
Type: Information
Event Source: USER32
Event
Category:
None
Event
ID:
1074
Date:
1/14/2014
Time:
10:55:24 PM
User:
DOMAIN\LOGIN
Computer:
COMPUTER_NAME
Description:
The process Explorer.EXE has initiated the restart
of computer COMPUTER_NAME on behalf of user DOMAIN\LOGIN for the following
reason: Other (Planned)
Reason Code: 0x85000000
Shutdown Type: restart
Comment: reboot
--
This resulted in the SQL Server resource group trying to
fail over to A, but due to the same file share “flapping” the group finally ended up
back on B after B was back up from its reboot:
Event
Type: Information
Event Source: Service Control
Manager
Event
Category:
None
Event
ID:
7035
Date:
1/14/2014
Time:
11:05:56 PM
User: DOMAIN\SERVICE_ACCOUNT_LOGIN
Computer:
COMPUTER_NAME
Description:
The SQL Server (INSTANCE_NAME) service was
successfully sent a start control.
--
The issue relevant to their situation related to a
cluster file share resource:
The share is on the Y: drive (Y:\FOLDER_NAME) but as seen in
the resource properties screenshot above there is no clustering dependency established to that drive. On a
file share cluster resource like this there should be a dependency on the
relevant drive, like this example from the “SQL Server MSSQL Share INSTANCE_NAME”
share:
Without a dependency on the drive resource, the file share
resource tries to come online as soon as the resource group fails over.
Unfortunately, since the file share actually *does* depend on the drive being
online (even without an established dependency relationship), the file share
fails with the following errors if the drive isn’t online yet:
--
--
Event
Type: Error
Event Source: ClusSvc
Event
Category: File Share Resource
Event
ID:
1068
Date:
2/12/2014
Time:
7:16:37 AM
User:
N/A
Computer:
COMPUTER_NAME
Description:
Cluster file share resource SHARE_NAME failed to
start with error 21.
--
Event
Type: Error
Event Source: ClusSvc
Event
Category: File Share Resource
Event
ID:
1053
Date:
2/12/2014
Time:
7:16:37 AM
User:
N/A
Computer:
COMPUTER_NAME
Description:
Cluster File Share SHARE_NAME cannot be brought
online because the share could not be created.
--
Event
Type: Error
Event Source: ClusSvc
Event
Category: Failover Mgr
Event
ID:
1069
Date:
2/12/2014
Time:
7:16:37 AM
User:
N/A
Computer:
COMPUTER_NAME
Description:
Cluster resource 'SHARE_NAME' in Resource Group 'INSTANCE_NAME'
failed.
--
The middle error is the most telling one – the file share
cannot be brought online because the share could not be created – because the
Y: drive isn’t online yet!
The dangerous catch here is that by default the file share will try
three times to come online, and if it can’t (because the drive isn’t online
yet) it results in the resource group
failing, causing another SQL
Server resource group failover, even if the SQL Server resource comes
online cleanly. This results in the group “flapping” back and forth
between the nodes until you get lucky and the Y: drive resource happens to come
online before the SHARE_NAME resource makes it through its three retry attempts
and kills the group again.
This may not seem important and is not the cause of the
actual initial failover event (in their case the initial failover was intentional as part of the Windows patching), but it is the reason why every time you do have
a SQL Server cluster failover event there are multiple failovers, as seen in this
SQL Server error log stack:
As can be seen here, on 01/14/2014 there was a failover, and
it resulted in multiple failovers (each SQL Error Log is a new start of the
MSSQLServer service) over the course of several minutes as the SHARE_NAME resource tried to come
online and failed. This happened again on 02/12. This can be seen
in the Windows System Log as well (shown filtered below):
Events 1068 and 1053 are the relevant events, and as you can
see on 01/14 from 5:51pm-5:58pm there were multiple failovers with the file share
failing, and then again that same night between 10:59pm and 11:02pm, and then
the most recent event on 02/12 between 7:15am and 7:16am.
There are configuration options on the SHARE_NAME file share
cluster object to change the number of retries or to make it so it doesn’t fail
the group when its object fails, but the most correct fix is to add the
dependency on the Y: drive to the file share object.
--
The ultimate answer of why the INSTANCE_NAME instance
ended up on the B node is the file share flapping – DOMAIN\LOGIN was performing regular
server maintenance (Windows patches) and mid-afternoon on 01/14 tried to
fail everything from A to B and then reboot A, and then later in the evening
tried to fail everything from B back to A and reboot B (and this is how I would
patch a cluster like this).
The catch is that the file share flapping caused many, many
failover events to occur each time, and resulted in the cluster instance ending
up on the “wrong” node (after a failover everything would normally be on A,
but due to the flapping A>B>A>B>A>B it actually ended up back on
B).
-
The ultimate answer to prevent this "flapping" is to add the file
share cluster resource dependency on the drive object.
**This issue should be corrected as soon as this situation is discovered even though it
will require a file share downtime, which may require a SQL Server downtime
depending on what the SHARE_NAME share is used for.**
While this example is about a file share resource, the same concept (and resolution) is valid for any clustered resource that relies on something else - for example, if your SQL Server instance cluster resource is missing its cluster dependency on the Network Name.
Hope this helps!
