Edit

sys.dm_db_mirroring_connections (Database mirroring)

Applies to: SQL Server

Returns a row for each connection established for database mirroring.

Column name Data type Nullable Description
connection_id uniqueidentifier Yes Identifier of the connection.
transport_stream_id uniqueidentifier Yes Identifier of the SQL Server Network Interface (SNI) connection used by this connection for TCP/IP communications.
state smallint Yes Current state of the connection. Possible values:

1 = New
2 = Connecting
3 = Connected
4 = Signed in
5 = Closed
state_desc nvarchar(60) Yes Current state of the connection. Possible values:

NEW
CONNECTING
CONNECTED
LOGGED_IN
CLOSED
connect_time datetime Yes Date and time at which the connection was opened.
login_time datetime Yes Date and time at which login for the connection succeeded.
authentication_method nvarchar(128) Yes Name of the Windows Authentication method, such as NTLM or KERBEROS. The value comes from Windows.
principal_name nvarchar(128) Yes Name of the login that was validated for connection permissions. For Windows Authentication, this value is the remote user name. For certificate authentication, this value is the certificate owner.
remote_user_name nvarchar(128) Yes Name of the peer user from the other database that is used by Windows Authentication.
last_activity_time datetime Yes Date and time at which the connection was last used to send or receive information.
is_accept bit Yes Indicates whether the connection originated on the remote side.

1 = The connection is a request accepted from the remote instance.

0 = The local instance started the connection.
login_state smallint Yes State of the login process for this connection. For possible values, see Login state values.
login_state_desc nvarchar(60) Yes Current state of login from the remote computer. For possible values, see Login state values.
peer_certificate_id int Yes The local object ID of the certificate used by the remote instance for authentication. The owner of this certificate must have CONNECT permissions to the database mirroring endpoint.
encryption_algorithm smallint Yes Encryption algorithm that is used for this connection. For possible values, see Encryption algorithm values.
encryption_algorithm_desc nvarchar(60) Yes Textual representation of the encryption algorithm. For possible values, see the encryption_algorithm_desc column in Encryption algorithm values.
receives_posted smallint Yes Number of asynchronous network receive operations that aren't yet completed for this connection.
is_receive_flow_controlled bit Yes Whether network receive operations are postponed due to flow control because the network is busy.

1 = True
sends_posted smallint Yes The number of asynchronous network send operations that aren't yet completed for this connection.
is_send_flow_control bit Yes Whether network send operations are postponed due to network flow control because the network is busy.

1 = True
total_bytes_sent bigint Yes Total number of bytes sent by this connection.
total_bytes_received bigint Yes Total number of bytes received by this connection.
total_fragments_sent bigint Yes Total number of database mirroring message fragments sent by this connection.
total_fragments_received bigint Yes Total number of database mirroring message fragments received by this connection.
total_sends bigint Yes Total number of network send requests issued by this connection.
total_receives bigint Yes Total number of network receive requests issued by this connection.
peer_arbitration_id uniqueidentifier Yes Internal identifier for the endpoint.
address nvarchar(256) Yes Peer address in the form of TCP://peer_host:peer_port.
encryption_key_bit_length int Yes Length of the session encryption keys, in bits. Possible values are 128 or 256.
encryption_protocol_version nvarchar(32) Yes When encryption_algorithm_desc is either RC4 (deprecated) or AES, the value is the negotiated UCS encryption protocol version number, from 1 to 4:

1 = SQL Server 2005 (9.x) and SQL Server 2008 (10.0.x)

2 = SQL Server 2012 (11.x)

3 = SQL Server 2012 (11.x) with UCS redirection support

4 = SQL Server 2016 (13.x)

When encryption_algorithm_desc is TLS, this value displays the TLS version (for example 1.2 or 1.3)

Login state values

The following table describes login_state and login_state_desc.

login_state login_state_desc Details
0 INITIAL Connection handshake is initializing.
1 WAIT LOGIN NEGOTIATE Connection handshake is waiting for Login Negotiate message.
2 ONE ISC Connection handshake initialized and sent security context for authentication.
3 ONE ASC Connection handshake received and accepted security context for authentication.
4 TWO ISC Connection handshake initialized and sent security context for authentication. There's an optional mechanism available for authenticating the peers.
5 TWO ASC Connection handshake received and sent accepted security context for authentication. There's an optional mechanism available for authenticating the peers.
6 WAIT ISC Confirm Connection handshake is waiting for Initialize Security Context Confirmation message.
7 WAIT ASC Confirm Connection handshake is waiting for Accept Security Context Confirmation message.
8 WAIT REJECT Connection handshake is waiting for SSPI rejection message for failed authentication.
9 WAIT PRE-MASTER SECRET Connection handshake is waiting for Pre-Master Secret message.
10 WAIT VALIDATION Connection handshake is waiting for Validation message.
11 WAIT ARBITRATION Connection handshake is waiting for Arbitration message.
12 ONLINE Connection handshake is complete and is online (ready) for message exchange.
13 ERROR Connection is in error.

Encryption algorithm values

The following table describes the possible values for the encryption algorithm.

encryption_algorithm encryption_algorithm_desc Corresponding DDL option
0 None Disabled
1 RC4 Required algorithm RC4
2 AES Required algorithm AES
3 None, RC4 Supported algorithm RC4
4 None, AES Supported algorithm AES
5 RC4, AES Required algorithm RC4 AES
6 AES, RC4 Required algorithm AES RC4
7 None, RC4, AES Supported algorithm RC4 AES
8 None, AES, RC4 Supported algorithm AES RC4

The RC4 algorithm is only supported for backward compatibility. New material can only be encrypted using RC4 or RC4_128 when the database is in compatibility level 90 or 100 (not recommended). Use one of the AES algorithms instead. In SQL Server 2012 (11.x) and later versions, material encrypted using RC4 or RC4_128 can be decrypted in any compatibility level.

Permissions

SQL Server 2019 (15.x) and earlier versions require VIEW SERVER STATE permission on the server.

SQL Server 2022 (16.x) and later versions require VIEW SERVER PERFORMANCE STATE permission on the server.

Physical joins

Diagram of physical joins for sys.join_dm_db_mirroring_connections.

Relationship cardinalities

From To Relationship
dm_db_mirroring_connections.connection_id dm_exec_connections.connection_id One-to-one

Related content

  • Last updated on