Have you ever encountered an issue with SQL Server not starting in a cluster? In this blog post, I will share a recent troubleshooting experience I had with one of my clients and provide a solution to fix this issue.
My client contacted me and informed me that they were unable to perform a failover of their SQL Server Clustered instance from Node1 to Node2. Upon investigation, we found the following error message in the system event log of Node2:
Log Name: System Source: Service Control Manager Date: 1/28/2018 3:21:30 PM Event ID: 7000 Task Category: None Level: Error Keywords: Classic User: N/A Computer: Node2 Description: The SQL Server (MSSQLSERVER) service failed to start due to the following error: The system cannot find the file specified.
Upon further examination, we discovered that the path of the sqlservr.exe file in the services.msc was incorrect. The expected path was H:\MSSQL\MSSQL14.MSSQLSERVER\MSSQL\Binn\, but there was no file found in that location.
After discussing the issue with the client, they informed us that they had recently experienced a drive issue and their storage team had replaced the drive. However, it seems that not all the necessary files were copied from the old drive to the new one.
To resolve this issue, we had two feasible options:
- Perform a “Repair” of the SQL Server instance.
- Perform a “RemoveNode” followed by an “AddNode”.
We decided to go with the second option. We first removed the problematic node from the cluster using the “RemoveNode” command, and then added it back using the “AddNode” command. Once this process was completed, we were able to successfully failover the SQL Server to Node2 without any further issues.
It is important to ensure that all necessary files and binaries are properly copied when replacing drives or making any changes to the underlying infrastructure of a SQL Server cluster. Failure to do so can result in issues like the one described in this blog post.
I hope this blog post has provided you with valuable insights into troubleshooting and fixing SQL Server cluster failover issues. If you have any questions or would like to share your own experiences, please feel free to leave a comment below.