SQL Server Distributed Cache On Umbraco 9+

The Distributed SQL Server Cache implementation (AddDistributedSqlServerCache) allows the distributed cache to use an SQL Server database as its backing store.

Please follow the steps below on your Umbraco project to impliment this approach

The first thing to do is to install the following nuget package:

Install-Package Microsoft.Extensions.Caching.SqlServer

On your appsetting.json create a section like below

"SqlCacheConfig": {
    "ConnectionString": "dbo",
    "InstanceName": "CacheStore"
  }

Create a class to map the config

public class SqlCacheSettings
{
    public string SchemaName { get; set; }
    public string TableName { get; set; }
}

After this, the next step is to add all dependencies required for the distributed SQL server cache. This can be done by using “AddDistributedSqlServerCache” from Starup.ConfigureServices. While calling this method, the connection string, schema name, and table name should be passed as shown in below code snippet.

services.AddDistributedSqlServerCache(options =>
{
    SqlCacheSettings sqlCacheConfig = new();
    _config.GetSection("SqlCacheConfig").Bind(sqlCacheConfig);

    if (!string.IsNullOrEmpty(sqlCacheConfig?.SchemaName) && !string.IsNullOrEmpty(sqlCacheConfig?.TableName))
    {
        options.SchemaName = sqlCacheConfig.SchemaName;
        options.TableName = sqlCacheConfig.TableName;
        options.ConnectionString = _config.GetConnectionString("umbracoDbDSN");
    }
});

Startup.cs file found in the root of the web project, add the following code under the ConfigureServices method above the services.AddUmbraco().

Use the below SQL script and execute it against your db from the SQL server

CREATE TABLE [dbo].[CacheStore](
 [Id] [nvarchar](449) NOT NULL,
 [Value] [varbinary](max) NOT NULL,
 [ExpiresAtTime] [datetimeoffset](7) NOT NULL,
 [SlidingExpirationInSeconds] [bigint] NULL,
 [AbsoluteExpiration] [datetimeoffset](7) NULL,
 PRIMARY KEY CLUSTERED 
 (
  [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

for more information read the below Microsoft .net documentation,

Distributed caching in ASP.NET Core

https://thecodeblogger.com/2021/06/09/use-distributed-sql-server-cache-with-net-core-web-apis/

To use the Azure Cache for Redis, read the below article where Gareth Wright explained very well.

Umbraco 10 - Azure Cache for Redis

That's it, Happy Coding 😊