SQL Server Distributed Cache On Umbraco 9+

Nijas Hameed
2 min readJan 22, 2024

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,

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.

That's it, Happy Coding 😊

--

--

Nijas Hameed

SDE @ ASCENDION | UMBRACIAN | .NET DEV | FULLSTACK DEV