Microsoft SQL Server Mirroring with Certificates

System Information

Primary (Principal)

Instance Hostname: WIN2019A
Instance Port: 1450
Endpoint Name: Endpoint_Mirroring
Endpoint Port: 5000

Secondary (Mirror)

Instance Hostname: WIN2019B
Instance Port: 1450
Endpoint Name: Endpoint_Mirroring
Endpoint Port: 5000

Overview

  1. Prepare Servers
  2. Configure Outbound Connections
  3. Configure Inbound Connections
  4. Create the Mirror Database
  5. Configure the Mirror Partners

Prepare Servers

Primary Host (Principal Server)

  1. Open the following TCP Ports in the Windows Firewall:
    5000 (Mirroring Endpoint)

Secondary Host (Mirror Server)

  1. Open the following TCP Ports in the Windows Firewall:
    5000 (Mirroring Endpoint)

Configure Outbound Connections

Primary Host (Principal Server)

  1. Connect to the instance on the primary server using SQL Server Management Studio
  2. Right-Click on the instance and select New Query
  3. Execute the following transactions, make sure to edit the ports and passwords with your system information
    -- Create a database master key.
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Super Strong Password>';
    GO
    
    -- Create a certificate for this instance.
    USE master;
    CREATE CERTIFICATE DEMO_A_cert 
       WITH SUBJECT = 'DEMO_Mirror certificate',
       EXPIRY_DATE = '20291231';
    GO
    
    -- Create a mirroring endpoint for the server using the certificate.
    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (
    	  LISTENER_PORT=5000 -- Specify endpoint port, must not be used for any other service
    	  , LISTENER_IP = ALL
       ) 
       FOR DATABASE_MIRRORING ( 
    	  AUTHENTICATION = CERTIFICATE DEMO_A_cert
    	  , ENCRYPTION = REQUIRED ALGORITHM AES
    	  , ROLE = ALL
       );
    GO
    -- Backup the certificate to file.
    -- The destination folder needs to exist and be writable.
    BACKUP CERTIFICATE DEMO_A_cert TO FILE = 'C:\ssl\DEMO_A_cert.cer';
    GO
    
  4. Copy the certificate to the Secondary Host using a secure method.

Secondary Host (Mirror Server)

  1. Connect to the instance on the secondary server using SQL Server Management Studio
  2. Right-Click on the instance and select New Query
  3. Execute the following transactions, make sure to edit the ports and passwords with your system information
    -- Create a database master key.
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Super Strong Password>';
    GO
    
    -- Create a certificate for this instance.
    USE master;
    CREATE CERTIFICATE DEMO_B_cert 
       WITH SUBJECT = 'DEMO_Mirror certificate',
       EXPIRY_DATE = '20291231';
    GO
    
    -- Create a mirroring endpoint for the server using the certificate.
    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (
    	  LISTENER_PORT=5000 -- Specify endpoint port, must not be used for any other service
    	  , LISTENER_IP = ALL
       ) 
       FOR DATABASE_MIRRORING ( 
    	  AUTHENTICATION = CERTIFICATE DEMO_B_cert
    	  , ENCRYPTION = REQUIRED ALGORITHM AES
    	  , ROLE = ALL
       );
    GO
    -- Backup the certificate to file.
    -- The destination folder needs to exist and be writable.
    BACKUP CERTIFICATE DEMO_B_cert TO FILE = 'C:\ssl\DEMO_B_cert.cer';
    GO
    
  4. Copy the certificate to the Primary Host using a secure method.

Configure Inbound Connections

Primary Host (Principal Server)

  1. Connect to the instance on the primary server using SQL Server Management Studio
  2. Right-Click on the instance and select New Query
  3. Execute the following transactions, make sure to edit the ports and passwords with your system information
    -- Create a login for the secondary host (Mirror Server)
    USE master;
    CREATE LOGIN DEMO_B_login WITH PASSWORD = '';
    GO
    -- Create a user for that login
    CREATE USER DEMO_B_user FOR LOGIN DEMO_B_login;
    GO
    -- Associate the secondary host certificate with that user
    CREATE CERTIFICATE DEMO_B_cert
       AUTHORIZATION DEMO_B_user
       FROM FILE = 'C:\ssl\DEMO_B_cert.cer'
    GO
    
    -- Grant CONNECT permission on the login for the remote mirroring endpoint
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DEMO_B_login];
    GO
    

Secondary Host (Mirror Server)

  1. Connect to the instance on the secondary server using SQL Server Management Studio
  2. Right-Click on the instance and select New Query
  3. Execute the following transactions, make sure to edit the ports and passwords with your system information
    -- Create a login for the primary host (Principal Server)
    USE master;
    CREATE LOGIN DEMO_A_login WITH PASSWORD = '';
    GO
    -- Create a user for that login
    CREATE USER DEMO_A_user FOR LOGIN DEMO_A_login;
    GO
    
    -- Associate the secondary host certificate with that user
    CREATE CERTIFICATE DEMO_A_cert
       AUTHORIZATION DEMO_A_user
       FROM FILE = 'C:\ssl\DEMO_A_cert.cer'
    GO
    
    -- Grant CONNECT permission on the login for the remote mirroring endpoint
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DEMO_A_login];
    GO
    

Create the Mirror Database

Create a Backup

  1. Connect to the instance on the primary server using SQL Server Management Studio
  2. Right-click the database and select Tasks >> Back Up…
  3. Select Full in the Backup type dropdown and click OK. Note the backup destination.
  4. Repeat this process but select Transaction Log next to the backup type. Use the same destination.
  5. Copy this file to the mirror host using a secure method

Restore the Backup

  1. Connect to the instance on the secondary server using SQL Server Management Studio
  2. Right-click on the Databases folder and select Restore Database
  3. Select Device as the source and select the backup copied earlier
  4. Enter the database name in the destination database field
  5. Click the Options link in the left pane
  6. Change the recovery state to RESTORE WITH NORECOVERY
  7. Click OK. The database should be in the Restoring state

Configure the Mirror Partners

Secondary Host (Mirror Server)

  1. Connect to the instance on the secondary server using SQL Server Management Studio
  2. Right-click on the instance and select New Query
  3. Execute the following, replace the database name with the name of your database and the host with your hostname
    ALTER DATABASE [mirrortest] SET PARTNER = 'TCP://win2019a:5000';
    GO

Primary Host (Principal Server)

  1. Connect to the instance on the secondary server using SQL Server Management Studio
  2. Right-click on the instance and select New Query
  3. Execute the following, replace the database name with the name of your database and the host with your hostname
    ALTER DATABASE [mirrortest] SET PARTNER = 'TCP://win2019b:5000';
    GO

At this point, the database on the principal server should be in the Principal / Synchronized state and the mirror database in the Mirror, Synchronized / Restoring… state.

To switch roles between the servers after complete, right-click the principal database and select Tasks >> Mirror then click Failover. The roles will be switched. Repeat this process on the new principal server to failback to the primary server.

If you receive any errors, verify DNS Resolution is working on both servers, check for any automatic transaction log backups that may have occurred during the copy of the database, and check the Application event log for errors.

How To Install and Enable EPEL Repo on CentOS 8

You can easily install various packages by configuring a CentOS 8.x system to use Fedora EPEL repos and third party packages. This quick tutorial explains how to configure a CentOS Linux version 8.x to use the Fedora Extra Packages for Enterprise Linux (EPEL) repository.

Run the following command:

# yum search epel
epel-release.noarch : Extra Packages for Enterprise Linux repository configuration

# yum -y install epel-release

Enable the PowerTools repo since EPEL package may depend on packages from it:

# yum config-manager --set-enabled PowerTools

To get more info about the package, run:

# yum info epel-release