While looking at in-memory database engines I started thinking about other in memory database engine types. SQLCE (SQL Server Compact Edition) is also a form of an In-Memory database engine. The difference is that what is in memory is the database engine alone, not the data it supports. The compact edition still uses a file for the data, just like traditional SQL Server. However, the executable database code is embedded with your application as a referenced DLL. It runs in the process of your application, and shares the CPU/Cores with your application as well.
An open source instance of this kind of engine may be found in SqlLite. SqlLite is a lightweight engine written in C, and is also referenced by your application. Moreover, it may be included in your application deployment, just as you include other third party DLLs. It has the highest database size of all lightweight database engines you may host locally, either as an application DLL, or as a server for your client application. ErikEJ has a great chart comparing different options that work on the Microsoft stack.
Why would you want to use a local instance, or embedded instance of SQL Server with your application? This option makes sense when your application may not have access to a centralized SQL Server. Perhaps you have an application that may work stand alone, or networked. If running stand alone this allows you to create an instance without having to maintain a fully functional networked server.
If you embed your database application, it simplifies the deployment. Users are not aware of anything related to your application from a storage perspective. Moreover, unless they decompile your application, they may not know how to access the contents of the database file. Just be aware, if you do use an embedded database, you now become responsible for the backup and restoration of the database.
Another use would be for synchronizing data captured while not connecting to a centralized server. Data is captured locally, and then synchronized with the centralized server at such time as connection is possible.
Are you using these smaller database engines? Share you strategy, and issues you’ve found?
Cheers,
Ben