TSQL: Database Mirroring with Certificates

Here’s some more TSQL for the 70-462 exam. The script shows the actions needed to configure database mirroring using certificates for authentication. Explanatory notes are included but you’re likely to need the training materials for this to make sense. TSQL is not included for the backup/restore parts needed for database mirroring.

SELECT *
FROM sys.symmetric_keys;
GO

-- Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Secret1234';
GO

-- Create certificate (SQL-A)
CREATE CERTIFICATE SQL_A_Cert
WITH SUBJECT = 'My Mirroring certificate'
GO

-- Create certificate (SQL-B)
CREATE CERTIFICATE SQL_B_Cert
WITH SUBJECT = 'My Mirroring certificate'
GO

-- Endpoint (SQL-A) certificate authentication
CREATE ENDPOINT Endpoint_Mirroring
AS TCP (LISTENER_IP = ALL, LISTENER_PORT = 7024)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQL_A_Cert, ROLE = ALL);
GO

-- Endpoint (SQL-B) certificate authentication
CREATE ENDPOINT Endpoint_Mirroring
AS TCP (LISTENER_IP = ALL, LISTENER_PORT = 7024)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQL_B_Cert, ROLE = ALL);
GO

-- Backup certificate SQL-A
BACKUP CERTIFICATE SQL_A_Cert TO FILE = 'C:\backup\SQL_A_Cert.cer';

-- Backup certificate SQL-B
BACKUP CERTIFICATE SQL_B_Cert TO FILE = 'C:\backup\SQL_B_Cert.cer';

-- SQL-A create login for sql_b
CREATE LOGIN SQL_B_login WITH PASSWORD = 'Pa$$w0rd';
GO

CREATE USER SQL_B_user FROM LOGIN SQL_B_login;
GO

-- SQL-B create login for sql_a
CREATE LOGIN SQL_A_login WITH PASSWORD = 'Pa$$w0rd';
GO

CREATE USER SQL_A_user FROM LOGIN SQL_A_login;
GO

-- create cert on sql-a from sql_b backup
CREATE CERTIFICATE SQL_B_Cert
FROM FILE = 'c:\backup\sql_b_cert.cer';
GO

-- create cert on sql-b from sql_a backup
CREATE CERTIFICATE SQL_A_Cert
FROM FILE = 'c:\backup\sql_a_cert.cer';
GO

-- on sql-a
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO SQL_B_login;
GO

-- on sql-b
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO SQL_A_login;
GO

-- on sql-b
SELECT *
FROM sys.endpoints
ALTER ENDPOINT Endpoint_Mirroring STATE = STARTED;
ALTER DATABASE [AdventureMirror] SET PARTNER = 'TCP://sql-a:7024';
GO

-- on sql-a
SELECT *
FROM sys.endpoints
ALTER ENDPOINT Endpoint_Mirroring STATE = STARTED;

ALTER DATABASE [AdventureMirror] SET PARTNER = 'TCP://sql-b:7024';
GO

-- DMVs to check the setup
SELECT *
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;

SELECT *
FROM sys.database_mirroring_endpoints;