Configuring Availability Groups to use Certificates

Prior to the release of SQL Server 2016, Database Mirroring was the only SQL Server Technology/feature at our fingertips that we could use to meet our High Availability (HA) / Disaster Recovery (DR) requirements for environments that were not residing in a Domain. Given with the release of SQL Server 2016 and Windows Server 2016, we now have the ability to create and support Non-Domain and Cross-Domain Windows Server Failover Clusters (WSFC’s). As such if we have built Non-Domain or Cross-Domain WSFC we need to undertake some prep-work differently to how we would setup and configure an Availability Group in a Domain Configuration. Just like Database mirroring we need to use certificates. In this post I will take you through how to configure an Availability Group using certificates.

Scenario

For this blog post we are following on from How to build a Non-Domain Cluster. So we have a 2 node Non-Domain cluster (SMC-WG1-SQL01 & SMC-WG1-SQL02) that we are going to configure with an Availability Group.

Build Steps:

1. On [SMC-WG1-SQL01] open SQL Server Configuration Manager | in the properties of the SQL Server Instance | Navigate to AlwaysOn High Availability | Tick the Enable AlwaysOn Availability Groups | Restart the Instance for this feature to take effect
2. On [SMC-WG1-SQL02] open SQL Server Configuration Manager | in the properties of the SQL Server Instance | Navigate to AlwaysOn High Availability | Tick the Enable AlwaysOn Availability Groups | Restart the Instance for this feature to take effect
3. On [SMC-WG1-SQL01] open Management Studio to run the following script commands:

Use Master
Go
-- Create Master Key
-- Use your own Password and not this demo password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1R3@llyStr0ngP@ssw0rd!';
Go
-- Create Certificate to be used across on SMC-WG1-SQL02
-- Change the Certificate Name and Subject to meet your environment
CREATE CERTIFICATE [SMC-WG1-SQL01-Cert] WITH SUBJECT = 'SMC-WG1-SQL01-Cert';
Go
-- Create Login for SMC-WG1-SQL02 Cluster Node
-- Change the Login Name and Password for your environment
CREATE LOGIN [SMC-WG1-SQL02-Login]  WITH PASSWORD = 'D1ff3rentStr0ngP@ssw0rd!';
Go
-- Create the User Account for the newly created Login on server SMC-WG1-SQL01
-- Change the User Account and Login to match above and your environment
CREATE USER [SMC-WG1-SQL02-User] FOR LOGIN [SMC-WG1-SQL02-Login];
Go
-- Backup the Certificate to copy across to SMC-WG1-SQL02
-- Change the location to meet your environment
BACKUP CERTIFICATE [SMC-WG1-SQL01-Cert] To FILE = '\SMC-WG1-SQL01CertificatesSQL-SMC-WG1-SQL01.cer';
Go

4. Manually copy the newly backed up certificate across to SMC-WG1-SQL02
5. On [SMC-WG1-SQL02] open Management Studio to run the following script commands:

Use Master
Go
-- Create Master Key
-- Use your own Password and not this demo password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1R3@llyStr0ngP@ssw0rd!';
Go
-- Create Certificate to be used across on SMC-WG1-SQL01
-- Change the Certificate Name and Subject to meet your environment
CREATE CERTIFICATE [SMC-WG1-SQL02-Cert] WITH SUBJECT = 'SMC-WG1-SQL02-Cert';
Go
-- Create Login for SMC-WG1-SQL01 Cluster Node
-- Change the Login Name and Password for your environment
CREATE LOGIN [SMC-WG1-SQL01-Login]  WITH PASSWORD = 'D1ff3rentStr0ngP@ssw0rd!';
Go
-- Create the User Account for the newly created Login on server SMC-WG1-SQL02
-- Change the User Account and Login to match above and your environment
CREATE USER [SMC-WG1-SQL01-User] FOR LOGIN [SMC-WG1-SQL01-Login];
Go
-- Backup the Certificate to copy across to SMC-WG1-SQL01
-- Change the location to meet your environment
BACKUP CERTIFICATE [SMC-WG1-SQL02-Cert] To FILE = '\SMC-WG1-SQL02CertificatesSQL-SMC-WG1-SQL02.cer';
Go
CREATE CERTIFICATE [SMC-WG1-SQL01-Cert] AUTHORIZATION [SMC-WG1-SQL01-User] FROM FILE = '\SMC-WG1-SQL02CertificatesSQL-SMC-WG1-SQL01.cer';
Go

6. Manually copy the newly backed up certificate across to SMC-WG1-SQL02
7. Back On [SMC-WG1-SQL01] run the following:

CREATE CERTIFICATE [SMC-WG1-SQL02-Cert] AUTHORIZATION [SMC-WG1-SQL02-User] FROM FILE = '\SMC-WG1-SQL01CertificatesSQL-SMC-WG1-SQL02.cer';
Go

8. We now need to create the Endpoints on all workgroup servers that will use the Certificates that we have just configured and allow for the communication to occur between the workgroup servers allowing for the configuration of the Availability Group.

CREATE ENDPOINT WGAG_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) 
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [SMC-WG1-SQL01-Cert] , ROLE = ALL);
Go
GRANT CONNECT ON ENDPOINT::WGAG_Endpoint TO [SMC-WG1-SQL02-Login];

9. Back on [SMC-WG1-SQL02] we can create the endpoint with the following scripts

CREATE ENDPOINT WGAG_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) 
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [SMC-WG1-SQL02-Cert] , ROLE = ALL);
Go
GRANT CONNECT ON ENDPOINT::WGAG_Endpoint TO [SMC-WG1-SQL01-Login];

IF (SELECT state FROM sys.endpoints WHERE name = N'WGAG_Endpoint') <> 0
BEGIN
 ALTER ENDPOINT [WGAG_Endpoint] STATE = STARTED
END


GO

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

10. On [SMC-WG1-SQL01] run the following script commands:

IF (SELECT state FROM sys.endpoints WHERE name = N'WGAG_Endpoint') <> 0
BEGIN
ALTER ENDPOINT [WGAG_Endpoint] STATE = STARTED
END

GO

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

Create Database SMC_WG1_AG_DB1;

-- Undertake an Initial Backup to allow the newly created Database to be ready to be added to an Availability Group.
BACKUP DATABASE [SMC_WG1_AG_DB1] 
TO  DISK = N'\SMC-WG1-SQL01BackupSMC_WG1_AG_DB1.bak' 
WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

GO

BACKUP LOG [SMC_WG1_AG_DB1] 
TO  DISK = N'\SMC-WG1-SQL01BackupSMC_WG1_AG_DB1_20161008213659.trn'
WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

-- Manually copy the full backup and TLog backup across to \SMC-WG1-SQL02Backup


GO -- Create our Workgroup Availability Group
CREATE AVAILABILITY GROUP [SMC_WG1_AG1]
WITH (
 AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
 DB_FAILOVER = ON,
 DTC_SUPPORT = NONE
)
FOR DATABASE [SMC_WG1_AG_DB1]
REPLICA ON N'SMC-WG1-SQL01' WITH (
 ENDPOINT_URL = N'TCP://SMC-WG1-SQL01.SMC.local:5022', 
 FAILOVER_MODE = AUTOMATIC, 
 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
 BACKUP_PRIORITY = 50, 
 SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
),
 N'SMC-WG1-SQL02' WITH (
 ENDPOINT_URL = N'TCP://SMC-WG1-SQL02.SMC.local:5022', 
 FAILOVER_MODE = AUTOMATIC, 
 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
 BACKUP_PRIORITY = 50, 
 SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
);

GO

11. On [SMC-WG1-SQL02] run the following script commands:

-- Join SMC-WG1-SQL02 to the AG
ALTER AVAILABILITY GROUP [SMC_WG1_AG1] JOIN;

GO
-- We are now ready to add a database to the AG

-- Start the Restoration of the the database to be added to the Availability Group

RESTORE DATABASE [SMC_WG1_AG_DB1] 
FROM  DISK = N'\SMC-WG1-SQL02BackupSMC_WG1_AG_DB1.bak' 
WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

RESTORE LOG [SMC_WG1_AG_DB1] 
FROM  DISK = N'\SMC-WG1-SQL02BackupSMC_WG1_AG_DB1_20161008213659.trn' 
WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO
-- Bring the AG online
ALTER DATABASE [SMC_WG1_AG_DB1] SET HADR AVAILABILITY GROUP = [SMC_WG1_AG1];
GO

12. On [SMC-WG1-SQL01] run the following script commands:

ALTER AVAILABILITY GROUP [SMC_WG1_AG1]
ADD LISTENER N'SMC_WG1_AGL1' (
WITH IP
((N'10.10.10.20', N'255.0.0.0')
)
, PORT=1433);

GO

Congratulations you have now successfully created a Non-Domain Availability Group utilizing certificates to allow the instances of SQL Server to communicate with each other. All steps have been put together based on my demonstration environment. Ensure that you undertake this in a non-production environment before going near production.