Setup Azure SQL database unit test DevOps environment with tSQLt

Question

Environment

• Server with Azure SQL
• Local with SQL Server Express
• Database deploy with SSDT

The plan

• [Developer] Run database unit test locally on SQL Server Express during development
• [DevOps] Once developer commit the changes, run all unit test cases in pipeline, proceed to deploy if all unit tests pass

The Issue

• [Test 1 - SQL Server Express 2019] Test project failed to deploy as Azure SQL uses a specific version of tSQLt (1.0.5873.27393) which fail with error

  CREATE or ALTER ASSEMBLY for assembly 'tSQLtCLR' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1.`

• [Test 2 - SQL Server Express 2014] Checked with SELECT @@VERION of 2014 and Azure SQL, the version number seem to be the same (12.0.2000.8), gave it a try but fails with error

  A project which specifies Microsoft Azure SQL Database v12 as the target platform cannot be published to SQL Server 2014

Other possible solution

1. Setup 2 difference project which include test cases from same project, where one with tSQLt for Azure SQL deployment and the other with tSQLt for SQL Server Express 2019 for local test (which I am not sure are there any difference between implements which may caused unpredictable errors)
2. Create multiple Azure Sql for each developers so they can test in parallel (which sound stupid to me...)

Any comments on how to solve SQL Server Express issue or other possible solutions are very welcome.

Answer 1

I have an SSDT tSQLt project which deploys fine to both SQL Express/localdb and Azure

(Though I don't in practice find deploying to Azure and running tSQLt unit tests there adds any value compared to just having the CI pipeline run them against localdb - but your milage may vary - tSQLt tests are primarily about testing your code is written correctly and this should be pretty agnostic to what platform you run it on)

There are differences between Azure SQL and on premise that might need testing (e.g. default isolation level of RC vs RCSI might cause different behaviour under conditions of concurrency) but tSQLt unit tests aren't for testing that.

The configuration I use as the debug database is localdb 2019 on which the following has been run

USE [master]

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'clr strict security', 0; RECONFIGURE;
EXEC sp_configure 'clr enabled', 1; RECONFIGURE;

The "Target Platform" is "Azure SQL Database" and that deploys without complaint to SQL Server 2019 local db.

It does mean that I need to restrict myself to syntax supported in both editions but this is rarely an issue (If I get an overwhelming urge to use LEAST and GREATEST then I can always use SQL Server 2022 local db instead)

Occasionally I have a need to use syntax only supported in Azure but I tend to find this is only post deploy scripts and I can add a IF @@VERSION LIKE '%Azure%' check to stop it running in localdb (and hide any incompatible syntax like CREATE USER ... FROM EXTERNAL PROVIDER in EXEC blocks).