EntLib6 Semantic Logging with SQL Server Sink

In the last couple of months we started our discussion about EventSource and Semantic Logging Application Block. You can refer to the previous posts here:

• EventSource & PerfView
• Semantic Out-Process Logging using Semantic Logging Application Block - File Sink
• SLAB - Reactive Event Sourcing
• To Log or Not to Log...Really???
• .net 4.5 EventSource & Windows Event Log - Microsoft's Nuget Package
• EventSource and Implenting Interfaces

In the above posts, we discussed how to create an Event Source and why do we actually need structured logging. We presented some examples of consuming the events generated by EventSource using tools including PerfView and SLAB's Semantic Logging Service. Event Sinks and listeners allow us to consume those events and pushed them out externally, including file system and Windows Event log. This post is about taking the discussion a little further to see how we can push out the events generated using an EventSource to SQL Server Database.

System Requirements
SLAB supports SQL Server 2008 and higher, SQL Server LocalDB and Windows Azure SQL Database. We will be using SQL Server Local DB for this example.

Sample Event Source
Let's create a sample console application SLABSqlEventSinkEx. We add the following EventSource to the project.


Let's try to verify if our EventSource has no issues by using PerfView. We discussed about using PerfView for ETW events here.


And we do see the event being generated. Please notice the selection below:



SLAB provides a number of Event sinks. Not all of them are in the same assembly.



Pattern & Practices team has distributed them among different Nuget packages. If you want to utilize the events generated by an event source and push them out to a SQL Server database then you would need to use the following package:



After packages installation, this is how my packages.config looks like.


Creating Database
The nuget package provides the necessary script to create database schema. We can find the scripts under ...\packages\EnterpriseLibrary.SemanticLogging.Database.1.0.1304.0\scripts folder. Just run the cmd file in the folder. It should run both sql files passing the required parameters.



The script creates a new database Logging with a single table, stored procedure and a couple of user defined types. It assumes installation of SqlLocalDB installation. Here we have connected with the database using Visual Studio SQL Server Object Explorer in Visual Studio 2012.



We can use SqlLocalDB command line tool to check the state of sql server instance used.



If you are using out-proc Semantic Logging Service to consume the events then you really don't need to download the package. The script and command file is copied into the service's folder when you install the nuget package. You can find them here:



Using SQL Server Sink with Semantic Logging Service
As we have discussed before SLAB allows us for out-proc logging using Semantic Logging Service. The service can run as a console application or windows service. You can see the this post where we introduced the service and its use with file system sink for event sources. Let's update the service configuration [SemanticLogging-svc.xml] to push the events from SqlApplicationEventSource to SQL Server database created above.


After updating the configuration, we can run the service as a console application as follows:



Run the application writing ETW Events. As the events are pushed out, the service writes the details to the specified database. Here is the same data as we saw in PerfView earlier in this post.



Using SLAB's In-Proc SQL Server Sink
We can also enable the events In-Proc. SqlDatabaselog can be used to create the listener to be used to enable the events. We can add the following code to the Main method of our console application created above.


But I have noticed an issue logging the data in SQL Server. I have discussed the issue here: [Discussion about Keyword issue]. The issue is based on using Keyword with the event method, which might seems related.

Download