Design considerations for realtime OPC system

Question

We are in the process of redesigning a disjointed realtime OPC system which has proven to be cumbersome. Our technology stack is C#, .NET 4 and SQL Server 2008 R2, hosted on 32 bit Windows Server 2003. Physical archtecture currently dictates that all tiers are to be hosted on a single server, although with sufficient motivation (read: ROI) this could be increased to 2.

The basic existing architecture is:

• External OPC devices call our webservice to populate SQL with realtime data, approxomately 300 events per second. We have no control over volume or batch processing here, although in the rewrite I would like to implement batching in the web service to spare SQL from 300 insert statements per second.
• SQL is used as a central resource for various components (about 9 in total, all to be redesigned) that perform tasks ranging from alarms to reporting. This is currently the biggest problem with the existing design, in that there is no single BLL or even DAL through which all these components consume/manipulate data or govern behavior.
• Components range from Windows Services to Web Services to Windows Applications. The biggest consumer of CPU time and SQL connections is a Windows Forms application that monitors all realtime data and raises alarms as required. It also does realtime trend graphs, which is quite expensive to run.

For the rewrite there is a strong push towards WPF with which, apart from the learning curve, I have no problem. My question is more concerned with the underlying architecture:

• I am currently doing some research on how to implement a single DAL and BLL. For the DAL I am leaning towards EF or nHibernate, with Linq-to-SQL also a possibility.
• For the BLL I only have experience in CSLA.NET, and I fear this may be a bit over the top for a system in which speed and resource consumption is crucial.

Does anybody have any experience with a similiar system, and are willing to share a few lessons or design guidelines?

Answer 1

Im not sure I like the idea of having an SQL server as a central point in the system. this server is going to get hammered - every time data changes on a device, it will write to the database. Every client will then continually refresh at a constant rate to detect if there are any changes. this is going to be a lot of work for the SQL server.

The OPC protocol involves clients subscribing to a server, so they can then be notified on change of any data. Using SQL in the middle prevents this.

Would you not be far better off using/creating an OPC server to retrieve all data from the device, then allowing each client to connect to this? That way they are only recieving data when it changes, rather than having to constantly check for updates?

If you need to log for historical reasons, you can always make an extra client which then logs the data to an SQL database.

Answer 2

I have some exposure to acquiring data from OPC servers, though the applications I implemented I believe were not as large scale as yours. For my application I had a publish - subscribe architecture based messaging layer, my suggestion based on my experience then would be

1) For your real time data acquisition you would need something based on a publish - subscribe mechanism, Biz talk server is the microsoft answer to ESB. So I would look at this.
2) Does your windows forms application need to look at the database directly ? I mean can it look at an intermediate that can say look at the db for historical purposes or subscribe to the real time feed if all it cares is real time information