You have to follow these 4 steps for each partner (Principal, Mirror, and Witness) for configuring database mirroring through using Certificates.
1. First you need to create a database Master Key, in the master database.
2. In the master database, create an encrypted certificate on the SQL Server Instance.
3. Then you have to create an endpoint for the SQL server instance using its certificate.
4. Back up the certificate and securely copy the backup file it to the other systems.
You must complete these above 4 steps for each partner and the witness, if there is a witness server.
Step1. Run below script for creating encryption key, certificate and end-points on Principal Server.....
1.1 On the master database, create the database master key, if needed through below script.
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password'
GO
1.2 Make a certificate for this Principal server.
USE MASTER
GO
CREATE CERTIFICATE Principal_Cert
WITH SUBJECT = 'Principal Certificate';
GO
1.3 Create a mirroring endpoint for server instance using the certificate.
CREATE ENDPOINT End_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE Principal_Cert,
ENCRYPTION = REQUIRED ALGORITHM RC4,
ROLE = ALL
)
GO
1.4 Back up the certificate and copy it to the Mirror Server, in order to configure inbound connections on the other servers.
USE MASTER
GO
BACKUP CERTIFICATE Principal_Cert
TO FILE = 'C:\Certificate\Principal_Cert.cer'
GO
Step 2. Run below script for creating encryption key, certificate and end-points on Mirror Server.....
2.1 On the master database, create the database master key, if needed through below script.
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password'
GO
2.2 Make a certificate for this Mirror server.
USE MASTER
GO
CREATE CERTIFICATE Mirror_Cert
WITH SUBJECT = 'Mirror Certificate';
GO
2.3 Create a mirroring endpoint for server instance using the certificate.
CREATE ENDPOINT End_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE Mirror_Cert,
ENCRYPTION = REQUIRED ALGORITHM RC4,
ROLE = ALL
)
GO
2.4 Back up the certificate and copy it to the Principal Server, in order to configure inbound connections on the other servers.
USE MASTER
GO
BACKUP CERTIFICATE Mirror_Cert
TO FILE = 'C:\Certificate\Mirror_Cert.cer'
GO
Step 3. Run below script for creating encryption key, certificate and end-points on Witness Server.....
3.1 On the master database, create the database master key, if needed through below script.
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password'
GO
3.2 Make a certificate for this Witness server.
USE MASTER
GO
CREATE CERTIFICATE Witness_Cert
WITH SUBJECT = 'Witness Certificate';
GO
3.3 Create a mirroring endpoint for server instance using the certificate.
CREATE ENDPOINT End_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE Witness_Cert,
ENCRYPTION = REQUIRED ALGORITHM RC4,
ROLE = ALL
)
GO
3.4 Back up the certificate and copy it to the Principal and Mirror Server, in order to configure inbound connections on the other servers.
USE MASTER
GO
BACKUP CERTIFICATE Witness_Cert
TO FILE = 'C:\Certificate\Witness_Cert.cer'
GO
Step 4. Run below script in Principal Instance for creating login, user and associate certificate with user on Principal Server.
Now copy the Mirror_Cert.cer and Witness_Cert.cer on to the Principal Instance.
4.1 We are creating a SQL Login here, for Windows logins, use the Grant Login instead of Create Login...
USE MASTER
GO
CREATE LOGIN Mirror_login WITH PASSWORD = 'Password'
GO
USE MASTER
GO
CREATE LOGIN Witness_login WITH PASSWORD = 'Password'
GO
4.2 Create a user for that login.
USE MASTER
GO
CREATE USER Mirror_User FOR LOGIN Mirror_login
GO
USE MASTER
GO
CREATE USER Witness_User FOR LOGIN Witness_login
GO
4.3 Associate the certificate with the user.
USE MASTER
GO
CREATE CERTIFICATE Mirror_Cert
AUTHORIZATION Mirror_User
FROM FILE = 'C:\Certificate\Mirror_Cert.cer'
GO
USE MASTER
GO
CREATE CERTIFICATE Witness_Cert
AUTHORIZATION Witness_User
FROM FILE = 'C:\Certificate\Witness_Cert.cer'
GO
4.4 Grant CONNECT permission on the login for the remote mirroring endpoint.
USE MASTER
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Mirror_login]
GO
USE MASTER
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Witness_login]
GO
Step 5. Run below script in Mirror Instance for create login, user and associate certificate with user.
Now copy the Principal_Cert.cer and Witness_Cert.cer on to the Mirror Instance.
5.1 We are creating a SQL Login here. For Windows logins, use the Grant Login instead of Create Login...
USE MASTER
GO
CREATE LOGIN Principal_login WITH PASSWORD = 'Password'
GO
USE MASTER
GO
CREATE LOGIN Witness_login WITH PASSWORD = 'Password'
GO
5.2 Create a user for that login.
USE MASTER
GO
CREATE USER Principal_User FOR LOGIN Principal_login
GO
USE MASTER
GO
CREATE USER Witness_User FOR LOGIN Witness_login
GO
5.3 Associate the certificate with the user.
USE MASTER
GO
CREATE CERTIFICATE Principal_Cert
AUTHORIZATION Principal_User
FROM FILE = 'C:\Certificate\Principal_Cert.cer'
GO
USE MASTER
GO
CREATE CERTIFICATE Witness_Cert
AUTHORIZATION Witness_User
FROM FILE = 'C:\Certificate\Witness_Cert.cer'
GO
5.4 Grant CONNECT permission on the login for the remote mirroring endpoint.
USE MASTER
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Principal_login]
GO
USE MASTER
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Witness_login]
GO
Step 6. Run below script in Witness Instance for create login, user and associate certificate with user.
Now copy the Principal_Cert.cer and Mirror_Cert.cer on to the Witness Instance.
6.1 We are creating a SQL Login here. For Windows logins, use the Grant Login instead of Create Login...
USE MASTER
GO
CREATE LOGIN Principal_login WITH PASSWORD = 'Password'
GO
USE MASTER
GO
CREATE LOGIN Mirror_login WITH PASSWORD = 'Password'
GO
6.2 Create a user for that login.
USE MASTER
GO
CREATE USER Principal_User FOR LOGIN Witness_login
GO
USE MASTER
GO
CREATE USER Mirror_User FOR LOGIN Witness_login
GO
6.3 Associate the certificate with the user.
USE MASTER
GO
CREATE CERTIFICATE Principal_Cert
AUTHORIZATION Principal_User
FROM FILE = 'C:\Certificate\Principal_Cert.cer'
GO
USE MASTER
GO
CREATE CERTIFICATE Mirror_Cert
AUTHORIZATION Mirror_User
FROM FILE = 'C:\Certificate\Mirror_Cert.cer'
GO
6.4 Grant CONNECT permission on the login for the remote mirroring endpoint.
USE MASTER
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Principal_login]
GO
USE MASTER
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Mirror_login]
GO
Step 7. Create the Mirrored Database on the Mirror Server using backups from the Principal Server.
7.1 Run below script against the Principal Instance.
USE MASTER
GO
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Full_01012011.bak'
GO
BACKUP LOG AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Log_01012011.trn'
GO
7.2 Copy AdventureWorks_Full_01012011.bak and AdventureWorks_Log_01012011.trn to the Mirror Server.
7.3 Execute this below script against the Mirror Instance.
USE MASTER
GO
RESTORE DATABASE AdventureWorks
FROM DISK = 'D:\Backups\AdventureWorks_FullBackup.bak'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks
FROM DISK = 'D:\Backups\AdventureWorks_LogBackup.trn'
WITH NORECOVERY
GO
Step 8. Configuring the Mirroring Partners
8.1 Change the server name as per yours and run the below script on Mirror server.
ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP://your_principal_server_name:5022'
GO
8.2 Change the server name as per yours and run the below script on principal server
ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP://your mirror server name:5023'
GO
ALTER DATABASE AdventureWorks
SET WITNESS = 'TCP://your witness server name:5024'
GO
NOTE: When you copying a certificate to another system or server, use a secure copy method. Be extremely careful to keep all of your certificates secure and safe.
