Overview

Canary doesn’t like reading from production tables (for various reasons) so we use a queue table that contains rows ready to be processed.  Once we process those rows we remove them from the table resulting in the table not growing endlessly.  This table can either be populated with a trigger on another table that makes a “copy” of the data for us into our table or you can have a SQL job that would periodically populate the table if triggers aren’t permitted.  The collector is a WCF windows service that runs as Local System by default which likely would not have sufficient permissions to the SQL database.  Therefore, Canary prefers a SQL service account be supplied in the configuration to allow read/delete or alternatively the service needs configured to run as a service account that has the necessary permissions.

SQL Server Setup

The queue table requires the following columns at a minimum - an auto increment column is a must along with tagname, timestamp and value columns. A sample table may look like the following:

CREATE TABLE [dbo].[DataHistory](

              [HistoryKey] [int] IDENTITY(1,1) NOT NULL,

              [TagName] [varchar](255) NULL,

              [Date] [datetime] NULL,

              [Value] [real] NULL

The schema of the table can be extended if there are metadata/properties that need to be attached to the tags being consumed.  This simply requires additional columns to be added and then configured.  For example if a description property is needed the schema would change to the following:

CREATE TABLE [dbo].[DataHistory](

              [HistoryKey] [int] IDENTITY(1,1) NOT NULL,

              [TagName] [varchar](255) NULL,

              [Date] [datetime] NULL,

              [Value] [real] NULL,

[Description] [nvarchar](max) NULL

Canary Admin Setup

Once the SQL server portion of the setup is complete, then the connection to the server/database needs to be defined within the Canary Admin.

Choose the SQL Collector Tile -> Click on the Configuration tab on the bottom of the screen -> Click on the New button in the top center of the screen.  Once the configuration is entered click the Create button in the top middle of the screen to establish a connection to the SQL server and begin collecting data.

The following is an example of a configuration to match the above schema which includes a Description property: