Use separate SQL Server machine for C.I. tests - Githubissues

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.

https://docs.microsoft.com/ef/

MIT License

13.78k stars 3.19k forks source link

Use separate SQL Server machine for C.I. tests #15122

Closed ajcvickers closed 2 years ago

ajcvickers commented 5 years ago

Currently by far our main source of test flakiness is caused by SQL Server failing to respond to connections or otherwise flaking out in similar ways. One idea to help make this better is to run SQL Server on a different box when using the C.I. such that:

It's not competing for resources with all the tests running
It's using a real TCP/IP connection, rather than a local connection (e.g. named pipes) to LocalDb

roji commented 5 years ago

Just an idea: test against an Azure-managed SQL Server instance. This could be more stable/less flakey/less management than managing a box etc.

ajcvickers commented 5 years ago

We had a discussion in triage about how to make sure that the machine is only used by one C.I. run at a time. This would either require:

A pool of servers, with infrastructure to pick one from the pool.
Each agent paired with a specific server instance. This would require BYOC or similar.

For now, we're going to hold off on this and try a couple of other things:

Stop using LocalDb. Use something beefier(🐄🐄🐄), like SQL Developer edition. Configure it to communicate with TCP/IP, even locally.
See if we can run on beefier(🐄🐄🐄) machines.

(Beefier!!! 🐮)

bricelam commented 5 years ago

I'm going to look into using the microsoft/mssql-server-windows-express Docker image for the EF6 CI. We could probably do something similar here--they also have one for developer edition.

bricelam commented 5 years ago

Ugg. Typical SQL Server: The images are built on Windows 1709, but AzDO requires at least 1803.

bricelam commented 5 years ago

Some notes: (Couldn't get either to work without new agents)

To build inside the SQL Server Docker image:

        - job: Windows
          pool:
            vmImage: win1803
          container:
            image: microsoft/mssql-server-windows-developer
            options: -e ACCEPT_EULA=Y -e SA_PASSWORD=Password12!
          variables:
            - Test__SqlServer__DefaultConnection: User ID=sa;Password=Password12!

To start SQL Server using the Docker image:

        - job: Windows
          variables:
            - Test__SqlServer__DefaultConnection: User ID=sa;Password=Password12!
          steps:
            - task: Docker@1
              displayName: Start SQL Server
              inputs:
                command: run
                imageName: microsoft/mssql-server-windows-developer
                ports: '1433:1433'
                envVars: |
                  ACCEPT_EULA=Y
                  SA_PASSWORD=Password12!

bricelam commented 5 years ago

Sounds like Helix has pools with support for Windows Containers. Might be worth front-loading that work...

bricelam commented 5 years ago

Found another way: (connect to container hosted elsewhere)

        - job: Windows
          variables:
            - Test__SqlServer__DefaultConnection: Data Source=mssql;User ID=sa;Password=Password12!
          services:
            mssql:
              image: mcr.microsoft.com/mssql/server
              env:
                ACCEPT_EULA: Y
                SA_PASSWORD: Password12!
                MSSQL_PID: Developer
              ports: 1433

bricelam commented 5 years ago

Well that didn't work as advertised... The setting seems to be ignored. Possibly an issue with the Arcade template? Giving up for now. Sticking with LocalDB on EF6.