Distributed SQL Server Cache — ASP.NET Core 9

ASP.NET Core MVC app demonstrating IDistributedCache backed by SQL Server — a production-relevant caching pattern for distributed systems where Redis is unavailable or SQL Server is the preferred infrastructure.

In many enterprise environments (UK financial sector, ERP systems), Redis is not available or not approved. SQL Server distributed caching via Microsoft.Extensions.Caching.SqlServer provides the same IDistributedCache interface — meaning the caching layer is swappable without changing application code.

HTTP Request
    │
    ▼
ProductService.GetProductName(id)
    │
    ├── Cache HIT  → return from dbo.DistributedCache (SQL Server)
    │
    └── Cache MISS → simulate DB call → store in cache (TTL: 5 min) → return

• Cache key: product-{id}
• TTL: 5 minutes (AbsoluteExpirationRelativeToNow)
• Storage: SQL Server table dbo.DistributedCache
• Interface: IDistributedCache (swappable with Redis, Memory cache, etc.)

• .NET 9 / ASP.NET Core MVC
• Microsoft.Extensions.Caching.SqlServer
• IDistributedCache abstraction
• SQL Server

Program.cs — register SQL Server cache:

builder.Services.AddDistributedSqlServerCache(options =>
{
    options.ConnectionString = builder.Configuration.GetConnectionString("DefaultConnection");
    options.SchemaName = "dbo";
    options.TableName = "DistributedCache";
});

ProductService.cs — cache-aside pattern:

public async Task<string> GetProductName(int id)
{
    string cacheKey = $"product-{id}";
    string productName = await _cache.GetStringAsync(cacheKey);

    if (productName == null)
    {
        productName = $"Product {id} from DB"; // DB call
        await _cache.SetStringAsync(cacheKey, productName,
            new DistributedCacheEntryOptions
            {
                AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(5)
            });
    }
    return productName;
}

1. Create the cache table in SQL Server:

dotnet tool install --global dotnet-sql-cache
dotnet sql-cache create "Server=.;Database=YourDb;Trusted_Connection=True;" dbo DistributedCache

2. Update connection string in appsettings.json:

"ConnectionStrings": {
  "DefaultConnection": "Server=.;Database=YourDb;Trusted_Connection=True;"
}

3. Run:

dotnet run

• IDistributedCache is an abstraction — swapping SQL Server for Redis requires zero application code changes, only DI registration
• SQL Server distributed cache is a practical choice in enterprise environments with strict infrastructure constraints
• Cache-aside pattern keeps the service layer clean and testable

Part of my 180-day public learning journey — building real .NET projects in public.