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.
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.
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.
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.
I hope you find this information useful. Let me know your thoughts.
