SqlSetup: Allows install BC and CONN on SQL Server 2022

[Kreby]

Problem description

I've recently upgraded my SqlServerDSc module to 16.1.0 while adding in support for Sql Server 2022 and had issues getting a successful installation. Upon further investigation I found the issue is related to BC and CONN feature support. Those features have been deprecated in Sql Server 2022 but are allowed in the SqlSetup resource to support previous editions. The bug comes in because the following

1. SqlSetup resource's Set-TargetResource accepts the BC and CONN as valid features for a 2022 install.
2. The setup.exe accepts the features when passed via the command line.
3. The setup.exe will then actually filter them out from the features line in the Configuration.ini it creates.
4. The setup.exe executes with out error as the deprecated features aren't trying to be installed.
5. The setup.exe will finish successfully without error.
6. The Test-TargetResource still expects those features to be installed and will look for them only to fail because they are not installed.

I've fixed this by having different feature install list for 2022 and previous versions. Though the resource probably should just prevent you from supplying those features if the version is 2022 to prevent this behavior.

Verbose logs

I thought I archived them before I fixed it but I guess I didn't.  The problem should be straight forward enough as described.  I can recreate the logs but it will take some time to get an environment setup.  So I'll just submit it without for the moment so as to not have to recreate all the other details and then I can add as an update if necessary.

DSC configuration

node localhost
    {        
        SqlSetup 'InstallDefaultInstance'
        {           
            InstanceName            = $SqlInstanceName
            Features                = 'SQLENGINE,REPLICATION,FULLTEXT,CONN,IS,BC'
            SQLCollation            = 'SQL_Latin1_General_CP1_CI_AS'
            SQLSysAdminAccounts     = 'BUILTIN\Administrators'
            InstallSharedDir        = 'C:\Program Files\Microsoft SQL Server'
            InstallSharedWOWDir     = 'C:\Program Files (x86)\Microsoft SQL Server'
            InstanceDir             = 'C:\Program Files\Microsoft SQL Server'
            InstallSQLDataDir       = 'S:'
            SQLUserDBDir            = 'S:\MSSQL\Data'
            SQLUserDBLogDir         = 'T:\MSSQL\Log'
            SQLTempDBDir            = 'S:\MSSQL\Data'
            SQLTempDBLogDir         = 'T:\MSSQL\Log'
            SQLBackupDir            = 'U:\MSSQL\Backup'
            SAPwd                   = $SqlServerSaCredential
            SecurityMode            = 'SQL'
            SourcePath              = $sqlSourcePath
            UpdateEnabled           = 'False'
            UpdateSource            = '.\Updates'
            ForceReboot             = $false
            SqlTempdbFileCount      = 4
            SqlTempdbFileSize       = 200
            SqlTempdbFileGrowth     = 100
            SqlTempdbLogFileSize    = 200
            SqlTempdbLogFileGrowth  = 100
            SQLSvcAccount           = $SqlServiceCredential
            AgtSvcAccount           = $SqlAgentServiceCredential
            AgtSvcStartupType       = 'Automatic'
        }
    }

Suggested solution

I think the easiest thing would be to just add a check similar to the SSMS check but for any of the Tools features, BC, CONN, SDK that are no longer supported in 2022.

SQL Server edition and version

Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64)   Oct  8 2022 05:58:25   Copyright (C) 2022 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2022 Datacenter 10.0 <X64> (Build 20348: ) (Hypervisor)

SQL Server PowerShell modules

Name            Version    Path
----            -------    ----
SqlServer       21.1.18256 C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18256\SqlServer.psd1
SqlServer       21.1.18221 C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18221\SqlServer.psd1
SQLPS           16.0       C:\Program Files (x86)\Microsoft SQL Server\160\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

Operating system

OsName               : Microsoft Windows Server 2022 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 2009
WindowsBuildLabEx    : 20348.1.amd64fre.fe_release.210507-1500
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

PowerShell version

Name                           Value
----                           -----
PSVersion                      5.1.20348.1366
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.20348.1366
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

SqlServerDsc version

Name         Version Path
----         ------- ----
SqlServerDsc 16.1.0  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\16.1.0\SqlServerDsc.psd1

[johlju]

Good suggestion. Sounds like a good idea with such assert being added.

[johlju]

I think we should add a command Assert-Feature that can evaluate if a feature is available in a parent feature (like SQLEngine is part of parent feature SQL), and also evaluate if a feature or parent feature is available in a certain SQL Server major version.

List of available features and what is available in each version:
https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-ver16#Feature

[johlju]

evaluate if a feature is available in a parent feature (like SQLEngine is part of parent feature SQL)

On second though, that sound more like a Test-Feature command. 🤔 That can be a separate issue.