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
Best Answer
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