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
- 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.
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,
- Open the System properties of the server
- In the Computer Name tab, click the Change button.
- 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.
- Click the More… button.
- 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.
- 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.
- To create the DNS entries on a Microsoft DNS server, open the DNS Manager administrative console.
- 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.
- Right-click on the DNS namespace and select the New Host (A or AAAA) … option
- 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.
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.
After adding the DNS entries, perform a simple DNS resolution test by using the PING command.
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.
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
- Review the previous tips on Step-by-step Installation of SQL Server 2016 on a Windows Server 2016 Failover Cluster – Part 1, Part 2, Part 3 and Part 4
- Review the previous tip on Implementing Database Mirroring in SQL Server 2005 across domains
- Read more on the following topics
