SQL cluster cannot failover. SQL services cannot start

Chong 866

Hi,

I have a 2 node SQL server cluster which cannot failover. The SQL program and DB are located in the shared disk

When we try to failover the resource, it cannot auto failover from node A to B. We need to stop node A, manual active all resource in node B. Then the SQL can start normally

Then we try to failback from node B to A. When we failover to node A, the disk can be found in node A, but the SQL service cannot start. We tried to stop node B and manual active all resource in node A, it still failed to start.

SQL.log

Thanks

MikeyQiaoMSFT-0444 3,190 Reputation points

2024-08-12T07:16:33.5633333+00:00

Hello, could you please provide your system information and the version information of your SQL Server?
Chong 866 Reputation points

2024-08-12T07:23:39.3+00:00

Windows Server 2016 and SQL 2016
MikeyQiaoMSFT-0444 3,190 Reputation points

2024-08-12T09:05:17.0133333+00:00

We need to stop node A, manual active all resource in node B. Then the SQL can start normally

Could you please further explain the meaning of this sentence? Do you mean that you took both Node A and Node B offline, then manually started Node B, and Node B came online successfully? I would like to ask whether you can see both nodes online simultaneously in the failover cluster manager.
Chong 866 Reputation points

2024-08-12T09:14:58.2233333+00:00

We found the SQL server services stopped around 15:00. We login to the node A and found that the disks are online, but the SQL Server service is stopped. We tried to restart the services and also reboot the node A, but the SQL services still fail and it cannot failover to node B.

Around 16:15, we stopped node A and move the cluster role to node B manually and all the shared disks present in node B.

Around 16:20, we can start the SQL server service in node B. Then we try to failover to node A again but it failed

After some research on the error code (0x103), I checked the cluster name in registry on both node A and B, they are the same: (p.s. we didn't change any SQL name, just see the error message is same as this link)

https://www.sherbaz.com/error-could-not-find-any-ip-address-that-this-sql-server-instance-depends-upon/

Any idea?
Chong 866 Reputation points

2024-08-12T09:15:28.97+00:00

duplicated
MikeyQiaoMSFT-0444 3,190 Reputation points

2024-08-12T09:24:50.2833333+00:00

SQL Server could not listen on IP address [10.2.1.19] because the cluster resource 'SQL IP Address 1 (cenprsqs)' is not online (state = 4). This is an informational message and may indicate that resource 'SQL Network Name (cenprsqs)' has OR type of dependency on several IP addresses some of which are currently offline or in a failed state. Further action is only required if it is generally possible to bind the IP address of the cluster resource 'SQL IP Address 1 (cenprsqs)' to a network segment on the current hosting node.

I found the above error in your logs, which is similar to the issue you are concerned about. The cluster manager is unable to listen to the SQL Server's IP address, and this might be related to your DNS configuration.
Chong 866 Reputation points

2024-08-12T09:28:10.1566667+00:00

Both node A and B are pointed to same DNS server. What DNS configuration I can check?

Thanks
MikeyQiaoMSFT-0444 3,190 Reputation points

2024-08-12T09:47:58.7933333+00:00

I suggest you check whether the SQL servers on nodes A and B have TCP/IP enabled, VIA disabled, and that the default port is not set to 1433 for both.
Chong 866 Reputation points

2024-08-12T09:52:55.2233333+00:00

Hi @MikeyQiaoMSFT-0444

Checked both nodes A and B have TCP/IP enabled.

For the VIA, I cannot find it in SQL Server Configuration Manager.

For the default port, we didn't changed it. So we need to change it not to use 1433 in SQL Server Configuration Manager?
Chong 866 Reputation points

2024-08-15T04:24:15.5066667+00:00

Any idea?
MikeyQiaoMSFT-0444 3,190 Reputation points

2024-08-15T05:54:59.6033333+00:00
When a failure occurs in Node A, it cannot automatically failover to Node B, but a manual failover is possible at this time.

After the manual failover is successful, Node A cannot be started.

Around 16:20, we can start the SQL server service in Node B.

But why does Node B also need to be restarted?

Can you provide other logs, such as the AlwaysOn_health_log?
MikeyQiaoMSFT-0444 3,190 Reputation points

2024-08-15T06:06:06.15+00:00

Additionally, I would like to ask if your cluster service is deployed in a multi-subnet environment.
Chong 866 Reputation points

2024-08-15T06:12:53.36+00:00

We didn't restart node B.

Around 16:15, we checked the shared disks can present on node B. But we have some checking on the server so just start the SQL services around 16:20.

We have event log and cluster log, but where can found the AlwaysOn_health_log? We setup the SQL cluster but didn't configure the availability groups.

Thanks
Chong 866 Reputation points

2024-08-15T06:20:09.3033333+00:00

No. The SQL servers didn't deploy in multi-subnet environment. Node A, node B and the cluster IP are in the same subnet.

(Node A have multiple NIC but we disabled those useless NIC already)
MikeyQiaoMSFT-0444 3,190 Reputation points

2024-08-15T06:20:56.4666667+00:00

Sorry, I overthought it. Could you provide another log?
Chong 866 Reputation points

2024-08-15T06:31:50.04+00:00

Sure. But the log size is too large so cannot upload to here.
MikeyQiaoMSFT-0444 3,190 Reputation points

2024-08-15T06:34:51.66+00:00

You can truncate the log to retain only the latest part.
Chong 866 Reputation points

2024-08-15T06:43:12.7433333+00:00

The issue occur on 9/8. Please rename the file from .xml to .zip and extract it. Thanks

nodeA Cluster.xml

nodeB Cluster.xml

nodeA system_log.xml

nodeA application_log.xml

nodeB application_log.xml

nodeB system_log.xml
MikeyQiaoMSFT-0444 3,190 Reputation points

2024-08-15T09:21:20.5633333+00:00

Hi，Chong> Microsoft-Windows-FailoverClustering,1223,IP Address Resource,Cluster IP address resource 'SAP AF3 IP' cannot be brought online because the cluster network 'Cluster Network 2' is not configured to allow client access. Please use the Failover Cluster Manager snap-in to check the configured properties of the cluster network.

I found this error in the log. Please follow the instructions in the link to modify the configuration in the cluster manager.https://learn.microsoft.com/en-us/troubleshoot/windows-server/high-availability/troubleshoot-cannot-bring-ip-address-online#check-the-role-that-is-configured-for-a-cluster-network
MikeyQiaoMSFT-0444 3,190 Reputation points

2024-08-15T09:48:53.66+00:00

......
Chong 866 Reputation points

2024-08-15T09:58:12.82+00:00

I checked the "Allow the cluster to use this network" and "Allow clients to connect through this network" options are enabled already.

However, the SQL services only work in node B, and failed to start at node A
Chong 866 Reputation points

2024-08-16T02:41:54.6433333+00:00

Hi @MikeyQiaoMSFT-0444

I am not sure but seems something wrong in the network.

The adaptor 1 of both nodes are on the same subnet (10.2.1.x) but the cluster validation report show they cannot communicate on UDP3343. As the cluster resource also in subnet 10.2.1.x, do there are something wrong on node A network so it cannot take the resource online ?

tmpF129.tmp.xml (Please rename from .xml to .mht)

Thanks
MikeyQiaoMSFT-0444 3,190 Reputation points

2024-08-16T03:29:52.36+00:00

I also noticed this error, but the UDP 3343 port is enabled by default. Try opening the port, and if it still fails, continue with the connectivity test.
Chong 866 Reputation points

2024-08-16T03:52:06.67+00:00

I use netstat to verify the port is listened and disabled the windows firewall already. As they are in the same subnet (no physical firewall), the communication should be OK.

But the validation report still show cannot connect in UDP 3343.

On the other hand, in validation report, the "cluster network 1" is "node A adaptor 1" (10.2.1.x) pair with "node B adaptor 1". (10.2.1.x)

However, in the "Failover Cluster Management console > Networks", the "cluster network 2" is "node A adaptor 4" (192.168.x.x) pair with "node B adaptor 1" (10.2.1.x)

Any idea?
MikeyQiaoMSFT-0444 3,190 Reputation points

2024-08-16T10:01:24.6666667+00:00

I suggest double-checking whether the UDP port is enabled. Additionally, is it possible to modify the adapter under the nodeA network to adaptor1 in the cluster manager, or disable adaptor4 in nodeA and reconfigure the cluster?
Chong 866 Reputation points

2024-08-19T08:05:56.6733333+00:00

Hi @MikeyQiaoMSFT-0444 ,

How to modify the adaptor under node A in cluster manager? I cannot find any setting in cluster manager, any PowerShell to modify the network setting? Or you mean modify the IP in NIC directly?

Thanks

1 answer

MANISH RAJDOOT 0 Reputation points

2024-08-15T06:00:31.5433333+00:00
A SQL cluster unable to failover or start its services indicates a critical issue. Potential causes include network problems, storage failures, cluster configuration errors, or SQL Server service-specific problems.

Initial Steps:

Check Cluster Health: Verify all nodes are online and communicating. Inspect the cluster network and storage resources for issues.

Review Event Logs: Examine Windows and SQL Server logs for error messages related to the failover attempt or service startup.

Validate Cluster Configuration: Run a cluster validation report to identify potential configuration issues.

Check SQL Service Dependencies: Ensure all necessary services (like SQL Server Agent, Browser, etc.) are started and dependent on the SQL Server service.

Verify Disk Configuration: Confirm that all disk resources are online, accessible, and properly configured in the cluster.

Test Network Connectivity: Verify network connectivity between cluster nodes and the SQL Server instance.

Potential Issues and Solutions:

Network Problems: If network connectivity is disrupted, failover and service startup will fail. Check network adapters, cables, switches, and firewalls.

Storage Failures: Disk failures or resource group issues can prevent failover. Check disk status, rebuild failed disks, and verify resource group configuration.

Cluster Configuration Errors: Incorrectly configured cluster resources or dependencies can cause problems. Review cluster configuration and run validation tests.

SQL Server Service Issues: Issues with SQL Server service startup might be due to configuration errors, resource conflicts, or file system permissions. Check service configuration, resolve conflicts, and verify permissions.

Additional Considerations:

Manual Failover: If automatic failover is not working, try a manual failover to isolate the issue.

Third-Party Tools: Consider using specialized tools for cluster and SQL Server troubleshooting.

Support Involvement: If the issue persists, contact Microsoft SQL Server support for assistance.

By systematically addressing these areas, you can increase the chances of resolving the SQL cluster failover and service start issues effectively.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SQL cluster cannot failover. SQL services cannot start

1 answer

Your answer