Datalogging to SQL


Thread Starter

Steven Keller

In the past have done some data logging to an Access database. I found that after awhile the database would grow to a huge size and I would have to compress it. Is the same situation true with SQL? I will be logging data, about every 10 sec. I will also be changing certain records about every 10 sec. The amount of the data that I will be logging is relatively small maybe 10 floating point numbers at a time, but it will be continuous. I need to know if I need to program my logging application to compress the database occasionally. Any suggestions would be helpful.

Steve -

I have used SQL 6.5 and 7.0 for storing process data like this for some time now. No compressing of the database is necessary, but a good maintenance plan is. If you are using 7.0, the Maintenance Plan Wizard will guide you through setting up an automatic backup schedule and basic DB maintenance. Make sure you have the SQL Server Agent enabled for your maintenance plan to fire automatically.

Anthony de la Rosa

I use AlarmSuite, Wonderware and SQL 6.5. I currently log 23 tags at 1 second interval and haven't really seriously looked at compression yet. What I have done is to write an sp to summarize the data from one of the AlarmSuite tables and set this up as a scheduled event that runs every 15 minutes -- I also have another sp that trims the entire database when the oldest data is over 2 years old. This sp runs every week and is also a scheduled event.

It's been pretty good so far -- I am running this on a Deskpro 6000 -- 200 Mhz with a 47GB drive and my database is only 20GB.

Not only does SQL not have this problem but it has a variety of maintenence options which can be automated to provide data management.

Also, for data logging to SQL Rockwell Software makes a product called RSSQL (pronouced Rascal) that designs and manages the transactions..
it will talk to any opc complient data server and any ole-db or odbc complient data base with a few exceptions..


Anthony Kerstens

The only thing I can add to that is to be aware of what version of SQL is supported by WW for the version of InTouch you are using. (Same goes for NT service packs)

If my memory serves correctly, InTouch 7.0 and 6.5 were compatible with MS SQL 6.5, but InTouch 7.1 is compatible with MS SQL 7.0. You can verify this on WW's website. Sorry I can't remember the URL, it's buried deep in the site.

Also, you may consider getting WW Industrial SQL Server, which is a modified version of MS SQL. The installation program actually first installs MS SQL, and then modifies it.

Anthony Kerstens P.Eng.

Nathan Boeger

I'm definately late in response here, but I'd like to provide an affordable option here to all looking at this type of system.

In this case, FactorySQL - would be an ideal platform. It is a program that runs as a windows service and can perform this datalogging with any PLC and most SQL databases. We highly encourage open source platforms such as MySQL and PostgreSQL.

Compression makes the most sense at the database level. Wonderware deliberately chooses to compress their data in InSQL before they store it. How can you possibly justify that when SQL databases support compressed tables - an option where the database itself seamlessly performs the compression?

I'd appreciate responses here. Does anybody have a justifiable reason to not support open source SQL databases?

Nathan Boeger
Integrator, MCSE
Inductive Automation
"Design simplicity cures engineered complexity"
Do you how many OPC servers FactorySQL will support at the same time? My facility has a three different PLC brands (not my idea).

Also, will it work with MSSQL Server?

No limit to the numbers or types of OPC servers FactorySQL supports. It's like any other OPC client.

To answer your second question, yes. I have used with MSSQL, MySQL, PostgreSQL, Oracle and Access.

Jon W. Brown

MS SQL Server and SQL Express (also MSDE 2000) handle the "housekeeping" (Automatic Database Grow and Shrink and other tasks that are manual for Access) for the database automatically. Both of these engines have larger table size limits and much better data handling capabilities than Access.

ICONICS offers the ability to log natively to MS SQL, MS DE 2000, and SQL Express in addition to Access in the TrendWorX32 product. This product supports data collection as fast as 10 MS from OPC Servers and other database engines. A variety of included features provide change driven logging, averaging, and totalization if desired. Very strong Historical Replay at the 10 MS rate is provided in the included Trendviewer component. Packages are available for different size systems, from very small systems with 75 Tags up to very large systems with 128,000 tags. More information is available at

You may also contact me directly at [email protected] if you have additional questions.

Jon W. Brown
ICONICS Strategic Account Manager