Which mini (file-based, not service) SQL Server should I use?



  • I'm making a little asp.net MVC app with EF that will maintain the state of alarm sensors (just for the garage - nothing commercial) and don't want to use a full-fat sql database - just a file that can be backed up with the website
    So I was going to go with SQL Server Compact Edition but it seems that it's been deprecated.

    The other MS options, including Developer and Express, are all service-based, which is too heavyweight for my liking.

    Anyone have any experience of SQLite? Is that an option?
    Of course, I could just stick with Compact Edition and not care about the deprecation (for now)?



  • SQLite is the preferred version that comes with ASPCore / .NetCore or whatever the fuck they are calling ASP.NET 5 these days.



  • @skotl Ah shoot - looks like last night's investigation was too biased toward wine consumption over accurate googling.
    So LocalDB is the answer?

    Edit: post crossed with lucas - SQLite or LocalDB then?



  • If you are going to be using ASP. Net 4.6 or 4.5 use LocalDb



  • @skotl said in Which mini (file-based, not service) SQL Server should I use?:

    SQLite or LocalDB then?

    Either; they both work with ASP.NET and EF, though since LocalDB is MS tech, it'll probably work that bit better


  • Discourse touched me in a no-no place

    @RaceProUK said in Which mini (file-based, not service) SQL Server should I use?:

    though since LocalDB is MS tech, it'll probably work that bit better

    I wouldn't count on it. Did you know that SQLite ships as part of Win10?



  • @dkf Actually, I did not.

    Today I learned :)



  • @dkf said in Which mini (file-based, not service) SQL Server should I use?:

    I wouldn't count on it. Did you know that SQLite ships as part of Win10?

    LocalDB is more like a full SQL server, and will do things like warn you if you try to overflow a varchar field (instead of just doing it, like SQLite will) and such. I'd go with LocalDB myself unless you had a really good reason not to.



  • Thanks folks

    I went with localdb and got it working now


  • Discourse touched me in a no-no place

    @blakeyrat SQLite basically makes all VARCHAR fields actually be TEXT fields, which it knows how to handle relatively efficiently. Except that it really doesn't do very much to enforce datatypes in the first place. Some people hate that, some like it. (The underlying actual datastore type is really a tagged union that is used for all columns, with code to handle efficient packing. It beats most more traditional DBs for actual data density, since it uses the actual widths of the values and not the maximum that the DBA expected.)

    AFAIK, SQLite is the first external open source project that Microsoft has truly brought into use in the Windows source code. Microsoft's pretty paranoid about licensing (having been badly burned in the past, apparently) but SQLite's licensing model — flat-out public domain — satisfied them that they wouldn't get screwed.

    In any case, SQLite's pretty nice provided you've not got a write-heavy load. Write-heavy loads need a server to solve the complex locking problems without sending the DB developers utterly crazy.


  • sockdevs

    @skotl said in Which mini (file-based, not service) SQL Server should I use?:

    Anyone have any experience of SQLite? Is that an option?

    yes, it is an option. use it it's amazing for small projects that need a relational database, it maintains performance reasonably well even unto some pretty complicated/big data sets.

    it's somewhat lacking in the sproc arena, but if you're doing heavy sproc you want a heavier weight DB anyway so that's not an issue.



  • If you can deal with the idiosyncrasies of SQLite's dynamic typing (aka, manifest typing or type affinity) then it's pretty good.

    However, be warned that SQLite doesn't scale to very heavy loads. Spiceworks, for example, is SQLite-based. We tested it, and although it worked fine in a test environment of ~100 devices, once we tried to demo it on the full network with > 5,000 devices it ran over a week trying to inventory them all and never finished. Spiceworks blamed SQLite, and they (somewhat shockingly) don't have any other DB options.

    The only other option I'm familiar with would be Firebird, which is available embedded and does have an ADO.Net provider. However, Firebird isn't that popular, and has been fairly slow to release new versions (v3, currently in RC, is several years behind the original planned date). My experience was that it worked very well, but it was a fairly small project that I worked on before SQLite hit v3 and really took off.


  • Discourse touched me in a no-no place

    @BaconBits said in Which mini (file-based, not service) SQL Server should I use?:

    Spiceworks blamed SQLite, and they (somewhat shockingly) don't have any other DB options.

    Well, the SQLite FAQ mentions some options in passing for when you're concurrency-heavy; Postgres is what I'd look at first (and it's what the SQLite authors have recommended to me directly in the past).


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.