First published on MSDN on Jun 28, 2017
Background
One of the new features of SQL Server 2016 is the ability to use SQL Server with Failover Cluster in a workgroup rather than joined to Active Directory. When working with SQL Server and Failover Clustering in a workgroup, many of the abilities that are normally used with Active Directory are no longer available, for example using Windows Authentication.
When using SQL Server Availability Groups in a workgroup cluster, some administrative items such as creating new listeners need to be completed manually.
The Problem
Attempting to use Failover Cluster Manager (FCM) to create resources such as a Client Access Point ([CAP], Also known as Network Name) may result in an error such as, " Error in Validation." , " Unable to determine if the computer '<CAPName>' exists in Domain '<WorkgroupName>'. The server is not operational .". The picture below shows a sample of the error that may occur.
Resolution
Utilizing PowerShell to work with the Workgroup Cluster will allow for the customization of each resource instead of the defaults that the GUI (FCM) may impose.
Below example of creating a CAP/Network Name utilizing PowerShell for use as a listener. Please note, to reuse the below script, you'll need to change the values to those you'd like to use and match your environment. This example creates the same listener that is show in the error using the FCM to the right.
Add-ClusterResource -Name "IPAddress1" -ResourceType "IP Address" -Group "WGAG"
Get-ClusterResource -Name IPAddress1 | Set-ClusterParameter -Multiple @{"Network" = "Cluster Network 1";"Address" = "20.250.250.9";"SubnetMask" = "255.0.0.0";"EnableDHCP" = 0}
Add-ClusterResource -Name "IPAddress2" -ResourceType "IP Address" -Group "WGAG"
Get-ClusterResource -Name IPAddress2 | Set-ClusterParameter -Multiple @{"Network" = "Cluster Network 2";"Address" = "30.250.250.9";"SubnetMask" = "255.0.0.0";"EnableDHCP" = 0}
Add-ClusterResource -Name "TestName" -Group "WGAG" -ResourceType "Network Name"
Get-ClusterResource -Name "TestName" | Set-ClusterParameter -Multiple @{"DnsName" = "TestName";"RegisterAllProvidersIP" = 1}
Set-ClusterResourceDependency -Resource TestName -Dependency "[IPAddress1] or [IPAddress2]"
Start-ClusterResource -Name TestName -Verbose