Logging events to a database
.png "patterns & practices Developer Center")
This scenario is one of several that describe the typical requirements for using the Semantic Logging Application Block in your applications. It describes the process for setting up the block to send log events to a database.
Typical Goals
You need to send logging information created as log entries to one or more sinks or targets, and the list of targets includes a database. You want to set up the Semantic Logging Application Block to use a suitable event sink, and configure the database to receive log entries.
Notes
The SqlDatabaseSink event sink is for use only with SQL Server and Azure SQL Database. By default, it uses buffering and saves accumulated log messages every 30 seconds.
The log messages that the database sink writes to the database are not deleted from the database if they are written inside an ambient transaction that is rolled back.
Solution
The following steps describe the process, and provide links to more details for each step.
To configure logging to a SQL Server database
- Define an event source for your application. For more information, see the topic Developing event sources using the .NET EventSource class.
- Write code to raise the events you defined in response to actions within the application. For more information, see the topic Raising events from your applications.
- Decide if you will use the in-process or out-of-process approach for collecting events. For more information to help you decide, see the topic Overview of logging using the Semantic Logging Application Block.
- If you choose to use the in-process approach:
- Use NuGet to add both the Semantic Logging Application Block and the Semantic Logging Application Block – SQL Server Sink packages to your Visual Studio project. For more information, see the topic Using the block in applications for the in-process scenario.
- Add code to configure the SQL Database sink. In addition to specifying the connection string for the target database, you can configure several properties of the sink that control buffering. For more information, see the topics Choosing and using event sinks and the section “Configuring buffering” in the topic Performance considerations.
- If you choose to use the out-of-process approach:
- Install the Out-of-Process Host service application. For more information, see the topic Installing and running the Out-of-Process Windows Service/Console Host.
- Configure the Out-of-Process Host to use the SQL Database event sink. In addition to specifying the connection string for the target database, you can configure several properties of the sink that control buffering. For more information, see the topics Using the trace event service for the out-of-process scenario, Choosing and using event sinks, and the section “Configuring buffering” in the topic Performance considerations.
- In Windows Explorer, open the subfolder containing the command file CreateSemanticLoggingDb.cmd.
- If you installed the application block and the SQL Database sink from NuGet, the scripts and the command file are in the folder \packages\EnterpriseLibrary.SemanticLogging.Database.[version]\scripts within your Visual Studio solution.
- If you downloaded the Out-of-Process Host from the CodePlex community site, the scripts and the command file are in the root folder where you extracted the files from the zip file.
- Edit the command file and scripts as required. The CreateSemanticLoggingDb.cmd command file executes two SQL scripts named CreateSemanticLoggingDatabase.sql and CreateSemanticLoggingDatabaseObjects.sql. By default, these scripts create a new database named Logging in the local SQL Server database, and add to it all of the tables and stored procedures required by the SQL Database sink to write log entries to a database. If you want to use a different server or database, specify this in the command file and the SQL scripts. For example, to use the default database server on a remote machine, you might use something like MYSERVERNAME, tcp:10.0.0.1, or myserver.mydomain.com. For more information, see the topic sqlcmd Utility on MSDN.
- After you are sure that the scripts contain the required settings, execute the CreateSemanticLoggingDb.cmd command file. You may need to do this in the context of an account that has administrative permissions within the database when using a database server other than the default local server.
- Ensure that the account you will use to run your application or the Out-of-Process Host has the required permissions to execute the stored procedures in the logging database.