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.

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.

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

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

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

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.