How To Configure the SQL Collector (version 25)

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 into the Canary queue table, or a SQL job that would periodically populate the table if triggers are not permitted. The Collector is a gRPC-based Windows service that runs on the Canary Service Account 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, change the account on which the service runs that would have the proper permissions and select the Integrated Security option in the Configuration screen.

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 client 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: