We want to use SqlServer Express as a data recorder for a piece of equipment. The purpose is to store all possible data values the equipment generates for a length of time so that if a problem occurs, we can search through the data to see what happened. The data is floating point numbers, like temperatures, etc.
For example, there are 200 sensors on the equipment. Every second, we want to store the 200 sensor values. The database would be one big table with 200 columns for the sensors and each second we write a row of data. Every day, the equipment would delete data older than 30 days, so that the database doesn't grow past a certain size.
Questions:
1. Any obvious reason we can't do this?
2. 30 days * 200 values * 4 bytes/value creates a 2 GB database. SqlServer Express should be able to handle that, right?
3. The equipment is running at a customer site. If the customer has a problem, we would like to be able to say to them something like, "Retrieve 3 hours of data starting last Monday at noon for Sensors A, B, and C and email it to us." We plan to give them an application that will let them put in a time range and select which sensors; it will search the database, collect the resulting data and put it in a file to send to us. Any recommendations on what format the file be in? Text? XML? Is there an obvious format that one uses to store a chunk of data from a database in?
The number and types of sensors will be different on each piece of equipment, so we don't have a predefined table or report format, we have to create it on the fly.
Thanks in advance for your thoughts.
1. I've done this many times.
2. SQL Express is limited to a 4GB database. With indexes, your data size should fit -but may be close. You will want to index the datetime column -make it the primary key.
3. Transfer files 'should' be easy to use by the recipient. xml is good, csv is good; both are easy to create and transfer. Some folks think that xml is the panacea.
Will you be using Kepware/Linkmaster?
|||
1. Good - you give me hope!
2. Why will I want to index the datetime column? Does it make it faster to search?
3. Wouldn't XML add a lot of overhead to the size of the file?
I never heard of Kepware/Linkmaster, but I'm going to look them up right now.
|||
You indicated that you would be searching for data from a datetime range. Searching a 2GB table will be quite 'slow' without the indexing. However, you will need to examine the trade-offs, less insertion overhead in index maintenance vs. slower query responses. If the queries are a 'rare' occurrance, then you may choose to forgo the indexing and live with slow query responses.
xml does add to file size, but the resulting files can be easily opened in Excel. In your situation, where you are the only recipient of the transfer file, csv may be a good solution. (Even the Fixed field table output may work for you too.)
|||Time to create a table and play around. Thank you for your advice.
