7.18.0 / SqlServer / Feedback

Feature Requests
1

I’ve testet the functionality for store the whole data in SqlServer instead of MongoDB - conclusion: it works!

Good job, thank you for this option!

At the setup i ran into some issues:

  • in the appsettings.json are only two points mentioned for the availability of MongoDb or Sql (eventStore, store), but there is a 3rd option (messaging) which require the same value. If i leave messaging to MongoDb i ran into exception that mongo connections is missing.

  • At the first start, the system stopped at startup with an exception of abort in migration context:

Unhandled exception. System.Threading.Tasks.TaskCanceledException: A task was canceled.
   at Squidex.Infrastructure.Migrations.DatabaseMigrator`1.InitializeAsync(CancellationToken ct) in ...\backend\src\Squidex.Data.EntityFramework\Infrastructure\Migrations\DatabaseMigrator.cs:line 30
  • I tried the setting “migration: false” and after that i got the error message from the connection error (which was a misconfiguration in local SqlServer) - this info is missing at the migration step (which could confuse, the server just stop) - please provide the connection error message if available

After the startup problems my instance finally started up with data stored in the local SQL Server. I made a short benchmark:

  • Restore a backup in MongoDB (localhost) ~2s
  • Restore in MSSQLServer (localhost/Express) ~ 22s (tested twice)
  • Backup size: Reading 3872/3872 events and 211 attachments

Due to the slower reaction i wouldn’t use it now for our current live environment, but for new developments i would give it a try.

Hint: if the SqlServer is a Azure SQL Database or Managed Instance the native JSON columns type could improve performance. Unfortunately the type is not available in SQL Server 2022 - haven’t tested the performance gain of the JSON field but maybe this could be configurable if it has a positive effect…
More info on this: JSON data type (preview) - SQL Server | Microsoft Learn

Cheers, Stefan

2

Thanks a lot:

Good point. I will add that.

This actually means that the server is not available. There should be a better exception message for that.

There has not been done that much performance testing. If you have a OTLP available it would be great to see some numbers.

3

Very likely not, it could contain sensitive data which you do not want to have in the logs necessarily.

4

I agree with you to not show sensitive data in the log - but if you have access to those logs, you might have access to the (unencrypted) configuration files too…

In case you turn off the migration with “migration: false” the connection error is printed into the errorlog (actually without sensitive data) - if you consider to secure the connection issues, the exception has to be catched there too but it will be hard to figure out what is wrong when we loose those exception message (meaning, that the connection couldn’t open, in my case it was a certificate error from the MSSQL Server which i was able to handle in the connectionString)