8

When running below query on the primary/secondary replica of a SQL Server Always On Availability Group™

SELECT DISTINCT local_tcp_port,protocol_type,num_reads,num_writes
FROM sys.dm_exec_connections 
WHERE local_net_address is not null;

Two local tcp ports show up for the Database mirroring protocol, 5022 & 63420

Server Name local_tcp_port  protocol_type       num_reads   num_writes
ServerName  5022            Database Mirroring  102942598   5
ServerName  63420           Database Mirroring  5           89655349

The 5022 port is expected, as this is the one configured as the mirroring endpoint.

The other one seems to be a dynamic port, why and for what is this one used?

Could it have to do with the fact that one is showing a high number of reads (5022) and the other one showing a high number of writes (63420).

Build version: 13.0.5264.1

7

When an application establishes a TCP connection, it specifies a port for the inbound (receive) port, and uses a (somewhat) randomly selected port for outbound (source, or send). The inbound port for AG replication is 5022 by default, so all replication traffic is sent TO port 5022. Accordingly, you see the network reads occurring on port 5022, as SQL Server is listening on port 5022 and reading packets that are sent to that port.

The outbound port, which is selected randomly (more or less), is the port it is sent FROM. Accordingly, you see the writes associated with the random port (63420 in your example), as SQL Server is writing data to this port to be sent on the connection.

See How are source ports determined... for more

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.