SqlServerDsc
SqlServerDsc copied to clipboard
dsccommunity
SqlAGListener: Error causing Listener Port number to be deleted.
Details of the scenario you tried and the problem that is occurring
When running my DSC Script for configuring AGs for SQL Server I started receiving an error on Servers that already had the listener configured.
When I check the SQL Server through management studio the listener no longer had the default 1433 port number listed.
Here is a screenshot of the SQLAGListener Section of my configuration script.
Verbose logs showing the problem
Suggested solution to the issue
The DSC configuration that is used to reproduce the issue (as detailed as possible)
Configuration SQLAG
{
param(
[Parameter(Mandatory = $true)]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential
)
Import-DscResource -ModuleName SqlServerDsc
Node $AllNodes.NodeName
{
# Adding the required service account to allow the cluster to log into SQL
SqlServerLogin AddNTServiceClusSvc
{
Ensure = 'Present'
Name = 'NT SERVICE\ClusSvc'
LoginType = 'WindowsUser'
ServerName = $Node.NodeName
InstanceName = $Node.InstanceName
PsDscRunAsCredential = $SqlAdministratorCredential
}
# Add the required permissions to the cluster service login
SqlServerPermission AddNTServiceClusSvcPermissions
{
DependsOn = '[SqlServerLogin]AddNTServiceClusSvc'
Ensure = 'Present'
ServerName = $Node.NodeName
InstanceName = $Node.InstanceName
Principal = 'NT SERVICE\ClusSvc'
Permission = 'AlterAnyAvailabilityGroup', 'ViewServerState', 'ConnectSQL'
PsDscRunAsCredential = $SqlAdministratorCredential
}
WindowsFeature FailoverFeature
{
Ensure = "Present"
Name = "Failover-Clustering"
DependsOn ='[SqlServerPermission]AddNTServiceClusSvcPermissions'
}
WindowsFeature RSATCLUSTERINGPOWERSHELL
{
Ensure = "Present"
Name = "RSAT-Clustering-Powershell"
DependsOn ='[WindowsFeature]FailoverFeature'
}
WindowsFeature RSATCLUSTERINGMGMT
{
Ensure = "Present"
Name = "RSAT-Clustering-Mgmt"
DependsOn ='[WindowsFeature]FailoverFeature'
}
WindowsFeature RSATCLUSTERINGCMDINTERFACE
{
Ensure = "Present"
Name = "RSAT-Clustering-CmdInterface"
DependsOn ='[WindowsFeature]RSATCLUSTERINGPOWERSHELL'
}
# Create a DatabaseMirroring endpoint
SqlServerEndpoint HADREndpoint
{
EndPointName = 'HADR'
Ensure = 'Present'
Port = 5022
ServerName = $Node.NodeName
InstanceName = $Node.InstanceName
PsDscRunAsCredential = $SqlAdministratorCredential
}
SqlAlwaysOnService EnableHADR
{
Ensure = 'Present'
ServerName = $Node.NodeName
InstanceName = $Node.InstanceName
PsDscRunAsCredential = $SqlAdministratorCredential
}
if ( $Node.Role -eq 'PrimaryReplica' )
{
# Create the availability group on the instance tagged as the primary replica
SqlAG AddTestAG
{
Ensure = 'Present'
Name = $Node.AvailabilityGroupName
InstanceName = $Node.InstanceName
ServerName = $Node.NodeName
FailoverMode = $Node.FailoverMode
AutomatedBackupPreference = 'Primary'
DatabaseHealthTrigger = $TRUE
AvailabilityMode ='SynchronousCommit'
DependsOn = '[SqlAlwaysOnService]EnableHADR', '[SqlServerEndpoint]HADREndpoint', '[SqlServerPermission]AddNTServiceClusSvcPermissions'
PsDscRunAsCredential = $SqlAdministratorCredential
}
SqlAGListener AvailabilityGroupListenerWithSameNameAsVCO
{
Ensure = 'Present'
Port = 1433
ServerName = $Node.NodeName
InstanceName = 'MSSQLSERVER'
AvailabilityGroup = $Node.AvailabilityGroupName
Name = $Node.AvailabilityGroupName
IpAddress = $Node.ListenerIP
PsDscRunAsCredential = $SqlAdministratorCredential
}
}
if ( $Node.Role -eq 'SecondaryReplica' )
{
# Add the availability group replica to the availability group
SqlWaitForAG SQLConfigureAG-WaitAGTest1
{
Name = $Node.AvailabilityGroupName
RetryIntervalSec = 15
RetryCount = 2
PsDscRunAsCredential = $SqlAdministratorCredential
}
SqlAGReplica AddReplica
{
Ensure = 'Present'
Name = $Node.NodeName
AvailabilityGroupName = $Node.AvailabilityGroupName
FailoverMode = $Node.FailoverMode
AvailabilityMode = $Node.AvailabilityMode
ServerName = $Node.NodeName
InstanceName = 'MSSQLSERVER'
ReadOnlyRoutingList = $Node.ReadOnlyRoutingList
PrimaryReplicaServerName = ( $AllNodes | Where-Object { $_.Role -eq 'PrimaryReplica' -and $_.AvailabilityGroupName -eq $Node.AvailabilityGroupName } ).NodeName
PrimaryReplicaInstanceName = ( $AllNodes | Where-Object { $_.Role -eq 'PrimaryReplica' -and $_.AvailabilityGroupName -eq $Node.AvailabilityGroupName } ).InstanceName
DependsOn = '[SqlAlwaysOnService]EnableHADR'
ProcessOnlyOnActiveNode = $Node.ProcessOnlyOnActiveNode
PsDscRunAsCredential = $SqlAdministratorCredential
}
}
}
}
SQL Server edition and version the target node is running
Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64) Nov 30 2018 12:57:58 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
SQL Server PowerShell modules present on the target node
Name Version Path
sqlserver 21.0.17240 C:\Program Files\WindowsPowerShell\Modules\sqlserver\21.0.17240\sqlserver.psd1
SQLPS 14.0 C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1
The operating system the target node is running
OsName : Microsoft Windows Server 2016 Standard OsOperatingSystemSKU : StandardServerEdition OsArchitecture : 64-bit WindowsBuildLabEx : 14393.2758.amd64fre.rs1_release_1.190104-1904 OsLanguage : en-US OsMuiLanguages : {en-US}
Version and build of PowerShell the target node is running
Name Value
PSVersion 5.1.14393.2758
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.2758
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
Version of the DSC module that was used ('dev' if using current dev branch)
SqlServerDsc 12.0.0.0
Hmm. Was the port number set to -1, and the Get-TargetReosurce failed? 🤔 Labeling this as a bug.
I didn't set to -1, and the GUI in management studio doesn't show it as being that. It's very strange because it seems to work every other time. However, once it deletes the port number it will not change it back no matter how many times you run it.
Can you still reproduce the error? If so I could gather some code for you to run manually to see what the values actually are (what is reported by the SMO object). Would be great to have that to be able to build a regression test so we know we fixed the issue, and that it doesn’t happen again.
In a new PowerShell session on the server reporting the issue, please run this. Try running this, change the strings to the proper names.
Import-Module SqlServer
$sqlServerObject = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'servername\instancename'
$availabilityGroupObject = $sqlServerObject.AvailabilityGroups['MyAgGroupName']
$availabilityGroupListener = $availabilityGroupObject.AvailabilityGroupListeners['MyListenerName']
$availabilityGroupListener | Select-Object -ExpandProperty PortNumber
$port = [uint16]( $availabilityGroupListener | Select-Object -ExpandProperty PortNumber )
$port
$presentIpAddress = $availabilityGroupListener.AvailabilityGroupListenerIPAddresses
$presentIpAddress
$presentIpAddress | Select-Object -First 1 -ExpandProperty IsDHCP
$dhcp = [bool]( $presentIpAddress | Select-Object -First 1 -ExpandProperty IsDHCP )
$dhcp
The port number looks like it was set to -1. A similar error was thrown when trying to convert it to [uint16]
Yep, unsigned integer cannot be negative. Thanks for confirming that! We either need to change the type to [int16] or just return the port number as 0 if it is -1. Not sure what -1 means, and what it means for the listener if it is set.
Wonder what caused the port number to be removed 🤔 I guess -1 means port number is not yet set or has not been set yet? :thinking:
Interesting..I would assume -1 does mean it has not been set. However, connections appear to still work even after it clears the port number. Will this just be fixed in the next release?
Anyone in the community can send in a PR to resolve this issue. Do you want to give it a try? I will happily review a PR for this change.
Though we should add a unit test to regression test this issue. So we need to a unit test that give the same error as above, and then solve the issue so that the unit test passes.
Yeah I don't mind sending in a PR. It looks like changing to a [int16] did the trick. How do I go about setting up the unit test?
Since it is the PortNumber we need to regression test, we need to mock the Get-SQLAlwaysOnAvailabilityGroupListener function to return a the -1 for the port number.
https://github.com/PowerShell/SqlServerDsc/blob/7209a03e43f7512dea90c562095bbf99789a43dd/DSCResources/MSFT_SqlAGListener/MSFT_SqlAGListener.psm1#L64
I though I would tell how to write the mock and test using permalinks pointing to existing tests. But the unit tests for the Get-TargetResource is in need for a refactor since it makes second-level call mocks (mocking function calls inside functions the Get-TargetResource is calling). So I made a unit test that mocks the first-level call, to the Get-SQLAlwaysOnAvailabilityGroupListener function.
If you are not familiar with Pester this shows a more correct way of building this unit test.
Add the following unit test (Context-block) somewhere inside the 'Get-TargetResource' Describe-block.
Context 'When the system is in the desired state' {
# Regression test for issue #1360
Context 'When the availability group exist, but the PortNumber is returned as -1' {
BeforeAll {
Mock -CommandName Get-SQLAlwaysOnAvailabilityGroupListener -MockWith {
return New-Object -TypeName Object |
Add-Member -MemberType NoteProperty -Name PortNumber -Value -1 -PassThru |
Add-Member -MemberType ScriptProperty -Name AvailabilityGroupListenerIPAddresses -Value {
return @(
# TypeName: Microsoft.SqlServer.Management.Smo.AvailabilityGroupListenerIPAddressCollection
(New-Object -TypeName Object | # TypeName: Microsoft.SqlServer.Management.Smo.AvailabilityGroupListenerIPAddress
Add-Member -MemberType NoteProperty -Name IsDHCP -Value $false -PassThru |
Add-Member -MemberType NoteProperty -Name IPAddress -Value '192.168.0.1' -PassThru |
Add-Member -MemberType NoteProperty -Name SubnetMask -Value '255.255.255.0' -PassThru
)
)
} -PassThru -Force
}
$testParameters = $defaultParameters.Clone()
}
It 'Should return the desired state as present' {
$result = Get-TargetResource @testParameters
$result.Ensure | Should -Be 'Present'
Assert-MockCalled Get-SQLAlwaysOnAvailabilityGroupListener -Exactly -Times 1 -Scope It
}
It 'Should return the same values as passed as parameters' {
$result = Get-TargetResource @testParameters
$result.ServerName | Should -Be $testParameters.ServerName
$result.InstanceName | Should -Be $testParameters.InstanceName
$result.Name | Should -Be $testParameters.Name
$result.AvailabilityGroup | Should -Be $testParameters.AvailabilityGroup
}
It 'Should return the correct values for the rest of the properties' {
$result = Get-TargetResource @testParameters
$result.IpAddress | Should -Be '192.168.0.1/255.255.255.0'
$result.Port | Should -Be -1
$result.DHCP | Should -Be $false
}
}
}
Run the tests using the following, and the test should throw the same error as described here. Once the error is resolved, the test should pass (this is Test-Driven development by the way).
Invoke-Pester .\Tests\Unit\MSFT_SqlAGListener.Tests.ps1 -CodeCoverage .\DSCResources\MSFT_SqlAGListener\MSFT_SqlAGListener.psm1
You need to have the latest Pester version installed Install-Module Pester -Force
Sorry to interject...
Yeah I don't mind sending in a PR. It looks like changing to a [int16] did the trick. How do I go about setting up the unit test?
If you set it to int16, you effectively limit this to using half of the available ports.
Maximum possible port number is 65535. Maximum value for signed 16-bit integer is 32767.
The value should be changed to int32.
Chris
