SQLite Support

[Adam_Summers]

When deploying SmartConnector on projects we have been challenged if we needed to use MSSQL as the database engine for the SmartConnector service.

I've also had on a couple of occasions the SmartConnector service failing to start as it cannot connect to the database. (I presume MSSQL was slow to boot as simply starting it later worked). *Note perhaps you could make the SmartConnector default to a DelayedStart service to allow a buffer for this?

So with the possibility of supporting EF6 with SQLite or an equivalent self contained "zero config" engine, has it been considered if we could support SQLite in a future version to make simple standalone configurations a little easier to deploy and less dependant upon the MSSQL service engine?

I'd not want to remove the support for MSSQL but wondered if it's possible to support both database targets?

[sesa180908_brid]

The issue with SmartConnector not starting immediately if the MSSQL service hasn’t started is discussed in the Installation and Configuration (see section 4.3).  We don’t do it by default of course but we’ll consider the delayed start suggestion.

We haven’t tried databases other than what are listed in the Installation and Configuration guide (see section 13.2).  Support for SQLite is something we can certainly consider for the backlog.  Is it just MSSQL that you are getting push back on or is it that and SQLExpress too?

[Benji]

While MS SQL support in my mind is of course a must have, I do think that SQLite would serve the majority of applications just as well, if not even better. Faster startup (no start up), and a smaller installation foot print. Easier. I can only envision MS SQL being a must when shared access with other applications is needed.

Curious why you mention SQLExpress as something else than MS SQL? Sure, I know there's different flavors with different capabilities, but at least in my head, it's all MS SQL.

[Adam_Summers]

Same thoughts as Benjamin, I had wrapped MSSQL and SQL Express into the same general "MSSQL heading" - not sure if you thought I was referring to LocalDB? (Which I've not yet tried in a deployed system, but still needs some installation to my knowledge, even though this may be less dependant on configuration and lighter on the host for startup)

I'm not getting lots of push back on MSSQL so it's not a high priority for me. Often we can leverage an existing SQL instance or create a new without problems. Nobody has had a problem with this so far, but people have just asked "if we must use MSSQL" so my thoughts were about making it simpler and easier to configure and install without the added SQL footprint on install and giving our consumers choice!

For me supporting a lighter "no install and config" database source is just a nice to have

[JeffBowman · Accepted solution]

While SQLite would indeed prove beneficial in some use cases, SQLite is not fully companionable with Entity Framework 6 (EF6). Which would most likely cause significant breaking changes to the data model and/or migrations for SmartConnector.. So it may not be a practical from a development perspective to add this support. The below links describes limitations in SQLite (yes I am aware this is written against EF Core.. but the limitations listed are for SQLite engine and not EF)

SQLite Limitations — Entity Framework Core 1.0.0 documentation

An alternative to SQLite, could be SQL Server Compact, also a single file database, it has full EF6 compatibility. The only benefit I see of SQLite over SQL Server Compact is that is can be up to a 100TB in size.. while SQL Server Compact can only be 4GB...But then again.. if someone is gonna have a database with SmartConnector anywhere near that size.. then we should advise against not using SQL Server.

[MAJLGC]

Sqlite support in latest Entity Framework version:

Entity Framework 7 con SQLite para aplicaciones de C#

[sesa180908_brid]

While I am a big fan of what was done in Entity Framework 7 (SmartConnectorServer uses Entity Framework 7), SmartConnector was built on Entity Framework 6.

From experience, the Entity Framework team did take some liberties in their re-design of Entity Framework and the migration path from earlier versions is not something that was recommended - even by them (at least it wasn't in the late beta/RC versions of Entity Framework).

So while getting support for SQLLite would be an added benefit for migrating, I don't think it alone warrants the effort in changing the underpinnings of SmartConnector. 

It is something we will certainly continue to monitor though.  Thanks for the input.

[Anonymous user]

Benjamin,

I would like to access the extended trend logs in the Enterprise Server database directly through a VB program using SQLite libraries that I am writing.  Is there any way to set (or retrieve) the SQLCipher encryption Password in Workstation in the Enterprise Server?

Steve

[sesa180908_brid]

Steve, this post is in the wrong forum.  You may want to repost your request in SmartStruxure Solution forum.