SQL Server Distributed Caching in .NET

 SQL Server Distributed Caching in .NET


SQL Server Distributed Caching is an alternative to in-memory caching when you need persistence, durability, and multi-instance access. It is useful for applications that require a shared cache across multiple servers without setting up a separate caching system like Redis or Memcached.



---


1. Why Use SQL Server Distributed Caching?


✅ Shared Cache Across Multiple Instances – Since the cache is stored in SQL Server, all application instances can access the same cache data.


✅ Persistence – Unlike in-memory caching, SQL Server caching persists even if the application restarts.


✅ No Additional Infrastructure – If your application already uses SQL Server, this avoids the need to set up Redis or Memcached.


✅ Reliable and Secure – Leverages SQL Server’s security, backups, and high availability.


🚫 Slower than Redis – Since SQL Server caching involves disk access, it is slower than in-memory caches like Redis.


🚫 Higher Load on Database – Heavy cache usage may increase SQL Server load, so it should be optimized properly.



---


2. Setting Up SQL Server Distributed Caching in .NET


Step 1: Install Required NuGet Package


To enable SQL Server Distributed Caching, install the Microsoft.Extensions.Caching.SqlServer package:


dotnet add package Microsoft.Extensions.Caching.SqlServer



---


Step 2: Configure SQL Server for Caching


Before using SQL Server caching, you must create the necessary cache table in your database. Microsoft provides a built-in tool to generate the required schema.


Create the SQL Server Cache Table


Run the following command in the Package Manager Console (PMC) or terminal:


dotnet sql-cache create "Server=your-server;Database=your-db;User Id=your-user;Password=your-password;" dbo CacheTable


This will create a table called CacheTable in the specified database. The table has the following structure:


CREATE TABLE dbo.CacheTable

(

    Id NVARCHAR(449) NOT NULL PRIMARY KEY,

    Value VARBINARY(MAX) NOT NULL,

    ExpiresAtTime DATETIMEOFFSET NOT NULL,

    SlidingExpirationInSeconds BIGINT NULL,

    AbsoluteExpiration DATETIMEOFFSET NULL

);


Id: Unique key for cached data.


Value: Serialized cached data stored in binary format.


ExpiresAtTime: The expiration time of the cache entry.


SlidingExpirationInSeconds: If used, the expiration time extends upon access.


AbsoluteExpiration: Hard expiration time, regardless of access.




---


Step 3: Configure SQL Server Caching in .NET Core


In Program.cs (for .NET 6 and later), add SQL Server caching to the service container:


var builder = WebApplication.CreateBuilder(args);


builder.Services.AddDistributedSqlServerCache(options =>

{

    options.ConnectionString = builder.Configuration.GetConnectionString("CacheDb");

    options.SchemaName = "dbo";

    options.TableName = "CacheTable";

});


var app = builder.Build();


For older versions (ASP.NET Core 3.1, .NET 5):


public void ConfigureServices(IServiceCollection services)

{

    services.AddDistributedSqlServerCache(options =>

    {

        options.ConnectionString = Configuration.GetConnectionString("CacheDb");

        options.SchemaName = "dbo";

        options.TableName = "CacheTable";

    });

}


Ensure the connection string is defined in appsettings.json:


{

  "ConnectionStrings": {

    "CacheDb": "Server=your-server;Database=your-db;User Id=your-user;Password=your-password;"

  }

}



---


Step 4: Using SQL Server Cache in Your Application


Once configured, use IDistributedCache to interact with the cache.


Storing Data in Cache


var cache = app.Services.GetRequiredService<IDistributedCache>();


var user = new UserProfile { Id = 1, Name = "John Doe", Age = 30 };

var serializedUser = JsonConvert.SerializeObject(user);


await cache.SetStringAsync("User_1", serializedUser, new DistributedCacheEntryOptions

{

    AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(10)

});


✅ SetStringAsync() stores the data in SQL Server.

✅ AbsoluteExpirationRelativeToNow ensures the data expires in 10 minutes.



---


Retrieving Data from Cache


var cachedData = await cache.GetStringAsync("User_1");

if (!string.IsNullOrEmpty(cachedData))

{

    var user = JsonConvert.DeserializeObject<UserProfile>(cachedData);

}


✅ The cache is checked before querying the database.

✅ If the data is not in the cache, fetch it from the database and cache it.



---


Removing Cached Data


await cache.RemoveAsync("User_1");


✅ Deleting the key ensures stale data is not used.



---


Step 5: Implementing Sliding Expiration


Sliding expiration resets the cache expiration time each time the cached data is accessed.


await cache.SetStringAsync("User_1", serializedUser, new DistributedCacheEntryOptions

{

    SlidingExpiration = TimeSpan.FromMinutes(5)

});


✅ If accessed within 5 minutes, the expiration resets.

✅ If not accessed, the cache entry expires.



---


3. SQL Server Caching Best Practices


✅ Use SQL Server caching only if you already use SQL Server – Otherwise, a dedicated caching system like Redis may be better.


✅ Optimize Database Load – Avoid excessive caching that increases SQL Server storage and load.


✅ Use Expiration Policies Wisely – Set appropriate TTL (time-to-live) and sliding expiration to balance freshness and performance.


✅ Monitor Cache Performance – Use logging and SQL Server monitoring tools to track cache hits/misses.


✅ Combine with Other Caching Mechanisms – SQL Server caching can be complemented with in-memory caching (e.g., IMemoryCache) for hybrid performance optimization.



---


4. When to Use SQL Server Distributed Caching?

SQL Server Distributed Caching is useful in scenarios where multiple applications or instances need to share cached data efficiently. Here are some situations where it makes sense to use it:

  1. Load Balancing Across Multiple Servers – If your application runs on multiple servers (e.g., in a web farm), a shared cache ensures consistency and avoids redundant queries.

  2. Session State Management – Storing session data in SQL Server allows web applications to maintain user sessions even if requests are handled by different servers.

  3. Reducing Database Load – Frequently accessed data (e.g., configurations, lookup tables) can be cached in SQL Server to minimize direct database queries.

  4. Scalability and High Availability – SQL Server supports clustering and replication, making it a reliable choice for caching in distributed environments.

  5. Data Consistency Needs – Unlike in-memory caches like Redis, SQL Server ensures data persistence and transactional consistency, which is crucial for applications needing strong data integrity.

  6. Limited Use of In-Memory Caching – If your infrastructure does not support an in-memory distributed cache (like Redis or NCache), SQL Server provides an alternative with built-in support in .NET applications (e.g., using IDistributedCache).


5. Conclusion


SQL Server Distributed Caching is a great choice for .NET applications that require a persistent, shared cache without additional infrastructure. While not as fast as Redis, it provides a familiar and reliable caching solution when SQL Server is already in use. By properly configuring expiration policies and monitoring database load, developers can effectively integrate SQL caching to improve application performance.

Comments

Post a Comment

Popular posts from this blog

Maxpooling vs minpooling vs average pooling

Image
Pooling is performed in neural networks to reduce variance and computation complexity. Many times, beginners blindly use a pooling method without knowing the reason for using it. Here is a comparison of three basic pooling methods that are widely used. The three types of pooling operations are: Max pooling: The maximum pixel value of the batch is selected. Min pooling: The minimum pixel value of the batch is selected. Average pooling: The average value of all the pixels in the batch is selected. The batch here means a group of pixels of size equal to the filter size which is decided based on the size of the image. In the following example, a filter of 9x9 is chosen. The output of the pooling method varies with the varying value of the filter size. The operations are illustrated through the following figures. Average, Max and Min pooling of size 9x9 applied on an image We cannot say that a particular pooling method is better over others generally. The choice of pooling operation is made...
Read more

Generative AI - Prompting with purpose: The RACE framework for data analysis

Image
  Prompting with purpose: The RACE framework for data analysis We've already established that the quality of your prompts directly influences the quality of your AI-generated outputs. A well-crafted prompt acts as a guiding light, leading the AI toward insightful and actionable results. But how can we consistently achieve this level of clarity and precision in our prompts? Let’s look into the RACE framework—an invaluable tool to elevate your prompts, ensuring they are clear, insightful, and evidence-based. This framework offers a structured approach to prompt engineering, helping you break down complex tasks into manageable steps and communicate your intentions effectively to the AI. This reading will assist you with developing a powerful methodology with generative AI for your data analysis tasks, leading to more accurate, relevant, and impactful results. What is the RACE framework? RACE is an acronym that stands for Research, Answer, Cite, and Expand. It serves as a valuable fram...
Read more

Best Practices for Storing and Loading JSON Objects from a Large SQL Server Table Using .NET Core

 Best Practices for Storing and Loading JSON Objects from a Large SQL Server Table Using .NET Core Introduction JSON (JavaScript Object Notation) is widely used for storing structured data in SQL Server. However, when dealing with large tables, inefficient handling of JSON data can lead to performance bottlenecks, high storage costs, and slow queries. In this article, we will cover best practices for saving and loading JSON objects from large SQL Server tables using .NET Core. We will focus on storage strategies, indexing, efficient querying, and optimized data retrieval using Entity Framework Core (EF Core) and Dapper. --- 1. Choosing the Right Storage Strategy SQL Server supports JSON natively, but choosing the correct storage method depends on your use case. Option 1: Store JSON as NVARCHAR(MAX) (Best for Flexibility & Simplicity) ✔ Ideal for semi-structured or frequently changing data. ✔ Suitable when you need to store the entire JSON object but rarely query individual fiel...
Read more