How To Configure the SQL Collector (version 23)

Overview

Canary does not 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 so that it is not endlessly growing.  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 are not 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] [bigint] IDENTITY(1,1) NOT NULL,
             [TagName] [varchar](255) NULL,
             [Date] [datetime] NULL,
             [Value] [real] NULL)

If the Value column would contain string values, make the Value column a varchar(n) field.

CREATE TABLE [dbo].[DataHistory](
             [HistoryKey] [bigint] IDENTITY(1,1) NOT NULL,
             [TagName] [varchar](255) NULL,
             [Date] [datetime] NULL,
             [Value] [varchar](255) NULL)

When populating the table:

• for Boolean values, use the strings 'True' and 'False', or 'true' and 'false'
• for all other values use the string representation of the value

The SQL Collector will attempt to convert the string in this order:

1. Try to convert to a Boolean
2. Try to convert to an integer
3. Try to convert to a float
4. Try to convert to a double
5. If value is null, then log a NoData in the historian
6. Log string as string if none of the conversions succeeded

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] [bigint] 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.

1. Open the Canary Admin and navigate to the SQL Collector tile.
2. Click on the 'Configuration' tab at the bottom of the screen.
3. Click the 'New' button at the top.
4. Once the configuration is entered, click the 'Create' button 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: