ASP.NET Core: SQL Server based distributed cache

ASP.NET Core supports also distributed cache solutions. Out-of-box there is support for SQL Server and Redis based distributed caches. This blog post focuses to SQL Server based cache we can use when there are really no better options.

We don’t need distributed cache with solutions running on one box. But if we have cluster of web servers or we have multiple instances of application running on cloud then we cannot use local memory cache anymore. These caches get filled at different times and they are not in synch meaning that one box may show older content while the other is showing up-to-date content.

NB! I’m not big fan of distributed cache based on relational database as relational database is often the most expensive options for data storage. Still there are situations where using relational database for distributed cache is our only option.

IDistributedCache interface

All distributed cache implementations follow IDistributedCache interface. It has methods for synchronous and asynchronous calls. How these calls are implemented is up to developer who builds the implementation of cache.

public interface IDistributedCache
{
   
byte[] Get(string
key);
   
Task<byte[]> GetAsync(string
key);
   
void Refresh(string
key);
   
Task RefreshAsync(string
key);
   
void Remove(string
key);
   
Task RemoveAsync(string
key);
   
void Set(string key, byte[] value, DistributedCacheEntryOptions
options);
   
Task SetAsync(string key, byte[] value, DistributedCacheEntryOptions options);
}

Preparing application for SQL Server cache

We start with configuring our project to support SQL Server distributed cache and related tooling. FIrst add reference to SqlServer cache NuGet package:

  • Microsoft.Extensions.Caching.SqlServer

After this unload the project, open project file and make sure there is CLI tools reference to SQL Server command-line tools.

<ItemGroup>
  <
DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="1.0.0"
/>
  <
DotNetCliToolReference Include="Microsoft.Extensions.Caching.SqlConfig.Tools" Version="1.0.0-msbuild3-final"
/>
</
ItemGroup
>

We are ready now to create database tables for cache. For this we have to open command-line and run the following command:

  • dotnet sql-cache create <connection string> <schema> <table>

As a result the following table is created to given database. In my case the table name is CacheTable.

CREATE TABLE [dbo].[CacheTable](
    [Id] [nvarchar](449) NOT NULL,
    [Value] [varbinary](max) NOT NULL,
    [ExpiresAtTime] [datetimeoffset](7) NOT NULL,
    [SlidingExpirationInSeconds] [bigint] NULL,
    [AbsoluteExpiration] [datetimeoffset](7) NULL,
 CONSTRAINT [pk_Id] 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]

CREATE NONCLUSTERED INDEX [Index_ExpiresAtTime] ON [dbo].[CacheTable]
(
    [ExpiresAtTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
 
      
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
 
      
ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
 
      
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Now we are done with preparation work and it’s time to get to some real code.

Configuring SQL Server cache

Before we can use caching we have to introduce required components to our application. To do this we have to add the following block of code to Configure() method of Startup class.

services.AddDistributedSqlServerCache(o =>
{
    o.ConnectionString = Configuration[
"ConnectionStrings:Default"
];
    o.SchemaName =
"dbo"
;
    o.TableName =
"Cache";
});

Let’s use distributed cache in default controller. For this we inject the instance of IDistributedCache to controller through its constructor.

public class HomeController : Controller
{
   
private readonly IDistributedCache
_cache;

   
public HomeController(IDistributedCache
cache)
    {
        _cache = cache;
    }
   
public async Task<IActionResult
> Index()
    {
       
await _cache.SetStringAsync("TestString", "TestValue"
);

       
var value = _cache.GetString("TestString"
);

       
return View();
    }
}

Let’s put breakpoint to after reading string from cache and run the application. The screenshot below shows the result.

ASP.NET Core: Playing with SQL Server cache

NB! Notice that there are both synchronous and asynchronous methods to deal with cache.

We can control absolute and sliding expiration through cache options like shown in the following code.

public async Task<IActionResult> Index()
{
   
var options = new DistributedCacheEntryOptions
    {
        AbsoluteExpiration =
DateTime
.Now.AddHours(1)
    };

   
await _cache.SetStringAsync("TestString", "TestValue"
, options);

   
var value = _cache.GetString("TestString"
);

   
return View();
}

Wrapping up

We started with IDistributedCache interface and moved then to SQL Server based distributed cache provided by Microsoft. There are tools that help us create cache table and we had to add reference to these tools manually by editing project file. To use cache we have to inject IDistirbutedCache to controllers and other components using dependency injection. IDistributedCache offers synchronous and asynchronous methods to handle cache. There are also options class available that helps us to control expiration of cache items.

References

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    3 thoughts on “ASP.NET Core: SQL Server based distributed cache

    • September 9, 2019 at 11:25 am
      Permalink

      Hello Gunnar
      The article explains well how to use SQL Server Cache. Do we have any benchmarks for performance when considering between SQL Server Cache vs Redis vs NCache?

    • September 9, 2019 at 3:07 pm
      Permalink

      I have done no benchmarks as relational database is not the best option for caching. If we are talking about heavy loads or big number of parallel users then I would go with Redis. It’s designed exactly for this task and compared to SQL Server it is easier and cheaper to scale.

    • January 10, 2020 at 5:42 pm
      Permalink

      I have configured and using it. However on each SetAsync call it is making 2 entries one is with my key and one is with GUID based value in Id.

    Leave a Reply

    Your email address will not be published. Required fields are marked *