Install-DbaInstance -Feature MachineLearning fails for SQL Server 2022

[bilodeauj]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

When running Install-DbaInstance -Version 2022 -Feature Engine,FullText,MachineLearning,IntegrationServices it errors out and fails do to the MachineLearning option. The Error states 'SQL_INST_MR' is not a valid value for settings 'FEATURES'.

My understanding is that when using the MachineLearning Feature with Install-DbaInstance this enables AdvancedAnalytics, SQL_INST_MR, SQL_INST_MPY in the config options, however with SQL Server 2022 you only use AdvancedAnalytics for the machine learning feature and SQL_INST_MR, SQL_INST_MPY are no longer used

Steps to Reproduce

Import-Module -Name dbatools

$config = @{
    UPDATEENABLED = "True"
    UPDATESOURCE = "MU"
    BROWSERSVCSTARTUPTYPE = "Disabled"
    USESQLRECOMMENDEDMEMORYLIMITS = "True"
}

$installParams = @{
    Version             = "2022"
    Feature             = 'Engine','FullText','MachineLearning','IntegrationServices'
    PerformVolumeMaintenanceTasks = $true
    SqlCollation        = 'SQL_Latin1_General_CP1_CI_AS'
    AuthenticationMode  = "Windows"
    InstancePath        = "E:\Data"
    DataPath            = "E:\Data\MSSQL16.MSSQLSERVER\MSSQL\Data"
    LogPath             = "F:\Log\MSSQL16.MSSQLSERVER\MSSQL\Data"
    TempPath            = "G:\TempDB\MSSQL16.MSSQLSERVER\MSSQL\Data"
    BackupPath          = "H:\SQL_Backups\MSSQLSERVER"
    AdminAccount        = "LAB\SQL_Admins"
    Path                = "C:\SQL2022"
    Restart             = $true
    Verbose             = $true
    Confirm             = $false
    Configuration       = $config
}

Install-DbaInstance @installParams

Please confirm that you are running the most recent version of dbatools

Major  Minor  Build  Revision
-----  -----  -----  --------
2      1      4      -1     

Other details or mentions

In order to work around this issue I listed the features in the $config variable instead of as one of the cmdlet Feature parameters

$config = @{ FEATURES = "SQLENGINE, FULLTEXT, ADVANCEDANALYTICS, IS" UPDATEENABLED = "True" UPDATESOURCE = "MU" BROWSERSVCSTARTUPTYPE = "Disabled" USESQLRECOMMENDEDMEMORYLIMITS = "True" }

$installParams = @{ Version = "2022" PerformVolumeMaintenanceTasks = $true SqlCollation = 'SQL_Latin1_General_CP1_CI_AS' AuthenticationMode = "Windows" InstancePath = "E:\Data" DataPath = "E:\Data\MSSQL16.MSSQLSERVER\MSSQL\Data" LogPath = "F:\Log\MSSQL16.MSSQLSERVER\MSSQL\Data" TempPath = "G:\TempDB\MSSQL16.MSSQLSERVER\MSSQL\Data" BackupPath = "H:\SQL_Backups\MSSQLSERVER" AdminAccount = "DESKTOP-9L6BV1G\SQL_Admins" Path = "C:\SQL2022" Restart = $true Verbose = $true Confirm = $false Configuration = $config }

Install-DbaInstance @installParams

What PowerShell host was used when producing this error

Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

Powershell 5.1

SQL Server Edition and Build number

SQL Server 2022 Developper Edition

.NET Framework Version

.NET Framework 4.8.9195.0

[andreasjordan]

There is a config file that might need a change to fix this. Will try to have a look at it in the next days...

[andreasjordan]

Don't know why my pull request was not working the way I wanted it to...

The file that needs to be changed is "dbatools-sqlinstallationcomponents.json" in the "bin" folder.

Here are the three entries for MachineLearing and how they need to look like:

    {
        "Feature": "AdvancedAnalytics",
        "Name": [
            "MachineLearning",
            "All"
        ],
        "Description": "Installs SQL Server 2017 Machine Learning Services or SQL Server 2016 R Services (In-Database).",
        "MinimumVersion": "14.0"
    },
    {
        "Feature": "SQL_INST_MR",
        "Name": [
            "MachineLearning",
            "All"
        ],
        "Description": "Applies to SQL Server 2017 Machine Learning Services. Pair with AdvancedAnalytics to install R Open and proprietary R packages.",
        "MinimumVersion": "14.0",
        "MaximumVersion": "15.0"
    },
    {
        "Feature": "SQL_INST_MPY",
        "Name": [
            "MachineLearning",
            "All"
        ],
        "Description": "Applies to SQL Server 2017 Machine Learning Services. Pair with AdvancedAnalytics to install Anaconda and proprietary Python packages.",
        "MinimumVersion": "14.0",
        "MaximumVersion": "15.0"
    },

We have to add "MaximumVersion": "15.0" to the two entries that are not needed for SQL Server 2022.

@bilodeauj Maybe you can change the file on your system and test the changes.

[potatoqualitee]

let me see what I can do

[andreasjordan]

@potatoqualitee : Yes, please do the change for me. Thanks.

[potatoqualitee]

So the encoding was indeed the issue. there were some non-breaking space "gremlins" which forced it to UTF-16 LE. I removed those spaces, saved it as UTF-8 and added your changes 👍🏼

[potatoqualitee]

You can see the file here:

https://github.com/dataplat/dbatools/blob/27d965a113f48e62b1634bbb93c140ea8b4124cb/bin/dbatools-sqlinstallationcomponents.json

and here were some of the things i removed

[andreasjordan]

Thanks!