Can non-durable Memory-Optimized Tables be used as a .Net / EF Core cache reliably?

Question

We're using EF Core with SQL Server as our DB Context in addition to some external data sources that we are currently caching in memory in IDictionary objects among others. We're also using OData to make that data available through our web service.

Ideally, both in our EF Core queries and in our OData mapping, query, expand / etc., everything is seen as a single datasource / EF Core provider / DB Context. No manual merging of data is required that way, which would make some annoying code redundant and eases development.

What we could do is to move all data to SQL Server. This can be done by saving the cached data in regular tables (or durable Memory-Optimized ones), but there can be some sensitive data in there. It's definitely still an option, but we'd rather not deal with that data being 'at rest' in our database, backups, etc.

So we're thinking: could we use the non-durable version of Memory-Optimized SQL Server tables for this? Could it work (reliably)?

We've got a (theoretic) solution, but it assumes the following:

1. The only time when a Memory-Optimized SQL Server table is reset, is when the server is restarted, whatever the cause may be.
2. There's never a situation where a single Memory-Optimized is reset. If one is reset, the others are as well.
3. A reboot of SQL Server takes a long time, so checking that can be done relatively infrequently and is definitely not needed before every query to make results reliable.

What we're thinking is to have a background worker process execute the following every x seconds:

• Query a dummy Memory-optimized table: SELECT COUNT(value) FROM DUMMY
  • If exception occurs (after retries), put web service in an error state
  • If count = 0, then assume a restart happened, put web service in an initializing state, start filling all Memory-optimized tables and afterwards add a value to the DUMMY table and remove initializing state.
  • If count = 1, continue.

Concrete questions about this proposed solution:

• Are our assumptions (mainly 1 and 2) correct?
• Any alternative solution using SQL Server events or so?
• Is there a simple way in EF Core to easily pause (disable) all querying (except for those from the worker process?)
• Any better solution in general?

Answer 1

Any better solution in general? we'd rather not deal with that data being 'at rest' in our database, backups, etc.

Honestly, I would just put the tables in a different database. You can use views or synonyms so they look like they are in the main database.

Your assumptions about non-durable tables are correct, and this should work.