sql-cache command line is not compatible with In Memory SQL Tables

[xantari]

Is your feature request related to a problem? Please describe.

Running dotnet sql-cache to create a distributed caching table using SQL Server is not able to be promoted to a In Memory OLTP table.

The reason for this is that you are using datetimeoffset(7) for the data type of the AbsoluteExpiration and ExpiresAtTime columns.

datetimeoffset column types are not allowed in in-memory SQL Server tables. You can read about this restriction here:

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/supported-data-types-for-in-memory-oltp?view=sql-server-ver15

What is interesting is that this same type of caching mechanism for session state using in memory tables has been available for full framework .NET applications for a few years now. You can review this for more details: https://blogs.msdn.microsoft.com/docast/2018/03/06/setting-up-asp-net-session-state-with-sql-in-memory-oltp-and-alwayson-database/

As you know in memory SQL Server tables is a very quick and cost effective way of getting a distributed cache cluster going in an environment that already has SQL Server Always On clusters implemented. It performs very fast like a redis cluster when the cache/session state tables are in-memory tables.

My hope would be to leverage our existing investments into our Always On SQL Server cluster to house things like ASP .NET Session state (for full framework applications), and Microsoft's SQL Server DistributedCache provider for .NET Core.

The problem is that only the full framework applications can benefit from in memory tables (see MSDN blog article above). Our .NET Core applications can NOT take advantage of in memory tables due to the data type decision on your cache table using the sql-cache command.

The only alternative would be to purchase docker enterprise and kubernetes and go through that huge learning curve and additional maintenance/server deployments to keep that infrastructure updated to use something like a Redis docker container. Alternatively you could spin up multiple Hyper-V / VMWare environments with Linux and install Redis that way. All less than Ideal options with lots of administrative overhead and costs (Docker EE is not free) when we already have a perfectly capable SQL Server Always On cluster to use.

Describe the solution you'd like

My suggestion is to make sql-cache create In Memory table compliant SQL table. I believe the only change that is required is to use something like datetime2(7) instead of datetimeoffset(7).

The reason why In Memory tables are preferable for things like session state and cache objects is because they are extremely fast and avoid the paging/locking issues that cause large performance bottlenecks when the SQL table resides on disk.

Another option is to allow for the creation of In Memory compliant tables with another command line switch for sql-cache.

Additional context

This request would greatly improve the SQL Distributed cache functionality for .NET Core applications.

[xantari]

FYI, We created a SQL In Memory table capable distributed cache implementation with only slight variations for your out of the box SQL Cache provider.

You can view it here: https://github.com/xantari/Microsoft.Extensions.Caching.SqlServerInMemory

[xantari]

Related: dotnet/extensions#1894

[xantari]

Related: https://github.com/dotnet/extensions/issues/741

[mkArtakMSFT]

Thanks for contacting us.
We do not plan to add new features in this area for now. We will reevaluate this decision in the future if we see enough customer ask for this.