Industrial Automation Tech Note 28 - TNIA28
Abstract:
This document describes configuring the SQL Sync feature in Crimson® 3.0, as well as installing SQL Express for testing and demonstration purposes. A properly installed memory card is required for data logging and SQL.
Products:
CR3000 / G308 / G310 / G315 / DSPZR / CSMSTRZR / PTV / Graphite® HMI / Graphite® Controllers
Use Case:
SQL Sync Synchronizing data logs to a Microsoft SQL Database.
Required Software:
Crimson 3.0 / 3.1
Required Firmware:
Crimson 3.0 Build 418+
Background
The Red Lion unit uses the Tabular Data Stream (TDS) protocol to communicate to the SQL Server via TCP/IP. The current SQL Sync configuration does not allow for connections to named instances, log files can only synchronize to the default instance. In order for the Red Lion unit to be able to establish the connection to the server, the PC running the server needs a rule in place for its firewall to allow traffic going to the defined TCP port to pass through (the default port is 1433). Another option, although less desirable, would be to disable the firewall.
SQL Server Configuration
1. Open SQL Server Configuration Manager.
2. Referring to Figure 1, in the left pane, expand SQL Server Network Configuration.
3. Select Protocols for MSSQLSERVER.
4. Double-click TCP/IP in the right pane to open TCP/IP Properties.
5. Set Enabled to Yes to enable the TCP/IP connection to the server.
Figure 1.
After clicking Apply or OK, a popup informs you that the changes will not take effect until the service is restarted. In order to restart the service:
1. Click on SQL Server Services in the left pane of SQL Server Configuration Manager.
2. Referring to Figure 2, in the right pane, find the item named SQL Server (MSSQLSERVER).
3. Right click it and choose Restart.
Figure 2.
Creating a Database
The Red Lion unit needs to be provided the name of the database that it will be writing to, so you will need to create a database.
1. Open Microsoft SQL Server Management Studio.
2. Connect to the server.
3. Referring to Figure 3, right-click on Databases.
4. Choose New Database….
Figure 3.
5. Give the database a name and click OK.
CRIMSON 3.0 CONFIGURATION
1. Click on SQL Sync listed under Services in the Communications section of Crimson 3.0.
2. Enable SQL Sync.
3. Fill in the Database Connection parameters.
*Example of database connection parameters seen below in Figure 4
Figure 4
a. Server: IP Address or name
b. TCP Port
c. Logon Username (Please note that with the latest version of Crimson 3.2.1032 the username requires the @server_name after the user_name as seen in Figure 4 where highlighted)
d. Logon Password
e. Database Name
4. Select the desired Table Configuration settings.
a. Table Name Prefix is optional
• Use when identically named logs from separate Red Lion units will be synchronized to the same database
• The table name prefix will be prepended to the log file name to create the SQL Server table name
b. Primary Key Type
• Timestamp: uses the time and date column of the log as the primary key
• Auto-Increment: creates a unique number for each entry for use as the primary key; this setting should be selected when synchronizing triggered, event, or security logs
5. Select Log Directories to be synchronized to the server.
6. Select if Security and Event logs should be synchronized.
7. Configure Data Synchronization options.
a. Manual Sync
1)No: Sync based on time
a) Sync Hours / Sync Minutes: configure only one to select how frequently the logs are synchronized
b) Sync Offset: Sync Hours and Minutes are executed based off of the real time clock, the offset adds a delay allowing multiple units to stagger their connection to the server
2)Yes: Sync occurs when ForceSQLSync function is executed
a) Fire Triggers: adds FIRE_TRIGGERS hint to SQL insert bulk command
b) Check Labels: compares column label with tag labels to generate the correct SQL column type; choosing Yes allows for the proper column types to be created when the tag label does not match the tag name.
c) Record Log File: used for troubleshooting purposes and should NOT be left on during normal operation
SQL Sync Execution
At the time of the Sync period (hours or minutes) and offset, or when the ForceSQLSync function is called, the SQL Sync service attempts to connect to the SQL Server via the configured IP and TCP/IP port. The messages sent to the SQL Server use the TDS protocol specified by Microsoft. Once connected, a logon request is sent to the SQL Server using the configured username and password. After connecting, the service scans for all logs in the “LOGS” directory on the CF card. For each log, the service processes any log files that have been updated or newly created since the last time the service executed. The service creates a timestamp file named “TIMEDATE.BIN” in each log directory to record the timestamp of the last record processed for that log. The service automatically identifies the table name and column names from the log file. The table name consists of the configured table name prefix (if any) prepended to the log name using an underscore, „_‟ . The service attempts to create the table automatically in SQL Server if it does not already exist. Column names correspond to the tag names at the top of the log file. The column type is determined by the type of the tag in the Crimson 3 runtime. The tags therefore must continue to exist in the C3 database while executing. Any tag that does not exist defaults to a SQL Server data type of VARCHAR(256). Also, modifying a C3 database by changing the tags in a log without renaming the log itself will cause the SQL Sync to fail for that log, since the column names for the table will have changed. You must delete the existing table in the database to fix this issue. Column names containing „.‟ or „[„ will be replaced with a „_‟ . Ending „]‟ will be omitted. So, for instance, an array tag of “Array1[0]” will be converted to a column name of “Array1_0”. The service then attempts to extract rows of data from the log‟ s CSV files. Each row in the CSV file becomes a new row in the SQL Server database table for that log. Note that the date and time columns are combined into a single date/time column in the SQL Server table. After all logs are processed, the SQL Server connection is closed.
SQL Sync Log File
If the Record Log File setting is enabled, the SQL Sync service creates a log of its actions on the CF card and of any errors it encounters. The log file is called “SQL.LOG” and appears in the root directory of the CF card. This feature can be useful in debugging issues with SQL Server synchronization. Note however, that there will be slight performance degradation because of the extra writes to CF. The log file shows; the log processed, each file processed, the last timestamp previously processed for that log, the column names and data types, and the commands issued to create the table and transfer the data. All log entries are time stamped. Errors appear with the word ERROR in front, the internal function name in brackets and the error message. For example, in the case of failure to connect to the SQL Server, the error appears as follows: “2011/05/02 13:56:32 ERROR: [ProcessAllLogFiles] Error Opening Socket”
SQL Sync Functions*
ForceSQLSync
This function forces the SQL Sync service to run immediately and transmit log data to the configured SQL Server. This only works if the “Manual Sync” property of the SQL Sync service has been set to “Yes”.
GetLastSQLSyncStatus
This function returns the status from the last time that the SQL Sync Service attempted to synchronize data logs with a SQL server.
GetLastSQLSyncTime
Returns the last time that the SQL Sync Service synchronized with a SQL server since the system started up. The returned value is suitable for formatting using the Crimson 3 time manipulation functions. Until the service attempts to synchronize, all three request types return 0, which represents January 1, 1997.
IsSQLSyncRunning
This function returns whether or not the SQL Sync Service is currently attempting to synchronize data logs with a SQL server.
* See the Crimson 3 Reference Manual for further information on these functions
Installing SQL Express
SQL Express is a free (limited) version of SQL usable for demonstration purposes, and can be downloaded from: http://www.microsoft.com/express/Database/. Most of the default configuration options can be left unchanged, but there are two items that need to be edited in order to allow the SQL Sync to properly execute.
1. SQL Express does not install as the default instance, which is required in order for the Red Lion unit to sync with the server, as we cannot sync to a named instance. There is a check box that gives the option to choose a named or default instance.
Figure 5.
2. Authentication method: Red Lion requires SQL Server authentication. Select the Mixed Mode option and specify the password for the administrative account.
Figure 6.
Disclaimer
It is the customer's responsibility to review the advice provided herein and its applicability to the system. Red Lion makes no representation about specific knowledge of the customer's system or the specific performance of the system. Red Lion is not responsible for any damage to equipment or connected systems. The use of this document is at your own risk. Red Lion standard product warranty applies.
HMS Technical Support
If you have any questions or trouble, contact HMS Technical Support by clicking here.