Create a Database Mirroring Endpoint for Windows Authentication (Transact-SQL)
This topic describes how to create a database mirroring endpoint that uses Windows Authentication in SQL Server 2012 by using Transact-SQL. To support database mirroring or AlwaysOn Availability Groups each instance of SQL Server requires a database mirroring endpoint. 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.
Ważne: |
---|
If a database mirroring endpoint exists and is already in use, we recommend that you use that endpoint. Dropping an in-use endpoint disrupts existing sessions. |
In This Topic
Before you begin: Security
To create a database mirroring endpoint, using: Transact-SQL
Before You Begin
Security
The authentication and encryption methods of the server instance are established by the system administrator.
Uwaga dotycząca zabezpieczeń |
---|
The RC4 algorithm is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you use AES. |
Permissions
Requires CREATE ENDPOINT permission, or membership in the sysadmin fixed server role. For more information, see GRANT Endpoint Permissions (Transact-SQL).
[Top]
Using Transact-SQL
To Create a Database Mirroring Endpoint That Uses Windows Authentication
Connect to the instance of SQL Server on which you want to create a database mirroring endpoint.
From the Standard bar, click New Query.
Determine if a database mirroring endpoint already exists by using the following statement:
SELECT name, role_desc, state_desc FROM sys.database_mirroring_endpoints
Ważne: If a database mirroring endpoint already exists for the server instance, use that endpoint for any other sessions you establish on the server instance.
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
Ważne: 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.
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.
[!UWAGA]
The RC4 algorithm is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you use AES.
<role> defines the role or roles that the server can perform. Specifying ROLE is required. However, the role of the endpoint is relevant only for database mirroring. For AlwaysOn Availability Groups, the role of the endpoint is ignored.
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.
[!UWAGA]
For more information about Database Mirroring options for different editions of SQL Server, see Features Supported by the Editions of SQL Server 2012 (https://go.microsoft.com/fwlink/?linkid=232473).
For a complete description of the CREATE ENDPOINT syntax, see CREATE ENDPOINT (Transact-SQL).
[!UWAGA]
To change an existing endpoint, use ALTER ENDPOINT (Transact-SQL).
Example: Creating Endpoints to Support for Database Mirroring (Transact-SQL)
The following example creates database mirroring 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).
Ważne: |
---|
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
[Top]
Related Tasks
To Configure a Database Mirroring Endpoint
Create a Database Mirroring Endpoint for AlwaysOn Availability Groups (SQL Server PowerShell)
Use Certificates for a Database Mirroring Endpoint (Transact-SQL)
Specify the Endpoint URL When Adding or Modifying an Availability Replica (SQL Server)
To View Information About the Database Mirroring Endpoint
[Top]
Zobacz także
Odwołanie
CREATE ENDPOINT (Transact-SQL)
Koncepcje
Choose an Encryption Algorithm
Specify a Server Network Address (Database Mirroring)
Example: Setting Up Database Mirroring Using Windows Authentication (Transact-SQL)