ApplicationMetrics.MetricLoggers.SqlServer

An implementation of an ApplicationMetrics metric logger which writes metrics and instrumentation information to a Microsoft SQL Server database.

Setup

1) Create/Update the Database and Objects

For new installations, run the CreateDatabase.sql script against a SQL Server instance to create the 'ApplicationMetrics' database and objects to store the metrics. The 'CREATE DATABASE' statement needs to be run separately, before the remainder of the script. The name of the database can be changed via a find/replace operation on the script (replacing all instances of 'ApplicationMetrics' with a desired database name). Alternatively, the objects can be created in an existing database. In any case, the 'InitialCatalog' component of the connection string passed to the SqlServerMetricLogger class should be set to the matching database name.

For existing installations, the database schema can be upgraded to the latest version by running the UpdateDatabase.sql script against the existing 'ApplicationMetrics' database. However if upgrading through multiple intermediate versions to get to the latest, the UpdateDatabase.sql scripts for the intermediate versions must also be run in sequence.

2) Setup and Call the SqlServerMetricLogger Class

The code below demonstrates the setup and use case (with fake metrics logged) of the SqlServerMetricLogger class...

````C# var connStringBuilder = new SqlConnectionStringBuilder(); connStringBuilder.DataSource = "127.0.0.1"; connStringBuilder.InitialCatalog = "ApplicationMetrics"; connStringBuilder.Encrypt = false; connStringBuilder.Authentication = SqlAuthenticationMethod.SqlPassword; connStringBuilder.UserID = "sa"; connStringBuilder.Password = "password";

using (var bufferProcessor = new SizeLimitedBufferProcessor(5)) using (var metricLogger = new SqlServerMetricLogger("DefaultCategory", connStringBuilder.ToString(), 20, 10, 0, bufferProcessor, IntervalMetricBaseTimeUnit.Millisecond, true)) { metricLogger.Start();

Guid beginId = metricLogger.Begin(new MessageSendTime());
Thread.Sleep(20);
metricLogger.Increment(new MessageSent());
metricLogger.Add(new MessageSize(), 2661);
metricLogger.End(beginId, new MessageSendTime());

metricLogger.Stop();

} ````

SqlServerMetricLogger accepts the following constructor parameters...

Retries are implemented using the configurable retry logic functionality in the Microsoft.Data.SqlClient library.

3) Viewing and Querying Logged Metrics

A view is available for each of the 4 different types of metrics (e.g. 'CountMetricInstancesView', 'AmountMetricInstancesView', etc...). Additionally a view which consolidates all logged metrics ('AllMetricInstancesView') can be queried. Standard SQL can be used to filter and aggregate the contents of these views.

A sample of the contents of 'AllMetricInstancesView' appears below...

Logging

Its possible that ApplicationMetrics and its client application could generate metrics more quickly than a SQL Server instance is able to consume them. In these situations the number of metrics processed, and/or the time taken to process them (depending on the buffer processing strategy used) would continue to increase over time (and eventually lead to out of memory or timeout errors). If an instance of IApplicationLogger is provided to the constructor, SqlServerMetricLogger will create log similar to the following...

Processed 550 metric events in 123 milliseconds.

...each time a set of buffered metrics are processed, allowing performance to be monitored and the aforementioned situations avoided.

Links

The documentation below was written for version 1.* of ApplicationMetrics. Minor implementation details may have changed in versions 2.0.0 and above, however the basic principles and use cases documented are still valid. Note also that this documentation demonstrates the older 'non-interleaved' method of logging interval metrics.

Full documentation for the project...
http://www.alastairwyse.net/methodinvocationremoting/application-metrics.html

A detailed sample implementation...
http://www.alastairwyse.net/methodinvocationremoting/sample-application-5.html

Release History