SQL Server Availability group is HA and DR method configured on production server to offload the primary server performance and make databases available in case of disaster.
SQL Server AG is available from SQL Server 2012. To configure SQL Server AG, we need to configure SQL cluster between two nodes which are part of SQL availability group.
Sometime SQL AG get out of sync, and we receive an alert saying that SQL cluster is not running or lost its communication to secondary node of SQL cluster or SQL service not starting
There are many types of error we get for that we need to review SQL cluster log and event viewer log.
Sometimes we get below error in SQL Cluster.
Cluster and HA is down on Server1/Server2. Looks like there is issue with communication between 2 nodes. Server2 is showing down in cluster manager.
Node ‘Server2’ failed to form a cluster. This was because the witness was not accessible. Please ensure that the witness resource is online and available. cluster and HA are down on this server
When you check Event viewer logs and SQL Cluster logs and found below error
The TLS server credential’s certificate does not have a private key information property attached to it. This most often occurs when a certificate is backed up incorrectly and then later restored. This message can also indicate a certificate enrollment failure.
This issue occurred because cluster certificate may get corrupted on SQL server which is part of SQL server Availability group.
How to Resolve this SQL Server service won’t start due to certificate issues?
Step 1: We have to delete corrupted certificate from the node which is part of SQL server cluster.
Step 2: Take backup of valid certificate from SQL cluster node where SQL Server service is running.
For this do the below steps :
Go to path --> RUN--> MMC --> File -->Add or Remove Snap-ins --> Certificates
Once you got into above path select below path:
Certificates snap-ins --> Computer account --> Finish --> OK
Once you got into above path Certificate tab will get opened.
Select Personal in certificates tab and you will find valid certificate. Take backup of certificate.
Step 3: Copy certificate file(.CER) taken in above step to node where SQL server service having issue and import certificate using import option:
Go to Certificates --> Personal --> Right click and select All task --> import -> select file --> OK
This will import certificate on server.
Step 4: Map imported certificate to SQL server service in SQL Server configuration manager.
SQL Server Network Configuration --> Protocol for MSSQLServer -->Property --> Goto Certificate Tab
Select certificate from drop down and click OK.
Step 5: Restart SQL service post certificate mapping.
Step 6: Post this go to Failover Cluster manager and verify Cluster service is running.
Post this open SSMS and connect SQL server and verify it. Also check listener connectivity for AlwaysON.