Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)
SQL Server 2008 R2
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.
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:
-
Configuring Host_A for outbound connections.
-
Configuring Host_B for outbound connections.
For information about this stage of setting up database mirroring, see How to: Allow Database Mirroring to Use Certificates for Outbound Connections (Transact-SQL).
-
Configuring Inbound Connections
This example shows the steps for:
-
Configuring Host_A for inbound connections.
-
Configuring Host_B for inbound connections.
For information about this stage of setting up database mirroring, see How to: Allow Database Mirroring to Use Certificates for Inbound Connections (Transact-SQL).
-
Creating the Mirror Database
For information on how to create a mirror database, see How to: Prepare a Mirror Database for Mirroring (Transact-SQL).
-
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.
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_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 How to: Allow Database Mirroring to Use Certificates for Outbound Connections (Transact-SQL).
[Top]
Configuring Inbound Connections
To configure Host_A for inbound connections
-
Create a login on HOST_A for HOST_B.
USE master;
CREATE LOGIN HOST_B_login WITH PASSWORD = '1Sample_Strong_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 How to: Allow Database Mirroring to Use Certificates for Inbound Connections (Transact-SQL).
[Top]
Creating the Mirror Database
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 Specifying a Server Network Address (Database Mirroring).
--At HOST_B, set server instance on HOST_A as partner (principal server):
ALTER DATABASE AdventureWorks2008R2
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 AdventureWorks2008R2
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 transacton safety.
ALTER DATABASE AdventureWorks2008R2
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]
Article ID: 70, Created On: 4/27/2011, Modified: 11/1/2013