Using wrong version of SQLLocaldb on Windows-Latest Devops Build Agent - possibly?

Question

My Azure DevOps CI build pipeline is configured to run on windows-latest build agent

pool:
vmImage: windows-latest

The SQLLocalDb version on window-latest build agents as stated here is v17

When I run the following as part of the yaml CI pipeline, it shows v15.0 (SQLServer 2019)

- script: |
    sqllocaldb versions
    sqllocaldb info
  displayName: sqllocaldb versions on build VM

How do I get to install/upgrade to the latest version so that I can use the ordinal parameter that was introduced for string_split in SQL Server

Is SQllocalDb for SQLserver 2022 available, if so, how do I get it?

*** Could not deploy package.
Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 8144, Level 16, State 3, Procedure <procedure_name>, Line 42 Procedure or function STRING_SPLIT has too many arguments specified.
Error SQL72045: Script execution error.

Answer 1

Upon checking the SQL Server installation path C:\Program Files\Microsoft SQL Server\ on the Microsoft-hosted windows-latest pipeline agent, there is only the sub folder of /LocalDB/Binn in 150 not 160.

To install SQllocalDb for SQL Server 2022, you may refer to the sample PowerShell script below running on the MS-hosted agent, which works for me.

pool:
  vmImage: windows-latest

steps:
- checkout: none
- powershell: |
    sqllocaldb versions
    sqllocaldb info
    tree "C:\Program Files\Microsoft SQL Server\" /F /A
  displayName: Check SQLLocalDB verion and SQL Server installation path
- powershell: |
    Write-Host "================ Download SQL2022 installer==============="
    mkdir SQL2022
    $url = "https://go.microsoft.com/fwlink/?linkid=2215160"
    $outputFilePath = "$(System.DefaultWorkingDirectory)\SQL2022\SQL2022-SSEI-Expr.exe"
    Invoke-WebRequest -Uri $url -OutFile $outputFilePath
    tree $(System.DefaultWorkingDirectory) /F /A
    
    Write-Host "================Download and extract SqlLocalDB.MSI from installation media==============="
    & "$(System.DefaultWorkingDirectory)\SQL2022\SQL2022-SSEI-Expr.exe" /Action=Download /MediaType=LocalDB /Quiet /MEDIAPATH=$(System.DefaultWorkingDirectory)\SQL2022
    Start-Sleep -s 60.00 # Wait for the downlaoding to complete
    tree $(System.DefaultWorkingDirectory) /F /A
    
    Write-Host "================Run the SqlLocalDB.MSI installer non-interactively=============="
    msiexec.exe /qb /i $(System.DefaultWorkingDirectory)\SQL2022\en-US\SqlLocalDB.msi IAcceptSqlLocalDBLicenseTerms=YES
    Start-Sleep -s 60.00 # Wait for the installation to complete
    
    Write-Host "================Check SQLLocalDB verion and SQL Server installation path again==============="
    sqllocaldb versions
    sqllocaldb info
    tree "C:\Program Files\Microsoft SQL Server\" /F /A
  displayName: Install SQllocalDb for SQL Server 2022

Answer 2

The standard SQL Express 2022 installer includes SQLLOCALDB.MSI.

Apart from the first step, I ran the rest of these from an admin command prompt, so should be doable in a pipeline.

1. Download the installer
2. Run the installer to download the install media

SQL2022-SSEI-Dev.exe /Action=Download /MediaType=CAB /Quiet

3. This will create SQLServer2022-DEV-x64-ENU.exe and SQLServer2022-DEV-x64-ENU.box
4. Unpack the installation media

SQLServer2022-DEV-x64-ENU.exe /q

5. This will create the folder SQLServer2022-DEV-x64-ENU
6. SqlLocalDB.MSI is then in SQLServer2022-DEV-x64-ENU\1033_ENU_LP\x64\Setup\x64
7. Run the installer without user input:

SQLServer2022-DEV-x64-ENU\1033_ENU_LP\x64\Setup\x64\SQLLOCALDB.MSI /qn IAcceptSqlLocalDBLicenseTerms=YES

8. Check version

sqllocaldb i "MSSQLLocalDB"

Steps on the way:

There's a direct link to a localDB installer at: https://www.hanselman.com/blog/download-sql-server-express

There's a Stack Exchange query re. unattended localDB installs at: Quiet (unattended) install of SQL Server Express LocalDb 2019