Deploy a Windows Server 2016 Failover Cluster without Active Directory - Part 1

By:   |   Updated: 2017-06-27   |   Comments (15)   |   Related: 1 | 2 | > Clustering


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

SQL Server Availability Groups were introduced in SQL Server 2012 as a replacement to Database Mirroring. While Database Mirroring was intended to be either a high availability OR disaster recovery solution, Availability Groups can be used for both local high availability AND disaster recovery. You can have multiple Availability Group replicas, depending on the version of SQL Server that you are using.

While Availability Group was a viable replacement for Database Mirroring, there were a couple of blocking issues that prevented customers from upgrading. The first one was licensing. Availability Group was only available in Enterprise Edition prior to SQL Server 2016. If a customer was running Database Mirroring in Standard Edition, there’s no way to upgrade without paying for expensive licenses. However, this is really not a big of a deal for large organizations who already are running Enterprise Edition or are covered under Software Assurance.

The second one was the requirement to run a Windows Server Failover Cluster (WSFC). Database Mirroring has no requirement for external dependencies other than DNS service. Availability Group required a WSFC. This means you need to have a team of highly skilled engineers and database administrators responsible for designing, implementing and managing a WSFC outside of SQL Server.

But what isn’t explicitly mentioned in most of the Microsoft documentation is that a WSFC requires Active Directory. WSFC’s dependency on Active Directory is a more challenging hurdle to overcome, especially if the existing Database Mirroring configuration does not use Active Directory. I have had several customers who postponed upgrading because they didn’t want to implement Active Directory specifically just for Availability Group.

Initial Attempts to Remove WSFC Dependency on Active Directory

Prior versions of Windows Server operating system required Active Directory when you deploy a WSFC: the member servers/nodes have to be joined to an Active Directory domain – the same Active Directory domain. A cluster name object (CNO) is created in Active Directory when a WSFC is created. When a SQL Server failover clustered instance (FCI) or an Availability Group listener name is created, a corresponding virtual computer object (VCO) is also created in Active Directory. The CNO and VCO will also have their corresponding DNS entries created. This is described in this Microsoft TechNet article: Overview of Active Directory accounts needed by a failover cluster. However, this tight integration between a WSFC and Active Directory is the main cause of issues when deploying and managing SQL Server failover clustered instance (FCI) or an Availability Group.

Windows Server 2012 R2 attempted to remove WSFC dependency on Active Directory when the feature called Active Directory-detached Cluster was introduced. This allowed administrators to deploy a WSFC without a corresponding CNO and, thereby, no corresponding VCO in Active Directory. Only the corresponding DNS entries will be created. However, there is a caveat to implementing an Active Directory-detached WSFC: this still requires that the WSFC member servers/nodes are joined to an Active Directory domain. 

Deploying an Active Directory Domain-independent WSFC

The previous tip on Step-by-step Installation of SQL Server 2016 on a Windows Server 2016 Failover Cluster - Part 1 introduced a new feature in Windows Server 2016:  Active Directory domain-independent failover clusters.  This enables administrators to deploy a WSFC without an Active Directory domain. The WSFC member servers/nodes could be a part of a workgroup and this configuration is a viable migration path from Database Mirroring to Availability Group.

While you can also create a WSFC with member servers/nodes in different Active Directory domains or forests, the goal of this tip is to create a WSFC with member servers/nodes that are not joined to an Active Directory domain in preparation for deploying a SQL Server Availability Group.

Prerequisites

Hardware

The hardware requirements for deploying a WSFC – whether the member servers/nodes are joined to an Active Directory domain or not – remain the same. All of the servers should be running Windows Server 2016 and must have the Windows Server 2016 Certified logo on the underlying hardware. And since the WSFC will be used specifically for SQL Server 2016 Availability Group, there is no requirement to use shared storage.

Accounts

The account that you will use to create the WSFC needs to be a member of the local Administrators group – this was the same in previous versions of the Windows Server operating system. This allows you to perform the installation and configuration of the WSFC.  While you can use the built-in local Administrator account, it is recommended to have a dedicated local user account specifically for this purpose. However, because there is not a centralized directory service like Active Directory for managing accounts, you will be responsible for manually managing the account on all of the member servers/nodes in your WSFC.  

A couple of things that you need to do:

  • Create a local user account on all of the member servers/nodes in the WSFC
  • The user name and password of the local user account must be the same on all of the member servers/nodes
  • Add the local user account as a member of the local Administrators group. In this example, the local user account clussvc was created. This will be used to create and manage the WSFC
administrators properties
  • Change the Remote User Account Control (UAC) LocalAccountTokenFilterPolicy registry setting. This registry setting affects how administrator credentials are applied to remotely administer the server. Since you are using a local user account, you will be passing the credentials from one of the member servers/nodes in the WSFC to another to perform administrative tasks. You need to do this on all of the member servers/nodes in the WSFC.

Open an elevated PowerShell command prompt and run the command below.

New-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1

DNS

Because the WSFC will be deployed without an Active Directory CNO, it will have to rely on DNS for both the administrative and client access points. This means that the DNS can potentially become a single point of failure. Talk to your DNS administrators regarding the reliability and resiliency of your DNS infrastructure. In the example below, the network adapter that will be used for client connectivity is configured to have both a preferred and an alternate DNS server. This has to be done on all of the member servers/nodes in the WSFC.

internet protocol version

You also need to configure the primary DNS suffix for all of the member servers/nodes in the WSFC. The primary DNS suffix is used in DNS name registration and DNS name resolution. This is for every member servers/nodes in the WSFC to access each other via a fully qualified domain name (FQDN).

To configure the primary DNS suffix for a server,

  1. Open the System properties of the server
  2. In the Computer Name tab, click the Change button.
computer name
  1. In the Computer Name/Domain Changes dialog box, review the network membership of the server. In the example below, the server is not a member of any Active Directory domain.
computer name
  1. Click the More… button.
  2. In the DNS Suffix and NetBIOS Computer Name dialog box, type the name of the DNS domain name in the Primary DNS suffix of this computer textbox. The example below uses the TESTDOMAIN.COM DNS domain name for the server.
test domain
  1. Click OK until all of the dialog boxes have been closed. You will be prompted to reboot the server.

After configuring the primary DNS suffix on all of the member servers/nodes in the WSFC, you need to add their corresponding DNS entries.  This is simply a mapping of the server hostname with its IP address. You can either ask your DNS administrator to perform this task for you or you can do it yourself, assuming you have administrative privileges on the DNS server.

  1. To create the DNS entries on a Microsoft DNS server, open the DNS Manager administrative console.
  2. Expand the Forward Lookup Zone for the DNS namespace that you used for the server’s primary DNS suffix. For this example, the Forward Lookup Zone for the DNS namespace TESTDOMAIN.COM is used.
  3. Right-click on the DNS namespace and select the New Host (A or AAAA) … option
new host
  1. In the New Host dialog box, type the server hostname and its corresponding IP address. Click the Add Host button to add the DNS entry.
new host

Do this for all of the member servers/nodes in the WSFC. For this example, the servers WSFC2016-WG1, WSFC2016-WG2, and WSFC2016-WG3 will be used.

host

After adding the DNS entries, perform a simple DNS resolution test by using the PING command.

administrator

Alternatively, if you are doing this for testing purposes, you can use local HOSTS file to perform the IP-to-hostname mappings.

DNS Dynamic Updates

Depending on how your DNS servers are configured, you need to talk to your DNS administrators regarding DNS dynamic updates. DNS client computers can use dynamic update to register and dynamically update their resource records with a DNS server whenever changes occur. This is typically used in conjunction with a DHCP server because the IP addresses of computers change on a regular basis.

Dynamic updates are performed in a secure fashion in DNS zones that are configured for Active Directory integration. This is a common configuration. However, if you don’t have an Active Directory infrastructure, the configuration might be slightly different. Below is a screenshot of how a Microsoft DNS server is configured for dynamic updates.

secure only

If not properly configured, the Failover Cluster Validation Wizard will fail. You can temporarily switch this to the Nonsecure and secure option prior to creating the WSFC and switch it back afterwards.

NOTE: The DNS-related tasks described above apply to Microsoft DNS servers. The process will be different if you are running a BIND DNS server in your network.

In the next tip in this series, you will go thru the process of creating the WSFC and configure the cluster quorum settings. 

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


Article Last Updated: 2017-06-27

Comments For This Article




Wednesday, July 10, 2024 - 2:47:49 PM - Edwin M Sarmiento Back To Top (92382)
John,

I might be asking the obvious. But did you add the Failover Clustering feature on the 3rd node prior to adding it to the existing 2-node failover cluster?

Also, is this on-premises or on Azure?

Plus, did you configure the Remote User Account Control (UAC) LocalAccountTokenFilterPolicy registry setting?

Tuesday, July 9, 2024 - 5:03:59 PM - john Back To Top (92374)
Hi,
Create cluster with 2 nodes works with -AdministrativeAccessPoint DNS option perfectly. I'm trying to add a third node with add-clusternode option, but I'm getting permission denied. (-AdministrativeAccessPoint DNS option is not available for add-clusternode command. any idea on how to add a third node?

Tuesday, December 20, 2022 - 7:14:57 AM - bass_player Back To Top (90766)
Leila,

Can you be more specific?

Saturday, December 17, 2022 - 9:43:35 AM - leila Back To Top (90762)
hello,
what about configuring windows cluster for nodes on different datacenter?

Thursday, October 22, 2020 - 3:24:11 PM - bass_player Back To Top (86687)
Did you try rebooting after changing the registry settings? Also, did you run the Failover Cluster Manager MMC as an Administrator?

Monday, October 19, 2020 - 10:29:31 PM - Federico von Hauske Back To Top (86661)
I tried on AWS Windows 2019/SQL Server I went trough the described process using Administrator local accounts, however got Error: You don't have administrative privileges" when try to connect to second node, I applied the change on computer name, add hosts records to hosts file to override any DNS routing, and change registry and be able to ping both servers, however got that error...any clue about it...thanks in advance

Tuesday, December 10, 2019 - 1:55:02 AM - sosogh Back To Top (83365)

Thank you for this article.

what config should I make to tell  the cluster to user  the "clussvc " account? 

via cluster manager ？ or the "log on" property of "cluster service" ? 

I created a two nodes cluster  , and want to use a file share as a witness ,  it seems that it is impossible ?

When I try to do that , I get the error  :

Could not grant the cluster access to the file share '\\192.168.251.17\share'.

There was an error granting the cluster access to the selected file share '\\192.168.251.17\share'.

Failed to grant permissions for the cluster 'mycluster' to access the share 'share'.

An error occurred looking up the security ID of the cluster name object for 'mycluster'.

No mapping between account names and security IDs was done

Thank you 

Wednesday, November 20, 2019 - 4:52:40 PM - bass_player Back To Top (83149)

Ferdinand,

Did you change the Remote User Account Control (UAC) LocalAccountTokenFilterPolicy registry setting? Also, you won't be able to use a file share as a witness since you need to grant read/write access to the Windows Server Failover Cluster computer account, which doesn't exist in Active Directory. Either you use a Cloud Witness, a Disk Witness or Node Majority. 

Tuesday, October 15, 2019 - 12:30:18 PM - Ferdinand Back To Top (82782)

I created a cluster on Server 2016, although I wasn't able to use a service account, I had to use the actual Administrator account, which was not my preferred workflow.  Is there anything special I should look at as to why the preferred account couldn't be used?

Also, I couldn't create a quroum on a network share.  Are there any tips for getting that enabled?

Wednesday, October 17, 2018 - 11:01:30 AM - bass_player Back To Top (77973)

Lucas,

Yes, you can use a listener without Active Directory. Refer to these two tips for additional information on how to do it. The listener name has to be manually created as a DNS entry.

Tuesday, October 16, 2018 - 3:44:52 PM - Lucas Medeiros Furtado Back To Top (77962)

 

Hello! This worked just fine in my case. 

Can we use a listener in this situation without active directory?

Wednesday, June 27, 2018 - 11:27:11 AM - bass_player Back To Top (76438)

 

It is very hard to answer that question without additional details. What happened that caused the Cluster service to not start?

Wednesday, June 27, 2018 - 5:19:57 AM - Noah Back To Top (76435)

Hi, the Cluster Service won't start. Any idea about this?

Thursday, August 24, 2017 - 7:25:39 PM - bass_player Back To Top (65260)

Ariel,

That is totally up to you. Ask your network and systems administrators about how the DNS zones are structured. Maybe you can have the hostnames in an existing DNS zone. 

Wednesday, August 23, 2017 - 5:52:27 PM - Ariel Lampano Back To Top (65213)

 Hi Edwin,

We have our domain but I would like to set-up a workgroup cluster. On your step #6.2., do I have to add manually the new zone (e.g. CLUSTER.HA.NET) to our existing DNS server? Then add the cluster hosts?

 

Thanks.

Ariel

 

 














get free sql tips
agree to terms