Update - jan-27
The problem has been resolved.
All new installations of the Thinkwise Platform will once again deploy properly on Azure databases.
Platform upgrades from 2020.2 and up have been adjusted to not re-introduce the conflicting data type.
Existing installations up until 2020.2 have a service fix available that will phase out the json user-defined data type.
-------------------------------------
An undocumented (or unintended) change
Somewhere around the start of this year, Azure has introduced a change to the managed SQL Server databases regarding user-defined data types.
The change did not affect any existing databases but prevents deploying any databases which use a user-defined type named json as an Azure SQL Server database.
The change has occurred overnight and we have yet to trace it back to a specific Azure Database update. So far, we have not been able to find any documentation regarding this change either. No versions of on-premise SQL Server databases seem to be affected by this change.
The problem can be easily reproduced on an Azure database as following:
create type tjson] from bit;
When you execute this code on any on-premise database, the type will be created as desired. No error occurs. However, on an Azure database this causes the following error:
The type 'varchar' already exists, or you do not have permission to create it.
Which is odd, as we were not creating a type named varchar. Querying the sys.types system catalog view on an Azure database does not yield a system type or user-defined data type for json either.
The problem seems to be within the query parser, as existing databases can use a user-defined datatype named json in their query engine with no problems (regardless of compatibility level).
Impact on the Thinkwise Platform
The result of this change is that it prevents installation and updates of Software Factories hosted as an Azure database.
The problems for the Thinkwise Platform occur when installing or upgrade a Software Factory. Either via the Deployment Center, via direct scripts as well as using the data migration tools in SQL Server Management Studio to move an existing database to Azure.
The error as described above can be seen in your SQL Server IDE of choice or in the Deployment Center log:
2022-01-17 09:44:00.351 +01:00 3Debug] Script file consists of '2303' batch statements.
2022-01-17 09:44:07.475 +01:00 4Error] The type 'varchar' already exists, or you do not have permission to create it.
2022-01-17 09:44:07.487 +01:00 4Error] Deployment failed with thrown exception:
System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: The type 'varchar' already exists, or you do not have permission to create it.
at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__180_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.Tasks.Task.Execute()Intelligent Application Manager (IAM) databases are not affected by this issue.
The pending solution
We are in the process of wiping out all traces of the user-defined data type named json in all supported Software Factory databases and replace them with the raw underlying datatype. This is unfortunately not trivial.
First, we need to provide a service fix, updating existing environments to phase out the data type.
We also need to phase out the usage of the json datatype in all install scripts to support new Software Factory installations on Azure.
Lastly, we need to update all cumulative platform upgrades to no longer use the datatype named json. But more importantly, the platform upgrades need to support both the scenario where the json user-defined data type has already been phased out and simultaneously support the scenario where the json user-defined datatype is still present.
This solution is currently being developed and tested. Platform version 2022.1 and up will have formally renamed the json user-defined data type.
