Azure Availability Set and scaling DreamFactory using SQL Server as configuration DB

Conclusion:

On SQL Server, database migration fails on foreign key relations between the user.id field <other_tables>.created_by_id and <other_tables>.last_modified_by_id fields. SQL Server documentation indicates the constraints ‘ON DELETE SET NULL’ are not allowed because they “may cause cycles or multiple cascade paths.” This is a secure approach, but prevents required DreamFactory operations that are not prevented by other SQL DB types.

The proper workaround for this would be to use ‘ON DELETE NO ACTION’ on the constraints and use database triggers to set the fields to NULL when parent record is deleted. You may put in the work to implement this workaround, but we have determined that rather than allow this in the official codebase we will simply not support SQL Server as the DreamFactory 2.0 system DB. The supported system DB types are therefore SQLite, MySQL, PostgreSQL. We will be removing the ‘sqlsrv’ option from the DreamFactory 2.0 setup command.