Engineering Tip

Microsoft SQLSVR Data Purge

Technique provided by Warren Humphrey, Sega Inc.

Microsoft SQLSVR is a cost effective way to provide a true SQL engine storage for large FactoryLink installations.  Benefits of using an SQL engine include ad-hoc reporting, data backup and multi-person access of data without using FactoryLink.

FactoryLink does not provide a means for "rolling over" data in SQLSVR.   This requires periodical purging of unneeded data from the server to ensure that there is space for storing most recent data.

This procedure uses SQLSVR scheduled tasks to automate the data purge of unneeded data.

Here is a brief overview of the steps necessary to use SQL Server for Factory Link trending.

1) Install SQL Server and select both the MS SQLServer and Enterprise Manager services to start when the system boots. Note: The WindowsNT "Server" service must also be installed and configured in automatic mode.

2) Install the ODBC32 drivers. These are available on the SQL Server CD or from Microsoft.

3) Configure an ODBC32 connection (In Windows Control Panel) for each FactoryLink database you wish to put on SQL Server. (eg. Trending, logging, alarms, etc.) If security is not an issue, a trusted connection can be selected to avoid the need to configure the ODBC historian in Factory Link with username and password.

4) Configure the FactoryLink ODBC Historian. Be sure there that no mailbox tags are common to both the DB4 and ODBC historians if both are being used. In the connection string field, type...DSN=[name assigned in ODBC32]. If trusted connection is not used, you must also supply the server name, username, and password in the connection string field. The FactoryLink manuals do not indicate the MS SQL Server syntax for this.

5) In SQL Server, first register the server before configuring a new database device under which the FactoryLink databases will reside. Also configure a new database device for transaction logs. Next configure each of the new FactoryLink databases. Be sure to follow the SQL manuals recommendations on space requirements for both the data and transaction log. IMPORTANT!!! When first defining a new database, be sure to specify a database device for the transaction log. Under the Options tab, select "Truncate log on checkpoint". Otherwise, your transaction log will fill up and stop logging new data.

6) In FactoryLink, create an IML procedure that will build a text string which is one of the valid MS-SQL Server datetime formats. Put this newly created tag (eg. TIMESTMP) in all log tables, but do not index on this field. Log it as a CHAR field.

7) To use SQL Server for trending, you must create scheduled tasks to delete old records and make room for new ones. A separate scheduled task is required for each table to be used for trending. Use something similar to the following T-SQL command when creating these scheduled tasks.

DELETE FROM tablename WHERE TIMESTMP < CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)

NOTE:  Be careful when selecting a datetime format as not all formats can be used for chronological comparison as a character field. One example of a datetime format that works would be yyyymmdd. This is MS-SQL format 112 in the MS-SQL Server manual. This format assures that the < and > operators will work correctly (or chronologically) on this character field.

Thanks to whumphrey@segainc.com