Implement a SQL Server 2016 Availability Group without Active Directory Part 1

By: Edwin Sarmiento   |   Updated: 2017-07-19   |   Comments (5)   |   Related: 1 | 2 | > Availability Groups

Problem

In a previous tip on Implementing Database Mirroring in SQL Server 2005 across domains, we have seen how we can configure Database Mirroring to achieve local high availability for SQL Server databases that are not joined to an Active Directory domain. We need to upgrade our SQL Server 2008 R2 databases before extended support ends. However, we do not have an Active Directory domain in our environment. How do we go about it?

Solution

This tip continues from the series on Deploy a Windows Server 2016 Failover Cluster without Active Directory. Part 1 covers configuring the Windows Server 2016 servers prior to joining them to the failover cluster while Part 2 goes thru the process of creating the failover cluster in a workgroup environment.

Since SQL Server Availability Groups run on top of a Windows Server Failover Cluster (WSFC), they have the same external dependencies regarding Active Directory and DNS. This dependency is one of the main reasons why some customers are still running on Database Mirroring even on SQL Server 2014. With Windows Server 2016 and WSFC without Active Directory, Availability Groups can now be a viable replacement for Database Mirroring.

Most of the steps outlined below are similar to configuring Availability Groups with Active Directory. But because there is no centralized directory service for managing accounts, you will need to rely on certificates for authenticating communication between replicas. Hence, the reference to the previous tip on Implementing Database Mirroring in SQL Server 2005 across domains. That also means you need to use T-SQL to accomplish those tasks.

Implementing SQL Server Availability Groups without Active Directory

Here’s a high-level overview of the steps for your reference. Be very careful with going thru all of the steps. It’s not as simple as you think it is. Step #1 is excluded since it is assumed that you already performed the installation.

1. Install SQL Server 2016 using the built-in service account
2. Enable SQL Server AlwaysOn Availability Groups feature
3. Create a database master key on the primary replica
4. Create the certificate that will be used to encrypt the Availability Group endpoint
5. Create the Availability Group endpoint for the primary replica using the certificate for authentication
6. Export the certificate to a file
7. Copy the certificate file to the secondary replicas
8. Create a login on the primary replica for use by the secondary replicas
9. Create a user for the login
10. Associate the certificate created in Step #4 with the user
11. Grant the CONNECT permission on the login
12. Create the Availability Group using T-SQL
13. Create an Availability Group listener name

Step #2: Enable SQL Server AlwaysOn Availability Groups Feature

NOTE: This process needs to be performed on all of the failover cluster nodes.

To enable the SQL Server 2016 AlwaysOn Availability Groups feature,

• Open SQL Server Configuration Manager. Double-click the SQL Server (MSSQLSERVER) service to open the Properties dialog box. Note the use of the built-in service account – NT Service\MSSQLSERVER.

• In the Properties dialog box, select the AlwaysOn High Availability tab. Check the Enable AlwaysOn Availability Groups check box. This will prompt you to restart the SQL Server service. Click OK.

• Restart the SQL Server service.

Step #3: Create a database master key on the primary replica

Each SQL Server instance that will function as a primary replica requires a database master key. The database master key is a symmetric key that is used to protect the private keys of certificates and asymmetric keys that are present in the database. The certificates referred to in this configuration will be used by the SQL Server logins that will connect to the Availability Group endpoint.

Starting with WSFC2016-WG1 as the primary replica, create the database master key.

--Run this on the primary replica/WSFC2016-WG1. Just to be sure, enable SQLCMD mode in SSMS  
:CONNECT WSFC2016-WG1
USE master  

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'y0ur$ecUr3PAssw0rd';  
GO
   

Step #4: Create the certificate that will be used to encrypt the Availability Group endpoint

Next, create the certificate that will be used to secure the Availability Group endpoint on the primary replica. This will also be used to secure the inbound traffic on the secondary replicas. Note that the primary replica will be sending transaction log records on the secondary replicas on a regular basis. Hence, the primary replica has to be authenticated on the secondary replicas.

--Run this on the primary replica/WSFC2016-WG1. Just to be sure, enable SQLCMD mode in SSMS  
:CONNECT WSFC2016-WG1
USE master  

CREATE CERTIFICATE WSFC2016_WG1_cert 
WITH SUBJECT = 'WSFC2016-WG1 certificate for Availability Group' 
GO
   

Step #5: Create the Availability Group endpoint on the primary replica using the certificate for authentication

Next, create the endpoint that the Availability Group will use on the primary replica. The endpoint type is DATABASE_MIRRORING. The default port number is used – 5022. Be sure to open up your firewall for both inbound and outbound traffic for this port. Also, the certificate created in Step #4 – WSFC2016_WG1_cert - is used to authenticate the endpoint.

--Run this on the primary replica/WSFC2016-WG1. Just to be sure, enable SQLCMD mode in SSMS  
:CONNECT WSFC2016-WG1
USE master  

CREATE ENDPOINT Endpoint_AvailabilityGroup 
STATE = STARTED 
AS TCP 
(
   LISTENER_PORT = 5022, LISTENER_IP = ALL
)  
   FOR DATABASE_MIRRORING 
(
   AUTHENTICATION = CERTIFICATE WSFC2016_WG1_cert, 
   ENCRYPTION = REQUIRED ALGORITHM AES,
   ROLE = ALL 
);  
GO
   

Step #6: Export the certificate to a file

After creating the certificate and assigning it to authenticate the endpoint, you can proceed to export it to the file system. The example below stores the certificate file to the C:\SQLAG folder. Be sure to create the folder prior to running the script.

--Run this on the primary replica/WSFC2016-WG1. Just to be sure, enable SQLCMD mode in SSMS  
:CONNECT WSFC2016-WG1
USE master  

BACKUP CERTIFICATE WSFC2016_WG1_cert 
TO FILE = 'C:\SQLAG\WSFC2016_WG1_cert.cer';  
GO
   

Step #7: Copy the certificate file to the secondary replicas

You need to copy the certificate file to all of the secondary replicas – in this case, WSFC2016-WG2 and WSFC2016-WG3. As mentioned in Step #4, this certificate will also be used for authenticating and securing the endpoint on the secondary replicas as the primary replica connects to them to send transaction log records.

Repeat steps #3 to #7 on all secondary replicas

Since all of the replicas will function as a primary replica in the event of either an automatic or manual failover (plus the fact that the secondary replicas need to send an acknowledgement to the primary replica after hardening the transaction log records on disk), you need to repeat steps #3 to #7 on all of them.

A complete script for the secondary replica WSFC2016-WG2 is shown below.

--Run this on the secondary replica/WSFC2016-WG2. Just to be sure, enable SQLCMD mode in SSMS  
--Repeat the same process for steps #3 to #7 on the secondary replicas
:CONNECT WSFC2016-WG2
USE master  

--STEP #3:  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'y0ur$ecUr3PAssw0rd';  
GO 

--STEP #4:  
CREATE CERTIFICATE WSFC2016_WG2_cert 
WITH SUBJECT = 'WSFC2016-WG2 certificate for Availability Group' 
GO  

--STEP #5:  
CREATE ENDPOINT Endpoint_AvailabilityGroup 
STATE = STARTED  
AS TCP
(
   LISTENER_PORT = 5022, LISTENER_IP = ALL
)  
   FOR DATABASE_MIRRORING 
(
   AUTHENTICATION = CERTIFICATE WSFC2016_WG2_cert, 
   ENCRYPTION = REQUIRED ALGORITHM AES,
   ROLE = ALL
);  
GO  

--STEP #6:  
BACKUP CERTIFICATE WSFC2016_WG2_cert 
TO FILE = 'C:\SQLAG\WSFC2016_WG2_cert.cer';  
GO 

--STEP #7: Copy the certificate to the other replicas
   

A complete script for the secondary replica WSFC2016-WG3 is shown below.

--Run this on the secondary replica/WSFC2016-WG3. Just to be sure, enable SQLCMD mode in SSMS  
--Repeat the same process for steps #3 to #7 on the secondary replicas
:CONNECT WSFC2016-WG3
--STEP #3:  
USE master  

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'y0ur$ecUr3PAssw0rd';  
GO 

--STEP #4:  
CREATE CERTIFICATE WSFC2016_WG3_cert 
WITH SUBJECT = 'WSFC2016-WG3 certificate for Availability Group' 
GO  

--STEP #5:  
CREATE ENDPOINT Endpoint_AvailabilityGroup 
STATE = STARTED  
AS TCP
(
   LISTENER_PORT = 5022, LISTENER_IP = ALL
)  
   FOR DATABASE_MIRRORING 
(
   AUTHENTICATION = CERTIFICATE WSFC2016_WG3_cert, 
   ENCRYPTION = REQUIRED ALGORITHM AES,
   ROLE = ALL 
);  
GO  

--STEP #6:  
BACKUP CERTIFICATE WSFC2016_WG3_cert 
TO FILE = 'C:\SQLAG\WSFC2016_WG3_cert.cer';  
GO 

--STEP #7: Copy the certificate to the other replicas
   

Once completed, you should have three (3) certificate files on all of your SQL Server instances.

Step #8: Create a login on the primary replica for use by the secondary replicas

The login created on the primary replica is used by the secondary replicas to connect to the Availability Group via the endpoint. You can think of the secondary replicas as client applications that require credentials to connect to the SQL Server instance – the primary replica.

--Run this on the primary replica/WSFC2016-WG1. Just to be sure, enable SQLCMD mode in SSMS  
:CONNECT WSFC2016-WG1
USE master  

CREATE LOGIN login_AvailabilityGroup 
WITH PASSWORD = 'y0ur$ecUr3PAssw0rd';  
GO
   

Step #9: Create a user for the login

Create a user to associate with the login that was created.

--Run this on the primary replica/WSFC2016-WG1. Just to be sure, enable SQLCMD mode in SSMS  
:CONNECT WSFC2016-WG1
USE master  

CREATE USER login_AvailabilityGroup  
FOR LOGIN login_AvailabilityGroup  
GO
   

Step #10: Associate the certificate created in Step #4 with the user

The secondary replicas will use the database user created in Step #9 to connect to the primary replica. Since each secondary replica has a corresponding certificate, you need to associate those certificates with the user. The script below creates two certificates based on the ones generated by the secondary replicas and assigns both to the user.

--Run this on the primary replica/WSFC2016-WG1. Just to be sure, enable SQLCMD mode in SSMS  
:CONNECT WSFC2016-WG1
USE master  

--Associate certificate from WSFC2016-WG2 with user
CREATE CERTIFICATE WSFC2016_WG2_cert  
AUTHORIZATION login_AvailabilityGroup  
FROM FILE = 'C:\SQLAG\WSFC2016_WG2_cert.cer'  
GO

--Associate certificate from WSFC2016-WG3 with user
CREATE CERTIFICATE WSFC2016_WG3_cert  
AUTHORIZATION login_AvailabilityGroup  
FROM FILE = 'C:\SQLAG\WSFC2016_WG3_cert.cer'  
GO
   

Step #11: Grant the CONNECT permission on the login

You, then, need to grant CONNECT permissions on the login to successfully establish the communication between Availability Group replicas.

--Run this on the primary replica/WSFC2016-WG1. Just to be sure, enable SQLCMD mode in SSMS  
:CONNECT WSFC2016-WG1
USE master  

GRANT CONNECT ON ENDPOINT::Endpoint_AvailabilityGroup 
TO [login_AvailabilityGroup];    
GO
   

Repeat steps #8 to #11 on all secondary replicas

Again, since all of the replicas will function as a primary replica in the event of either an automatic or manual failover, you need to repeat steps #8 to #11 on all of them.

A complete script for the secondary replica WSFC2016-WG2 is shown below.

--Run this on the secondary replica/WSFC2016-WG2. Just to be sure, enable SQLCMD mode in SSMS  
--Repeat the same process for steps #8 to #11 on the secondary replicas
:CONNECT WSFC2016-WG2
USE master  

--STEP #8:  
CREATE LOGIN login_AvailabilityGroup 
WITH PASSWORD = 'y0ur$ecUr3PAssw0rd';  
GO

--STEP # 9:
CREATE USER login_AvailabilityGroup  
FOR LOGIN login_AvailabilityGroup  
GO

--STEP # 10:
--Associate certificate from WSFC2016-WG1 with user
CREATE CERTIFICATE WSFC2016_WG1_cert  
AUTHORIZATION login_AvailabilityGroup  
FROM FILE = 'C:\SQLAG\WSFC2016_WG1_cert.cer'  
GO

--Associate certificate from WSFC2016-WG3 with user
CREATE CERTIFICATE WSFC2016_WG3_cert  
AUTHORIZATION login_AvailabilityGroup  
FROM FILE = 'C:\SQLAG\WSFC2016_WG3_cert.cer'  
GO

--STEP # 11:
GRANT CONNECT ON ENDPOINT::Endpoint_AvailabilityGroup 
TO [login_AvailabilityGroup];  
GO
   

A complete script for the secondary replica WSFC2016-WG3 is shown below.

--Run this on the secondary replica/WSFC2016-WG2. Just to be sure, enable SQLCMD mode in SSMS  
--Repeat the same process for steps #8 to #11 on the secondary replicas
:CONNECT WSFC2016-WG3
USE master  

--STEP #8:  
CREATE LOGIN login_AvailabilityGroup 
WITH PASSWORD = 'y0ur$ecUr3PAssw0rd';  
GO

--STEP # 9:
CREATE USER login_AvailabilityGroup  
FOR LOGIN login_AvailabilityGroup  
GO

--STEP # 10:
--Associate certificate from WSFC2016-WG1 with user
CREATE CERTIFICATE WSFC2016_WG1_cert  
AUTHORIZATION login_AvailabilityGroup  
FROM FILE = 'C:\SQLAG\WSFC2016_WG1_cert.cer'  
GO

--Associate certificate from WSFC2016-WG2 with user
CREATE CERTIFICATE WSFC2016_WG2_cert  
AUTHORIZATION login_AvailabilityGroup  
FROM FILE = 'C:\SQLAG\WSFC2016_WG2_cert.cer'  
GO

--STEP # 11:
GRANT CONNECT ON ENDPOINT::Endpoint_AvailabilityGroup 
TO [login_AvailabilityGroup];  
GO
   

This process is very similar to the one described in the previous tip on Implementing Database Mirroring in SQL Server 2005 across domains. In fact, the scripts used in this tip are a variation of the ones from the previous tip. Complicated as it already is, this is just to prepare the SQL Server instances that you will use as Availability Group replicas to meet the security requirements of authenticating the logins and securing the endpoints. It doesn’t even cover the creation and configuration of the Availability Group just yet.

In the next tip in this series, you will go thru the process of creating the Availability Group and a corresponding listener name.

Next Steps

• Read the previous tips – Part 1 and Part 2 - on deploying a Windows Server 2016 Failover Cluster without Active Directory
• Review the previous tips on SQL Server AlwaysOn Availability Group Configuration
  • SQL Server AlwaysOn Availability Groups - Part 1 configuration
  • SQL Server AlwaysOn Availability Groups - Part 2 Availability Groups Setup
• Review the previous tip on Implementing Database Mirroring in SQL Server 2005 across domains

About the author

Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips