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

App Configuration
1

We would like to scale DreamFactory in Azure horizontally in a single Availability Set with multiple web DreamFactory instances using a single SQL DB as configuration/session/user management DB. Changes to htdocs/config/database.php didn’t seem to make any difference. Version 2.0.1 supposedly should support different DB to host sessions, but there is not much documentation on the topic. Please help.

2

The system DB connection is defined in your .env file, found at the root of your installation. I’m not aware of any documentation on this yet, other than the GitHub readme.

3

Got it, thanks! How do I run php artisan commands on windows installation?

4

Not sure how much time you want to invest in getting things like this running in a testing environment like Windows, but it should be something like C:\path\to\php.exe artisan {command}

5

Got it working with correct paths.
Next I started the setup and got this error trying to generate SQL schema:

  • Welcome to DreamFactory setup wizard.

Running Migrations…
Migration table created successfully.
SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Introduci
ng FOREIGN KEY constraint ‘user_created_by_id_foreign’ on table ‘user’ may cause
cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO A
CTION, or modify other FOREIGN KEY constraints. (SQL: alter table “user” add con
straint user_created_by_id_foreign foreign key (“created_by_id”) references “use
r” (“id”) on delete set null):

6

We haven’t tested using SQL Server as the system DB. In this case, the foreign key is referencing a field in the same table. Maybe this isn’t supported in SQL Server?

Try the following modifications and run setup again:

  • Edit file database/migrations/xxxx_xx_xx_xxxxxx_create_system_tables.php

  • Comment out the following two lines (line 37 and 39)

    $t->foreign('created_by_id')->references('id')->on('user')->onDelete('set null');
    $t->foreign('last_modified_by_id')->references('id')->on('user')->onDelete('set null');

  • Run the setup command again.

7

Tried to disable foreign keys in all the scripts, got one step further.
Now I am getting out of memory error:
Running Seeder…
PHP Fatal error: Out of memory (allocated 1871446016) (tried to allocate 65488
bytes) in C:\Bitnami\dreamfactory-2.0.1-0\apps\dreamfactory\htdocs\vendor\dreamf
actory\df-core\src\Components\ConnectionAdapter.php on line 135

I tried to update php.ini memory_limit=3000M with not much success.

8

Looks like there was a circular dependency in ConnectionAdapter.php
Updated to be:
protected static function getSqlSrvDsn(array $config)
{
// First we will create the basic DSN setup as well as the port if it is in
// in the configuration options. This will give us the basic DSN we will
// need to establish the PDO connections and return them back for use.
if (in_array(‘dblib’, static::getAvailableDrivers())) {
return static::getDblibDsn($config);
} else {
return static::getNonDblibDsn($config);
}
}

9

Update:
Was able to initialize the DB and create first admin user using command line.

Tried to run and got error in the apache log:
REST Exception #400 > Unknown relationship: app_lookup_by_app_id, referer: http://127.0.0.1/dreamfactory/dist/index.html

10

This is an issue with SQL Server not permitting related data creation to traverse multiple tables. which MySQL and PostgreSQL permit. While it’s probably a security feature in SQL Server, it’s preventing proper relationships from being set up in your system DB. We are still testing and have not gotten SQL Server working fully as the system DB yet.

11

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.

12

Thank you for the follow up. I have already tried the approach you suggested, only to face other issues with no match for data types for the constraint fields. After I addressed it by aligning the types of the references columns, the data setup finished successfully. However the system operations are still not working, while able to log in, adding records to the database does not seem to work. I have abandoned the idea as you have concluded dropping the support.

13

Is this still the case in 2017 or is SQL server now supported as the system db?

I could not get sqlite to work so I tried the sqlsrv as it was listed by the setup…
Please advice if there are any steps that can be taken.

I do not want to introduce MySQL or any other database server just for this one system.