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,
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 😊