A Serilog sink that writes events to Microsoft SQL Server. While a NoSql store allows for more flexibility to store the different kinds of properties, it sometimes is easier to use an already existing MS SQL server. This sink will write the logevent data to a table and can optionally also store the properties inside an Xml column so they can be queried.

Package - Serilog.Sinks.MSSqlServer | Platforms - .NET Framework 4.5 and .NET Standard 2.0

At minimum a connection string and table name are required.

To use a connection string from the connectionStrings section of your application config, specify its name as the value of the connection string.

Older .NET Framework applications can use the ConfigurationManager API shown below. Newer .NET Framework applications (using a Framework version that is .NET Standard compliant) should use the Microsoft.Extensions.Configuration version in the next section.

var connectionString = @"Server=...";  // or the name of a connection string in the app config
var tableName = "Logs";
var columnOptions = new ColumnOptions();  // optional

var log = new LoggerConfiguration()
    .WriteTo.MSSqlServer(connectionString, tableName, columnOptions: columnOptions)
    .CreateLogger();

The application configuration parameter is optional for .NET Standard libraries or .NET Core applications.

var appSettings = new ConfigurationBuilder()
    .SetBasePath(Directory.GetCurrentDirectory())
    .AddJsonFile("appsettings.json")
    .Build(); // more likely you will inject an IConfiguration reference

var connectionString = @"Server=...";  // or the name of a connection string in the app config
var tableName = "Logs";
var columnOptions = new ColumnOptions();  // optional

var log = new LoggerConfiguration()
    .WriteTo.MSSqlServer(connectionString, tableName, appConfiguration: appSettings, columnOptions: columnOptions)
    .CreateLogger();

.NET Framework libraries or applications can call ReadFrom.AppSettings() to configure Serilog using the Serilog.Settings.AppSettings package. This will apply configuration parameters from the app.config or web.config file:

<add key="serilog:using:MSSqlServer" value="Serilog.Sinks.MSSqlServer" />
<add key="serilog:write-to:MSSqlServer.connectionString" value="Server=..."/>
<add key="serilog:write-to:MSSqlServer.tableName" value="Logs"/>
<add key="serilog:write-to:MSSqlServer.autoCreateSqlTable" value="true"/>

.NET Standard libraries and .NET Core applications can call ReadFrom.Configuration(IConfiguration) to configure Serilog using the Serilog.Settings.Configuration package (version 3.0.0-dev-00111 or newer). This will apply configuration parameters from the application configuration (not only appsettings.json as shown here, but any other valid IConfiguration source):

{
  "Serilog": {
    "Using":  ["Serilog.Sinks.MSSqlServer"],
    "MinimumLevel": "Debug",
    "WriteTo": [
      { "Name": "MSSqlServer", 
        "Args": { 
            "connectionString": "Server...",
            "tableName": "Logs"
        } 
      }
    ]
  }
}

You'll need to create a table like this in your database:

CREATE TABLE [Logs] (

   [Id] int IDENTITY(1,1) NOT NULL,
   [Message] nvarchar(max) NULL,
   [MessageTemplate] nvarchar(max) NULL,
   [Level] nvarchar(128) NULL,
   [TimeStamp] datetimeoffset(7) NOT NULL,  -- use datetime for SQL Server pre-2008
   [Exception] nvarchar(max) NULL,
   [Properties] xml NULL,
   [LogEvent] nvarchar(max) NULL

   CONSTRAINT [PK_Logs] 
     PRIMARY KEY CLUSTERED ([Id] ASC) 
	 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
	       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
     ON [PRIMARY]

) ON [PRIMARY];

Remember to grant the necessary permissions for the sink to be able to write to the log table.

If you don't plan on using one or more columns, you can specify which columns to include in the columnOptions.Store parameter (see below).

The Level column should be defined as a TinyInt if the columnOptions.Level.StoreAsEnum is set to true.

If you set the autoCreateSqlTable option to true, the sink will create a table for you in the database specified in the connection string. Make sure that the user associated with this connection string has enough rights to make schema changes.

The "standard columns" used by this sink (apart from obvious required columns like Id) are described by the StandardColumn enumeration and controlled through code by the columnOptions.Store collection.

By default (and consistent with the SQL command to create a table, above) these columns are included:

• StandardColumn.Message
• StandardColumn.MessageTemplate
• StandardColumn.Level
• StandardColumn.TimeStamp
• StandardColumn.Exception
• StandardColumn.Properties

You can change this list, as long as the table definition is consistent:

// Don't include the Properties XML column.
columnOptions.Store.Remove(StandardColumn.Properties);

// Do include the log event data as JSON.
columnOptions.Store.Add(StandardColumn.LogEvent);

You can also store your own log event properties as additional columns; see below.

By default any log event properties you include in your log statements will be saved to the Properties column (and/or LogEvent column, per columnOption.Store). But they can also be stored in their own columns via the AdditionalDataColumns setting.

var columnOptions = new ColumnOptions
{
    AdditionalDataColumns = new Collection<DataColumn>
    {
        new DataColumn {DataType = typeof (string), ColumnName = "User"},
        new DataColumn {DataType = typeof (string), ColumnName = "Other"},
    }
};

var log = new LoggerConfiguration()
    .WriteTo.MSSqlServer(@"Server=.\SQLEXPRESS;Database=LogEvents;Trusted_Connection=True;", "Logs", columnOptions: columnOptions)
    .CreateLogger();

The log event properties User and Other will now be placed in the corresponding column upon logging. The property name must match a column name in your table. Be sure to include them in the table definition.

By default, additional properties will still be included in the data saved to the XML Properties or JSON LogEvent column (assuming one or both are enabled via the columnOptions.Store parameter). This is consistent with the idea behind structured logging, and makes it easier to convert the log data to another (e.g. NoSQL) storage platform later if desired.

However, if necessary, then the properties being saved in their own columns can be excluded from the data. Use the columnOptions.Properties.ExcludeAdditionalProperties parameter in the sink configuration to exclude the redundant properties from the XML.

Custom columns can be defined with the name and data type of the column in SQL Server. Columns specified must match database table exactly. DataType is case sensitive, based on SQL type (excluding precision/length). This section will be processed automatically if it exists in the application's web.config or app.config file.

  <configSections>
    <section name="MSSqlServerSettingsSection"
             type="Serilog.Configuration.MSSqlServerConfigurationSection, Serilog.Sinks.MSSqlServer"/>
  </configSections>
  <MSSqlServerSettingsSection>
    <Columns>
      <add ColumnName="EventType" DataType="int"/>
      <add ColumnName="Release" DataType="varchar"/>
    </Columns>
  </MSSqlServerSettingsSection>      

For projects using the Serilog Configuration package, most properties of the ColumnOptions object are configurable. (The only property not currently supported is the filter-predicate columnOptions.Properties.PropertyFilter).

The equivalent of adding custom columns as shown in the .NET Framework example above looks like this:

{
  "Serilog": {
    "Using":  ["Serilog.Sinks.MSSqlServer"],
    "MinimumLevel": "Debug",
    "WriteTo": [
      { "Name": "MSSqlServer", 
        "Args": { 
            "connectionString": "Server...",
            "tableName": "Logs",
            "columnOptionsSection": {
              "customColumns": [
                { "ColumnName": "EventType", "DataType": "int", "AllowNull": false },
                { "ColumnName": "Release", "DataType": "varchar", "DataLength": 32 }
              ]
            }
        } 
      }
    ]
  }
}

As the name suggests, columnOptionSection is an entire configuration section in its own right. All possible entries and some sample values are shown below. All properties and subsections are optional.

"columnOptionsSection": {
    "addStandardColumns": [ "LogEvent" ],
    "removeStandardColumns": [ "MessageTemplate", "Properties" ],
    "customColumns": [
        { "ColumnName": "EventType", "DataType": "int", "AllowNull": false },
        { "ColumnName": "Release", "DataType": "varchar", "DataLength": 32 }
    ],
    "disableTriggers": true,
    "id": { "columnName": "Id" },
    "level": { "columnName": "Level", "storeAsEnum": false },
    "properties": { 
        "columnName": "Properties",
        "excludeAdditionalProperties": true, 
        "dictionaryElementName": "dict",
        "itemElementName": "item",
        "omitDictionaryContainerElement": false, 
        "omitSequenceContainerElement": false, 
        "omitStructureContainerElement": false, 
        "omitElementIfEmpty": true, 
        "propertyElementName": "prop",
        "rootElementName": "root",
        "sequenceElementName": "seq",
        "structureElementName": "struct",
        "usePropertyKeyAsElementName": false
    },
    "timeStamp": { "columnName": "Timestamp", "convertToUtc": true },
    "logEvent": { "columnName": "LogEvent", "excludeAdditionalProperties": true },
    "message": { "columnName": "Message" },
    "exception": { "columnName": "Exception" },
    "messageTemplate": { "columnName": "MessageTemplate" }
}

The log event JSON can be stored to the LogEvent column. This can be enabled by adding the LogEvent column to the columnOptions.Store collection. Use the columnOptions.LogEvent.ExcludeAdditionalProperties parameter to exclude redundant properties from the JSON. This is analogue to excluding redundant items from XML in the Properties column.

To take advantage of SQL Server's XML support, the default storage of the log event properties is in the Properties XML column.

The serialization of the properties can be controlled by setting values in the in the columnOptions.Properties parameter.

Names of elements can be controlled by the RootElementName, PropertyElementName, ItemElementName, DictionaryElementName, SequenceElementName, StructureElementName and UsePropertyKeyAsElementName options.

The UsePropertyKeyAsElementName option, if set to true, will use the property key as the element name instead of "property" for the name with the key as an attribute.

If OmitDictionaryContainerElement, OmitSequenceContainerElement or OmitStructureContainerElement are set then the "dictionary", "sequence" or "structure" container elements will be omitted and only child elements are included.

If OmitElementIfEmpty is set then if a property is empty, it will not be serialized.

Extracting and querying the properties data directly can be helpful when looking for specific log sequences.

Given the following XML property collection:

<properties>
  <property key="Action">GetUsers</property>
  <property key="Controller">UserController</property>
</properties>

The following query will extract the Action property and restrict the query based on the Controller property using SQL Servers built-in XQuery support.

SELECT 	[Message]
  , [TimeStamp]
  , [Exception]
  , [Properties].value('(//property[@key="Action"]/node())[1]', 'nvarchar(max)') as Action
FROM [Logs]
WHERE [Properties].value('(//property[@key="Controller"]/node())[1]', 'nvarchar(max)') = 'UserController'