Closed codykonior closed 1 month ago
It turns out on PowerShell 5.1 it's not possible to look at that AdditionalMetaData property, and none of the other properties on the modules are appropriate to differentiate the two modules, so the major versions need to be hardcoded.
The problematic function is Get-SqlDscPreferredModule, which can be fixed with:
$availableModule = $null
$availableModules = Get-Module -Name $Name -ListAvailable |
Where-Object -FilterScript { $_.Name -ne 'SqlServer' -or $_.Version.Major -le 21 -or $PSVersionTable.PSVersion.Major -ge 7 } |
ForEach-Object -Process {
@{
PSModuleInfo = $_
CalculatedVersion = $_ | Get-SMOModuleCalculatedVersion
}
}
Examples:
PS C:\Windows\system32> $PSVersionTable
Name Value
---- -----
PSVersion 5.1.14393.693
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.693
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
PS C:\Windows\system32> Get-Module SqlServer -ListAvailable
Directory: C:\Program Files\WindowsPowerShell\Modules
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Script 22.3.0 SqlServer {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvail...
Script 21.1.18256 SqlServer {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvail...
PS C:\Windows\system32> Import-Module SqlServerDsc -Force; Get-SqlDscPreferredModule # Old Code
Directory: C:\Program Files\WindowsPowerShell\Modules
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Script 22.3.0 SqlServer {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvail...
PS C:\Windows\system32> Import-Module SqlServerDsc -Force; Get-SqlDscPreferredModule # Fixed Code
Directory: C:\Program Files\WindowsPowerShell\Modules
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Script 21.1.18256 SqlServer {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvail...
SqlServer module versions 22 and above (e.g. 22.3.0) require PowerShell 7.
This cannot be so. The module version 22.2.0 has been used in Windows PowerShell and the integration tests without any problem when running Windows Server 2022.
VERBOSE: Imported PowerShell module 'SqlServer' with version '22.2.0' from path 'C:\Program
Files\WindowsPowerShell\Modules\SqlServer\22.2.0\SqlServer.psm1'
I also see in the module manifest for SqlServer 22.3.0 that the minimum required version is Windows PowerShell 5.1.
If 22.3.0 does no longer work I cannot be because it requires 7.0. 🤔
Correction. The integration tests for testing the commands uses SqlServer 22.2.0 on Windows PowerShell (link above).
The integration tests for the DSC resources uses SqlServer 22.0.59 on Windows PowerShell:
Thanks Johan. Weird I thought I saw something say it had changed to PS 7 sending me on a wild goose chase.
But now Googling it seems that the SqlServer 22 modules may have a quiet dependency on NetFx472 or 4.8 which isn’t installed by default on Windows Server 2016.
I’ll fiddle with it and find out if that resolves it.
...quiet dependency on NetFx472 or 4.8...
Okay didn't know that.
The other integration tests for SQL Server 2019 and below uses SqlServer 21.1.18256 because there the version 22.0.59 did not work, don't remember why though - but think it had something to do with SQL Server and not Windows. 🤔 Maybe not relevant to this issue.
Let me know what you find. 🙂
It is a net framework 4.7.2 dependency (which I install, but, too late in my DSC to take effect). Sorry for the waste of time.
No worries, glad you found the cause. 🙂
Was there a reason for reopening this one?
Problem description
SqlServerDsc works fine with SqlServer module 21.1.18256 installed. This is the last version (aside from 21.1.18257-preview) which is for PowerShell 5.1.
SqlServer module versions 22 and above (e.g. 22.3.0) require PowerShell 7. This is fine.
However if you have any DSC configurations which use SqlServerDsc, it seems to internally be loading the SqlServer module without validating the version number. This leads it to loading the wrong one (e.g. a PowerShell 7 version under PowerShell 5.1) and this causes all of DSC to fail.
Verbose logs
How to reproduce
Set up DSC resources with SqlServerDsc so that Get-DscConfiguration exits with success. Now copy an SqlServer module 22.3.0 to the system. Then run Get-DscConfiguration again which would go through and re-validate everything.
Expected behavior
Wherever SqlServerDsc does an Import-Module of the SqlServer module it needs to load an appropriate one for the version of PowerShell DSC is running under.
Current behavior
It loads whatever the latest version is instead even though it's for the wrong version of PowerShell.
Suggested solution
It can sort by Version descending, and then select the first one where either AdditionalMetaData.PowerShellVersion does not exist (e.g. on 5.1 version'd modules) or [version] $PSVersionTable.PSVersion -ge AdditionalMetaData.PowerShellVersion.
Operating system the target node is running
PowerShell version and build the target node is running
Module version used