Use Distributed SQL Server Cache with .NET Core Web APIs
Distributed SQL Server Cache with .NET Core Web API

Use Distributed SQL Server Cache with .NET Core Web APIs

In previous article, we have seen what is distributed cache and we also had a look at distributed memory cache implementation. In this article, we will use the same demo project and modify it to use SQL Server distributed cache.

Distributed SQL Server Cache

Distributed SQL server cache uses SQL Server as the backing store to store the cached data. A database and a table can be created in a SQL server instance. The connection string for this database can be provided to application so that it can connect to this data store. This connection string can be either kept in a configuration file or secrets store.

The dotnet CLI providers a command to create the SQL database and table. In this article, we are going to use the command to setup database.

Advantage of using SQL server distributed cache is it is very easy to setup. Also, if your application already uses SQL server database, then it is just matter of creating a new database and setup the table.

Create Cache Store

For creating the store for storing cached items, we can run below commands.

First command is to install the dotnet CLI tools. Once these are downloaded and installed globally, then can be used for any project on that machine. Second command is to create a SQL table in the schema and database pointed by connection string.

This second command would not create the database, but it just creates the table. Hence, the SQL database to be used should already be created before running it. Otherwise you may get error saying “Cannot open database requested by login. The login failed.

Once below two commands have been executed, you can open the SQL Server Management Studio to see that the table is created.

## Install DOTNET-SQL-CACHE tools globally so that we can run next command
dotnet tool install –global dotnet-sql-cache
## Run DOTNET CLI command
## parameters are – connection string, schema name and table name – in that order
## This command just creates tables and indexes. It does not create database
## Database specified in connection string should already be created.
dotnet sql-cache create
"Data Source=.;Initial Catalog=MyCacheDb;Integrated Security=True;" dbo CacheStore
view raw commands.sh hosted with ❤ by GitHub

SQL Table Schema

If because of any reasons, you are not able to run the command, you can also run below SQL script to create the database. But I would recommend against it as we do not know if future versions of distributed SQL server cache implementation would rely on same schema.

Let’s have a look at the schema.

USE [MyCacheDb]
GO
/****** Object: Table [dbo].[CacheStore] Script Date: 08/06/2021 10:29:38 pm ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
view raw Cache.sql hosted with ❤ by GitHub

The table created has below fields:

  • Id, the primary key for the table
  • Value, where the cached value would be stored (byte[])
  • ExpiresAtTime, time at which the entry would expire
  • SlidingExpirationInSeconds, a bigint number representing number of seconds specified in sliding expiration policy. This value can be null.
  • AbsoluteExpiration, the datetime at which the entry would expire as per absolute expiration policy. This value can also be null.

Demo App

Below are the steps to setup distributed SQL Server cache with .NET Core API.

NuGet Package

Create a sample Web API project and add a reference to a nuget package:

Startup

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

Controllers

Every class which needs to use distributed cache should use IDistributedCache for interacting with cache. This dependency can be injected via constructors. Then Get and Set methods can be used for interacting with cache store.

Note that IDistributedCache returns the cached item (if found), as a byte[]. Similarly while caching new item, it should be passed as byte array to the set method.

Code

Below is the code snippet. Full code can be seen in my GitHub repository.

// Startup.cs to configure distributed memory cache.
public class Startup
{
public void ConfigureServices(IServiceCollection services)
{
// Specify ConnectionString, Schema and Table Name
services.AddDistributedSqlServerCache(options =>
{
options.ConnectionString = Configuration.GetConnectionString("CacheDbConnection");
options.SchemaName = "dbo";
options.TableName = "CacheStore";
});
services.AddControllers();
}
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
// Some code…
}
}
// WeatherForecastController.cs – Showing usage of cache
[ApiController]
[Route("[controller]")]
public class WeatherForecastController : ControllerBase
{
private static readonly string[] Summaries = new[]
{
"Freezing", "Bracing", "Chilly", "Cool",
"Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching"
};
private readonly ILogger<WeatherForecastController> _logger;
private readonly IDistributedCache _distributedCache;
private readonly string WeatherForecastKey = "WeatherForecast";
public WeatherForecastController(ILogger<WeatherForecastController> logger, IDistributedCache distributedCache)
{
_logger = logger;
_distributedCache = distributedCache;
}
[HttpGet]
public async Task<IEnumerable<WeatherForecast>> Get()
{
// Find cached item
byte[] objectFromCache = await _distributedCache.GetAsync(WeatherForecastKey);
if (objectFromCache != null)
{
// Deserialize it
var jsonToDeserialize = System.Text.Encoding.UTF8.GetString(objectFromCache);
var cachedResult = JsonSerializer.Deserialize<IEnumerable<WeatherForecast>>(jsonToDeserialize);
if (cachedResult != null)
{
// If found, then return it
return cachedResult;
}
}
// If not found, then recalculate response
var result = GenerateResponse();
// Serialize the response
byte[] objectToCache = JsonSerializer.SerializeToUtf8Bytes(result);
var cacheEntryOptions = new DistributedCacheEntryOptions()
.SetSlidingExpiration(TimeSpan.FromSeconds(10))
.SetAbsoluteExpiration(TimeSpan.FromSeconds(30));
// Cache it
await _distributedCache.SetAsync(WeatherForecastKey, objectToCache, cacheEntryOptions);
return result;
}
}

How does data looks in SQL table ?

If we run a select query on the SQL table, we can see that the data stored is in plain text. Value field is not readable, because it is an array of bytes – but still it is not encrypted.

Depending on what kind of data is being stored in cache, additional measures can be taken to enable encryption.

One of the options can be application enforcing some mechanism to ensure that cached data is not readable. Other option can be to enable TDE on SQL Server so that data remains encrypted on disk – so in case of theft of the disk data would not be readable.

.NET Core API and Distributed SQL Server Cache store

I hope you find this information useful. Let me know your thoughts.

Leave a Reply