Almost Realtime Data and Web application

Question

I have a computer that is recording 100 different data points into an OPC server.

I've written a simple OPC client that can read all of this data.

I have a front-end website on a different network that I would like to consume this data. I could easily set the OPC client to send the data to a SQL server and the website could read from it, but that would be a lot of writes.

If I wanted the data to be updated every 10 seconds I'd be writing to the database every 10 seconds. (I could probably just serialize the 100 points to get 1 write / 10 seconds but that would also limit my ability to search the data later). This solution wouldn't scale very well. If I had 100 of these computers the situation would quickly grow out of hand.

Obviously I am well out of my league here and I have no experience with working with a large amount of data like this.

What are my options and what should I research?

Answer 1

Given the amount of data you've proposed, putting all 100 data points into a single record seems like a reasonable approach.

That said, most systems of this type use a binary file format to store the real-time data, and then post-process it later for analysis. Systems of this type can process real-time data in the megabits per second range.

Have a look at this White Paper:

http://www.microsoft.com/casestudies/ServeFileResource.aspx?4000003362

It explains how McClaren Systems uses the FILESTREAM type in SQL Server 2008 to manage high-throughput telemetry from Formula One race cars.

Answer 2

While its a good idea to look ahead for possible obstacles an application may face down the road, you shouldnt be fixing problems that you dont know you'll have.

My advice is to proceed without over complicating things. Send the data to the sql server, and query it as you would normally. Make sure your classes are abstract enough that future changes to the storage system wont require major coding changes across all levels of the application. If and when you notice the server is having problems keeping up, then start looking at options to change how data is written and retrieved. Because the reality is a properly tuned database server can handle a significant load, 24x7, without missing a beat.