To prepare for database mirroring,user has to perform three configuration steps:
1.Configuring Security and communication between instances:To establish a database mirror connection,SQL Server uses endpoints to specify the connection between servers. SQL Server performs authentication over the endpoints.This can be achieved by using Windows authentication or certificate based authentication.If witness server is also in the picture,then we need to specify the communication and authentication between the principal and the witness and between the mirror and witness. Here,since we will be creating the end point for database mirroring,only TCP can be used as transport protocol. Each database mirroring endpoint listens on a unique TCP port number.
The endpoints can be created with the CREATE ENDPOINT TSQL statement.
Syntax:
CREATE ENDPOINT endPointName [ AUTHORIZATION login ]
[ STATE = { STARTED | STOPPED | DISABLED } ]
AS { HTTP | TCP } (
<protocol_specific_arguments>
)
FOR { SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING } (
<language_specific_arguments>
)
<AS TCP_protocol_specific_arguments> ::=
AS TCP (
LISTENER_PORT = listenerPort
[ [ , ] LISTENER_IP = ALL | ( 4-part-ip ) | ( “ip_address_v6” ) ]
)
<FOR DATABASE_MIRRORING_language_specific_arguments> ::=
FOR DATABASE_MIRRORING (
[ AUTHENTICATION = {
WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
| CERTIFICATE certificate_name
| WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate_name
| CERTIFICATE certificate_name WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
[ [ [ , ] ] ENCRYPTION = { DISABLED | { { SUPPORTED | REQUIRED }
[ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ] }
]
[ , ] ROLE = { WITNESS | PARTNER | ALL }
)
Authentication= <authentication_options>
WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
Specifies the TCP/IP authentication requirements for connections for this endpoint. The default is WINDOWS. Along with the authentication the user has to mention the authorization method(NTLM or Kerberos).By default,the NEGOTIATE option is set,which will caus ethe endpoint to negotiate between NTLM or Kerberos.
CERTIFICATE certificate_name
The user can also specify that the endpoint has to authenticate using a certificate.This can be done by specifying the CERTIFICATE keyword and the name of the certificate.For certificate based authentication,the endpoint must have the certificate with the matching public key
WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate_name
Specifies that endpoint has to first try to connect by using Windows Authentication and, if that attempt fails, to then try using the specified certificate.
CERTIFICATE certificate_name WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
Specifies that endpoint has to first try to connect by using the specified certificate and, if that attempt fails, to then try using Windows Authentication.
Encryption
Next,we will take a look at the encryption option.By default,database mirroring uses RC4 encryption.
ENCRYPTION = { DISABLED | SUPPORTED | REQUIRED } [ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ]
Specifies whether encryption is used in the process. The default is REQUIRED.
Encryption options:
|
Option |
Description |
|
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 connections to this endpoint must use encryption. Therefore, to connect to this endpoint, another endpoint must have ENCRYPTION set to either SUPPORTED or REQUIRED |
Encryption Algorithm.
|
Option |
Description |
|
RC4 |
Specifies that the endpoint must use the RC4 algorithm. This is the default. |
|
AES |
Specifies that the endpoint must use the AES algorithm. |
|
AES RC4 |
Specifies that the two endpoints will negotiate for an encryption algorithm with this endpoint giving preference to the AES algorithm. |
|
RC4 AES |
Specifies that the two endpoints will negotiate for an encryption algorithm with this endpoint giving preference to the RC4 algorithm. |
RC4 is a relatively weak algorithm, and AES is a relatively strong algorithm. But AES is considerably slower than RC4. If security is a higher priority than speed,then AES is recommended.