Real Time Data Acqusition

Question

I was just asked to assist in a new project which involves getting data in real time or near real time from PLC's into a SQL Server database.  I've never done anything like this before and couldn't find much info in BOL.

What are my options for getting continuous data into SQL Server?

We can use OLE procedures inside a SP to retrieve data from the source (RS Linx) and possibly trigger the SP every few seconds with SQL Agent but that seems a bit obtuse.  I'm thinking there must be a better way to get continuous data into SQL Server.

Thanks.

Answer 1

The easiest way is to connect from the data source to the server and send the data as it arrives.  You could use an ODBC driver, OLE DB driver, ADO.NET, etc. to connect to SQL Server and push the data up with an insert.  This assumes that the source can utilize an ODBC driver or OLE DB provider for SQL Server directly.  If this is the case, this is probably the safest option and the easiest to implement.


The other option, and one that I have used in the past for scalable, real-time data collection with a persistent DB back-end in a heterogenous OS environment is to do the following:

1.) Write a middle-tier service that provides a simple message passing paradigm for pushing updates to the server or pulling data down from the server.
       -- this can be tailored to suit your latency allowances vs data lost etc.
                    -- one latency option is to compress messages and send bulk updates to the server (e.g. motion tracking or video collection)
                    -- one lossy option is to bin messages by type and discard all but the last of that type (e.g. video streaming or positional tracking data)

2.) Connect data pumps to the message passing service.

3.) Connect the server to the message passing service.
      -- Some designs connect the service directly to the DB
      -- Others treat the DB as another client, registered as a data store.  (I prefer this one as it allows for many-to-many replication of either data sources or data stores)
      -- process running on the server with SQL Server that is a client, transforming messages into ODBC / OLE DB API calls.


This can be very easy with out of the box middleware.  I am not very familiar with all the middleware vendors, myself.  It is also not particularly hard to write in native code.  The advantage is that it runs cross-platform, allows flexibility in the number and type of back-end store as well as the number and type of data collection agents.  The disadvantage of this approach is that it has one lynch-pin -- the message passing service itself, which can limit scalability and provides a single point of failure for the whole system.



Hope that helps,

John

---

This post is provided 'as is' and confers no express or implied warranties or rights.