1

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?

staterium
  • 1,980
  • 2
  • 20
  • 32
  • 1
    If I understand this correctly you've got a lot of microsoft software applications which all have databases hosted in a Single microsoft SQL Server, the system is getting "cranky". I'd see an ESB implementation on top of everything, even though you only have one technology. This would help you distribute data better between the apps, but without more SQL Server instances and at least two server machines, there is now way to make this work. Reporting should run on a warehouse, a smart one for your case, other apps should be prioritized and organized depending on their level of "real timing". – Catalin Marin Apr 12 '11 at 08:04
  • @Catalin: Not just a single server - a single database, and in some cases a single table. This will be properly refactored in the rewrite. – staterium Apr 12 '11 at 08:07
  • I am currently working on the rewriting of an old software from desktop to web, from Delphi to PHP. Business handled 100% by stored procedures. If you could give me some details regarding the applications that you currently have and their use, I might be able to help. – Catalin Marin Apr 12 '11 at 08:55
  • have you taken a look at the existing SDK for OPC UA? – Devendra D. Chavan Apr 12 '11 at 13:50

2 Answers2

1

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

user213702
  • 707
  • 6
  • 3
  • Biztalk server is probably a bit over the top, nevermind the cost implications. We are however designing a publish/subscribe solution, possibly using http://pubsub.codeplex.com/. – staterium Apr 29 '11 at 08:08
0

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.

Gavin Coates
  • 1,366
  • 1
  • 20
  • 44