Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.dm_db_mirroring_connections

 

This is a view in the mssqlsystemresource database.  Returns a row for each connection established for database mirroring.

Column name Data type Description

connection_id

uniqueidentifier

Identifier of the connection.

transport_stream_id

uniqueidentifier

Identifier of the SQL Server Network Interface (SNI) connection used by this connection for TCP/IP communications.

state

smallint

Current state of the connection. Possible values:

1 = NEW

2 = CONNECTING

3 = CONNECTED

4 = LOGGED_IN

5 = CLOSED

state_desc

nvarchar(60)

Current state of the connection. Possible values:

NEW

CONNECTING

CONNECTED

LOGGED_IN

CLOSED

connect_time

datetime

Date and time at which the connection was opened.

login_time

datetime

Date and time at which login for the connection succeeded.

authentication_method

nvarchar(128)

Name of the Windows Authentication method, such as NTLM or KERBEROS. The value comes from Windows.

principal_name

nvarchar(128)

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)

Name of the peer user from the other database that is used by Windows Authentication.

last_activity_time

datetime

Date and time at which the connection was last used to send or receive information.

is_accept

bit

Indicates whether the connection originated on the remote side.

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

0 = The connection was started by the local instance.

login_state

smallint

State of the login process for this connection. Possible values:

0 = Initial

1 = Negotiate

2 = SSPI

3 = PublicKeyLogin

4 = PublicKeyTentative

5 = LoggedIn

6 = Arbitration

login_state_desc

nvarchar(60)

Current state of login from the remote computer. Possible values:

Initial

Negotiate

SSPI

PublicKeyLogin

PublicKeyTentative

Arbitration

LoggedIn

peer_certificate_id

int

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

Encryption algorithm that is used for this connection. NULLABLE. Possible values:

Value Description Corresponding DDL option
0 NONE Disabled
1 RC4 {Required | Required algorithm RC4}
2 AES Required algorithm AES
3 NONE, RC4 {Supported | Supported algorithm RC4}
4 NONE, AES Supported algorithm RC4
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

 

encryption_algorithm_desc

nvarchar(60)

Textual representation of the encryption algorithm. NULLABLE. Possible Values:

Description Corresponding DDL option
NONE Disabled
RC4 {Required | Required Algorithm RC4}
AES Required Algorithm AES
NONE, RC4 {Supported | Supported Algorithm RC4}
NONE, AES Supported Algorithm RC4
RC4, AES Required Algorithm RC4 AES
AES, RC4 Required Algorithm AES RC4
NONE, RC4, AES Supported Algorithm RC4 AES
NONE, AES, RC4 Supported Algorithm AES RC4

 

receives_posted

smallint

Number of asynchronous network receives that have not yet completed for this connection.

is_receive_flow_controlled

bit

Whether network receives have been postponed due to flow control because the network is busy.

1 = True

sends_posted

smallint

The number of asynchronous network sends that have not yet completed for this connection.

is_send_flow_controlled

bit

Whether network sends have been postponed due to network flow control because the network is busy.

1 = True

total_bytes_sent

bigint

Total number of bytes sent by this connection.

total_bytes_received

bigint

Total number of bytes received by this connection.

total_fragments_sent

bigint

Total number of database mirroring message fragments sent by this connection.

total_fragments_received

bigint

Total number of database mirroring message fragments received by this connection.

total_sends

bigint

Total number of network send requests issued by this connection.

total_receives

bigint

Total number of network receive requests issued by this connection.

peer_arbitration_id

uniqueidentifier

Internal identifier for the endpoint. NULLABLE.

 




Syntax
CREATE VIEW sys.dm_db_mirroring_connections AS
    SELECT
	    connection_id,
	    transport_stream_id,
	    state,
	    state_desc = sn.name,
	    connect_time,
	    login_time,
	    authentication_method,
	    principal_name,
	    remote_user_name,
	    last_activity_time,
	    is_accept,
	    login_state,
	    login_state_desc = ln.name,
	    peer_certificate_id,
	    encryption_algorithm = encalg,
	    encryption_algorithm_desc = ag.name,
	    receives_posted,
	    is_receive_flow_controlled,
	    sends_posted,
	    is_send_flow_controlled,
	    total_bytes_sent,
	    total_bytes_received,
	    total_fragments_sent,
	    total_fragments_received,
	    total_sends,
	    total_receives,
	    peer_arbitration_id
    FROM OpenRowset (TABLE SBCONNECTIONENDPOINTS) as c
    LEFT OUTER JOIN sys.syspalvalues ag ON ag.class = 'EPET' AND ag.value = c.encalg
    LEFT OUTER JOIN sys.syspalvalues sn ON sn.class = 'EPCS' AND sn.value = c.state
    LEFT OUTER JOIN sys.syspalvalues ln ON ln.class = 'EPLS' AND ln.value = c.login_state
        WHERE c.endpoint_type = 1 -- x_eetDbMirroring

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash