Results 1 to 11 of 11

Thread: Database choices using EF - What are your thoughts?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2004
    Location
    Kansas, USA
    Posts
    352

    Database choices using EF - What are your thoughts?

    I have written a app that uses SQLite and a local internal database. At the time I thought it would be simpler. The app was originally written for a single machine. Now we will be rolling the app out to multiple machines. I need to modify one of the tables in the database and it seems to me that the easiest method is to do so manually on each copy of the database. Currently that is one local development copy and one production machine copy.

    To make things worse I have been using Devart's most excellent Entity Developer and a database first approach to make the EF part much easier. I do not see anything in there for migration support.

    I am now rethinking my decision to use a local SQLite database.

    I have three choices that seem to come to the top:

    1. Stay with a single, local SQLite database on each local machine for each install. Maintenance issues.
    2. Use a database on an existing local SQL Server. Makes the SQL server necessary to the app.
    3. Use AWS RDS. High availability and excellent backup. Cost and latency are the drawbacks.



    My app places low demand on the database with a single write about every 15 seconds - 60 seconds from each machine with the possibility of four machines total. There will be a single read once every 2- 8 hours on average that would retrieve data in the few hundred Kb at most.

    What are your thoughts?
    Thanks,
    Eric
    --------------------------------------------------------------------------------------------------------------------
    VB.net/C# ... Visual Studio 2019
    "None of us are as smart as all of us."

  2. #2
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Database choices using EF - What are your thoughts?

    Of course it depends but I would have maintained a central database.
    staying with a local database would require somehow to retrieve new data and synchronize the databases and that is a real pain.
    Although you must most probably drop the sql lite for a sql server
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  3. #3
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: Database choices using EF - What are your thoughts?

    If machines are still stand-alone then nothing wrong with SQLite. If changes are needed to structure, they are done within the app. This is a messy way to do it (i.e., ignoring the Throw every time after it has made the change), but it only happens once per day in my case when program starts.

    Code:
            conData.ConnectionString = "Data Source=" & My.Application.Info.DirectoryPath & "\data.s3db" & ";Version=3;"
            Try
                conData.Open()
    
                Try
                    Dim cmd As SQLiteCommand = conData.CreateCommand
                    cmd.CommandText = "alter table scalerecords add column QTY integer default 1"
                    cmd.ExecuteNonQuery()
    
                Catch ex As Exception
    
                End Try
            Catch ex As Exception : err(ex.ToString)
    
            End Try
    If they need to share data, then a central server would likely be better.

  4. #4
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Database choices using EF - What are your thoughts?

    If you don't care for db synchronization then go with any option.
    If synchronization is an issue it looks to me like a real pain to try to synchronize all the databases. And what would be the root database to synchronize? Wouldn't it be somewhat a "central" db?
    Again if you don't care about that then any solution is fine.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  5. #5
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Database choices using EF - What are your thoughts?

    Quote Originally Posted by flycast View Post

    1. Stay with a single, local SQLite database on each local machine for each install. Maintenance issues.
    2. Use a database on an existing local SQL Server. Makes the SQL server necessary to the app.
    3. Use AWS RDS. High availability and excellent backup. Cost and latency are the drawbacks.



    My app places low demand on the database with a single write about every 15 seconds - 60 seconds from each machine with the possibility of four machines total. There will be a single read once every 2- 8 hours on average that would retrieve data in the few hundred Kb at most.

    What are your thoughts?
    1) If each PC really is a standalone installation with no requirement to share data with other machines then a local SQLite might still be an easy choice, if you are updating the software then you might be able to include schema changes in the update. Might be worth investigation EF Code Migrations as a possible option.

    2) A database on a SQL server would make access to SQL a requirement but it would prevent the need to update a DB on every single installation. This could also make the database shared between multiple PCs so you have a single database that is always up to date and no need to merge or replicate data between PCs. This really depends on if each installation should be considered stand alone or not though.

    3) AWS RDS can certainly be useful and by resizing your instance as demand changes you mitigate some of the costs, https://aws.amazon.com/rds/aurora/serverless/ is even nicer if MySQL could be a targeted database platform. RDS however can certainly run expensive if you aren't careful but you do get things like included backups, high availability - it really depends on how important these things are. As much as latency can be an issue you will also need to strongly consider security - if you are having your applications access the RDS directly that will mean opening up firewall ports and allowing direct access to the database over the internet, this is probably something you don't want to do unless you really have no other options.

    Personally I wouldn't opt for RDS in this case (as much as I do like the cloud) simply because the costs could be excessive for a fairly low usage database.

    So really it is a choice between a central SQL database or multiple local SQLite databases - and I would say if each install is a stand alone install then SQLite is probably easier and cheaper, if the installs need to share data then a central database server might fit your needs better.

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2004
    Location
    Kansas, USA
    Posts
    352

    Re: Database choices using EF - What are your thoughts?

    An additional issues that I just ran up against as well. With the local SQLite the database is installed in the "c:\user\[current user]\AppData\Roaming/[App name]" folder on each machine. This means a different location and a different connect string for each machine. Another pain.

    I am leaning toward the local SQLServer solution. I do not really care that much about sharing data between machines but the maintenance of multiple SQLite database files just really does not make sense. Centralized would be easier to maintain. Local SQLite makes some sense with a SINGLE install.

    @PlausiblyDamp - I agree that security could be a hassle with RDS. Just a follow up question though. With Aurora serverless RDS I kind of wonder how AWS decides that you do not need the instance and stops charging you? How is latency with serverless RDS?
    Thanks,
    Eric
    --------------------------------------------------------------------------------------------------------------------
    VB.net/C# ... Visual Studio 2019
    "None of us are as smart as all of us."

  7. #7
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: Database choices using EF - What are your thoughts?

    Quote Originally Posted by flycast View Post
    An additional issues that I just ran up against as well. With the local SQLite the database is installed in the "c:\user\[current user]\AppData\Roaming/[App name]" folder on each machine. This means a different location and a different connect string for each machine. Another pain.
    There's no need for that. I just keep the SQLite db in the same folder as the exe. If you want it in one of the special folders then use whatever special folder is needed in the connection string.

  8. #8
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Database choices using EF - What are your thoughts?

    Quote Originally Posted by flycast View Post
    @PlausiblyDamp - I agree that security could be a hassle with RDS. Just a follow up question though. With Aurora serverless RDS I kind of wonder how AWS decides that you do not need the instance and stops charging you? How is latency with serverless RDS?
    If you go Aurora serverless as an option you specify a minimum and maximum capacity unit, a capacity unit is a combination of cpu and ram. AWS will be monitoring the memory and processing load of your cluster and will increase or decrease the capacity units based on this workload. Optionally you can also have AWS pause the cluster after a certain amount of time of inactivity, this will reduce your running costs to storage alone while paused.

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Database choices using EF - What are your thoughts?

    Quote Originally Posted by topshot View Post
    There's no need for that. I just keep the SQLite db in the same folder as the exe.
    I wouldn't do that.. the installation folders where programs go are getting more and more locked down... which means it's harder to write to files in those folders (if not impossible) ... Databases should be going into the app's dat folder, then use the "|DataDirectory|" (I think that's the right directive) moniker in the connection string to route it to the correct location.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2004
    Location
    Kansas, USA
    Posts
    352

    Re: Database choices using EF - What are your thoughts?

    Quote Originally Posted by techgnome View Post
    I wouldn't do that.. the installation folders where programs go are getting more and more locked down... which means it's harder to write to files in those folders (if not impossible) ... Databases should be going into the app's dat folder, then use the "|DataDirectory|" (I think that's the right directive) moniker in the connection string to route it to the correct location.

    -tg
    I agree.

    I am using Devart's Entity Developer to build all the EF code. I am having issues using anything but a "pure" path for the connection string:

    Code:
    C:\users\myusername\AppData\Myappname\database.db
    This doesn't work as a connection string:

    Code:
    %DataDirectory%\MyAppname\database.db
    They do have a template that builds the C# code for EF but it is hard to understand where the connection string is coming from.
    Thanks,
    Eric
    --------------------------------------------------------------------------------------------------------------------
    VB.net/C# ... Visual Studio 2019
    "None of us are as smart as all of us."

  11. #11
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: Database choices using EF - What are your thoughts?

    Quote Originally Posted by flycast View Post
    This doesn't work as a connection string:

    Code:
    %DataDirectory%\MyAppname\database.db
    It should be |DataDirectory|\MyAppname\database.db but maybe that doesn't help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width