Environment
The plan
The Issue
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.`
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
Any comments on how to solve SQL Server Express issue or other possible solutions are very welcome.
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).