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

By:   |   Comments (7)   |   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

To continue this series on Implement a SQL Server 2016 Availability Group without Active Directory, we will look at creating the SQL Server Availability Group. In Part 1, you learned how to configure the security requirements for authenticating the replicas of the Availability Group. This tip will walk you through the creation of the Availability Group and its corresponding listener name.

Implementing SQL Server Availability Groups without Active Directory

For reference, a review of the high-level steps is shown below. This tip will continue on Step #12.

  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 #12: Create the Availability Group using T-SQL

It’s tempting to use the New Availability Group Wizard to create the Availability Group. However, if you do so, you won’t be able to modify the parameter for the login used to connect to the replicas. By default, the wizard will use the SQL Server service account credentials to connect to the replicas. But since the service accounts are all local user accounts (and that they are built-in accounts that Windows manages), you won’t be able to know what the password is. You can work around this limitation by creating a local user account on all of the failover cluster nodes with the same name and password as described in this previous tip. You can then use the local user account as the SQL Server service account credential.

specify replicas

For this example, the login created in Step #8 will be used.

Use the T-SQL script below to create the Availability Group on the SQL Server instance that you want to configure as the primary replica. It is assumed that you already have a database named Northwind in your SQL Server instance configured in FULL recovery model with at least one full database backup. 

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

GO

CREATE AVAILABILITY GROUP [Workgroup_AG]
WITH 
(
   AUTOMATED_BACKUP_PREFERENCE = PRIMARY,
   DB_FAILOVER = ON,
   DTC_SUPPORT = NONE
)
FOR DATABASE [Northwind]
REPLICA ON 
N'WSFC2016-WG1' WITH 
(
   ENDPOINT_URL = N'TCP://WSFC2016-WG1.TESTDOMAIN.COM:5022', 
   FAILOVER_MODE = AUTOMATIC, 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
   SECONDARY_ROLE
   (
      ALLOW_CONNECTIONS = NO
   )
),
   
N'WSFC2016-WG2' WITH 
(
   ENDPOINT_URL = N'TCP://WSFC2016-WG2.TESTDOMAIN.COM:5022',
   FAILOVER_MODE = AUTOMATIC, 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
   SECONDARY_ROLE
   (
      ALLOW_CONNECTIONS = NO)
   ),
   
N'WSFC2016-WG3' WITH 
(
   ENDPOINT_URL = N'TCP://WSFC2016-WG3.TESTDOMAIN.COM:5022', 
   FAILOVER_MODE = AUTOMATIC, 
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
   SECONDARY_ROLE
   (
      ALLOW_CONNECTIONS = NO)
   );
GO

The following parameters and their corresponding values are used to create the Availability Group

  • Workgroup_AG: the name of the Availability Group
  • AUTOMATED_BACKUP_PREFERENCE = PRIMARY: specifies preference on the primary replica when performing automated database backups
  • DB_FAILOVER = ON: specifies database-level health detection action; this will automatically failover the Availability Group if one of the databases on the primary replica is offline
  • DTC_SUPPORT = NONE: no DTC support
  • FAILOVER_MODE = AUTOMATIC: specifies automatic failover of the Availability Group
  • AVAILABILITY_MODE = SYNCHRONOUS_COMMIT: specifies synchronous mode Availability Group replication
  • SECONDARY_ROLE (ALLOW_CONNECTIONS = NO): specifies secondary replica databases to only be on standby and not used for read-only workloads

After creating the Availability Group on the primary replica, proceed to join the secondary replicas to the Availability Group.

--Run this on the secondary replica/WSFC2016-W2. Just to be sure, enable SQLCMD mode in SSMS  
--Join secondary replica to the Availability Group
:Connect WSFC2016-WG2
ALTER AVAILABILITY GROUP [Workgroup_AG] JOIN;
GO

--Run this on the secondary replica/WSFC2016-W3. Just to be sure, enable SQLCMD mode in SSMS  
--Join secondary replica to the Availability Group
:Connect WSFC2016-WG3
ALTER AVAILABILITY GROUP [Workgroup_AG] JOIN;
GO

After joining the secondary replicas to the Availability Group, proceed to add the database on the secondary replicas to the Availability Group. This is the Join Only option in the Select Initial Data Synchronization dialog box when you run the New Availability Group Wizard.

At this point, it is assumed that you’ve already taken a full and log backup of the database on the primary replica and restored them in the NORECOVERY mode on the secondary replicas. If you haven’t done so, go ahead and do that now before proceeding. 

--Run this on the secondary replica/WSFC2016-W2. Just to be sure, enable SQLCMD mode in SSMS  
-- Add database to Availability Group on secondary replicas
:Connect WSFC2016-WG2
ALTER DATABASE [Northwind] 
SET HADR AVAILABILITY GROUP = [Workgroup_AG];
GO

--Run this on the secondary replica/WSFC2016-W3. Just to be sure, enable SQLCMD mode in SSMS  
--Add database to Availability Group on secondary replicas
:Connect WSFC2016-WG3
ALTER DATABASE [Northwind] 
SET HADR AVAILABILITY GROUP = [Workgroup_AG];
GO

You can view the Availability Group dashboard for the state and configuration information.

workgroup

A few things to consider when implementing Availability Groups – be it for those with Active Directory or without.

  • Dedicated network adapter for Availability Group replication traffic. Most of the examples available online use a single network adapter configuration. Some might say that they use multiple physical network adapters that are teamed (using NIC teaming) and presented to the operating system as a single network adapter. Remember that production traffic will go thru the same network adapter. You also have to consider the cluster heartbeat communication and, for this case, the Availability Group replication traffic. This is the reason why all of the examples I use for failover clustering have a minimum of two (2) network adapters. A future tip will cover how to configure a dedicated network adapter for Availability Group replication traffic.
  • Production workload on secondary replicas. Reporting and read-only workloads are not the only ones considered as production workloads on secondary replicas. Database backups are also included. DBCC CHECKDB, too. Running maintenance routines on secondary replicas require that you license those instances. Now, I am not going to dispute the arguments around running consistency checks on all Availability Group replicas. They are all valid. But valid arguments should be evaluated against the cost incurred to the business, in this case, licensing cost. 
  • Quorum configuration. This is something that needs to be emphasized every time a SQL Server failover clustered instance or Availability Group is implemented. I consider the quorum to be the most complicated, most confusing yet the most important aspect of a Windows Server Failover Cluster. This tip covered the supported quorum configuration for an Availability Group without an Active Directory domain. The goal is to always have majority of votes. If quorum is lost, the Availability Group goes offline. This may not be obvious if the replicas are standalone instances since the system databases are not part of the Availability Group. Configure properly and monitor extensively.
  • Synchronous versus Asynchronous. The decision to use either synchronous or asynchronous replication should be based on recovery objectives (RPO/RTO) and service level agreements (SLA). The choice to use asynchronous over synchronous is often made based on performance SLAs, not recovery objectives. Clearly and realistically define recovery objectives and SLAs and let them dictate the appropriate configuration.

Step #13: Create an Availability Group listener name

Once the Availability Group has been created and validated, proceed to create the listener name. Unlike when creating the Availability Group, you can create the listener name using SQL Server Management Studio.

  • From within Object Explorer, expand the AlwaysOn High Availability node and the Availability Groups node
  • Right-click the Availability Group Listeners node and select the Add Listener command. This opens the New Availability Group Listener dialog box
object explorer
  • In the new Availability Group Listener dialog box, specify the listener name and the port number. In the example below, port 1433 is used for simplicity.
select a page
  • In the Network Mode: drop-down list, select Static IP and click the Add button.
  • In the Add IP Address dialog box, select the appropriate subnet and provide a virtual IP address for the corresponding listener name. Click OK.
add ip address
  • Click OK to create the listener name.

Alternatively, you can use the T-SQL script below to create the Availability Group listener name. 

--Run this on the primary replica/WSFC2016-WG1. Just to be sure, enable SQLCMD mode in SSMS  
:CONNECT WSFC2016-WG1
USE [master]
GO
ALTER AVAILABILITY GROUP [Workgroup_AG]
ADD LISTENER N'Workgroup_AG_LN' 
(
WITH IP
(
)
, PORT=1433);
GO

Implementing a SQL Server Availability Group with an Active Directory domain requires proper planning and thorough documentation.  Use this tip series as a guide to help you successfully provide high availability to your SQL Server databases using Availability Groups.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento 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


Comments For This Article




Tuesday, November 26, 2019 - 12:03:36 PM - bass_player Back To Top (83200)

sosogh,

In Step#11, the login_AvailabilityGroup was granted permissions to connect to the endpoint. 

Because the machines are not joined to an Active Directory domain, the credentials used to connect from the primary to the secondary will be the machine account - represented by the NT Service\MSSQLSERVER account. Because this account only exists in the context of the local machine, it cannot be authenticated by the other machine. How we worked around this is by creating certificates.

The certificates were generated using the Database Master Key (Step#3 and Step#4) from one machine (primary) and imported to the other machine (secondary). We then created a login (login_AvailabilityGroup) on the secondary that will impersonate this machine (primary) using the certificate imported from the primary. Every time the primary replica connects to the secondary replica, it connects using the machine account of the primary, the secondary authenticates it by using the certificate imported from the primary and associates it with the login login_AvailabilityGroup.

Because the secondary replica can function as the primary replica after a failover, the whole process is done on the primary using the certificates generated from the secondary. The same login - login_AvailabilityGroup - was created on all replicas to minimize complexity and confusion but you can use any login so long as it is associated with the certificate imported from the other machine.

Monday, November 25, 2019 - 8:34:40 PM - sosogh Back To Top (83194)

Thank you for your great work on this accurate, detailed and intelligent article.

In the article , it says:

if you do so, you won’t be able to modify the parameter for the login used to connect to the replicas. 

For this example, the login created in Step #8 will be used.

My understanding is that the account which is created in step 8# is "login_AvailabilityGroup".

But it seems that  "login_AvailabilityGroup" is not mentioned in the  T-SQL script in this artcle ?

How do I determine if the "login_AvailabilityGroup" is being used?

Wednesday, July 31, 2019 - 9:59:24 AM - Roee Back To Top (81915)

Jeremy Kadlec

I think I understand, let me know if I understand correctly :

1. I need to create same local user on all replicas. 

2. Create login and sql user mapped to that local user using certificates for all replicas in all replicas. 

3. Change the sql service account to that user. 

4. Create the AG 

5. Join the replicas to the AG

 

Am I right? 

Wednesday, July 31, 2019 - 8:42:45 AM - Jeremy Kadlec Back To Top (81913)

Roee,

Please refer to this tip - https://www.mssqltips.com/sqlservertip/4991/implement-a-sql-server-2016-availability-group-without-active-directory-part-1/.

Thank you,
Jeremy Kadlec
Community Co-Leader

Wednesday, July 31, 2019 - 7:05:56 AM - Roee Back To Top (81911)

Great article - real life saver!

On step 12 it says "the login created in step 8 will be used"

However I am not seeing any reference to any user. 

How I can achieve this?

Thanks in advance.

Saturday, April 20, 2019 - 10:04:08 AM - bass_player Back To Top (79609)

JDA,

You can create additional Availability Groups, each one with its own dedicated listener name. 

It is challenging to know the real issue without digging deeper in the cluster error log. If I were to guess, this error might be caused by using the same virtual network name and/or IP address for the additional listener name. This can happen even with an Active Directory domain. The only difference is that the error message can be more descriptive if this error happened with an Active Directory domain.

Wednesday, April 17, 2019 - 1:18:44 PM - JDA Back To Top (79586)

Hello,

With MSQL 2016 Standard Edition, it's not possible to replicate more than one database per Availability Group.

But is it possible to create an additionnal Availability Group with a dedicated Availability Group Listener , without active Directory?

In my lab test I was successfully installed an Availability Group with SQL 2016 STD Edition with Windows 2016 STD server, without Active Directory on the LAN.

But when I try to create an additional availability group, I can't create a dedicated Availability Group Listener. I get this error:

"Msg 41009, Level 16, State 7, Line 3

The Windows Server Failover Clustering (WSFC) resource control API returned error code 87. The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid. For information about this error code, see "System Error Codes" in the Windows Development documentation.

Msg 19476, Level 16, State 3, Line 3

The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator.

Msg 19476, Level 16, State 1, Line 3

The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator."

Since I have to manage two databases, Is there a solution to get this working in MSSQL Standard Edition without Active Directory.

Has anyone succedded this configuration?

Thanks.














get free sql tips
agree to terms