Get-SqlDscPreferredModule: Should be possible to specify which version of the SqlServer module is imported

[YaroBear]

Hello!

We have been using SqlServerDSC on systems that have the SqlServer v21.x.x powershell module installed for some time.

We have a requirement now where we need to have both SqlServer v21 and v22 installed on the system.

Is there any way we can specify which version of SqlServer module is imported in SqlServerDSC? We mainly use the SqlScriptQuery module and want to stick with using v21 for some time.

Great work, love what you guys have built! Thanks!

[YaroBear]

Maybe something like Import-Module -Name "sqlserver" -RequiredVersion 21.1.18245 -Force after Import-DscResource -ModuleName SqlServerDsc would override the sqlserver module in the current session? For example:

Import-DscResource -ModuleName SqlServerDsc

Import-Module -Name "sqlserver" -RequiredVersion 21.1.18245 -Force

Node $ServerName
    {
        SqlScriptQuery DatabaseSpecs {
        ...

[kilasuit]

It should be possible as per the docs on Import-DSCResource
This though depends on how you are making use of DSC as it's changed lots in recent years

Can you add some more environment details, like is this in Azure, are you using Machine Configuration at all etc etc so that can ensure to give the right advice here.

[YaroBear]

Certainly!

• We are using Azure Windows Server 2016+ VMs hooked up to Azure Automation Account as our pull server.
• We use DSC 1.1 and do not use Machine Configuration.

What part of the documentation of Import-DSCResource are you referring to?

Tracing the code from SqlScriptQuery.psm1 -> InvokeSqlScript -> Import-SqlDscPreferredModule is where SqlServer module is imported.
I don't think running Import-Module -Name "sqlserver" -RequiredVersion 21.1.18245 -Force before the resource block will work, because SqlScriptQuery resource is going to override that import before Invoking the Sql script.

[johlju]

You cannot specify what version of SqlServer to import during compilation, or prior to compilation. As mentioned above the (latest installed) module SqlServer is returned by Get-SqlDscPreferredModule and is then imported by the command Import-SqlDscPreferredModule.

So I think Get-SqlDscPreferredModule must be extended to support a specific version (probably by reading an environment variable $env:SMODefaultModuleVersion) and then make sure Import-SqlDscPreferredModule can import the specific module version returned by Get-SqlDscPreferredModule. I suggest that Get-SqlDscPreferredModule return the module object instead of just the module name.

Happy to review a PR that resolves this.

[YaroBear]

Awesome, thanks for the help! I’ll see if I can cook up a PR here

[YaroBear]

While working on this, I found a bug in these lines of code. The version field isn't getting populated for SQLServer module as expected. Only SQLPS.

I can't tell exactly why. When I'm debugging, the code seems to exit prematurely on line 116 even though all the keys are present. No error codes.

My code changes are modifying this block of code anyway, just thought I'd bring it up.

Here is an output when I run this code on my machine:

Name      Path                                                                                        Version
----      ----                                                                                        -------
SqlServer C:\Users\yaroslav.berejnoi\Documents\PowerShell\Modules\SqlServer\22.0.49\SqlServer.psd1    
SqlServer C:\Users\yaroslav.berejnoi\Documents\PowerShell\Modules\SqlServer\21.1.18256\SqlServer.psd1 
SqlServer C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18256\SqlServer.psd1              
SqlServer C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18245\SqlServer.psd1              
SqlServer C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18147\SqlServer.psd1              
SQLPS     C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1   130

I tried both PS 7.3.6 and 5.1.19041.3031.

As a quick test for someone else to try, this can simply be copy and pasted into any powershell window. Same code, just with the $Name variable populated:

$Name = @('sqlserver', 'SQLPS')
$availableModule = Get-Module -FullyQualifiedName $Name -ListAvailable |
        Select-Object -Property @(
            'Name',
            'Path',
            @{
                Name       = 'Version'
                Expression = {
                    if ($_.Name -eq 'SQLPS')
                    {
                        <#
                            Parse the build version number '120', '130' from the Path.
                            Older version of SQLPS did not have correct versioning.
                        #>
                        (Select-String -InputObject $_.Path -Pattern '\\([0-9]{3})\\' -List).Matches.Groups[1].Value
                    }
                    else
                    {
                        $versionToReturn = $_.Version

                        if ($_.ContainsKey('PrivateData') -and $_.PrivateData.ContainsKey('PSData') -and $_.PrivateData.PSData.ContainsKey('Prerelease'))
                        {
                            if (-not [System.String]::IsNullOrEmpty($_.PrivateData.PSData.Prerelease))
                            {
                                $versionToReturn = '{0}-{1}' -f $_.Version, $_.PrivateData.PSData.Prerelease
                            }
                        }

                        $versionToReturn
                    }
                }
            }
        )

$availableModule

[johlju]

It is ContainsKey that throws an error that is not caught for some reason. There were several issues, thanks for catching that.
If you would like I can push these changes, or can you incorporate them in your change? What ever is easiest for you.

Here is the working code:

$Name = @('sqlserver', 'SQLPS')
$availableModule = Get-Module -FullyQualifiedName $Name -ListAvailable |
        Select-Object -Property @(
            'Name',
            'Path',
            @{
                Name       = 'Version'
                Expression = {
                    if ($_.Name -eq 'SQLPS')
                    {
                        <#
                            Parse the build version number '120', '130' from the Path.
                            Older version of SQLPS did not have correct versioning.
                        #>
                        (Select-String -InputObject $_.Path -Pattern '\\([0-9]{3})\\' -List).Matches.Groups[1].Value
                    }
                    else
                    {
                        $versionToReturn = $_.Version.ToString()

                        if ($null -ne $_.PrivateData -and $null -ne $_.PrivateData.PSData -and $null -ne $_.PrivateData.PSData.Prerelease)
                        {
                            if (-not [System.String]::IsNullOrEmpty($_.PrivateData.PSData.Prerelease))
                            {
                                $versionToReturn = '{0}-{1}' -f $versionToReturn, $_.PrivateData.PSData.Prerelease
                            }
                        }

                        $versionToReturn
                    }
                }
            }
        )

$availableModule

This correctly returns

Name      Path                                                                                      Version
----      ----                                                                                      -------
SqlServer C:\Program Files\WindowsPowerShell\Modules\SqlServer\22.0.49\SqlServer.psd1               22.0.49-preview1
SqlServer C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18080\SqlServer.psd1            21.1.18080
SQLPS     C:\Program Files (x86)\Microsoft SQL Server\160\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1 160
SQLPS     C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1 130

[YaroBear]

I can incorporate them. Thanks!

[johlju]

Hey, I just saw that the line if (-not [System.String]::IsNullOrEmpty($_.PrivateData.PSData.Prerelease)) seems redundant now? We are already checking that it is not null on the if statement above. 🤔  No it is not, the string can be set to empty and then it is not a prerelease, so the line should still be there. 🙂