0

I'm trying to install an instance of SQL Server 2014 Express on the Visual Studio Online Build agent, however it seems to get stuck and times out after half an hour (I'm trialling it out and using the free version for now).

I'm currently running the following powershell at the start of the build:

choco feature enable -n=allowGlobalConfirmation
choco install mssqlserver2014express --package-parameters='silentArgs:"/IACCEPTSQLSERVERLICENSETERMS /Q /ACTION=install /INSTANCEID=SQLEXPRESS /INSTANCENAME=SQLEXPRESS /UPDATEENABLED=FALSE /FEATURES=SQLENGINE"' -y

This installs fine locally and in AppVeyor, but freezes on VSO Build. Here is the console log up until it times out:

Progress: 99% - Saving 311.7 MB of 311.73 MB
Progress: 100% - Completed download of C:\Users\VssAdministrator\AppData\Local\Temp\chocolatey\chocolatey\MsSqlServer2014Express\SQLEXPR.exe (311.73 MB).
Download of SQLEXPR.exe (311.73 MB) completed.
Hashes match.
C:\Users\VssAdministrator\AppData\Local\Temp\chocolatey\chocolatey\MsSqlServer2014Express\SQLEXPR.exe
Extracting...
Installing...
Installing MsSqlServer2014Express...

I'd be happy to hear answers that don't directly solve the installation problem. For instance, I've found advice elsewhere suggesting that you shouldn't be running integration tests on the build agent. However, I disagree with this advice - I've got a 1000 database tests which only take 5 minutes to run - and I'd prefer to fail the build if these tests fail rather than progress in the build pipeline. Additionally I don't want to mock the database - I'm using Dapper not Entity Framework and want to test that my code interacts with the database schema correctly.

I also saw advice suggesting you should setup your own machine with a SQL instance pre-installed. I'd prefer not to do this since we don't have the resource to setup and maintain (apply security patches etc) the machine. I've trialled using an Azure SQL server, which while easy to maintain, takes too long to create a new database. Each database seems to take about 2 minutes to create, and each test run creates 20 databases.

Update I've tried the microsoft/mssql-server-windows-express docker image on AppVeyor and VSTS. AppVeyor completes the install in ~6minutes but VSTS takes ~19minutes. The download for both took ~4.5mins so I would be happy to use this in AppVeyor with caching, but 19minutes is far too long in VSTS. Does anyone know how to diagnose the bottleneck in VSTS?

docker pull microsoft/mssql-server-windows-express
docker run -d -p 1433:1433 -e sa_password=sgddsgbhidjsghuisg -e ACCEPT_EULA=Y microsoft/mssql-server-windows-express
berkeleybross
  • 1,314
  • 2
  • 13
  • 27
  • What do the database tests do? Are they simply validating the mappings between your entity and database or they more involved like unit testing stored procedures? – tj-cappelletti May 01 '18 at 16:15
  • They are much more involved - inserting data and reading it back out, making sure deletes work (with cascade rules) etc. – berkeleybross May 01 '18 at 16:17
  • Have you looked into containerizing your integration tests? That would be ideal, IMO. Another option: Use an Azure SQL instance. Installing SQL Server on the hosted agent is definitely not going to work. – Daniel Mann May 01 '18 at 17:22
  • Would you mind elaborating that? (Maybe in a full answer?) I'm not sure I understand what you mean - I'm familiar with the concept of containers but never actually used them – berkeleybross May 01 '18 at 17:24
  • @DanielMann again would it be possible to elaborate on "definately not going to work"? It installed on AppVeyor machines in 3 minutes (in addition to being installed out the box!). I just can't figure out whats different when running in VSTS – berkeleybross May 01 '18 at 17:35
  • You can't cache the docker image on Hosted Agent. Regarding the time issue, you may share the detail log on the OneDrive (Set system.debug variable to true, then queue build and share log) – starian chen-MSFT May 02 '18 at 02:02
  • Do you solve this issue? – starian chen-MSFT May 10 '18 at 01:28
  • @starianchen-MSFT no unfortunately not. I attempted to run the build with system.debug as true, but it didn't produce any more relevant information. We're currently investigating AppVeyor and getting legal sign off, but with the fallback plan of using a Private Agent in VSTS – berkeleybross May 10 '18 at 18:48
  • The private agent is better. – starian chen-MSFT May 11 '18 at 01:26

1 Answers1

2

Installing SQL Server on the hosted agent is almost definitely not going to work. The hosted agent is provisioned fresh on every build, then deprovisioned. In addition, it has limited access to install third-party software.

If maintaining an integration test environment isn't going to fly, you have some options:

  1. Use localdb, which should be available on the hosted agent.

  2. Containerize your integration tests and run them in a container -- explaining exactly how Docker works and how to accomplish that is too big to go into detail here, but the basic idea is that you create dockerfile that defines your SQL environment, build/run the container on the hosted agent, run your tests against the container, then throw the container away.

  3. Use an Azure SQL instance -- again, too big to go into specifics, but the basic process is that you'd have an ARM template that defines your SQL instance, you'd run your ARM template to create the SQL instance, deploy your schema and data to the SQL instance, run your tests, then deprovision the SQL instance.

Daniel Mann
  • 57,011
  • 13
  • 100
  • 120
  • Thanks for the more thorough explanation. I've experimented with using Docker in both VSTS and AppVeyor, and whilst it actually installs now it's... not exactly fast. I've added information in my question. I'd appreciate any more help you're willing to give! – berkeleybross May 01 '18 at 20:57
  • @berkeleybross Not much I can offer in terms of speed, unfortunately. I'd explore the localdb option first, since that should work with minimal messing around. – Daniel Mann May 02 '18 at 01:13
  • Relevant - https://stackoverflow.com/a/76166257/7991646 – jrbe228 May 03 '23 at 16:43