Repository files navigation

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 8.0, .NET Standard 2.0

• 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

The most basic minimalistic sink initialization is done like this.

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

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

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 SerilogWriteTo configuration method (orAuditTo, or similar variations). Settings for the sink are configured using aMSSqlServerSinkOptions object passed to the configuration method. The table is configured with an optionalColumnOptions 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

At minimum,connectionString andMSSqlServerSinkOptions.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 theMSSqlServerSinkOptions object are discussed in theMSSqlServerSinkOptions Object topic.

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

Like other sinks,restrictedToMinimumLevel controls theLogEventLevel messages that are processed by this sink. This parameter is ignored if the propertyLevelSwitch 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 theMSSqlServerSinkOptions.BatchPostingLimit andMSSqlServerSinkOptions.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 ofFormat Providers for details about theformatProvider arguments.

The parameterlogEventFormatter can be used to specify a custom renderer implementingITextFormatter which will be used to generate the contents of theLogEventcolumn. 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 documentationCustom text formatters.

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

• appConfiguration
• sinkOptionsSection
• columnOptionsSection

The full configuration root provided to theappConfiguration 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 theConnectionStrings section.

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

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-basedapp.config orweb.config files, andMicrosoft.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.

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

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.

varlogDB=@"Server=...";varsinkOpts=newMSSqlServerSinkOptions();sinkOpts.TableName="Logs";varcolumnOpts=newColumnOptions();columnOpts.Store.Remove(StandardColumn.Properties);columnOpts.Store.Add(StandardColumn.LogEvent);columnOpts.LogEvent.DataLength=2048;columnOpts.PrimaryKey=columnOpts.TimeStamp;columnOpts.TimeStamp.NonClusteredIndex=true;varlog=newLoggerConfiguration().WriteTo.MSSqlServer(connectionString:logDB,sinkOptions:sinkOpts,columnOptions:columnOpts).CreateLogger();

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

varappSettings=newConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("appsettings.json").Build();varlogDB=@"Server=...";varsinkOpts=newMSSqlServerSinkOptions{TableName="Logs"};varcolumnOpts=newColumnOptions();varlog=newLoggerConfiguration().WriteTo.MSSqlServer(connectionString:logDB,sinkOptions:sinkOpts,columnOptions:columnOpts,appConfiguration:appSettings).CreateLogger();

Projects can loadMSSqlServerSinkOptions andColumnOptions objects from an XML configuration file such asapp.config orweb.config. The sink configuration method automatically checksConfigurationManager, so there is no code to show, and no additional packages are required. See theExternal Configuration Syntax topic for details.

Requires configuration package version3.0.0 or newer.

.NET Standard projects can callReadFrom.Configuration() to configure Serilog using theSerilog.Settings.Configuration package. This will apply configuration arguments from all application configuration sources (not onlyappsettings.json as shown here, but any other validIConfiguration source). This package can configure the sink itself withMSSqlServerSinkOptions as well asColumnOptions table features. See theExternal Configuration Syntax topic for details.

Projects can configure the sink from XML configuration by callingReadFrom.AppSettings() using theSerilog.Settings.AppSettings package. This will apply configuration arguments from the project'sapp.config orweb.config file. This is independent of configuringMSSqlServerSinkOptions orColumnOptions from external XML files. See theExternal Configuration Syntax topic for details.

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 sinkdoes not fail silently -- it can throw exceptions. You should wrap audit logging output in atry/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 usingAuditTo instead ofWriteTo.

• connectionString
• sinkOptions
• columnOptions
• formatProvider
• logEventFormatter

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

TheMSSqlServerSinkOptions.BatchPostingLimit andMSSqlServerSinkOptions.BatchPeriod parameters are ignored because the audit sink writes log events immediately.

ForM.E.C-compatible projects,appConfiguration,sinkOptionsSection andcolumnOptionsSection arguments are also provided, just as they are with the non-audit configuration extensions.

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 theId column (options and performance impacts are discussed in theStandard 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 theColumnOptions 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 mustexactly 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));

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).

If the audit version of the sink is used or the sink optionUseSqlBulkCopy is set totrue, only INSERT statements are used and no SELECT permission is required.

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 namedSerilog, a database user namedSerilog, and assigned to that user are the rolesSerilogAutoCreate andSerilogWriter. As the name suggests, theSerilogAutoCreate 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 theSerilogWriter role would be restricted to the log table only, and that table has to already exist to use table-specificGRANT 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 tableSecuredLog, 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.

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

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

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

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

A flag specifiying if the log events database should be created if it does not exist. It defaults tofalse. If this is set totrue the propertyAutoCreateSqlTable should also be set totrue.

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

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

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.

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.

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

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

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

An optional action to customize the underlying SqlConnection object. Can be used to set properties such asAccessTokenCallback.

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

• Store
• PrimaryKey
• ClusteredColumnstoreIndex
• DisableTriggers
• AdditionalColumns

This is a list of columns that have special handling when a log event is being written. These are explained in theStandard Columns topic. Only the Standard Columns which are in the log table should be present in theStore collection. This is aList<> ofStandardColumn enumeration members, so you can simplyAdd orRemove columns to change the list. The order of appearance does not matter. TheColumnOptions 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.

By default, theId 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 theCustom Property Columns topic). SQL Server requires primary key indexes to always beNOT NULL so the column-levelAllowNull property will be overridden if set totrue.

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

NOTE: If you do not set theNonClusteredIndex property on the primary key column totrue, 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-incrementingId column as a primary key isn't particularly useful for query purposes).

Setting this totrue 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.

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

This is aCollection<> ofSqlColumn objects that you create to define custom columns in the log event table. Refer to theCustom Property Columns topic for more information.

Each Standard Column in theColumnOptions.Store list and any custom columns you add to theAdditionalColumns collection areSqlColumn objects with the following properties:

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

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

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 inAdditionalColumns and is ignored for standard columns.

PropertyName can contain a simple property name likeSomeProperty but it can also be used to hierarchically reference sub-properties with expressions likeSomeProperty.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 likeSomeProperty.SomeArray[2] will not work. Hierarchical property resolution can be disabled usingResolveHierarchicalPropertyName in case you need property names containing dots which should not be treated as hierarchical.

Controls whether hierarchical sub-property expressions inPropertyName are evaluated (see above). The default istrue. If set tofalse any value is treated as a simple property name and no hierarchical sub-property binding is done.

This property can be set to nearly any value in theSystem.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 .NETDataColumn 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 astimestamp andudt, and deprecated types liketext andimage 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 SQLdecimal 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 .NETdecimal type has a much higher maximum value of 79,228,162,514,264,337,593,543,950,335.

Determines whether the column can store SQLNULL values. The default istrue. Some of the other features likePrimaryKey have related restrictions, and some of the Standard Columns impose restrictions (for example, theId column never allows nulls).

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

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.

By default (and consistent with the SQL DDL to create a table shown earlier) these columns are included in a newColumnOptions.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 datacolumnOptions.Store.Remove(StandardColumn.Properties);// we do want JSON data and OpenTelemetrycolumnOptions.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 likeColumnName as described in the topicSqlColumn Objects.

TheId column is an optional table identity column. It defaults to theint data type but can also be configured asbigint. Example how to change the data type to bigint:

varcolOptions=newSerilog.Sinks.MSSqlServer.ColumnOptions();colOptions.Id.DataType=System.Data.SqlDbType.BigInt;Log.Logger=newLoggerConfiguration().WriteTo.MSSqlServer(columnOptions:colOptions)// ...

TheAllowNull property is alwaysfalse. 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 theId column wasalways present as anint 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 theId 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 theId as the SQLbigint 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.

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

In caseDataLength 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).

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

IfDataLength is set to a value different to -1 longer text will be truncated. SeeMessage column for details.

This column stores the event level (Error, Information, etc.). For backwards-compatibility reasons it defaults to a length ofnvarchar(max) characters, but 12 characters is recommended. Alternately, theStoreAsEnum property can be set totrue which causes the underlying level enum integer value to be stored as a SQLtinyint column. TheDataType property can only be set tonvarchar ortinyint. Setting theDataType totinyint is identical to settingStoreAsEnum totrue.

This column stores the time the log event was sent to Serilog as a SQLdatetime (default),datetime2 ordatetimeoffset type. Ifdatetime2 ordatetimeoffset should be used, this can be configured as follows.

varcolumnOptions=newColumnOptions();columnOptions.TimeStamp.DataType=SqlDbType.DateTimeOffset;

varcolumnOptions=newColumnOptions();columnOptions.TimeStamp.DataType=SqlDbType.DateTime2;

Please be aware that you have to configure the sink fordatetimeoffset if the used logging database table has aTimeStamp column of typedatetimeoffset. If the underlying database usesdatetime2 for theTimeStamp column, the sink must be configured to usedatetime2. On the other hand you must not configure fordatetimeoffset if theTimeStamp column is of typedatetime ordatetime2. 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 theConvertToUtc property is set totrue, 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 isnot 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 timeexclusive of timezone information. This makes it easy to reference time stamps written from different or changing timezones.

When an exception is logged as part of the log event, the exception message is stored here automatically. TheDataType must benvarchar.

Similar to the columnsMessage andMessageTemplate, settingDataLength to a specific value different from -1 will effectively truncate any exception message to the stated length inDataLength. SeeMessage column for details.

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

TheDataType defaults tonvarchar and it is strongly recommended that this not be changed, but the SQLxml type is also supported. Using thexml 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 thexml data type.

TheExcludeAdditionalProperties setting is described in theCustom Property Columns topic.

Names of elements can be controlled by theRootElementName,PropertyElementName,ItemElementName,DictionaryElementName,SequenceElementName,StructureElementName andUsePropertyKeyAsElementName options.

TheUsePropertyKeyAsElementName option, if set totrue, will use the property key as the element name instead of "property" for the name with the key as an attribute.

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

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

This column stores log event property values as JSON. Typically you will use either this column or the XML-basedProperties column, but not both. This column'sDataType must always benvarchar.

By default this column is not used unless it is added to theColumnOptions.Store property as documentedabove.

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

These two columns store the OpenTelemetryTraceId andSpanId log event properties which are documentedhere. TheDataType of these columns must benvarchar orvarchar.

By default these columns are not used unless they are added to theColumnOptions.Store property as documentedabove.

By default, any log event properties you include in your log statements will be saved to the XMLProperties column or the JSONLogEvent column. But they can also be stored in their own individual columns via theAdditionalColumns collection. This adds overhead to write operations but is very useful for frequently-queried properties. OnlyColumnName is required; the default configuration isvarchar(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. SeeMessage column for details.

varcolumnOptions=newColumnOptions{AdditionalColumns=newCollection<SqlColumn>{newSqlColumn{ColumnName="EnvironmentUserName",PropertyName="UserName",DataType=SqlDbType.NVarChar,DataLength=64},newSqlColumn{ColumnName="UserId",DataType=SqlDbType.BigInt,NonClusteredIndex=true},newSqlColumn{ColumnName="RequestUri",DataType=SqlDbType.NVarChar,DataLength=-1,AllowNull=false},}};varlog=newLoggerConfiguration().WriteTo.MSSqlServer(@"Server=...",sinkOptions:newMSSqlServerSinkOptions{TableName="Logs"},columnOptions:columnOptions).CreateLogger();

In this example, when a log event contains any of the propertiesUserName,UserId, andRequestUri, the property values would be written to the corresponding columns. The property names must match exactly (case-insensitive). In the case ofUserName, that value would be written to the column namedEnvironmentUserName.

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

Note the use of theSqlDbType enumerations for specifyingDataType. Unlike previous versions of the sink, .NETSystem data types andDataColumn objects are no longer used for custom column definition.

By default, properties matching a custom column will still be included in the data saved to the XMLProperties or JSONLogEvent 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 thecolumnOptions.Properties.ExcludeAdditionalProperties parameter to exclude the redundant properties from theProperties XML column, orcolumnOptions.LogEvent.ExcludeAdditionalProperties if you're using the JSONLogEvent column.

Standard Columns are always excluded from the XMLProperties column but Standard Columns are included in the JSON data for backwards-compatibility reasons. They can be excluded from the JSONLogEvent column withcolumnOptions.LogEvent.ExcludeStandardColumns.

Projects targeting frameworks which are compatible withSystem.Configuration automatically have support for XML-based configuration (eitherapp.config orweb.config) of aMSSqlServerSinkOptions parameters and aColumnOptions table definition, and theSerilog.Settings.AppSettings package adds XML-based configuration of other direct sink arguments (likecustomFormatter orrestrictedToMinimumLevel).

Projects targeting frameworks which are compatible withMicrosoft.Extensions.Configuration can apply configuration-driven sink setup andMSSqlServerSinkOptions orColumnOptions settings using theSerilog.Settings.Configuration package or by supplying the appropriate arguments through code.

All properties of theMSSqlServerSinkOptions class are configurable and almost all of theColumnOptions class except theProperties.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, becausePrimaryKey is aSqlColumn object reference when configured through code, external configuration uses aprimaryKeyColumnName 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 theSqlColumn 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 toMSSqlServerSinkOptions andColumnOptions 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.

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. TheAdditionalColumns collection can also be populated from a key namedcustomColumns (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" }}

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

  <configSections>    <sectionname="MSSqlServerSettingsSection"type="Serilog.Configuration.MSSqlServerConfigurationSection, Serilog.Sinks.MSSqlServer"/>  </configSections>  <MSSqlServerSettingsSectionDisableTriggers="false"ClusteredColumnstoreIndex="false"PrimaryKeyColumnName="Id"><!-- SinkOptions parameters-->    <TableNameValue="Logs"/>    <SchemaNameValue="EventLogging"/>    <AutoCreateSqlTableValue="true"/>    <BatchPostingLimitValue="150"/>    <BatchPeriodValue="00:00:15"/><!-- ColumnOptions parameters-->    <AddStandardColumns>        <addName="LogEvent"/>        <addName="TraceId"/>        <addName="SpanId"/>    </AddStandardColumns>    <RemoveStandardColumns>        <removeName="Properties"/>    </RemoveStandardColumns>    <Columns>      <addColumnName="EventType"DataType="int"/>      <addColumnName="EnvironmentUserName"PropertyName="UserName"DataType="varchar"DataLength="50" />      <addColumnName="Release"DataType="varchar"DataLength="64"AllowNull="true"NonClusteredIndex="false"/>    </Columns>    <ExceptionColumnName="Ex"DataLength="512"/>    <IdNonClusteredIndex="true"/>    <LevelColumnName="Severity"StoreAsEnum="true"/>    <LogEventExcludeAdditionalProperties="true"ExcludeStandardColumns="true"/>    <MessageDataLength="1024"/>    <MessageTemplateDataLength="1536"/>    <PropertiesDataType="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"/>    <TimeStampConvertToUtc="true"/>  </MSSqlServerSettingsSection>

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

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

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 yourreal configuration code and any external configuration sources, and asimple example of code which reproduces the problem. If you're getting an error message, please include the exact message.

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'sSelfLog feature. At runtime, exceptions are silently reported throughSelfLog. Refer toDebugging and Diagnostics in the main Serilog documentation to enableSelfLog output.

Any Serilog application shouldalways callLog.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. CallingLog.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 theLog.CloseAndFlush call in afinally block in console-driven apps where aMain loop controls the overall startup and shutdown process. Refer to theSerilog.AspNetCore sample code for an example. More exotic scenarios like dependency injection may warrant hooking theProcessExit event when the logger is registered as a singleton:

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

If you initialize the sink withWriteTo 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 usingSqlBulkCopy.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.

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. Evenfinally blocks usually fail to execute. If you aren't seeing your last few events written, try testing your application outside of Visual Studio.

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

Please check your NuGet references and confirm you are specifically referencingSerilog.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.

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 inissue 283 andissue 208.

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.

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

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

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

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

Given the following XML properties:

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

The following query will extract theAction property and restrict the query based on theController property using SQL Server's built-in XQuery support.

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

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 addingEncrypt=False. Otherwise the connection will fail with aSqlException. Refer to theSqlClient documentation for details.

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.