Integration Tests

[gvee-uk]

Feels like we need a full-blown (i.e. requires SSISDB and SQL Agent) SQL Server to deploy things to ensure that the solution works as expected on the target.

i.e. unit testing only gets us so far - the "true test" is once we use it in a production equivalent environment.

We'd chatted previously about spinning up a SQL instance in Azure as a deployment target.

We'll need to think about how we verify that things are successful upon deployment, and how we hook that in to the build pipeline(s).

[GooseLF]

Docker and SQL Server on Linux?

[gvee-uk]

As far as I've been able to work out during previous tinkering - SSIS isn't supported on Linux. I would be more than happy to be proven wrong on this, mind!

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-ssis?view=sql-server-ver16 🤯

[gvee-uk]

The plot thickens... SSIS might work on Linux, but not via Docker it seems.

Also, running a Managed SQL Server in Azure is looking overtly expensive (~£600pcm) because of vCore licensing.

I wonder if a VM with developer edition would be sufficient?

SQL Server 2019 Developer is a full-featured free edition, licensed for use as a development and test database in a non-production environment.

Source: https://www.microsoft.com/en-us/sql-server/sql-server-downloads

[gvee-uk]

@DataShedEd pointed me in the direction of "SQL Server 2019 on Windows Server 2019" - an Azure resource provided by Microsoft.

Looks to cost ~£8.50pcm, which is far more favourable!

Going to give this a spin up and tinker to see if it's viable for our needs

[gvee-uk]

Boy, that was mighty painful.

Summary of learnings:

• If you try run it on the smallest VM (Standard B1s), it doesn't work.
• Second smallest VM (Standard B2s, 2vCPU & 4GB) seems ok enough (and costs ~£30pcm)
• Once configured you need to (both of these tasks can be scripted, if needs be):
  • start SQL Agent
  • configure SSISDB
• If you want to deploy stuff to SSISDB (e.g. create the folder/environment/etc. then you cannot do this via SQL Authentication)
• Windows authentication is "fun". Doable, but fiddly. In short:

  CREATE LOGIN [AzureAD\GeorgeVerney] FROM WINDOWS;
  GRANT CONNECT TO [AzureAD\GeorgeVerney];
  EXEC sp_addsrvrolemember
       @loginame = 'AzureAD\gvee-uk'
     , @rolename = 'sysadmin'
  ;

• Kerberos double-hop (need I say more?). I mean, I will, but ugh.
  • Running sql-deployment-tool locally with Windows Auth/Trusted Connection didn't work - the double hop (local -> VM - > SQL Server) was too much and ended up yielding an anonymous login error
  • Copying the tooling to the server locally and running it there seemed to be the only way to make it work nicely (and I had to connect to the VM as my Windows Auth user, obvs)
  • To grant access to the VM through an AAD account you have to be added to Virtual Machine User Login or Virtual Machine Administrator Login at the resource group level (not the VM itself, it's not enough!).

So I have something working in Azure, just, but I'm not particularly happy about it.

Methinks Docker is a superior solution, if we can get it to work, as this has the added benefits of:

1. on-demand infrastructure (cattle not pets)
2. opportunity to run integration tests locally

[gvee-uk]

Welp, SSIS on Linux is entirely different, so not sure Docker is the way to go (unless we build a Windows Docker image, but that will not be available to run locally for non-Windows users).

The SQL Agent side of things is easy enough:

docker run \
    --env ACCEPT_EULA=Y \
    --env MSSQL_AGENT_ENABLED=True \
    --env SA_PASSWORD=Ohs0Secure. \
    --publish 1433:1433 \
    --detach \
    mcr.microsoft.com/mssql/server:2019-CU16-ubuntu-20.04

The main magic there 👆 is the MSSQL_AGENT_ENABLED=True part.

I'll maybe spend another hour trying to coerce a Linux container in to running SSIS, but I'm far from optimistic.