Setting Up Database Mirroring
Using Certificates (Transact-
SQL)
Configuring Outbound Connections
A) To configure Host_A(PRINCIPLE_SERVER) for outbound
connections
1.On the master database, create the database master key,
if needed.
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD =
'<1_Strong_Password!>';
GO
2.Make a certificate for this server instance.
USE master;
CREATE CERTIFICATE HOST_A_cert
WITH SUBJECT = 'HOST_A certificate',
expiry_date='03/23/2080' ;
GO
3.Create a mirroring endpoint for server instance using the
certificate.
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_A_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
4.Back up the HOST_A(PRINCIPLE_SERVER) certificate, and
copy it to other system, HOST_B.
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\HOST_A_cert.cer';
B ) To configure Host_B(SECONDARY SERVER) for outbound
connections
1. On the master database, create the database master key,
if needed.
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD =
'<Strong_Password_#2>';
GO
2. Make a certificate on the HOST_B server instance.
CREATE CERTIFICATE HOST_B_cert
WITH SUBJECT = 'HOST_B certificate for database mirroring',
expiry_date='03/23/2080' ;
GO
3. Create a mirroring endpoint for the server instance on
HOST_B.
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_B_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
4. Back up HOST_B certificate.
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\HOST_B_cert.cer';
GO
5. Using any secure copy method, copy C:\HOST_B_cert.cer
to HOST_A.
C ) Configuring Inbound Connections
i) To configure Host_A(PRINCIPLE_SERVER) for inbound
connections
1. Create a login on HOST_A for HOST_B.
USE master;
CREATE LOGIN HOST_B_login WITH PASSWORD =
'1Sample_Strong_Password!@#';
GO
2. Create a user for that login.
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO
3. Associate the certificate with the user.
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = 'C:\HOST_B_cert.cer'
GO
4. Grant CONNECT permission on the login for the remote
mirroring endpoint.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO
[HOST_B_login];
GO
D) To configure Host_B(secondary_SERVER) for inbound
connections
1. Create a login on HOST_B for HOST_A.
USE master;
CREATE LOGIN HOST_A_login WITH PASSWORD =
'=Sample#2_Strong_Password2';
GO
2. Create a user for that login.
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
GO
3. Associate the certificate with the user.
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = 'C:\HOST_A_cert.cer'
GO
4. Grant CONNECT permission on the login for the remote
mirroring endpoint.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO
[HOST_A_login];
GO
NOTE: LOGIN SHOLUD BE CONNECT WISE VERSE
1. FIRST TIME Password should be reset of login users on
both the server
CERTIFICATED EXPIRE FOLLOW THE BELOW STEP
select name,expiry_date,* from sys.certificates
select * from sys.endpoints
SELECT name,type_desc,port, * FROM
sys.tcp_endpoints
1) Run this code first on the principal server.
Create Certificate principal_user_new_Mar23
expiry_date='12/31/2080' -- Provide far dated expiry date
With Subject ='principal_user_new_Mar23',
Go
--Alter the Endpoint to authenticate using the new certificate
alter Endpoint SQL_Mirror
State = Started
AS TCP (Listener_Port = 5022, Listener_IP = ALL)
For Database_Mirroring
(
Authentication = Certificate principal_user_new_Mar23, -- Use the new
certificate created in above step
Role = ALL
)
Go
--Backup the new certificate and copy it over to the Mirror server
Backup Certificate principal_user_new_Mar23
To File ='C:\RELEASES\principal_user_new_Mar23.cer' -- You need to
change the drive location to appropriate folder on your server
Go
2) Run this code next on the mirror server.
------RUN THIS SECOND ON MIRROR----
Create Certificate mirror_cert_new_Mar23
With Subject ='mirror_cert_new_Mar23',
expiry_date='03/23/2019' -- Provide Suitable Expiry date
Go
--Alter the Endpoint to authenticate using the new certificate
Alter Endpoint SQL_Mirror
State = Started
AS TCP (Listener_Port = 7023, Listener_IP = ALL)
For Database_Mirroring
(
Authentication = Certificate mirror_cert_new_Mar23,
Role = ALL
)
Go
--Backup the new certificate and copy it over to the Principal server
Backup Certificate mirror_cert_new_Mar23
To File ='C:\RELEASES\mirror_cert_new_Mar23.cer'-- You need to change
the drive location to appropriate folder on your server
Go
3) Ensure the certificates backed up in step 1 and 2 are copied across. That
is, the certificate backed up in step 1 on the principal needs to be copied over
to the mirror and the certificate backed up in step 2 on the mirror server needs
to be copied over to the principal server in the appropriate folder.
4) Run this code next on the principal server.
Use master
Go
-- Create a new SQL login with complex password
Create Login mirror_new_Mar23 With Password ='P@ssw0rd'
GO
-- Associate a user with the new Login
Create User mirror_user_new_Mar23 for Login mirror_new_Mar23
GO
-- Attach the Certificate created on the mirror Instance to the User–
Create Certificate mirror_cert_new_Mar23
Authorization mirror_user_new_Mar23
From File ='C:\RELEASES\mirror_cert_new_Mar23.cer' --Provide appropriate
folder location
GO
-- Grant Connect permissions to the new user on the mirroring endpoint
Grant Connect on Endpoint::SQL_mirror TO [mirror_new_Mar23]
GO
4) Run this code next on the mirror server.
Use master
Go
-- Create a new SQL login with complex password
Create Login principal_new_Mar23 With Password ='P@ssw0rd'
GO
-- Associate a user with the new Login
Create User principal_user_new_Mar23 for Login principal_new_Mar23
GO
-- Attach the Certificate created on the Principal to the User–
Create Certificate principal_user_new_Mar23
Authorization principal_user_new_Mar23
From File ='C:\RELEASES\principal_user_new_Mar23.cer'
GO
-- Grant Connect permissions to the new user on the mirroring endpoint
Grant Connect on Endpoint::SQL_Mirror TO [principal_new_Mar23]
GO
