Sitecore Logging. Write it to SQL!

Today I have been trying to make Sitecore CMS log to the database instead of file system. After hitting a few roadblocks, I’ve managed to find an answer via our helpful support portal.

Now it is not a secret that we are using an open source logging framework called log4net and that it is possible to configure it to use SQL database for storage instead of the file system.

This could come extremely handy due to a couple of reasons.

1. By default, file system logging means that Sitecore frequently creates a new file during system restart with a new timestamp, which may not be really what you expect, but this is by design.

2. Parsing of text files is an extremely creative and involving process. There are tools and solutions to facilitate in that but, I prefer writing SQL queries to get my data.

There are definitely more, but you catch my drift…

So how to get it working? As I mentioned, our support folks, or I should say, solution consultants, came up with 3 easy steps.

1. Create a table to store your logs. I’d recommend creating a separate database for that called “SitecoreSystem” as you don’t want to add a custom table to your Sitecore database, believe me!

CREATE TABLE [dbo].[Log] ( 
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Date] [datetime] NOT NULL ,
[Thread] [varchar] (255) NOT NULL ,
[Level] [varchar] (20) NOT NULL ,
[Logger] [varchar] (255) NOT NULL ,
[Message] [varchar] (4000) NOT NULL 
) ON [PRIMARY]

2. Define your own “SQL based appender” in <log4net> section of web.config.

<appender name="ADONetAppender_SqlServer" type="log4net.Appender.ADONetAppender, Sitecore.Logging" >
<param name="ConnectionType" value="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<param name="ConnectionString" value="user id=sa;password=sitecorerocks;Data Source=.\\sql2008;Database=SitecoreSystem" />
<param name="CommandText" value="INSERT INTO Log ([Date],[Thread],[Level],[Logger],[Message]) VALUES (@log_date, @thread, @log_level, @logger, @message)" />
<param name="Parameter">
<param name="ParameterName" value="@log_date" />
<param name="DbType" value="DateTime" />
<param name="Layout" type="log4net.Layout.PatternLayout">
<param name="ConversionPattern" value="%d{yyyy&apos;-&apos;MM&apos;-&apos;dd HH&apos;:&apos;mm&apos;:&apos;ss&apos;.&apos;fff}" />
</param>
</param>
<param name="Parameter">
<param name="ParameterName" value="@thread" />
<param name="DbType" value="String" />
<param name="Size" value="255" />
<param name="Layout" type="log4net.Layout.PatternLayout">
<param name="ConversionPattern" value="%t" />
</param>
</param>
<param name="Parameter">
<param name="ParameterName" value="@log_level" />
<param name="DbType" value="String" />
<param name="Size" value="50" />
<param name="Layout" type="log4net.Layout.PatternLayout">
<param name="ConversionPattern" value="%p" />
</param>
</param>
<param name="Parameter">
<param name="ParameterName" value="@logger" />
<param name="DbType" value="String" />
<param name="Size" value="255" />
<param name="Layout" type="log4net.Layout.PatternLayout">
<param name="ConversionPattern" value="%c" />
</param>
</param>
<param name="Parameter">
<param name="ParameterName" value="@message" />
<param name="DbType" value="String" />
<param name="Size" value="4000" />
<param name="Layout" type="log4net.Layout.PatternLayout">
<param name="ConversionPattern" value="%m" />
</param>
</param>
</appender>

3. Reference it now in <root> section, comment out the standard file based appender:

<root>
<priority value="INFO"/>
<appender-ref ref="ADONetAppender_SqlServer" />
<!--<appender-ref ref="LogFileAppender"/>-->
</root>

Troubleshooting

1. Make sure to double back slash the SQL name, e.g.  (.\\sqlexpress) in ConnectionString.

2. If you don’t see any entries in the Log database straight after the change, don’t panic. There is a threshold which should be exceeded before anything is written to the database. To test, launch tree serialization process – as this creates a lot of log entries, you will definitely see those unless something is wrong.

3. If something is wrong, add the following to your web.config. And if you don’t see anything written to the database, check the log file specified below.

<system.diagnostics>
<trace autoflush="true" indentsize="4">
<listeners>
<add name="myListener" type="System.Diagnostics.TextWriterTraceListener" initializeData="D:\logs\error.log" />
<remove name="Default" />
</listeners>
</trace>
</system.diagnostics>

 Open questions

- Maintainability
Now the most interesting part. You will probably need to create a scheduled agent that cleans up the Log table periodically for obvious reasons.

What next?

You can take it further and add custom parameters to your logs. Let’s leave that till next time.

Share if you think it is useful. Cheers!