Sample Word Document

/* This document contains the TSQL needed to configure Classic Mirroring using certificates for encrypted mirroring connections*/

We will need 2 servers with the same version of SQL Server on Principal and Mirror.

We will also create 2 Logins and 2 users.  PRINCIPAL_LOGIN and PRINCIPAL_USER ; MIRROR_LOGIN and MIRROR_USER

Based on these logins, we will create a certificate. For testing purposes, we will use a database called mirrordbtest. Please replace this with the user database that you want to mirror.

Note: Before starting, please take a full and log backup from Principal to restore on Mirror with option RESTORE WITH NORECOVERY.

https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/prepare-a-mirror-database-for-mirroring-sql-server?view=sql-server-ver15#CombinedProcedure

Create a directory to store the certificates on each server.  In this example script below, we used C:\Certs to simplify the script across all servers. All 2 certificates will be stored here.

We are using port 5022 for the mirroring endpoint so please open the firewall/Network Security Group for port 5022.*/

--===========================================================--

--===========================================================--

/*Create the master key, certificate from the master key, and encrypt the mirroring endpoint on the

Principal * Please use a secure password /

--create a master key on Principal

USE master; 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWOrd123!'; 

GO

--create a certificate from the master key

USE master; 

CREATE CERTIFICATE PrincipalCert  

   WITH SUBJECT = 'Principal Certificate'; 

GO 

--CREATE the mirroring endpoint

CREATE ENDPOINT Mirroring 

   STATE = STARTED 

   AS TCP ( 

      LISTENER_PORT=5022 

      , LISTENER_IP = ALL 

   )  

   FOR DATABASE_MIRRORING (  

      AUTHENTICATION = CERTIFICATE PrincipalCert 

      , ENCRYPTION = REQUIRED ALGORITHM AES 

      , ROLE = ALL 

   ); 

GO 

--Backup the certificate and transfer it to the mirror server

BACKUP CERTIFICATE PrincipalCert TO FILE = 'C:\certs\PrincipalCert'; 

GO 

/*Create the master key, certificate from the master key, and encrypt the mirroring endpoint

on the Mirror*/

--create a master key on Mirror

USE master; 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWOrd123!'; 

GO

--create a certificate from the master key

USE master; 

CREATE CERTIFICATE MirrorCert  

   WITH SUBJECT = 'Mirror Certificate'; 

GO 

--CREATE the mirroring endpoint

CREATE ENDPOINT Mirroring 

   STATE = STARTED 

   AS TCP ( 

      LISTENER_PORT=5022 

      , LISTENER_IP = ALL 

   )  

   FOR DATABASE_MIRRORING (  

      AUTHENTICATION = CERTIFICATE MirrorCert 

      , ENCRYPTION = REQUIRED ALGORITHM AES 

      , ROLE = ALL 

   ); 

GO 

--Backup the cert and transfer it to the Principal server

BACKUP CERTIFICATE MIrrorCert TO FILE = 'C:\certs\MirrorCert'; 

GO 

--===========================================================--

/*After all 2 servers have generated the certificates, place all 2 certificates on each server in the C:\certs directory*/

--===========================================================--

/*This portion of the script is to create the logins, users, and set CONNECT ON ENDPOINT permissions on the Principal for Mirror*/

--On the Principal, create a login for the mirror server

USE master; 

CREATE LOGIN Mirror_Login WITH PASSWORD = 'PassWord123!'; 

GO 

--create a user from the login

CREATE USER Mirror_User FOR LOGIN Mirror_Login; 

GO 

--create a certificate that the login uses for authentication

CREATE CERTIFICATE MirrorCert 

   AUTHORIZATION Mirror_User 

   FROM FILE = 'C:\certs\MirrorCert' 

GO

--grant connect for login

GRANT CONNECT ON ENDPOINT::Mirroring TO [Mirror_login]; 

GO

--===========================================================--

--===========================================================--

/*This portion of the script is to create the logins, users, and set CONNECT ON ENDPOINT permissions on the Mirror for Principal*/

--On the Mirror, create a login for the Principal server

USE master; 

CREATE LOGIN Principal_Login WITH PASSWORD = 'PassWord123!'; 

GO 

--create a user from the login

CREATE USER Principal_User FOR LOGIN Principal_Login; 

GO 

--create a certificate that the login uses for authentication

CREATE CERTIFICATE Principalcert 

   AUTHORIZATION Principal_User 

   FROM FILE = 'C:\certs\PrincipalCert' 

GO

--grant connect for login

GRANT CONNECT ON ENDPOINT::Mirroring TO [Principal_Login]; 

GO

--===========================================================--

--===========================================================--

/*In this section, we will configure mirroring for the database on each server. */

--At Mirror, set Principal server as partner. We are using principal IP because there is no domain DNS

ALTER DATABASE mirrordbtest

    SET PARTNER = 'TCP://Principal_IP:5022';   .

GO

--At Principal, set Mirror server as partner. We are using Mirror IP because there is no domain DNS.

ALTER DATABASE mirrordbtest

    SET PARTNER = 'TCP://Mirror_IP:5022'; 

GO

-- Turn off transaction safety since Principal and mirror servers are far away to each other:

ALTER DATABASE mirrordbtest SET PARTNER SAFETY OFF

go

Note: Turning off transaction safety shifts the session into asynchronous operating mode, which maximizes performance

https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/change-transaction-safety-in-a-database-mirroring-session-transact-sql?view=sql-server-ver15

**Check the mirroring setting at the database level:

More information: Setting Up Database Mirroring

 https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/setting-up-database-mirroring-sql-server?view=sql-server-ver15