Virtual-environments: Update SQL Server Data Tools to SSDT 2017 (or later) for vm image 'windows-2019'

[KrispyX]

Tool information

• Tool name: SQL Server Data Tools
• Add or update? Update
• Desired version: SQL Server Data Tools 2017 (or later)

Question, Bug, or Feature?:
When I create a local instance of LocalDB on build agents from the 'windows-2019' specification, the agents appear to be running SQL Server Data Tools 2016 (version 13.1.4001.0)

sqllocaldb create MSSQLLocalDB
LocalDB instance "MSSQLLocalDB" created with version 13.1.4001.0. <--SSDT 2016

My database deployment scripts requires SSDT 2017 (or later), because they contains newer in-built functions like TRIM(), LEFT(), etc... that were only introduced in SSDT 2017.

LocalDB instance "MSSQLLocalDB" created with version 14.0.3238.1 <-- SSDT 2017

Would it be possible to install SSDT 2017 (or later) on these build agents?

Thanks in advance,

Virtual environments affected

• [ ] macOS 10.15
• [ ] Ubuntu 16.04 LTS
• [ ] Ubuntu 18.04 LTS
• [ ] Windows Server 2016 R2
• [X] Windows Server 2019

[KrispyX]

I notice a similar request was actioned here for vm image vs2017-win2016. https://github.com/actions/virtual-environments/issues/484

It looks like in that instance SSDT was upgraded to version 15.9.3, so if that could happen for vm image windows-2019, that would be really great.

Thanks.

[KrispyX]

Hi. Is there any eta on when this might be implemented? I only ask because a similar request https://github.com/actions/virtual-environments/issues/484 appeared to be resolved within a few days. Thanks in advance,

[maxim-lobanov]

@KrispyX , it is on our backlog but no ETA for now. We will update the issue as soon as it is implemented.

[blueelvis]

@KrispyX - I was able to get the v14 version of localdb by adding a PowerShell task below -

Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))


choco -v

choco install sqllocaldb

It installs chocolatey package manager and then installs the 2017 version of sqllocaldb. It roughly adds 2-3 minutes of build time.

[leotsarev]

I wonder why readme for Windows 2019 image states localdb version as Version: 15.0.4538.1

[Darleev]

@KrispyX we have added installation of SSDT workload for Visual Studio 2019 in scope of feature request to the image and it will be rolled out next week.
In case of any questions, please let us know, we will be glad to assist you further.

[KrispyX]

Thanks @Darleev. Much appreciated.

[Darleev]

@KrispyX SSDT workload for Visual Studio has been installed to the images. Could you please verify and let us know?

[KrispyX]

Hi @Darleev. I tried creating a local instance of LocalDB on build agents on the 'windows-2019' image, but the agents still appear to be running SQL Server Data Tools 2016 (version 13.1.4001.0).

I also tried explicitly targeting versions 14.0 and 15.0, but I get the message "The specified LocalDB version is not available on this computer."

[Darleev]

@KrispyX Let me summarize all details in scope of this case.
We have upgraded SSDT tools for Visual Studio, however it does not resolve the issue, since localdb version is the same.
So, as a workaround, it is possible to try the following steps in runtime:

• Install SQLEXPRESS using command: choco install sql-server-express
• After installation %SERVERNAME%\SQLEXPRESS instance will be created with full access for current user who installed the package
• You can connect to this instance and use it. I have tested that version is Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
  I'm going to close the issue.
  Feel free to contact us in case of any questions.