How to ensure SQL Cluster is running properly after failover

Question

How to ensure SQL Cluster is running properly after failover?

Answer

Here are a few suggestions:

Verify Cluster Status

Check Cluster Nodes: Ensure that all nodes in the cluster are online and available.

Get-ClusterNode

Check Cluster Resource Status: Verify that all cluster resources are online.

Get-ClusterResource

Check Quorum Configuration: Ensure the quorum configuration is correct and operational.

Get-ClusterQuorum

Verify SQL Server Instances

SQL Server Services: Check that the SQL Server and SQL Server Agent services are running on the active node.

Get-Service -Name MSSQLSERVER, SQLSERVERAGENT

Check SQL Server Error Logs

Review Error Logs: Look at the SQL Server error logs for any errors or warnings that may indicate issues.

EXEC xp_readerrorlog 0, 1, N'failover';

Verify Databases

Database Status: Check the status of all databases to ensure they are online and accessible.

SELECT name, state_desc FROM sys.databases;

Database Consistency: Run DBCC CHECKDB to ensure database integrity.

DBCC CHECKDB ('YourDatabaseName');

Verify Connectivity

Client Connections: Ensure that clients can connect to the SQL Server instance.

SELECT @@SERVERNAME AS 'ServerName';

Network Configuration: Verify that the network configuration is correct and that the SQL Server is listening on the appropriate ports.

Perform Functional Tests

Query Execution: Execute some queries to ensure the SQL Server is processing requests correctly.

SELECT COUNT(*) FROM YourTable;

Application Tests: Verify that the applications that depend on the SQL Server are functioning as expected.

Review Cluster Events

Cluster Events: Check the cluster events in the Failover Cluster Manager for any issues during the failover process.

Get-ClusterLog -Destination C:\ClusterLogs -TimeSpan 5

Monitor Performance

Performance Metrics: Monitor key performance metrics (CPU, memory, disk I/O) to ensure the SQL Server is performing optimally.

Get-Counter -Counter "\Processor(_Total)% Processor Time"

SQL Server Performance: Use SQL Server performance monitoring tools to check for any performance bottlenecks.

Backup and Restore Verification

Backup Jobs: Ensure that all backup jobs are scheduled and running correctly on the new primary node.

SELECT * FROM msdb.dbo.sysjobs WHERE name LIKE '%backup%';

Restore Test: Perform a test restore of a recent backup to verify backup integrity.

Review Cluster Configuration

Cluster Configuration: Verify that the cluster configuration is as expected, including preferred owners and possible owners settings for SQL Server resources.

Get-ClusterResource -Name "SQL Server (INST1)" | Get-ClusterOwnerNode

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer

Check Cluster Status: Use the Cluster Management Tool or SQL Server Management Studio to verify the cluster's status. Ensure all nodes are online and the cluster is functional.
Verify SQL Server Status: Check the SQL Server instance status on the new primary node. Ensure it's running and accepting connections.
Check Database Status: Verify the status of all databases. Ensure they're online and accessible.
Validate Data Consistency: Run DBCC CHECKDB to ensure data consistency and integrity.
Monitor Performance: Closely monitor performance metrics like CPU, memory, and disk usage to ensure the cluster is operating within expected limits.
Test Connectivity: Perform test queries and connections to ensure applications can connect and interact with the database seamlessly.
Review Cluster Logs: Examine cluster logs for any errors or issues during the failover process.
Verify Quorum and Witness: Ensure the quorum and witness configurations are correct and functioning as expected.

Answer

Hi，WANG Yuzhi

After a failover occurs, you should first verify the cluster status to ensure the continuity of the production environment and that the remaining nodes of the cluster are functioning properly, perform a database integrity check (DBCC CHECKDB command), monitor the database performance, and verify whether the SQL Server service is running normally on the new active node.

Then, gather information, diagnose the cause of the failure, and assess whether the current failover strategy meets the actual business needs.

The last step is to troubleshoot the issue to restore the original cluster status. Below are a few official documents for reference.

Failover Policy for Failover Cluster Instances

View and Read Failover Cluster Instance Diagnostics Log

Recover from failover cluster instance failure

Best Regards,

Mikey Qiao

If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.

Answer

Ensuring that an SQL Server Cluster is running properly after a failover involves several steps. These steps include verifying the cluster configuration, checking the availability of resources, and ensuring that all services are operational. Here is a comprehensive approach to ensure your SQL Server Cluster is running properly after a failover:

1. Verify Cluster Configuration and Status

Check the Cluster Nodes: Ensure all cluster nodes are online and functioning. Use the Failover Cluster Manager to verify the status of each node.
Validate Cluster Configuration: Run the cluster validation wizard to check for configuration issues.
```
  Test-Cluster -Node "Node1","Node2"
```

2. Verify SQL Server Instance

Check SQL Server Services: Ensure that SQL Server services are running on the active node.
```
  Get-Service -Name "MSSQLSERVER"
```
Failover Cluster Manager: Check the status of SQL Server resources in the Failover Cluster Manager.

3. Database Health Checks

Database State: Verify that all databases are in a healthy state.
```
  SELECT name, state_desc FROM sys.databases;
```
Database Integrity: Run integrity checks on your databases.
```
  DBCC CHECKDB ('YourDatabaseName');
```

4. Logins and Permissions

Logins: Ensure that all necessary logins are present and have the correct permissions.


  SELECT name, sid, type_desc FROM sys.server_principals WHERE type_desc = 'SQL_USER';

5. Job and Agent Status

SQL Server Agent: Verify that the SQL Server Agent is running and that scheduled jobs are functioning correctly.
```
  EXEC msdb.dbo.sp_help_job;
```

6. Availability Groups and Replication (if applicable)

Availability Groups: Check the status of AlwaysOn Availability Groups.


  SELECT ag.name, ar.replica_server_name, ar.role_desc

  FROM sys.availability_replicas ar

  JOIN sys.availability_groups ag ON ar.group_id = ag.group_id;

Replication: Verify that replication is functioning correctly.
```
  EXEC sp_replmonitorhelppublisher;
```

7. Application Connectivity

Application Testing: Ensure that applications can connect to the database without issues.
Connection Strings: Verify that connection strings in your application configurations point to the correct SQL Server instance.

8. Event Logs and Alerts

SQL Server Logs: Review SQL Server error logs for any issues or warnings.
```
  EXEC sp_readerrorlog;
```

Windows Event Logs: Check Windows event logs on all cluster nodes for any related errors.


  Get-EventLog -LogName System -Newest 100 | Where-Object { $_.Source -eq "FailoverClustering" }

9. Performance Monitoring

Performance Counters: Monitor SQL Server performance counters to ensure there are no bottlenecks.
```
  Get-Counter -Counter "\SQLServer:Buffer Manager\Buffer cache hit ratio"
```
Resource Utilization: Check CPU, memory, and disk utilization on the active node.

10. Backup and Restore Verification

Backups: Verify that backups are running as scheduled and that recent backups are available.
```
  SELECT backup_finish_date, database_name, type

  FROM msdb.dbo.backupset;
```
Restore Test: Perform a test restore to ensure backups are valid.

Ensuring that a SQL Server Cluster is running properly after a failover involves a multi-step process that includes verifying the cluster and SQL Server configurations, checking the health of databases, ensuring that logins and permissions are correct, confirming that jobs and agents are running, validating replication and availability groups, testing application connectivity, reviewing event logs, monitoring performance, and verifying backup and restore functionality. Each of these steps helps ensure the overall health and availability of your SQL Server Cluster after a failover.

Share via