xSQLServerAlwaysOnAvailabilityGroup: Fails when assigning FQDN to parameter SQLServer

[johlju]

Details of the scenario you try and problem that is occurring:
When assigning FQDN to the SQLServer parameter, for example 'sqltest.company.local', then the resource fails.

VERBOSE: [SQLTEST]: LCM:  [ Start  Set      ]  [[xSQLServerAlwaysOnAvailabilityGroup]SQL2016-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1]
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2016-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-04-02_12-39-02: Importing SQLPS module.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2016-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-04-02_12-39-02: Connected to SQL sqltest.company.local\SQL2016
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2016-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-04-02_12-39-02: Getting the effective permissions for the login 'NT SERVICE\ClusSvc' on 'SQL20
16'.
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2016-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-04-02_12-39-02: Connected to SQL sqltest.company.local\SQL2016
VERBOSE: [SQLTEST]:                            [[xSQLServerAlwaysOnAvailabilityGroup]SQL2016-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1] 2017-04-02_12-39-02: The login 'NT AUTHORITY\SYSTEM' is not present.
VERBOSE: [SQLTEST]: LCM:  [ End    Set      ]  [[xSQLServerAlwaysOnAvailabilityGroup]SQL2016-TESTCLU02-AddAvailabilityGroupTESTCLU02-AG1]  in 0.8070 seconds.
PowerShell DSC resource MSFT_xSQLServerAlwaysOnAvailabilityGroup  failed to execute Set-TargetResource functionality with error message: Creating the availability group 'TESTCLU02-AG1' failed with the error 'Microsoft.SqlServer.Manageme
nt.Smo.SmoException: The current instance 'sqltest.company.local\SQL2016' must be included as an availability replica to create the availability group 'TESTCLU02-AG1'.
   at Microsoft.SqlServer.Management.Smo.AvailabilityGroup.ScriptCreate(StringCollection query, ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()'.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure

Workaround is to change SQLServer to NETBIOS name, for example 'SQLTEST'.

The DSC configuration that is using the resource (as detailed as possible):

xSQLServerAlwaysOnAvailabilityGroup $resourceConfigName
{
    Ensure = 'Present'
    Name = 'TESTCLU02-AG1'
    SQLInstanceName = 'SQL2016'
    SQLServer = 'sqltest.company.local'

    PsDscRunAsCredential = $SqlAdministratorCredential
}

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
Windows Server 2012 R2, SQL Server 2016 and WMF 5.0

Version of the DSC module you're using, or 'dev' if you're using current dev branch:
Dev

[randomnote1]

I think I see what the problem is here. I need to think through how to resolve this one since the availability group replica can be created with either the FQDN or the NETBIOS names, and this should support both.

[johlju]

You mean SQL Server should accept FQDN here? It felt like SQL Server responded with that it did not recognized FQDN server name when the cmdlet New-SqlAvailabilityGroup was called (but I can be wrong). Maybe something missing in SQL?

I worked around it for the time being. :)

xSQLServerAlwaysOnAvailabilityGroup $resourceConfigName
{
    Ensure = 'Present'
    Name = $Node.AvailabilityGroupName
    SQLInstanceName = $Node."$($currentSqlInstance)InstanceName"
    # This must be the Netbios name (bug issue #468)
    SQLServer = ($Node.NodeName -split '.', 2, 'simplematch' | Select-Object -First 1)
}

[randomnote1]

Yeah, connecting to SQL with the FQDN isn't the problem. When the replica is added to the availability group, it has to either be the FQDN or the NETBIOS name. At the moment, I'm detecting the name of the name of the replica from the NetName(?) of the server object, therefore it is always the NETBIOS name.

I need to add some logic to translate between NETBIOS and FQDN so using the FQDN to connect to the instance doesn't break the resource.

[johlju]

EndpointHostName is set to $EndpointHostName = $serverObject.NetName. But changing that does not help.

This fails

xSQLServerAlwaysOnAvailabilityGroup $resourceConfigName
{
    Ensure = 'Present'
    Name = $Node.AvailabilityGroupName
    SQLInstanceName = $Node."$($currentSqlInstance)InstanceName"

    SQLServer = 'sqltest.company.local'
    EndpointHostName = 'sqltest'

    PsDscRunAsCredential = $SqlAdministratorCredential
}

This fails also

xSQLServerAlwaysOnAvailabilityGroup $resourceConfigName
{
    Ensure = 'Present'
    Name = $Node.AvailabilityGroupName
    SQLInstanceName = $Node."$($currentSqlInstance)InstanceName"

    SQLServer = 'sqltest.company.local'
    EndpointHostName = 'sqltest.company.local'

    PsDscRunAsCredential = $SqlAdministratorCredential
}

But this do work

xSQLServerAlwaysOnAvailabilityGroup $resourceConfigName
{
    Ensure = 'Present'
    Name = $Node.AvailabilityGroupName
    SQLInstanceName = $Node."$($currentSqlInstance)InstanceName"

    SQLServer = 'sqltest'
    EndpointHostName = 'sqltest.company.local'

    PsDscRunAsCredential = $SqlAdministratorCredential
}

So I think it is line 334 that is the issue.

When SQLServer is set to 'sqltest.company.local' (FQDN) $serverObject.Name returns 'sqltest.company.local\SQL2016'.
When SQLServer is set to 'sqltest' (NETBIOS) $serverObject.Name returns 'sqltest\SQL2016'.

Maybe New-SqlAvailabilityReplica cmdlet only allows NETBIOS name in the Name parameter? In this documentation for Name parameter it says Computer\Instance which maybe means NETBIOS computer name. :)

[randomnote1]

Starting on this one.

[johlju]

@randomnote1 Great! Labeled it as in progress.

[randomnote1]

Notes for myself:

Test the following scenarios for the supplied SQLServer parameter, availability group replica names, and database mirroring endpoints.

• NetBIOS name (short name)
• FQDN
• IP Address