Serilog SQL Sink: Efficient Logging to SQL Databases

Welcome back to our ongoing series on logging with Serilog. In this article, we will dive into another remarkable sink provided by Serilog – SQL Sinks.

As we continue to explore various logging options, the Serilog SQL Sinks will take center stage, offering a robust solution for efficient log management.

Why logging to SQL?

Many factors can play a vital role to consider SQL as a logging destination.

Enterprise Applications

Serilog SQL Sinks are particularly beneficial for large-scale enterprise applications that generate a high volume of log data. By utilizing SQL Sinks, organizations can centralize log storage in SQL databases, making it easier to manage and analyze logs from multiple components or services within the application.

This centralized approach provides a holistic view of the system’s behavior, facilitates troubleshooting, and enables better performance analysis.

Compliance and Audit Trails

Industries with strict compliance requirements, such as finance, healthcare, or government sectors, can leverage Serilog SQL Sinks to meet auditing and compliance standards.

By storing log data in SQL databases, organizations can maintain a detailed audit trail of system activities, user actions, and security-related events.

The structured nature of SQL databases allows for efficient querying and reporting, facilitating compliance audits and investigations.

Real-time Monitoring and Alerting

Serilog SQL Sinks can be integrated with real-time monitoring systems or log management platforms that support SQL database inputs.

This enables organizations to set up real-time alerts and notifications based on specific log events or conditions.

By leveraging SQL queries and triggers, organizations can define alerting rules and receive immediate notifications for critical events, enabling prompt response and issue resolution.

Configure SQL Sinks in .Net Core

We have already seen the example of Email Sinks and Seq Server Sink in our earlier tutorials. So let’s continue using the same project and extend the functionality to support the additional sink for the SQL server.

Github Link for Serilog Demo Project

Or in case you want to set up a fresh new project. You can just create the .Net Core application and install the following packages.

Nuget Packages for SQL Sinks

To set up a SQL sink in Serilog, you need to install the Serilog.Sinks.MSSqlServer NuGet package. This package provides the necessary components and functionality to log events into a SQL database.

If you are setting up the new project, make sure to install the basic Nuget packages required for Serilog and loading configurations from appsettings.json.

Once the installation is done, We can update the configuration for the SQL sink.

Configuration for SQL Sink in Serilog

We can start logging into the database with minimal configurations in place. And, then we go step to the advanced level like defining the tables, columns data retention policies.

{
  "Serilog": {
    "WriteTo": [
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;Encrypt=false",
          "tableName": "Logs",
          "autoCreateSqlTable": true,
          "batchPostingLimit": 100,
          "period": "0.00:00:10"
        }
      }
    ]
  }
}

It’s important to handle sensitive information like the SQL connection string securely when working with source code. To protect the confidentiality of the connection string, make sure to remove it before checking the code into source control.

zero-tolerance policy on security

• "connectionString": The connection string for the target SQL database.
• "tableName": The name of the table where the log entries will be stored.
• "autoCreateSqlTable" (optional): Specifies whether the table should be automatically created if it doesn’t exist.
• "batchPostingLimit" (optional): The number of log events to include in each batch when writing to the SQL database.
• "period" (optional): The time interval to wait before flushing the log events to the SQL database.

Please update the above configuration to match your installed SQL server configuration. Once updated we can run the application and check the database to validate the logs.

As we see above a new table has been created called Logs which we specified in the configuration section of the Serilog SQL sink.

And, When we query the table. it gave us the data with the following columns in it.

By default, it has created a few columns which are enough to log the basic information along with the timestamp and log level.

However, We have the option to customize various aspects of their behavior to suit your specific requirements.

{
  "Serilog": {
    "WriteTo": [
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;Encrypt=false",
          "tableName": "Logs",
          "autoCreateSqlTable": true,
          "batchPostingLimit": 100,
          "period": "0.00:00:10",
"columnOptionsSection": {
            "addStandardColumns": [ "LogEvent" ],
            "removeStandardColumns": [ "Properties" ],
            "customColumns": [
              { "ColumnName": "Username", "DataType": "nvarchar", "DataLength": 100, "AllowNull": true },
              { "ColumnName": "MachineName", "DataType": "nvarchar", "DataLength": 50, "AllowNull": true }
            ]
          },
          "useBulkInsert": true,
          "batchSize": 1000,
          "retainedDataDuration": "365.00:00:00"
        }
      }
    ]
  }
}

Using the addStandardColumns property, You can adjust the list of standard columns based on your specific requirements. Additionally, the "removeStandardColumns" option allows you to exclude certain standard columns if needed.

"useBulkInsert" and "batchSize": Enable and configure bulk insert for improved performance. Setting "useBulkInsert" to true enables bulk insert, and "batchSize" specifies the number of log events to include in each bulk insert operation.

"retainedDataDuration": Specifies the duration for which log data should be retained in the SQL database. In this example, it is set to 365 days.

When I ran the application again, this is what I found in the table.

• A new column has been added called LogEvent
• The properties Column has been removed
• Two custom columns called UserName and MachineName have been added to the table

However, we see that values for UserName and MachineName are not being populated.

To populate values in the Username and MachineName columns, you can use Serilog’s log event enrichers. Enrichers allow you to add additional properties to each log event dynamically.

Update the Program.cs file to include the piece of code shown below. Also, We need to install the additional Nuget package called Serilog.Enrichers.Environment to capture the MachineName.

using DK.Serilog.Demo.Services.Emp;
using Serilog;

IConfiguration configuration = new ConfigurationBuilder()
    .AddJsonFile("appsettings.json")
    .Build();

var logger= new LoggerConfiguration()
    .ReadFrom.Configuration(configuration)
    .Enrich.FromLogContext()
    .Enrich.WithMachineName()
    .Enrich.WithProperty("Username", GetCurrentUserName())
    .CreateLogger();

//This is just for demo purpose. Actual UserName must come from the CurrentContext or some other logic
static string GetCurrentUserName()
{
    return "Deependra";
}
var builder = WebApplication.CreateBuilder(args);
Serilog.Debugging.SelfLog.Enable(Console.WriteLine);
// Add services to the container.
builder.Services.AddRazorPages();
builder.Services.AddScoped<IEmployeeManager, EmployeeManager>();
builder.Logging.ClearProviders();
builder.Logging.AddSerilog(logger);
var app = builder.Build();
// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Error");
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();
}
app.UseHttpsRedirection();
app.UseStaticFiles();
app.UseRouting();
app.UseAuthorization();
app.MapRazorPages();
try
{
    Log.Information("Starting up...");
    app.Run();
}
catch (Exception ex)
{
    Log.Fatal(ex, "Application start-up failed!");
}

After the installation and making these changes in the program.cs file. Lets run the application again and check the data in the database.

Conclusion

Throughout this article, we explored various aspects of the SQL sink, including its setup, configuration, and advanced options. We discussed how to configure the connection string, define table mappings, enable batch writing and bulk insert for improved performance, set data retention policies, and enrich log events with additional information.

By utilizing the SQL sink effectively, you can centralize your log data in an SQL database, enabling easier log analysis, monitoring, and troubleshooting. The ability to customize column mappings, optimize performance, and include contextual information enhances the value of your log data.

---

Frequently Asked Questions