Serilog.Sinks.MSSqlServer 7.0.2-dev-00102

Serilog.Sinks.MSSqlServer

A Serilog sink that writes events to Microsoft SQL Server. This sink will write the log event data to a table and can optionally also store the properties inside an XML or JSON column so they can be queried. Important properties can also be written to their own separate columns.

Package - Serilog.Sinks.MSSqlServer | Minimum Platforms - .NET Framework 4.6.2, .NET 6.0, .NET Standard 2.0

Topics

• Quick Start
• Sink Configuration
• Audit Sink Configuration
• Table Definition
• MSSqlServerSinkOptions Object
• ColumnOptions Object
• SqlColumn Objects
• Standard Columns
• Custom Property Columns
• External Configuration Syntax
• Troubleshooting
• Querying Property Data
• Breaking Changes
• Deprecated Features

Quick Start

The most basic minimalistic sink initialization is done like this.

Log.Logger = new LoggerConfiguration()
    .WriteTo
    .MSSqlServer(
        connectionString: "Server=localhost;Database=LogDb;Integrated Security=SSPI;",
        sinkOptions: new MSSqlServerSinkOptions { TableName = "LogEvents" })
    .CreateLogger();

Sample Programs

There is a set of small and simple sample programs provided with the source code in the sample directory. They demonstrate different ways to initialize the sink by code and configuration for different target frameworks.

Sink Configuration

The sink can be configured completely through code, by using configuration files (or other types of configuration providers), a combination of both, or by using the various Serilog configuration packages. There are two configuration considerations: configuring the sink itself, and configuring the table used by the sink. The sink is configured with a typical Serilog WriteTo configuration method (or AuditTo, or similar variations). Settings for the sink are configured using a MSSqlServerSinkOptions object passed to the configuration method. The table is configured with an optional ColumnOptions object passed to the configuration method.

All sink configuration methods accept the following arguments, though not necessarily in this order. Use of named arguments is strongly recommended. Some platform targets have additional arguments.

• connectionString
• sinkOptions
• columnOptions
• restrictedToMinimumLevel
• formatProvider
• logEventFormatter

Basic Arguments

At minimum, connectionString and MSSqlServerSinkOptions.TableName are required. If you are using an external configuration source such as an XML file or JSON file, you can use a named connection string instead of providing the full "raw" connection string.

All properties in the MSSqlServerSinkOptions object are discussed in the MSSqlServerSinkOptions Object topic.

Table configuration with the optional ColumnOptions object is a lengthy subject discussed in the ColumnOptions Object topic and other related topics.

Like other sinks, restrictedToMinimumLevel controls the LogEventLevel messages that are processed by this sink. This parameter is ignored if the property LevelSwitch in the sink options is set.

This is a "periodic batching sink." The sink will queue a certain number of log events before they're actually written to SQL Server as a bulk insert operation. There is also a timeout period so that the batch is always written even if it has not been filled. By default, the batch size is 50 rows and the timeout is 5 seconds. You can change these through by setting the MSSqlServerSinkOptions.BatchPostingLimit and MSSqlServerSinkOptions.BatchPeriod arguments.

Consider increasing the batch size in high-volume logging environments. In one test of a loop writing a single log entry, the default batch size averaged about 14,000 rows per second. Increasing the batch size to 1000 rows increased average write speed to nearly 43,000 rows per second. However, you should also consider the risk-factor. If the client or server crashes, or if the connection goes down, you may lose an entire batch of log entries. You can mitigate this by reducing the timeout. Run performance tests to find the optimal batch size for your production log table definition and log event content, network setup, and server configuration.

Refer to the Serilog Wiki's explanation of Format Providers for details about the formatProvider arguments.

The parameter logEventFormatter can be used to specify a custom renderer implementing ITextFormatter which will be used to generate the contents of the LogEventcolumn. If the parameter is omitted or set to null, the default internal JSON formatter will be used. For more information about custom text formatters refer to the Serilog documentation Custom text formatters.

Platform-Specific Arguments

These additional arguments are accepted when the sink is configured from a library or application that supports the .NET Standard-style Microsoft.Extensions.Configuration packages. They are optional.

• appConfiguration
• sinkOptionsSection
• columnOptionsSection

The full configuration root provided to the appConfiguration argument is only required if you are using a named connection string. The sink needs access to the entire configuration object so that it can locate and read the ConnectionStrings section.

If you define the sink options or the log event table through external configuration, you must provide a reference to the sinkOptionsSection and/or columnOptionsSection via the argument by the same name.

External Configuration and Framework Targets

Because of the way external configuration has been implemented in various .NET frameworks, you should be aware of how your target framework impacts which external configuration options are available. System.Configuration refers to the use of XML-based app.config or web.config files, and Microsoft.Extensions.Configuration (M.E.C) collectively refers to all of the extensions packages that were created as part of .NET Standard and the various compliant frameworks. M.E.C is commonly referred to as "JSON configuration" although the packages support many other configuration sources including environment variables, command lines, Azure Key Vault, XML, and more.

Your Framework	TFM	Project Types	External Configuration
.NET Framework 4.6.2+	net462	app or library	System.Configuration
.NET Framework 4.6.2+	net462	app or library	Microsoft.Extensions.Configuration
.NET Standard 2.0	netstandard2.0	library only	Microsoft.Extensions.Configuration
.NET 6.0+	net6.0	app or library	System.Configuration
.NET 6.0+	net6.0	app or library	Microsoft.Extensions.Configuration

Although it's possible to use both XML and M.E.C configuration with certain frameworks, this is not supported, unintended consequences are possible, and a warning will be emitted to SelfLog. If you actually require multiple configuration sources, the M.E.C builder-pattern is designed to support this, and your syntax will be consistent across configuration sources.

Code-Only (any .NET target)

All sink features are configurable from code. Here is a typical example that works the same way for any .NET target. This example configures the sink itself as well as table features.

var logDB = @"Server=...";
var sinkOpts = new MSSqlServerSinkOptions();
sinkOpts.TableName = "Logs";
var columnOpts = new ColumnOptions();
columnOpts.Store.Remove(StandardColumn.Properties);
columnOpts.Store.Add(StandardColumn.LogEvent);
columnOpts.LogEvent.DataLength = 2048;
columnOpts.PrimaryKey = columnOpts.TimeStamp;
columnOpts.TimeStamp.NonClusteredIndex = true;

var log = new LoggerConfiguration()
    .WriteTo.MSSqlServer(
        connectionString: logDB,
        sinkOptions: sinkOpts,
        columnOptions: columnOpts
    ).CreateLogger();

Code + Microsoft.Extensions.Configuration

Projects can build (or inject) a configuration object using Microsoft.Extensions.Configuration and pass it to the sink's configuration method. If provided, the settings of MSSqlServerSinkOptions and ColumnOptions objects created in code are treated as a baseline which is then updated from the external configuration data. See the External Configuration Syntax topic for details.

var appSettings = new ConfigurationBuilder()
    .SetBasePath(Directory.GetCurrentDirectory())
    .AddJsonFile("appsettings.json")
    .Build();

var logDB = @"Server=...";
var sinkOpts = new MSSqlServerSinkOptions { TableName = "Logs" };
var columnOpts = new ColumnOptions();

var log = new LoggerConfiguration()
    .WriteTo.MSSqlServer(
        connectionString: logDB,
        sinkOptions: sinkOpts,
        columnOptions: columnOpts,
        appConfiguration: appSettings
    ).CreateLogger();

Code + System.Configuration

Projects can load MSSqlServerSinkOptions and ColumnOptions objects from an XML configuration file such as app.config or web.config. The sink configuration method automatically checks ConfigurationManager, so there is no code to show, and no additional packages are required. See the External Configuration Syntax topic for details.

External using Serilog.Settings.Configuration

Requires configuration package version 3.0.0 or newer.

.NET Standard projects can call ReadFrom.Configuration() to configure Serilog using the Serilog.Settings.Configuration package. This will apply configuration arguments from all application configuration sources (not only appsettings.json as shown here, but any other valid IConfiguration source). This package can configure the sink itself with MSSqlServerSinkOptions as well as ColumnOptions table features. See the External Configuration Syntax topic for details.

External using Serilog.Settings.AppSettings

Projects can configure the sink from XML configuration by calling ReadFrom.AppSettings() using the Serilog.Settings.AppSettings package. This will apply configuration arguments from the project's app.config or web.config file. This is independent of configuring MSSqlServerSinkOptions or ColumnOptions from external XML files. See the External Configuration Syntax topic for details.

Audit Sink Configuration

A Serilog audit sink writes log events which are of such importance that they must succeed, and that verification of a successful write is more important than write performance. Unlike the regular sink, an audit sink does not fail silently -- it can throw exceptions. You should wrap audit logging output in a try/catch block. The usual example is bank account withdrawal events -- a bank would certainly not want to allow a failure to record those transactions to fail silently.

The constructor accepts most of the same arguments, and like other Serilog audit sinks, you configure one by using AuditTo instead of WriteTo.

• connectionString
• sinkOptions
• columnOptions
• formatProvider
• logEventFormatter

The restrictedToMinimumLevel parameter is not available because all events written to an audit sink are required to succeed.

The MSSqlServerSinkOptions.BatchPostingLimit and MSSqlServerSinkOptions.BatchPeriod parameters are ignored because the audit sink writes log events immediately.

For M.E.C-compatible projects, appConfiguration, sinkOptionsSection and columnOptionsSection arguments are also provided, just as they are with the non-audit configuration extensions.

Table Definition

If you don't use the auto-table-creation feature, you'll need to create a log event table in your database. In particular, give careful consideration to whether you need the Id column (options and performance impacts are discussed in the Standard Columns topic). The table definition shown below reflects the default configuration using auto-table-creation without changing any sink options. Many other variations are possible. Refer to the ColumnOptions Object topic to understand how the various configuration features relate to the table definition.

IMPORTANT: If you create your log event table ahead of time, the sink configuration must exactly match that table, or errors are likely to occur.

CREATE TABLE [Logs] (

   [Id] int IDENTITY(1,1) NOT NULL,
   [Message] nvarchar(max) NULL,
   [MessageTemplate] nvarchar(max) NULL,
   [Level] nvarchar(max) NULL,
   [TimeStamp] datetime NULL,
   [Exception] nvarchar(max) NULL,
   [Properties] nvarchar(max) NULL

   CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED ([Id] ASC)
);

Permissions

At a minimum, writing log entries requires SELECT and INSERT permissions for the log table. (SELECT is required because the sink's batching behavior uses bulk inserts which reads the schema before the write operations begin).

SQL permissions are a very complex subject. Here is an example of one possible solution (valid for SQL 2012 or later):

CREATE ROLE [SerilogAutoCreate];
GRANT SELECT ON sys.tables TO [SerilogAutoCreate];
GRANT SELECT ON sys.schemas TO [SerilogAutoCreate];
GRANT ALTER ON SCHEMA::[dbo] TO [SerilogAutoCreate]
GRANT CREATE TABLE ON DATABASE::[SerilogTest] TO [SerilogAutoCreate];

CREATE ROLE [SerilogWriter];
GRANT SELECT TO [SerilogWriter];
GRANT INSERT TO [SerilogWriter];

CREATE LOGIN [Serilog] WITH PASSWORD = 'password';

CREATE USER [Serilog] FOR LOGIN [Serilog] WITH DEFAULT_SCHEMA = dbo;
GRANT CONNECT TO [Serilog];

ALTER ROLE [SerilogAutoCreate] ADD MEMBER [Serilog];
ALTER ROLE [SerilogWriter] ADD MEMBER [Serilog];

This creates a SQL login named Serilog, a database user named Serilog, and assigned to that user are the roles SerilogAutoCreate and SerilogWriter. As the name suggests, the SerilogAutoCreate role is not needed if you create the database ahead of time, which is the recommended course of action if you're concerned about security at this level.

Ideally the SerilogWriter role would be restricted to the log table only, and that table has to already exist to use table-specific GRANT statements, so that's another reason that you probably don't want to use auto-create if you're concerned about log security. Table-level restrictions would look like this (assuming you name your log table SecuredLog, of course):

GRANT SELECT ON [dbo].[SecuredLog] TO [SerilogWriter];
GRANT INSERT ON [dbo].[SecuredLog] TO [SerilogWriter];

There are many possible variations. For example, you could also create a logging-specific schema and restrict access that way.

MSSqlServerSinkOptions Object

Basic settings of the sink are configured using the properties in a MSSqlServerSinkOptions object:

• TableName
• SchemaName
• AutoCreateSqlDatabase
• AutoCreateSqlTable
• EnlistInTransaction
• BatchPostingLimit
• BatchPeriod
• EagerlyEmitFirstEvent
• LevelSwitch
• UseSqlBulkCopy

TableName

A required parameter specifying the name of the table used to write the log events.

SchemaName

An optional parameter specifiying the database schema where the log events table is located. It defaults to "dbo".

AutoCreateSqlDatabase

A flag specifiying if the log events database should be created if it does not exist. It defaults to false. If this is set to true the property AutoCreateSqlTable should also be set to true.

AutoCreateSqlTable

A flag specifiying if the log events table should be created if it does not exist. It defaults to false.

EnlistInTransaction

A flag to make logging SQL commands take part in ambient transactions. It defaults to false. Logging operations could be affected from surrounding TransactionScope's in the code, leading to log data being removed on a transaction rollback. This is by default prevented by the sink adding Enlist=false to the ConnectionString that is passed. This option can be used to change this behavior so that Enlist=true is added instead (which is the default for SQL connections) and logging commands might be part of transactions. Only change this option to true if you have a good reason and really know what you are doing!

BatchPostingLimit

Specifies a maximum number of log events that the non-audit sink writes per batch. The default is 50. This setting is not used by the audit sink as it writes each event immediately and not in a batched manner.

BatchPeriod

Specifies the interval in which the non-audit sink writes a batch of log events to the database. It defaults to 5 seconds. This setting is not used by the audit sink as it writes each event immediately and not in a batched manner.

EagerlyEmitFirstEvent

A Flag to eagerly write a batch to the database containing the first received event regardless of BatchPostingLimit or BatchPeriod. It defaults to true. This setting is not used by the audit sink as it writes each event immediately and not in a batched manner.

LevelSwitch

A switch allowing the pass-through minimum level to be changed at runtime. If this is set, the parameter restrictedToMinimumLevel in the sink configuration method is ignored.

UseSqlBulkCopy

A flag to use SqlBulkCopy instead of individual INSERT statements when writing log events. The default is true. This setting is not used by the audit sink as it always uses INSERT statements to write events.

ColumnOptions Object

Features of the log table are defined by changing properties on a ColumnOptions object:

• Store
• PrimaryKey
• ClusteredColumnstoreIndex
• DisableTriggers
• AdditionalColumns

Store

This is a list of columns that have special handling when a log event is being written. These are explained in the Standard Columns topic. Only the Standard Columns which are in the log table should be present in the Store collection. This is a List<> of StandardColumn enumeration members, so you can simply Add or Remove columns to change the list. The order of appearance does not matter. The ColumnOptions object also has a property for each individual Standard Column providing access to column-specific settings. The properties match the Standard Column names (Id, Message, etc.) These are discussed in the documentation for each Standard Column.

PrimaryKey

By default, the Id Standard Column is the table's primary key. You can set this property to any other column (either Standard Columns or custom columns you define; see the Custom Property Columns topic). SQL Server requires primary key indexes to always be NOT NULL so the column-level AllowNull property will be overridden if set to true.

The primary key is optional; set this property to null to create a heap table with no primary key.

NOTE: If you do not set the NonClusteredIndex property on the primary key column to true, the primary key constraint will be created as a clustered index. Clustered indexing is the default for backwards-compatibility reasons, but generally speaking this is not the best option for logging purposes (applications rarely emit fully unique properties, and using the unique auto-incrementing Id column as a primary key isn't particularly useful for query purposes).

ClusteredColumnstoreIndex

Setting this to true changes the table to the clustered columnstore index (CCI) format. A complete discussion of CCI is beyond the scope of this documentation, but generally it uses high compression to dramatically improve search speeds. It is not compatible with a table primary key or a non-columnstore clustered index, and supporting (max) length character-data columns requires SQL 2017 or newer.

DisableTriggers

Disabling triggers can significantly improve batch-write performance. Only applies when SqlBulkCopy is used.

AdditionalColumns

This is a Collection<> of SqlColumn objects that you create to define custom columns in the log event table. Refer to the Custom Property Columns topic for more information.

SqlColumn Objects

Each Standard Column in the ColumnOptions.Store list and any custom columns you add to the AdditionalColumns collection are SqlColumn objects with the following properties:

• ColumnName
• PropertyName
• DataType
• AllowNull
• DataLength
• NonClusteredIndex

ColumnName

Any valid SQL column name can be used. Standard Columns have default names assigned but these can be changed without affecting their special handling.

PropertyName

The optional name of a Serilog property to use as the value for a custom column. If not provided, the property used is the one that has the same name as the specified ColumnName. It applies only to custom columns defined in AdditionalColumns and is ignored for standard columns.

PropertyName can contain a simple property name like SomeProperty but it can also be used to hierachically reference sub-properties with expressions like SomeProperty.SomeSubProperty.SomeThirdLevelProperty. This can be used to easily bind additional columns to specific sub-properties following the paradigm of structured logging. Please be aware that collections are not supported. This means expressions like SomeProperty.SomeArray[2] will not work.

DataType

This property can be set to nearly any value in the System.Data.SqlDbType enumeration. Unlike previous versions of this sink, SQL column types are fully supported end-to-end, including auto-table-creation. Earlier limitations imposed by the use of the .NET DataColumn object no longer apply. Most of the Standard Columns only support a limited subset of the SQL column types (and often just one type). Some of the special-case SQL column types are excluded such as timestamp and udt, and deprecated types like text and image are excluded. These are the supported SQL column data types:

• bigint
• bit
• char
• date
• datetime
• datetime2
• datetimeoffset
• decimal
• float
• int
• money
• nchar
• nvarchar
• real
• smalldatetime
• smallint
• smallmoney
• time
• tinyint
• uniqueidentifier
• varchar
• xml

Numeric types use the default precision and scale. For numeric types, you are responsible for ensuring the values you write do not exceed the min/max values of the underlying SQL column data types. For example, the SQL decimal type defaults to 18-digit precision (and scale 0) meaning the maximum value is 10¹⁸-1, or 999,999,999,999,999,999, whereas the .NET decimal type has a much higher maximum value of 79,228,162,514,264,337,593,543,950,335.

AllowNull

Determines whether or not the column can store SQL NULL values. The default is true. Some of the other features like PrimaryKey have related restrictions, and some of the Standard Columns impose restrictions (for example, the Id column never allows nulls).

DataLength

For character-data and binary columns, this defines the column size (or maximum size if variable-length). The value -1 indicates (max) length and is the property's default. If the column data type doesn't support this, the setting is ignored. Note that clustered columnstore indexing is incompatible with (max) length columns prior to SQL 2017.

Supported SQL column data types that use this property:

• char
• nchar
• nvarchar
• varchar

NonClusteredIndex

Any individual column can be defined as a non-clustered index, including the table primary key. Use this with caution, indexing carries a relatively high write-throughput penalty. One way to mitigate this is to keep non-clustered indexes offline and use batch reindexing on a scheduled basis.

Standard Columns

By default (and consistent with the SQL DDL to create a table shown earlier) these columns are included in a new ColumnOptions.Store list:

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

There are the following additional standard columns which are not included by default (for backwards-compatibility reasons):

• StandardColumn.LogEvent
• StandardColumn.TraceId
• StandardColumn.SpanId

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

// we don't need XML data
columnOptions.Store.Remove(StandardColumn.Properties);

// we do want JSON data and OpenTelemetry
columnOptions.Store.Add(StandardColumn.LogEvent);
columnOptions.Store.Add(StandardColumn.TraceId);
columnOptions.Store.Add(StandardColumn.SpanId);

In addition to any special properties described below, each Standard Column also has the usual column properties like ColumnName as described in the topic SqlColumn Objects.

Id

The Id column is an optional table identity column. It defaults to the int data type but can also be configured as bigint. Example how to change the data type to bigint:

var colOptions = new Serilog.Sinks.MSSqlServer.ColumnOptions();
colOptions.Id.DataType = System.Data.SqlDbType.BigInt;

Log.Logger = new LoggerConfiguration().WriteTo.MSSqlServer(columnOptions: colOptions)
    // ...

The AllowNull property is always false. If it is included in the table, it must be an auto-incrementing unique identity column and is automatically configured and auto-created as such.

Previous versions of this sink assumed the Id column was always present as an int identity primary key with a clustered index. Other configurations are possible and probably preferable, however this is still the default for backwards-compatibility reasons. Carefully consider your anticipated logging volume and query requirements. The default setting is not ideal in real-world scenarios since a clustered index is primarily of use when the key is used for sorting or range searches. This is rarely the case for the Id column.

No Id column: If you eliminate the column completely, the log table is stored as an unorded heap (as long as you don't define a different clustered primary key, which is not recommended). This is the ideal write-speed scenario for logging, however any non-clustered indexes you add will slightly degrade write performance.

Non-clustered primary key: You can also retain the column as an identity primary key, but using a non-clustered index. The log is still stored as an unordered heap, but writing a non-clustered index is slightly faster. Non-clustered indexes on other columns will reference the Id primary key. However, read performance will be slightly degraded since it requires two reads (searching the non-clustered index, then dereferencing the heap row from the Id).

BigInt data type: For very large log tables, if you absolutely require an identity column, you may wish to define the Id as the SQL bigint datatype. This 8-byte integer (equivalent to a c# long integer) will permit a maximum identity value of 9,223,372,036,854,775,807. This will slightly degrade both read and write performance.

Message

This column stores the formatted output (property placeholders are replaced with property values). It defaults to nvarchar(max). The DataType property can only be set to character-storage types.

In case DataLength is set to a specific value different from -1, any message longer than that length will be effectively truncated to that size. Example: DataLength is set to 15 and the message is "this is a very long message" (without the quotes), the truncated text stored in the database will be: "this is a ve..." (again without quotes).

MessageTemplate

This column stores the log event message with the property placeholders. It defaults to nvarchar(max). The DataType property can only be set to character-storage types.

If DataLength is set to a value different to -1 longer text will be truncated. See Message column for details.

Level

This column stores the event level (Error, Information, etc.). For backwards-compatibility reasons it defaults to a length of nvarchar(max) characters, but 12 characters is recommended. Alternately, the StoreAsEnum property can be set to true which causes the underlying level enum integer value to be stored as a SQL tinyint column. The DataType property can only be set to nvarchar or tinyint. Setting the DataType to tinyint is identical to setting StoreAsEnum to true.

TimeStamp

This column stores the time the log event was sent to Serilog as a SQL datetime (default), datetime2 or datetimeoffset type. If datetime2 or datetimeoffset should be used, this can be configured as follows.

var columnOptions = new ColumnOptions();
columnOptions.TimeStamp.DataType = SqlDbType.DateTimeOffset;

var columnOptions = new ColumnOptions();
columnOptions.TimeStamp.DataType = SqlDbType.DateTime2;

Please be aware that you have to configure the sink for datetimeoffset if the used logging database table has a TimeStamp column of type datetimeoffset. If the underlying database uses datetime2 for the TimeStamp column, the sink must be configured to use datetime2. On the other hand you must not configure for datetimeoffset if the TimeStamp column is of type datetime or datetime2. Failing to configure the data type accordingly can result in log table entries with wrong timezone offsets or no log entries being created at all due to exceptions during logging.

While TimeStamp may appear to be a good candidate as a clustered primary key, even relatively low-volume logging can emit identical timestamps forcing SQL Server to add a "uniqueifier" value behind the scenes (effectively an auto-incrementing identity-like integer). For frequent timestamp range-searching and sorting, a non-clustered index is better.

When the ConvertToUtc property is set to true, the time stamp is adjusted to the UTC standard. Normally the time stamp value reflects the local time of the machine issuing the log event, including the current timezone information. For example, if the event is written at 07:00 Eastern time, the Eastern timezone is +4:00 relative to UTC, so after UTC conversion the time stamp will be 11:00. Offset is stored as +0:00 but this is not the GMT time zone because UTC does not use offsets (by definition). To state this another way, the timezone is discarded and unrecoverable. UTC is a representation of the date and time exclusive of timezone information. This makes it easy to reference time stamps written from different or changing timezones.

Exception

When an exception is logged as part of the log event, the exception message is stored here automatically. The DataType must be nvarchar.

Similar to the columns Message and MessageTemplate, setting DataLength to a specific value different from -1 will effectively truncate any exception message to the stated length in DataLength. See Message column for details.

Properties

This column stores log event property values as XML. Typically you will use either this column or the JSON-based LogEvent column, but not both.

The DataType defaults to nvarchar and it is strongly recommended that this not be changed, but the SQL xml type is also supported. Using the xml type causes SQL server to convert the string data to a storage-efficent representation which can be searched much more quickly, but there is a measurable CPU-overhead cost. Test carefully with realistic workloads before committing to the xml data type.

The ExcludeAdditionalProperties setting is described in the Custom Property Columns topic.

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.

LogEvent

This column stores log event property values as JSON. Typically you will use either this column or the XML-based Properties column, but not both. This column's DataType must always be nvarchar.

By default this column is not used unless it is added to the ColumnOptions.Store property as documented above.

The content of this column is rendered as JSON by default or with a custom ITextFormatter passed by the caller as parameter logEventFormatter. Details can be found in Sink Configuration.

TraceId and SpanId

These two columns store the OpenTelemetry TraceId and SpanId log event properties which are documented here. The DataType of these columns must be nvarchar or varchar.

By default these columns are not used unless they are added to the ColumnOptions.Store property as documented above.

Custom Property Columns

By default, any log event properties you include in your log statements will be saved to the XML Properties column or the JSON LogEvent column. But they can also be stored in their own individual columns via the AdditionalColumns collection. This adds overhead to write operations but is very useful for frequently-queried properties. Only ColumnName is required; the default configuration is varchar(max).

If you specify a DataLength other than -1 on a column of character data types (NVarChar, VarChar, Char, NChar) longer text will be truncated to the specified length. See Message column for details.

var columnOptions = new ColumnOptions
{
    AdditionalColumns = new Collection<SqlColumn>
    {
        new SqlColumn
            {ColumnName = "EnvironmentUserName", PropertyName = "UserName", DataType = SqlDbType.NVarChar, DataLength = 64},

        new SqlColumn
            {ColumnName = "UserId", DataType = SqlDbType.BigInt, NonClusteredIndex = true},

        new SqlColumn
            {ColumnName = "RequestUri", DataType = SqlDbType.NVarChar, DataLength = -1, AllowNull = false},
    }
};

var log = new LoggerConfiguration()
    .WriteTo.MSSqlServer(@"Server=...",
        sinkOptions: new MSSqlServerSinkOptions { TableName = "Logs" },
        columnOptions: columnOptions)
    .CreateLogger();

In this example, when a log event contains any of the properties UserName, UserId, and RequestUri, the property values would be written to the corresponding columns. The property names must match exactly (case-insensitive). In the case of UserName, that value would be written to the column named EnvironmentUserName.

Unlike previous versions of the sink, Standard Column names are not reserved. If you remove the Id Standard Column from the ColumnOptions.Store list, you are free to create a new custom column called Id which the sink will treat like any other custom column fully under your control.

Note the use of the SqlDbType enumerations for specifying DataType. Unlike previous versions of the sink, .NET System data types and DataColumn objects are no longer used for custom column definition.

Excluding redundant data

By default, properties matching a custom column will still be included in the data saved to the XML Properties or JSON LogEvent column. This is consistent with the idea behind structured logging, and makes it easier to convert the log data to another document-data storage platform later, if desired.

However, the properties being saved in their own columns can be excluded from these catch-all columns. Use the columnOptions.Properties.ExcludeAdditionalProperties parameter to exclude the redundant properties from the Properties XML column, or columnOptions.LogEvent.ExcludeAdditionalProperties if you're using the JSON LogEvent column.

Standard Columns are always excluded from the XML Properties column but Standard Columns are included in the JSON data for backwards-compatibility reasons. They can be excluded from the JSON LogEvent column with columnOptions.LogEvent.ExcludeStandardColumns.

External Configuration Syntax

Projects targeting frameworks which are compatible with System.Configuration automatically have support for XML-based configuration (either app.config or web.config) of a MSSqlServerSinkOptions parameters and a ColumnOptions table definition, and the Serilog.Settings.AppSettings package adds XML-based configuration of other direct sink arguments (like customFormatter or restrictedToMinimumLevel).

Projects targeting frameworks which are compatible with Microsoft.Extensions.Configuration can apply configuration-driven sink setup and MSSqlServerSinkOptions or ColumnOptions settings using the Serilog.Settings.Configuration package or by supplying the appropriate arguments through code.

All properties of the MSSqlServerSinkOptions class are configurable and almost all of the ColumnOptions class except the Properties.PropertyFilter predicate expression, and all elements and lists shown are optional. In most cases configuration key names match the class property names, but there are some exceptions. For example, because PrimaryKey is a SqlColumn object reference when configured through code, external configuration uses a primaryKeyColumnName setting to identify the primary key by name.

Custom columns and the stand-alone Standard Column entries all support the same general column properties (ColumnName, DataType, etc) listed in the SqlColumn Objects topic. The following sections documenting configuration syntax omit many of these properties for brevity.

If you combine external configuration with configuration through code, external configuration changes will be applied in addition to MSSqlServerSinkOptions and ColumnOptions objects you provide through code (external configuration "overwrites" properties defined in configuration, but properties only defined through code are preserved).

IMPORTANT: Some of the following examples do not reflect real-world configurations that can be copy-pasted as-is. Some settings or properties shown are mutually exclusive and are listed below for documentation purposes only.

JSON (Microsoft.Extensions.Configuration)

Keys and values are not case-sensitive. This is an example of configuring the sink arguments.

{
  "Serilog": {
    "Using":  ["Serilog.Sinks.MSSqlServer"],
    "MinimumLevel": "Debug",
    "WriteTo": [
      { "Name": "MSSqlServer",
        "Args": {
            "connectionString": "NamedConnectionString",
            "sinkOptionsSection": {
                "tableName": "Logs",
                "schemaName": "EventLogging",
                "autoCreateSqlTable": true,
                "batchPostingLimit": 1000,
                "batchPeriod": "0.00:00:30"
            },
            "restrictedToMinimumLevel": "Warning",
            "columnOptionsSection": { . . . }
        }
      }
    ]
  }
}

As the name suggests, columnOptionSection is an entire configuration section in its own right. The AdditionalColumns collection can also be populated from a key named customColumns (not shown here) for backwards-compatibility reasons.

"columnOptionsSection": {
    "disableTriggers": true,
    "clusteredColumnstoreIndex": false,
    "primaryKeyColumnName": "Id",
    "addStandardColumns": [ "LogEvent", "TraceId", "SpanId" ],
    "removeStandardColumns": [ "MessageTemplate", "Properties" ],
    "additionalColumns": [
        { "ColumnName": "EventType", "DataType": "int", "AllowNull": false },
        { "ColumnName": "Release", "DataType": "varchar", "DataLength": 32 },
        { "ColumnName": "EnvironmentUserName", "PropertyName": "UserName", "DataType": "varchar", "DataLength": 50 },
        { "ColumnName": "All_SqlColumn_Defaults",
            "DataType": "varchar",
            "AllowNull": true,
            "DataLength": -1,
            "NonClusteredIndex": false
        }
    ],
    "id": { "nonClusteredIndex": true },
    "level": { "columnName": "Severity", "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": {
        "excludeAdditionalProperties": true,
        "excludeStandardColumns": true
    },
    "message": { "columnName": "Msg" },
    "exception": { "columnName": "Ex" },
    "messageTemplate": { "columnName": "Template" }
}

XML ColumnOptions (System.Configuration)

Keys and values are case-sensitive. Case must match exactly as shown below.

  <configSections>
    <section name="MSSqlServerSettingsSection"
             type="Serilog.Configuration.MSSqlServerConfigurationSection, Serilog.Sinks.MSSqlServer"/>
  </configSections>
  <MSSqlServerSettingsSection DisableTriggers="false"
                       ClusteredColumnstoreIndex="false"
                       PrimaryKeyColumnName="Id">

    
    <TableName Value="Logs"/>
    <SchemaName Value="EventLogging"/>
    <AutoCreateSqlTable Value="true"/>
    <BatchPostingLimit Value="150"/>
    <BatchPeriod Value="00:00:15"/>

    
    <AddStandardColumns>
        <add Name="LogEvent"/>
        <add Name="TraceId"/>
        <add Name="SpanId"/>
    </AddStandardColumns>
    <RemoveStandardColumns>
        <remove Name="Properties"/>
    </RemoveStandardColumns>
    <Columns>
      <add ColumnName="EventType" DataType="int"/>
      <add ColumnName="EnvironmentUserName"
           PropertyName="UserName"
           DataType="varchar"
           DataLength="50" />
      <add ColumnName="Release"
           DataType="varchar"
           DataLength="64"
           AllowNull="true"
           NonClusteredIndex="false"/>
    </Columns>
    <Exception ColumnName="Ex" DataLength="512"/>
    <Id NonClusteredIndex="true"/>
    <Level ColumnName="Severity" StoreAsEnum="true"/>
    <LogEvent ExcludeAdditionalProperties="true"
              ExcludeStandardColumns="true"/>
    <Message DataLength="1024"/>
    <MessageTemplate DataLength="1536"/>
    <Properties DataType="xml"
                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 ConvertToUtc="true"/>
  </MSSqlServerSettingsSection>

XML Sink (Serilog.Settings.AppSettings)

Refer to the Serilog.Settings.AppSettings package documentation for complete details about sink configuration. This is an example of setting some of the configuration parameters for this sink.

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

Troubleshooting

This is a relatively complex sink, and there are certain common problems which you should investigate before opening a new issue to ask for help. If you do open a new issue, please be sure to tell us all of the Serilog packages you are using and which versions, show us your real configuration code and any external configuration sources, and a simple example of code which reproduces the problem. If you're getting an error message, please include the exact message.

Always check SelfLog first

After configuration is complete, this sink runs through a number of checks to ensure consistency. Some configuration issues result in an exception, but others may only generate warnings through Serilog's SelfLog feature. At runtime, exceptions are silently reported through SelfLog. Refer to Debugging and Diagnostics in the main Serilog documentation to enable SelfLog output.

Always call Log.CloseAndFlush

Any Serilog application should always call Log.CloseAndFlush before shutting down. This is especially important in sinks like this one. It is a "periodic batching sink" which means log event records are written in batches for performance reasons. Calling Log.CloseAndFlush should guarantee any batch in memory will be written to the database (but read the Visual Studio note below). You may wish to put the Log.CloseAndFlush call in a finally block in console-driven apps where a Main loop controls the overall startup and shutdown process. Refer to the Serilog.AspNetCore sample code for an example. More exotic scenarios like dependency injection may warrant hooking the ProcessExit event when the logger is registered as a singleton:

AppDomain.CurrentDomain.ProcessExit += (s, e) => Log.CloseAndFlush();

Consider batched sink SqlBulkCopy behavior

If you initialize the sink with WriteTo then it uses a batched sink semantics. This means that it does not directly issue an SQL command to the database for each log call, but it collectes log events in a buffer and later asynchronously writes a bulk of them to the database using SqlBulkCopy. If SqlBulkCopy fails to write a single row of the batch to the database, the whole batch will be lost. Unfortunately it is not easily possible (and probably only with a significant performance impact) to find out what lines of the batch caused problems. Therefore the sink cannot easily retry the operation with the problem lines removed. Typical problems can be that data (like the log message) exceeds the field length in the database or fields which cannot be null are null in the log event. Keep this in mind when using the batched version of the sink and avoid log events to be created with data that is invalid according to your database schema. Use a wrapper class or Serilog Enrichers to validate and correct the log event data before it gets written to the database.

Test outside of Visual Studio

When you exit an application running in debug mode under Visual Studio, normal shutdown processes may be interrupted. Visual Studio issues a nearly-instant process kill command when it decides you're done debugging. This is a particularly common problem with ASP.NET and ASP.NET Core applications, in which Visual Studio instantly terminates the application as soon as the browser is closed. Even finally blocks usually fail to execute. If you aren't seeing your last few events written, try testing your application outside of Visual Studio.

Try a dev package

If you're reading about a feature that doesn't seem to work, check whether you're reading the docs for the main branch or the dev branch -- most Serilog repositories are configured to use the dev branch by default. If you see something interesting only described by the dev branch documentation, you'll have to reference a dev-versioned package. The repository automatically generates a new dev package whenever code-related changes are merged.

Are you really using this sink?

Please check your NuGet references and confirm you are specifically referencing Serilog.Sinks.MSSqlServer. In the early days of .NET Core, there was a popular Core-specific fork of this sink, but the documentation and NuGet project URLs pointed here. Today the package is marked deprecated, but we continue to see some confusion around this.

.NET Framework apps must reference Microsoft.Data.SqlClient

If you are using the sink in a .NET Framework app, make sure to add a nuget package reference to Microsoft.Data.SqlClient in your app project. This is necessary due to a bug in SqlClient which can lead to exceptions about missing Microsoft assemblies. Details can be found in issue 283 and issue 208.

Querying Property Data

Extracting and querying the property column directly can be helpful when looking for specific log sequences. SQL Server has query syntax supporting columns that store either XML or JSON data.

LogEvent JSON

This capability requires SQL 2016 or newer. Given the following JSON properties:

{
  "Properties": {
    "Action": "GetUsers",
    "Controller": "UserController"
  }
}

The following query will extract the Action property and restrict the query based on the Controller property using SQL Server's built-in JSON path support.

SELECT
  [Message], [TimeStamp], [Exception],
  JSON_VALUE(LogEvent, '$.Properties.Action') AS Action
FROM [Logs]
WHERE
  JSON_VALUE(LogEvent, '$.Properties.Controller') = 'UserController'

Properties XML

Given the following XML properties:

<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 Server's 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'

Breaking Changes

Release 6.0.0

Microsoft.Data.SqlClient was upgraded to >4.0.0 which introduces a breaking change regarding connection strings. If your SQL Server does not use encryption you have to explicitly specify this in the connection string by adding Encrypt=False. Otherwise the connection will fail with a SqlException. Refer to the SqlClient documentation for details.

Deprecated Features

Feature	Notes
UseAzureManagedIdentity	Since the update of Microsoft.Data.SqlClient in sink release 5.8.0 Active Directory auth capabilities of SqlClient can be used. You can specify one of the supported AD authentication methods, which include Azure Managed Identities, directly in the connection string. Refer to the SqlClient documentation for details.
AdditionalDataColumns	Use the AdditionalColumns collection instead. Configuring the sink no longer relies upon .NET DataColumn objects or .NET System types.
Id.BigInt	Use Id.DataType = SqlDb.BigInt instead. (The BigInt property was only available in dev packages).
Binary and VarBinary	Due to the way Serilog represents property data internally, it isn't possible for the sink to access property data as a byte array, so the sink can't write to these column types.

Most deprecated features are still available, but they are marked with the [Obsolete] attribute (which results in a compiler warning in your project) and will be removed in a future release. You should switch to the replacement implementations as soon as possible. Where possible, internally these are converted to the replacement implementation so that they only exist at the configuration level.