Using database mirroring inbound and Always On Availability Groups outbound on the same SQL Server

Machine setup

We have the following sql servers and databases

There is already an Always On Availability Group DemoAG1 from WIN-AG1\SQL2016X1 to WIN-AG2\SQL2016X1

The Always On Availability Group contains database justdave1 and is using an Active Directory Detached Cluster i.e. certificates

List of tasks

We execute the following steps

Task details

Create the database justdave3 on Mirroring Source WIN-M1


MD C:\Data

CREATE DATABASE [justdave3] ON PRIMARY 
( NAME = N'jd1', FILENAME = N'C:\DATA\jd1.mdf')
 LOG ON 
( NAME = N'jd1_log', FILENAME = N'C:\DATA\jd1_log.ldf');

On the mirroring source WIN-M1\SQL2008X1 configure for an outbounding mirroring connection

We are going to use certificates to show a more flexible setup

On the mirroring source configure for an outbounding mirroring connection - create a database master key,certificate and mirroring endpoint

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD='MyPass1';

CREATE CERTIFICATE ServerCertMirror1 WITH SUBJECT='Database Mirroring 1';

MD C:\Keys

BACKUP CERTIFICATE ServerCertMirror1 TO FILE='C:\Keys\ServerCertMirror1.cer'
 WITH PRIVATE KEY (FILE='C:\Keys\ServerCertMirror1.key',
 ENCRYPTION BY PASSWORD='BackupPass1');

-- We specify algorithm AES as on SQL Server 2008R2 the default is weaker algorithm RC4
CREATE ENDPOINT [Mirroring_endpoint1]
STATE=STARTED
AS TCP (LISTENER_PORT = 7024, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE ServerCertMirror1,
ENCRYPTION = REQUIRED ALGORITHM AES );

On the mirroring target WIN-AG1\SQL2016X1 configure for an outbounding mirroring connection

On the mirroring target there is already setup for an outbound connection as it is using certificates

USE master;

MD C:\Keys

-- Find the existing certificate for the Always On Basic Availability Group
-- 
SELECT B.name,USER_NAME( B.principal_id) AS principal_name,
  A.pvt_key_encryption_type_desc,A.issuer_name,A.subject,
  A.expiry_date
  FROM sys.certificates AS A,
    sys.database_mirroring_endpoints AS B
  WHERE A.certificate_id = B.certificate_id;

-- Backup the certificate for the AG group
BACKUP CERTIFICATE ServerCertAG1 TO FILE='C:\Keys\ServerCertAG1.cer'
 WITH PRIVATE KEY (FILE='C:\Keys\ServerCertAG1.key',
 ENCRYPTION BY PASSWORD='<1_Strong_Password!>1');

On the mirroring target WIN-AG1\SQL2016X1 configure for an inbound mirroring connection

We create a login/user, restore certificate,associate certificate to user,grant connect on endpoint

As can only have 1 mirroring endpoint on a server we have to reuse the mirroring endpoint for the AG

We authorize the mirroring user to use the AG endpoint


-- Machine WIN-AG1

MD C:\Keys

USE master;

CREATE LOGIN MIRROR_win_m1_x1 
   WITH PASSWORD = '1Sample_Strong_Password!@#';

CREATE USER MIRROR_win_m1_x1 FOR LOGIN MIRROR_win_m1_x1;

-- copy backup certificate files from WIN-M1 to WIN-AG1 C:\Keys 

-- create certificate associating with user
CREATE CERTIFICATE ServerCertMirror1 
AUTHORIZATION MIRROR_win_m1_x1
FROM FILE='C:\Keys\ServerCertMirror1.cer'
WITH PRIVATE KEY (FILE = 'C:\Keys\ServerCertMirror1.key', 
    DECRYPTION BY PASSWORD = 'BackupPass1');

-- Reuse the AG Mirroring Endpoint
-- name Hadr_endpoint

SELECT name
  FROM sys.database_mirroring_endpoints;

GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [MIRROR_win_m1_x1];

-- For connections from 2008 R2 we need to change the endpoint to encyption type RC4

ALTER ENDPOINT [Hadr_endpoint]
    FOR DATA_MIRRORING ( ENCRYPTION  = REQUIRED ALGORITHM RC4 );

On the mirroring source WIN-AG1\SQL2016X1 configure for an inbound mirroring connection

Create login/user, restore certificate,associate certificate to user,grant connect on endpoint


-- Machine WIN-M1

MD C:\Keys

USE master;

CREATE LOGIN MIRROR_win_m1_x1 
   WITH PASSWORD = '1Sample_Strong_Password!@#';

CREATE USER MIRROR_win_m1_x1 FOR LOGIN MIRROR_win_m1_x1;

-- copy backup certificate files from WIN-AG1 to WIN-M1 C:\Keys 

-- create certificate associating with user
CREATE CERTIFICATE ServerCertAG1 
AUTHORIZATION MIRROR_win_m1_x1
FROM FILE='C:\Keys\ServerCertAG1.cer'
WITH PRIVATE KEY (FILE = 'C:\Keys\ServerCertAG1.key', 
    DECRYPTION BY PASSWORD = '<1_Strong_Password!>1');

-- Reuse the Mirroring Endpoint
-- name Mirroring_endpoint1
SELECT name
  FROM sys.database_mirroring_endpoints;

GRANT CONNECT ON ENDPOINT::Mirroring_endpoint1 TO [MIRROR_win_m1_x1];

On the mirroring source backup database justdave3 with a full backup and restore to the mirroring target with NORECOVERY


On machine WIN-M1

MD C:\Backups

BACKUP DATABASE justdave3 TO DISK = 'C:\Backups\justdave3.bak';

-- Copy the backup to machine WIN-AG1

On machine WIN-AG1

MD C:\DATA

RESTORE DATABASE justdave3 FROM DISK = 'C:\Backups\justdave3.bak' WITH NORECOVERY;

Setup the firewall on both machines

On machine WIN-M1 

-- Check the port for database mirroring endpoint on SQL Server SQL2008X1
-- Port 7024
SELECT B.name,B.port
 FROM sys.database_mirroring_endpoints as A,
   sys.tcp_endpoints as B
WHERE A.endpoint_id = B.endpoint_id;

-- Open the firewall for the database mirroring endpoint
netsh advfirewall firewall add rule name="Mirroring EndPoint" dir=in action=allow protocol=TCP localport=7024

On machine WIN-AG1

-- Check the port for database mirroring endpoint on SQL Server SQL2016X1
-- Port 5022
SELECT B.name,B.port
 FROM sys.database_mirroring_endpoints as A,
   sys.tcp_endpoints as B
WHERE A.endpoint_id = B.endpoint_id;

-- The firewall will already been opened with rule "SQL AG" to allow the Always On Availability Group to be created

Perform mirroring endpoint checks


USE master;

-- Check the encryption type matches on both mirroring endpoints
-- Both should be AES.
select encryption_algorithm_desc from sys.database_mirroring_endpoints;

-- Check the user permissions on the endpoint
-- User MIRROR_win_m1_x1 should be permissions on the database mirroring endpoint on both server
SELECT EP.name, SP.STATE,   
   CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))   
      AS GRANTOR,   
   SP.TYPE AS PERMISSION,  
   CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))   
      AS GRANTEE   
   FROM sys.server_permissions SP , sys.endpoints EP,
   sys.database_mirroring_endpoints DEP 
   WHERE SP.major_id = EP.endpoint_id  
   AND EP.endpoint_id = DEP.endpoint_id
   ORDER BY Permission,grantor, grantee; 
  • Activate database mirroring between the 2 machines

    
    -- On the mirroring target setup mirroring
    -- On machine WIN-AG1 Server 2016X1
    
    ALTER DATABASE justdave3 
        SET PARTNER = 'TCP://WIN-M1.justdave.contso.com:7024';
    
    -- On the mirroring source setup mirroring
    -- On machine WIN-N1 Server 2008X1
    
    ALTER DATABASE justdave3 
        SET PARTNER = 'TCP://WIN-AG1.justdave.contso.com:5022';
    

    Final result is we can combine database mirroring an and an Always On Availability Group out on the same server!