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.

Introduction:

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.