How To: Use a Non-SQL Server Monitoring Database

  • Article

The following procedure shows how to use a non-SQL Server monitoring database within Windows Server AppFabric. If using a SQL Server database, these views and the staging table are created by the AppFabric configuration process. With a non-SQL Server database, you will have to create these database entities using whatever functionality of the non-SQL database program allows you to accomplish this.

Configuring a Non-SQL Monitoring Database

  1. Implement the monitoring staging table and public views. Choose a non-SQL Server database specific mechanism from the database vendor to create the staging table and views. The staging table and the views with the expected types are specified in Monitoring Database Views and Tables. The actual types may be database specific, but the types must be ones that the ADO provider can translate to a .NET Framework type. The AppFabric Dashboard monitoring tools will work only if the query to the database type results in an expected .NET Framework type. For example, nvarchar in SQL Server maps to the String .NET Framework type, and the varchar and char types in non-SQL Server databases also map to the String .NET Framework type.

  2. Populate the public views by using the data in the staging table. The Event Collection service writes event data the columns of the staging table corresponding to each event emitted from a WCF or WF service. The non-SQL Server database implementation then moves the data from the staging table to tables that back the public views using whatever means is appropriate, such as a database job or trigger.  The mapping that defines how data from each event type populates the public views is documented in Monitoring Database Event Mappings. For events emitted from WCF and WF service the Event Collection service collects the events and writes the events to the monitoring database staging table ASStagingTable. The ASStagingTable table is a temporary event storage table. To consume the event data in the AppFabric Dashboard, the data from the ASStagingTable has to be exposed using the public view corresponding to an event type. The non-SQL Server database implementation has to move the data from the ASStagingTable table to tables that back the public views using an appropriate mechanism for the database implementation, such as a database trigger or a job.  The mapping that defines how data from each event type populates the public views is documented in Monitoring Database Event Mappings.

    An example of how this works and what you will need to do is as follows.   When a WCF service operation is invoked the .NET runtime emits a 214 - OperationCompleted (https://go.microsoft.com/fwlink/?LinkId=187225) (WCF operation completed) event. When that event occurs, the columns in the staging table applicable to the WCF operation completed event are populated with values from the collected event by the Event Collection service. The data from the staging table is then mapped to populate the columns in the ASEventSources, ASWcfEvents, and ASEvents views. The AppFabric Dashboard reads this data and displays it in the Tracked Events Page. For the OperationCompleted event, look at EventID 214 in the mapping document in Monitoring Database Event Mappings. This indicates how the data from the staging table for this event is exposed. In addition to the common columns written to the staging table (see the Common Mapping for All Events section in the mapping document) two additional columns are written – MethodName and Duration. These columns are what you map in an implementation specific manner into the corresponding interim tables that support the ASEventSources, ASWcfEvents, and ASEvents views.

    Note

    The Monitoring Database Event Mappings topic contains a subset of the mapping events. For a complete set of mappings, see the download from the Microsoft Download Center. This contains one mapping document for each event id. The mapping document for an event specifies the columns in the ASStagingTable that are populated when the Event Collection service writes a row for this event. It also indicates the views that expose data for this event, and how the data from the ASStagingTable is mapped to the columns in a view.

    Note

    In a SQL Server implementation of the monitoring database, the public views are built on tables that contain the actual event data. The SQL Server Provider moves the data from the staging table to the tables on which the public views are built. This job done by the SQL Server provider is what you will need to do manually in a non-SQL Server database.

  3. Ensure that the connection string for the new monitoring database is registered in the root Web.config file. This allows the Event Collection service to use this connection string to write events to the monitoring database.

  4. Ensure that the identity of the Event Collection service has read and write permissions to the staging table.

  5. Ensure that the user viewing data in IIS with the AppFabric Dashboard has permissions to read from the views.

  6. Test by running the Event Collection service:

    1. Ensure that the staging table is getting populated.

    2. Ensure that the dashboard metrics work.

    3. Ensure that tracked events can be displayed.

    4. Ensure that related events can be displayed at the End-to-End Monitoring level.

You can take the following optional steps to further assist in the configuration and increased performance of writing events to a non-SQL Server database:

  • Implement a bulk copy provider for the specific database and register it in the root Web.config file. Doing so allows the Event Collection service to invoke the provider to write events in bulk to the staging table. If a bulk copy provider is not registered, then the event collector will use batch inserts using the database-specific ADO provider. For more information, see Event Collection Bulk Copy Interface.

  • Create a custom provider for the non-SQL Server database to be invoked by the AppFabric configuration tool. This provider is different from a bulk copy provider. It is the provider that setup uses to insert into the registry the connection string for the new database for use by the Event Collection service. Again, this user-interface functionality is not required but provides a simpler way to configure the connection string of the non-SQL Server database. For more information about using custom providers, see How To: Register Custom Providers. For more information about the AppFabric configuration tool, which can be optionally launched at the end of the AppFabric installation process, see Installation Results Page and Configure Windows Server AppFabric.