Database setup for datalogging We're currently starting to build a version 2 of our system and one of the things we've noticed with version 1 is that we'll have quite a bit of data so we started to rethink our approach to how to store the data.

Basically we have dataloggers which send data back to the server every 5 minutes. With current system we only have few sensors as we've just been starting so we only have approx 250 loggers out there and they are generating data at a pace of 2.5gb per year or about 21M rows per year. The data is mainly just numbers like light levels, temperature, humidity etc. We expect these logger numbers to grow quite fast and we should have thousands of loggers by end of the next year. 

However with this next generation platform we'll have multiple sensors that can be used and multiple similar sensors can be connected to one system. System will have about 8 ports for sensor boards but each sensor board can have multiple sensors. As an rough example one system can have anywhere from 5 to 100 different kind of sensor inputs. ( 2-10 times as much as we currently have)

Data is only written once and then only read. 99% of the reads are to data which is maximum of two months old.

So far I've come up with few different options and I'd love to hear if someone has good insights which one might be the most scalable. 


Have a main Ping table which contains some basic data for each report and then have separate tables for each sensor board types. If all of the data needs to be shown the select would be at most 8 table join. 
Have one very wide table with all the ping data
Fully or partly use NoSQL ideas and insert most of the ping data in JSON or similar format. 


Secondly it would be interesting to hear as to what database setup would be the most scalable:


Have a production server which has maximum of 2 months data so it would be easier to keep fully in memory. Offload all historical data to secondary server. 
Have everything in one server.
Sharding?
Replication?
We are doing something very similar at my place and this very question was in my head for a while a few weeks back.

After noticing that logs were consuming 98% of our DBs in SQL Server I did some research on how we should handle this problem.

It all depends on the structure of your logs but our logs are not really relational so storing them in a RDBMS and consuming all the resources built for the RDBMS does not make sense.

I started looking into NoSQL stores and found DB Engines Ranking to be very useful to compare the feature sets of each DB engine.

Personally I found that an eventual consistency solution worked for me as it has good sharding, availability, and fast reads on huge data sets.

Check the CAP Theorem wiki entry to learn about the trade offs on different types of NoSQL solution.

We are currently testing a Cassandra deployment for our logs with MapReduce jobs.