• Article

How to: Create a Mirroring Endpoint for Windows Authentication (Transact-SQL)

Each database mirroring server instance requires a unique listener port that is assigned to the database mirroring endpoint of the instance. A server instance can have only one database mirroring endpoint, which has a single port. A database mirroring endpoint can use any port that is available on the local system when the endpoint is created. All database mirroring sessions on a server instance listen on that port, and all incoming connections for database mirroring use that port.

When creating the endpoint, a system administrator specifies the authentication and encryption methods of the server instance.

Important

If a database mirroring endpoint exists and is already in use, we recommend that you use that endpoint for every session on the server instance. Dropping an in-use endpoint disrupts the connections of the existing sessions. If a witness has been set for a session, dropping the database mirroring endpoint can cause the principal server of that session to lose quorum; if that occurs, the database is taken offline and its users are disconnected. For more information, see Quorum: How a Witness Affects Database Availability.

To create a mirroring endpoint using Windows Authentication

  1. Connect to the server instance for which you want to create a database mirroring endpoint.

  2. Determine if a database mirroring endpoint already exists by using the following statement:

    SELECT name, role_desc, state_desc FROM sys.database_mirroring_endpoints

    Important

    If a database mirroring endpoint already exists for the server instance, use that endpoint for any other sessions you establish on the server instance.

  3. To use Transact-SQL to create an endpoint to use with Windows Authentication, use a CREATE ENDPOINT statement. The statement takes the following general form:

    CREATE ENDPOINT <endpointName>

        STATE=STARTED

        AS TCP ( LISTENER_PORT = <listenerPortList> )

        FOR DATABASE_MIRRORING

        (

            [ AUTHENTICATION = WINDOWS [ <authorizationMethod> ]

            ]

            [ [,] ENCRYPTION = REQUIRED

                    [ ALGORITHM { <algorithm> } ]

            ]

            [,] ROLE = <role>

        )

    where

    • <endpointName> is a unique name for the database mirroring endpoint of the server instance.

    • STARTED specifies that the endpoint is to be started and to begin listening for connections. A database mirroring endpoint typically is created in the STARTED state. Alternatively, you can start a session in a STOPPED state (the default) or DISABLED state.

    • <listenerPortList> is a single port number (nnnn) on which you want the server to listen for database mirroring messages. Only TCP is allowed; specifying any other protocol causes an error.

      A port number can be used only once per computer system. A database mirroring endpoint can use any port that is available on the local system when the endpoint is created. To identify the ports currently being used by TCP endpoints on the system, use the following Transact-SQL statement:

      SELECT name, port FROM sys.tcp_endpoints

      Important

      Each server instance requires one and only one unique listener port.

    • For Windows Authentication, the AUTHENTICATION option is optional, unless you want the endpoint to use only NTLM or Kerberos to authenticate connections. <authorizationMethod> specifies the method used to authenticate connections as one of the following: NTLM, KERBEROS, or NEGOTIATE. The default, NEGOTIATE, causes the endpoint to use the Windows negotiation protocol to choose either NTLM or Kerberos. Negotiation enables connections with or without authentication, depending on the authentication level of the opposite endpoint. For information about these methods, see Endpoint Authentication Types.

    • ENCRYPTION is set to REQUIRED by default. This means that all connections to this endpoint must use encryption. However, you can disable encryption or make it optional on an endpoint. The alternatives are as follows:

      Value

      Definition

      DISABLED

      Specifies that data sent over a connection is not encrypted.

      SUPPORTED

      Specifies that the data is encrypted only if the opposite endpoint specifies either SUPPORTED or REQUIRED.

      REQUIRED

      Specifies that data sent over a connection must be encrypted.

      If an endpoint requires encryption, the other endpoint must have ENCRYPTION set to either SUPPORTED or REQUIRED.

    • <algorithm> provides the option of specifying the encryption standards for the endpoint. The value of <algorithm> can be one following algorithms or combinations of algorithms: RC4, AES, AES RC4, or RC4 AES.

      AES RC4 specifies that this endpoint will negotiate for the encryption algorithm, giving preference to the AES algorithm. RC4 AES specifies that this endpoint will negotiate for the encryption algorithm, giving preference to the RC4 algorithm. If both endpoints specify both algorithms but in different orders, the endpoint accepting the connection wins.

      Note

      Though considerably faster than AES, RC4 is a relatively weak algorithm, while AES is a relatively strong algorithm. Therefore, we recommend that you use the AES algorithm.

    • <role> defines the role or roles that the server can perform. Specifying ROLE is required.

      To allow a server instance to serve as one role for one database mirroring session and different role for another session, specify ROLE=ALL. To restrict a server instance to being either a partner or a witness, specify ROLE=PARTNER or ROLE=WITNESS, respectively.

      Note

      For SQL Server Express, WITNESS is the only option available.

    For a complete description of the CREATE ENDPOINT syntax, see CREATE ENDPOINT (Transact-SQL).

    Note

    To change an existing endpoint, use ALTER ENDPOINT (Transact-SQL).

Example

The following example creates endpoints for the default server instances on three separate computer systems:

Role of server instance

Name of host computer

Partner (initially in the principal role)

SQLHOST01\.

Partner (initially in the mirror role)

SQLHOST02\.

Witness

SQLHOST03\.

In this example, all three endpoints use port number 7022, though any available port number would work. The AUTHENTICATION option is unnecessary, because the endpoints use the default type, Windows Authentication. The ENCRYPTION option is also unnecessary, because the endpoints are all intended to negotiate the authentication method for a connection, which is the default behavior for Windows Authentication. Also, all of the endpoints require the encryption, which is the default behavior.

Each server instance is limited to serving as either a partner or a witness, and the endpoint of each server expressly specifies which role (ROLE=PARTNER or ROLE=WITNESS).

Important

Each server instance can have only one endpoint. Therefore, if you want a server instance to be a partner in some sessions and the witness in others, specify ROLE=ALL.

--Endpoint for initial principal server instance, which
--is the only server instance running on SQLHOST01.
CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7022 )
    FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO
--Endpoint for initial mirror server instance, which
--is the only server instance running on SQLHOST02.
CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7022 )
    FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO
--Endpoint for witness server instance, which
--is the only server instance running on SQLHOST03.
CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7022 )
    FOR DATABASE_MIRRORING (ROLE=WITNESS);
GO

For a complete example showing security setup, preparing the mirror database, setting up the partners, and adding a witness, see Setting Up Database Mirroring.