New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

xSQLServerAlwaysOnAvailabilityGroup: Fails when assigning FQDN to parameter SQLServer #468

Closed
johlju opened this Issue Apr 2, 2017 · 7 comments

Comments

Projects
None yet
2 participants
@johlju
Contributor

johlju commented Apr 2, 2017

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

This comment has been minimized.

Show comment
Hide comment
@randomnote1

randomnote1 Apr 4, 2017

Contributor

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.

Contributor

randomnote1 commented Apr 4, 2017

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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Apr 4, 2017

Contributor

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)
}
Contributor

johlju commented Apr 4, 2017

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

This comment has been minimized.

Show comment
Hide comment
@randomnote1

randomnote1 Apr 4, 2017

Contributor

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.

Contributor

randomnote1 commented Apr 4, 2017

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

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Apr 5, 2017

Contributor

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. :)

Contributor

johlju commented Apr 5, 2017

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

This comment has been minimized.

Show comment
Hide comment
@randomnote1

randomnote1 Sep 22, 2017

Contributor

Starting on this one.

Contributor

randomnote1 commented Sep 22, 2017

Starting on this one.

@johlju johlju added in progress and removed help wanted labels Sep 22, 2017

@johlju

This comment has been minimized.

Show comment
Hide comment
@johlju

johlju Sep 22, 2017

Contributor

@randomnote1 Great! Labeled it as in progress.

Contributor

johlju commented Sep 22, 2017

@randomnote1 Great! Labeled it as in progress.

@randomnote1

This comment has been minimized.

Show comment
Hide comment
@randomnote1

randomnote1 Sep 22, 2017

Contributor

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
Contributor

randomnote1 commented Sep 22, 2017

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

@johlju johlju closed this in #853 Oct 9, 2017

johlju added a commit that referenced this issue Oct 9, 2017

xSQLServerAlwaysOnAvailabilityGroup: Resolved FQDN and Logic Bugs (#853)
- Changes to xSQLServerAlwaysOnAvailabilityGroup
  - Refactored the unit tests to allow them to be more user friendly and to test
    additional SQLServer variations.
    - Each test will utilize the Import-SQLModuleStub to ensure the correct
      module is loaded (issue #784).
  - Fixed an issue when setting the SQLServer parameter to a Fully Qualified
    Domain Name (FQDN) (issue #468).
  - Fixed the logic so that if a parameter is not supplied to the resource, the
    resource will not attempt to apply the defaults on subsequent checks
    (issue #517).
- Added the CommonTestHelper.psm1 to store common testing functions.
  - Added the Import-SQLModuleStub function to ensure the correct version of the
    module stubs are loaded (issue #784).

@johlju johlju removed the in progress label Oct 9, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment