Remigiusz ZalewskiRemigiusz Zalewski

.NET logging to database - Serilog

dotnet-logging-to-database-serilog
Sponsor this newsletter →

Introduction

A flat-file log is fine for tailing during development and painful for anything else - answering "how many failed logins came from this IP in the last hour" from a text file means grepping and hoping the format is consistent. Writing structured logs into a database table turns that into a SELECT. This video wires Serilog's MSSqlServer sink into a .NET app and covers the configuration that keeps it from becoming a performance problem under real load.

🎬 Watch the full video here:

Wiring the sink

builder.Host.UseSerilog((context, configuration) => configuration
    .WriteTo.MSSqlServer(
        connectionString: context.Configuration.GetConnectionString("Logging"),
        sinkOptions: new MSSqlServerSinkOptions { TableName = "Logs", AutoCreateSqlTable = true }));

AutoCreateSqlTable provisions the Logs table on first run with Serilog's standard columns (Message, Level, TimeStamp, Exception, Properties) - useful to get started, though a real production setup typically checks the generated schema in as an explicit migration instead of relying on auto-creation indefinitely.

Structured properties land in their own column

logger.LogWarning("Failed login attempt for {Email} from {IpAddress}", email, ipAddress);

The Properties column stores Email and IpAddress as structured XML/JSON rather than baking them into the flat Message text - which means a query like "every failed login from this IP" is a real WHERE clause against structured data, not a LIKE '%ipAddress%' string search against unstructured text.

A separate connection string, deliberately

Logging to the same database the application otherwise reads and writes to works, but a dedicated logging database (or at minimum a dedicated login with only INSERT rights on the Logs table) is worth the extra setup - it isolates a burst of logging activity from affecting application query performance, and it means the logging path can't accidentally be used to tamper with application data even if something went wrong with the connection.

The part that actually matters at scale: batching

Writing a Log.Information call as a synchronous individual INSERT on every call is the naive version and it doesn't hold up under real traffic - Serilog's MSSqlServer sink batches writes instead:

sinkOptions: new MSSqlServerSinkOptions
{
    TableName = "Logs",
    BatchPostingLimit = 50,
    BatchPeriod = TimeSpan.FromSeconds(5)
}

Logs are buffered in memory and flushed as a batch either every 50 entries or every 5 seconds, whichever comes first - one round trip for fifty log lines instead of fifty. This is the difference between logging being nearly free and logging becoming a meaningful chunk of the app's own database load under high traffic.

Common pitfalls

  • Writing one INSERT per log call with no batching, adding real database load under high-throughput logging.
  • Logging to the same database and connection pool the app depends on for its actual work, letting a logging spike compete with real queries.
  • Relying on AutoCreateSqlTable in production indefinitely instead of checking the schema into a real migration.
  • String-interpolating the log message instead of using message templates, losing the structured Properties column entirely.
  • No retention or archival policy on the Logs table, letting it grow indefinitely until it becomes its own performance problem.

Key Takeaways

  • Serilog's MSSqlServer sink writes structured log properties into their own queryable column, not just a flat message string.
  • Use message templates ({Email}, {IpAddress}), not string interpolation, to keep properties structured.
  • A dedicated logging database or restricted login isolates logging load from the app's primary database traffic.
  • BatchPostingLimit and BatchPeriod batch writes so logging doesn't become one database round trip per log call.
  • Plan a retention policy for the log table - it grows indefinitely otherwise.

Get the Full Source Code

The complete runnable solution - the Serilog MSSqlServer sink configuration with batching - is available to Patreon supporters. If you want structured logging into SQL Server ready to drop into your own app instead of rebuilding it from the walkthrough above, you can find it on Patreon.

Resources