SQL Server 2016 has been out and available for public use (General Availability) since June 1, 2016. Prior to this release the only way we could design and build SQL Server’s flagship High Availability (HA) / Disaster Recovery (DR) feature – Availability Groups was to have a Windows Server Failover Cluster (WSFC) in place. The basic requirement to build a WSFC was for all cluster nodes / servers to reside in a domain. Due to this requirement if you happened to be setup or need to transfer data between work group servers or across domains, you needed to use Database Mirroring to meet your requirements of transferring the data.
With the release of Windows Server 2016, we are now finally able to create a Non-Domain (Workgroup) or Cross-Domain WSFC. Combining SQL Server 2016 with Windows Server 2016, allows us to design, build and configure an Availability Group environment between workgroup servers or servers in different domains.
In this blog post we will look at how we can setup and use a Non-Domain WSFC utilizing Availability Groups.
- Minimum 2 workgroup servers (built with Windows Server 2016)
- Local Account on all workgroup servers with the same name (members of local Administrators Group)
- Static IP Address on all workgroup servers
- SMC-WG1-SQL01 – 10.100.10.3
- SMC-WG1-SQL02 – 10.100.10.4
- DNS Server
- IP Address – 10.0.0.1
- SQL Server 2016
- Workgroup Cluster details
- Cluster Name – SMCWG1Cluster
- IP Address – 10.100.10.20
Just like any implementation around Availability Groups, the preparation is the most important part to ensure success. Before moving on to the next phase I ensured that my workgroup servers were built and fully patched to the latest operating system patch level.
- Ensure the primary DNS Suffix is configured on all workgroup servers.
- Open Server Manager | Click on Local Server | select the Computer Name | Change | More
- Set the primary DNS Suffix (in my case SMC.local)
- Click OK | OK | OK
- Manually create “A Records” in DNS for all workgroup servers
- Run IPConfig /registerDNS on each workgroup server and wait for the server information to become available in DNS before proceeding.
- Confirm all workgroup servers are able to talk to each other using Ping [workgroup servername]
- Add the Clustering Roles and Features to al workgroup servers. Open Server Manager | Click Manage | Select the Add Roles and Features
- Click Next until you reach the Features Tab | Select Failover Clustering | Click Next until you can install the feature
- Repeat all of the above steps on all of the workgroup servers to be included in the Non-Domain WSFC
Create Non-Domain WSFC
Now that we have worked through all of the pre-requisite steps to prepare all of our workgroup servers we are ready to create the WSFC. Now you may have in the past used the Failover Cluster Manager GUI application to create a WSFC. Given we are creating a workgroup WSFC we need to create the cluster using PowerShell cmdlets. To achieve this we have 2 commands that we need to run from our Windows Powershell ISE window:
## run this command on all workgroup cluster nodes New-ItemProperty -path HKLM:SOFTWAREMicrosoftWindowsCurrentVersionPoliciesSystem -Name LocalAccountTokenFilterPolicy -Value 1 ## Create the Non Domain Cluster New-Cluster -Name SMCWG1Cluster -node SMC-WG1-SQL01,SMC-WG1-SQL02 -AdministrativeAccessPoint Dns -StaticAddress 10.100.10.20
For your environments you will need to change the following:
- -Name: [YourClusterName]
- -Node: [YourServer1],[YourServer2],[YouServerN]
- -StaticAddress: [Your Cluster IP Address]
Upon the successful completion you have now created a Non-Domain WSFC.
SQL Server 2016
We are now ready to start and look at our SQL Server 2016 installation on all of the workgroup servers. I am not going to take you through how to install SQL Server 2016. Once you have completed come back and continue with the setup of an Availability Group by following the steps in – Configuring Availability Groups to use certificates.
Congratulations, we have successfully walked through the setup of a Non-Domain WSFC and configured SQL Server 2016 and Availability Groups. All steps have been put together based on my demonstration environment. Ensure that you undertake this in a non-production environment before going near production.