resume

msSQL2014 AlwaysON Troubleshooting tips

You may get error or warnings are different levels of configuration and installation. Case by case basis the error reasons can be identified. You may contact me to know my experiences however Prime troubleshooting points I have listed below.

1.Check Firewall setting to allow 1433 and 5022 ports. try to telnet from each other host to verify that ports are opened.

firewall

  1. Check if windows logon user has grant CONNECT permission on the replica end points. if not then create using the following SQL

USE master;
GO
CREATE LOGIN [domain/primary_node$] FROM WINDOWS;
GO
GRANT CONNECT on ENDPOINT::Hadr_endpoint TO [domain/primary_node$];
GO

ts

  1. check if replica end point is listening to the 5022 port or not.
  • If endpoint is not available then then create the port using following SQL

create endpoint [Hadr_endpoint]
state=started
as tcp (listener_port = 5022, listener_ip = all)
for database_mirroring (role = all, authentication = windows negotiate, encryption = required algorithm aes)
GO

  • If endpoint is not started then start using the following SQL

alter endpoint [Hadr_endpoint] state = started

ts1

  1. check error description using the following SQL

select r.replica_server_name, r.endpoint_url,
rs.connected_state_desc, rs.last_connect_error_description,
rs.last_connect_error_number, rs.last_connect_error_timestamp
from sys.dm_hadr_availability_replica_states rs join sys.availability_replicas r
on rs.replica_id=r.replica_id
where rs.is_local=1

in the output, in column “error description” you will find significant important information to troubleshot.

  1. sometime AlwaysON databases might get out of sync and may show as suspect or Not syncronizing

Run the following SQL to resume it.

ALTER DATABASE [database name] SET HADR RESUME

  1. AG is in resolving state

it could be due to network fluctuation or node going into high CPU utilization. to fix increase the timeout by the following SQL

AVAILABILITY GROUP AG1 SET (HEALTH_CHECK_TIMEOUT = 40000);