Efcore: Use separate SQL Server machine for C.I. tests

[ajcvickers]

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]

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]

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]

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]

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

[bricelam]

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]

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

[bricelam]

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]

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.