Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)

Article
11/25/2024

Applies to: SQL Server

This example shows all the stages required to create a database mirroring session using certificate-based authentication. The examples in this topic use Transact-SQL. Unless you can guarantee that your network is secure, we recommend that you use encryption for database mirroring connections.

When copying a certificate to another system, use a secure copy method. Be extremely careful to keep all of your certificates secure.

Example

The following example demonstrates what must be done on one partner that resides on HOST_A. In this example, the two partners are the default server instances on three computer systems. The two server instances run in nontrusted Windows domains, so certificate-based authentication is required.

The initial principal role is taken by HOST_A, and the mirror role is taken by HOST_B.

Setting up database mirroring using certificates involves four general stages, of which three stages-1, 2, and 4-are demonstrated by this example. These stages are as follows:

Configuring Outbound Connections

This example shows the steps for:
1. Configuring Host_A for outbound connections.
2. Configuring Host_B for outbound connections.
For information about this stage of setting up database mirroring, see Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL).
Configuring Inbound Connections

This example shows the steps for:
1. Configuring Host_A for inbound connections.
2. Configuring Host_B for inbound connections.
For information about this stage of setting up database mirroring, see Allow a Database Mirroring Endpoint to Use Certificates for Inbound Connections (Transact-SQL).
Creating the Mirror Database

For information on how to create a mirror database, see Prepare a Mirror Database for Mirroring (SQL Server).
Configuring the Mirroring Partners

Configuring Outbound Connections

To configure Host_A for outbound connections

On the master database, create the database master key, if needed. Replace <password> with a valid password.
```
USE master;  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';  
GO  
```

Make a certificate for this server instance.

USE master;  
CREATE CERTIFICATE HOST_A_cert   
   WITH SUBJECT = 'HOST_A certificate';  
GO

Create a mirroring endpoint for server instance using the certificate.

CREATE ENDPOINT Endpoint_Mirroring  
   STATE = STARTED  
   AS TCP (  
      LISTENER_PORT=7024  
      , LISTENER_IP = ALL  
   )   
   FOR DATABASE_MIRRORING (   
      AUTHENTICATION = CERTIFICATE HOST_A_cert  
      , ENCRYPTION = REQUIRED ALGORITHM AES  
      , ROLE = ALL  
   );  
GO

Back up the HOST_A certificate, and copy it to other system, HOST_B.

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\HOST_A_cert.cer';  
GO

Using any secure copy method, copy C:\HOST_A_cert.cer to HOST_B.

To configure Host_B for outbound connections

On the master database, create the database master key, if needed.

USE master;  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password_#2>';  
GO

Make a certificate on the HOST_B server instance.

CREATE CERTIFICATE HOST_B_cert   
   WITH SUBJECT = 'HOST_B certificate for database mirroring';  
GO

Create a mirroring endpoint for the server instance on HOST_B.

CREATE ENDPOINT Endpoint_Mirroring  
   STATE = STARTED  
   AS TCP (  
      LISTENER_PORT=7024  
      , LISTENER_IP = ALL  
   )   
   FOR DATABASE_MIRRORING (   
      AUTHENTICATION = CERTIFICATE HOST_B_cert  
      , ENCRYPTION = REQUIRED ALGORITHM AES  
      , ROLE = ALL  
   );  
GO

Back up HOST_B certificate.

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\HOST_B_cert.cer';  
GO

Using any secure copy method, copy C:\HOST_B_cert.cer to HOST_A.

For more information, see Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL).

[Top of Example]

Configuring Inbound Connections

To configure Host_A for inbound connections

Create a login on HOST_A for HOST_B. Replace <password> with a valid password.

USE master;  
CREATE LOGIN HOST_B_login WITH PASSWORD = '<password>';  
GO

--Create a user for that login.

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;  
GO

--Associate the certificate with the user.

CREATE CERTIFICATE HOST_B_cert  
   AUTHORIZATION HOST_B_user  
   FROM FILE = 'C:\HOST_B_cert.cer'  
GO

Grant CONNECT permission on the login for the remote mirroring endpoint.

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];  
GO

To configure Host_B for inbound connections

Create a login on HOST_B for HOST_A.

USE master;  
CREATE LOGIN HOST_A_login WITH PASSWORD = '=Sample#2_Strong_Password2';  
GO

Create a user for that login.

CREATE USER HOST_A_user FOR LOGIN HOST_A_login;  
GO

Associate the certificate with the user.

CREATE CERTIFICATE HOST_A_cert  
   AUTHORIZATION HOST_A_user  
   FROM FILE = 'C:\HOST_A_cert.cer'  
GO

Grant CONNECT permission on the login for the remote mirroring endpoint.

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];  
GO

Important

If you intend to run in high-safety mode with automatic failover, you must repeat the same setup steps to configure the witness for outbound and inbound connections. Setting up the inbound connections when a witness is involved requires that you set up logins and users for the witness on both of the partners and for both partners on the witness.

For more information, see Allow a Database Mirroring Endpoint to Use Certificates for Inbound Connections (Transact-SQL).

[Top of Example]

Creating the Mirror Database

For information on how to create a mirror database, see Prepare a Mirror Database for Mirroring (SQL Server).

Configuring the Mirroring Partners

On the mirror server instance on HOST_B, set the server instance on HOST_A as the partner (making it the initial principal server instance). Substitute a valid network address for TCP://HOST_A.Mydomain.Corp.Adventure-Works``.com:7024. For more information, see Specify a Server Network Address (Database Mirroring).
```
--At HOST_B, set server instance on HOST_A as partner (principal server):  
ALTER DATABASE AdventureWorks   
    SET PARTNER = 'TCP://HOST_A.Mydomain.Corp.Adventure-Works.com:7024';  
GO  
```
On the principal server instance on HOST_A, set the server instance on HOST_B as the partner (making it the initial mirror server instance). Substitute a valid network address for TCP://HOST_B.Mydomain.Corp.Adventure-Works.com:7024.
```
--At HOST_A, set server instance on HOST_B as partner (mirror server).  
ALTER DATABASE AdventureWorks   
    SET PARTNER = 'TCP://HOST_B.Mydomain.Corp.Adventure-Works.com:7024';  
GO  
```
This example assumes that the session will be running in high-performance mode. To configure this session for high-performance mode, on the principal server instance (on HOST_A), set transaction safety to OFF.
```
--Change to high-performance mode by turning off transaction safety.  
ALTER DATABASE AdventureWorks   
    SET PARTNER SAFETY OFF  
GO  
```
Note

If you intend to run in high-safety mode with automatic failover, leave transaction safety set to FULL (the default setting) and add the witness as soon as possible after executing the second SET PARTNER 'partner_server' statement. Note that the witness must first be configured for outbound and inbound connections.

[Top of Example]

Share via

Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)

Example

Configuring Outbound Connections

Configuring Inbound Connections

Creating the Mirror Database

Configuring the Mirroring Partners

Related Tasks

See Also

Feedback

Additional resources