Disclaimer: Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.
SQL Server uses an ‘End Point’ To talk to other SQL Servers Replicas within an Always On Availability Group(AAG). The most common problem we notice is firewalls blocking the port for SQL to talk between servers on Always On Availability Group(AAG), The AAG port needs to be open and remain open for SQL to work with AAG’s. By default, Standard SQL Server connections works on port 1433, but AAG’s do not talk to each other on this port.
you’re looking for the End Point Listener port, by default this is 5022.
How to check which port your endpoint is using for Always On:
select name, port, is_dynamic_port, state from sys.tcp_endpoints select * from sys.database_mirroring_endpoints
This also tells you if the endpoint is stopped(1) or started(0)
To check and see who the owner of your endpoints are, run this statement:
USE master GO SELECT e.name as EndpointName, sp.name AS EndpointOwner, et.PayloadType, e.state_desc FROM sys.endpoints e INNER JOIN sys.server_principals sp ON e.principal_id = sp.principal_id RIGHT OUTER JOIN ( VALUES ( 2, 'TSQL'), ( 3, 'SERVICE_BROKER'), ( 4, 'DATABASE_MIRRORING') ) AS et ( typeid, PayloadType ) ON et.typeid = e.type
There is a simple way to see if SQL Server is listening on this port; from the secondary you are having an issue with do the following;
--> Start CMD (RUN > CMD) -->Type; Telnet <Primary AAG IP Address> <SPACE> <Port Number> For example; TELNET 192.168.0.1 5022
If telnet works you will see a black screen, if it doesn’t you will get a connection failed message. Simple?
If it works, your secondary server should connect okay you’re likely seeing a different issue, if it doesn’t either the port is blocked OR the endpoint isn’t running. you will find the endpoint in SSMS under;
Server Objects > Endpoints > Database Mirroring
By Default AAG uses the “Hadr_endpoint” name.
If the endpoint isn’t there, you should create it, again;
CREATE ENDPOINT [Hadr_endpoint] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE , ENCRYPTION = REQUIRED ALGORITHM AES) GO
You have checked the firewalls
What next? try restarting the endpoint;
use master GO alter endpoint [Hadr_endpoint] state = stopped; GO alter endpoint [Hadr_endpoint] state = started; GO
If that fails you could try recreating the endpoint;
USE [master] GO DROP ENDPOINT [Hadr_endpoint] GO CREATE ENDPOINT [Hadr_endpoint] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE , ENCRYPTION = REQUIRED ALGORITHM AES) GO
In case of a multi node AAG, if one node is working fine, it’s likely not an endpoint issue. it’s more likely network/firewall.