Serilog dotnet core function app and sql sink

Question

I need to use dotnet5 with Azure Functions so followed the guidance to create a new solution: https://docs.microsoft.com/en-us/azure/azure-functions/dotnet-isolated-process-guide.

This worked great so next job was to add in serilog with sinks for console and sql server.

I have added nuget packages:

Serilog.AspNetCore v4.1.0
Serilog.Sinks.MSSqlServer v5.6.0

Here is the Program.Main:

static void Main(string[] args)
{
    string EventName = "Main";
    var columnOptions = new ColumnOptions
    {
        AdditionalColumns = new Collection<SqlColumn>
        {
            new SqlColumn
                {ColumnName = "EventName", DataType = SqlDbType.NVarChar, DataLength = 32, NonClusteredIndex = true}
        }
    };

    Log.Logger = new LoggerConfiguration()
                .MinimumLevel.Override("Microsoft.Azure", LogEventLevel.Warning)
                .Enrich.FromLogContext()
                .WriteTo.Console()
                .WriteTo.MSSqlServer(
                    logEventFormatter: new RenderedCompactJsonFormatter(),
                    restrictedToMinimumLevel: LogEventLevel.Debug,
                    connectionString: "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=SmsRouter",
                    sinkOptions: new MSSqlServerSinkOptions
                    {
                        TableName = "LogEvents",
                        AutoCreateSqlTable = true,
                    },
                    columnOptions: columnOptions)
                .CreateLogger();

    try
    {
        Log.Information("Starting up {EventName}", EventName);
        var host = new HostBuilder()
        .UseSerilog()
        .ConfigureFunctionsWorkerDefaults()
        .ConfigureServices(s =>
        {
            //services configured here
        })
        .Build();

        host.Run();
    }
    catch (Exception ex)
    {
        Log.Fatal(ex, "Application start-up failed");
    }
    finally
    {
        Log.CloseAndFlush();
    }
}

You can see the line Log.Information("Starting up {EventName}", EventName); This works and is logged to both console and Sql Server :)

After the App is started it will sit and wait for a Http request - as shown below:

[Function("SendSimpleSms")]
public async Task<QueueAndHttpOutputType> RunSimple([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post")] HttpRequestData req,
    FunctionContext executionContext)
{
    string EventName = "SendSimpleSms";
    try
    {
        Log.Information("Starting: {EventName}", EventName);

My problem is, this log request "Starting: SendSimpleSms" is logged to the console window but not to Sql Server.

Anyone see what I have wrong please?

Enable Serilog's self-logging to see what's wrong with eg Serilog.Debugging.SelfLog.Enable(msg => System.Diagnostics.Debug.WriteLine(msg)); or Serilog.Debugging.SelfLog.Enable(msg => Console.WriteLine(msg)); — Panagiotis Kanavos
BTW there's no LocalDB in Azure, so I assume the code you posted is only for testing? In production you'd have to use a connection string pointing to a real database, either Azure SQL or SQL Server on a VM. BUT both may end up being more expensive per MB than Log Analytics or App Insights — Panagiotis Kanavos
Yes localdb for testing. I was using application insights but I find the 5 minute delay frustrating. Also, mixing up my custom log entries with those from the Az Func host doesn't feel right. — Rob Bowman
The SQL Server sink also buffers log messages. Logging and monitoring are different beasts, with different requirements. Logging has large messages and latency, monitoring the opposite. If you want low latency you should use a Prometheus sink or OpenTelemetry, which was built precisely for cloud environments. ASP.NET Core fully supports OpenTelemetry now, emitting Activity/Span IDs that allow you to track all calls made for a specific request — Panagiotis Kanavos

Rob Bowman Rob Bowman · Accepted Answer · 2021-06-01T07:35:00

Thanks to Panagiotis Kanavos for making me aware of the Serilog self-logging.

I added the following into program.main, after the LoggerConfiguration:

Serilog.Debugging.SelfLog.Enable(Console.Error);

This then made me aware that the sql sink was unable to log because the length of a custom property exceeded that of its column

Serilog dotnet core function app and sql sink

1 Answers