SQL SERVER – SharePoint not Working After Failover to Another Node

Anything that can go wrong will go wrong while Murphy is out of town. While I was onsite to help a client, one such issue had cropped up in their SharePoint environment where SQL Server was used as backend and was a part of Windows failover cluster. When SQL Server was failed over from Active to the Passive node, users were not able to connect to SharePoint site and they were not able to open any SharePoint page. Since it was critical for their business, I got engaged to provide suggestions.

When they failed over, we could see that the SQL Server group had failed over successfully and all the SQL related resources were up and running. It’s just that the SharePoint users, who were not able to do anything. I started with basics and started to look in the SQL Server Errorlog. The below error was continuously getting logged.

2018-04-10 18:49:09.47 Logon       Error: 18456, Severity: 14, State: 38.
2018-04-10 18:49:09.47 Logon       Login failed for user ‘Domain\UserName’. Reason: Failed to open the explicitly specified database. [CLIENT: xx.xx.xx.xx]

Unfortunately, above error message didn’t tell us the database name. We run a profiler to get this information and from the profiler output, we could see that the above error was getting logged for the SharePoint content database – MySite_ContentDB

Then we tried to access this database from SSMS –

USE [MySite_ContentDB]

It failed with below error message.

Msg 945, Level 14, State 2, Line 1
Database ‘MySite_Content’ cannot be opened due to inaccessible files or insufficient memory or disk space.

I have double checked and made sure the database files are available at their desired location and made sure the proper permissions were set on these files. Still, we were just not able to access this database via GUI in SSMS or using any T-SQL commands.

Suspecting corruption and just thinking this database might not have come online, I tried to set the ONLINE status for this database

ALTER DATABASE [MySite_Content] SET ONLINE

This gave us some meaningful error(s).

Msg 5591, Level 16, State 5, Line 1
FILESTREAM feature is disabled.
Msg 5105, Level 16, State 14, Line 1
A file activation error occurred. The physical file name ‘:\’ may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 945, Level 14, State 2, Line 1
Database ‘MySite_Content’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Now everything made sense in this world. As the error said, filestream feature was not enabled on this node. Surprisingly, it was enabled on Node1. The documentation mentions that SharePoint application uses the RBS feature to store binary large objects (BLOBs) outside the content database. For this, FileStream feature needs to be enabled on the database server hosting the content database. And now because this is a clustered SQL instance, this feature needs to be enabled on all the participating nodes.

I asked my client to enable FileStream on this node. Refer Enable FileStream

After that SharePoint sites started working and we could also run the commands which were failing above.

ALTER DATABASE [MySite_Content] SET ONLINE
GO
USE [MySite_Content]
GO

My client also tested multiple failovers and made sure the SharePoint sites were working fine. I always thought that changes to clustered SQL Server instance, it would be done automatically on other nodes but looks like this didn’t happen for this client. I didn’t get time to investigate the cause. Have you come across analogous situation? Do you know the cause? Please share via comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)

First appeared on SQL SERVER – SharePoint not Working After Failover to Another Node

from SQL Authority with Pinal Dave https://ift.tt/2HltU0j