asked
Data Base Design
I'm looking for better data base architecture/design to insert the >800000 records for day. My requirement is There is a more then >500 sensors continuously send the data for every second(24/7). I need to collect the data from sensors, 1. To be store in data base, 2. Need to be take the back up for every 1-2 minute, 3. Need to create the separate table for Reporting, 4. I need to create separate DB for alerts like if captured temperature is >50 deg then store data in normal dB or if <50 store in alert DB. Please give the better architecture to design DB.
To get it clear from the start - you wont get a complete database design from this forum. We are too busy doing our day jobs to spend a lot of time like that without being paid for it. If you are interested in hiring someone to advise you then that is a different sort of question. My initial thoughts go first to the hardware, you will need to buy hardware that is capable of moving large amounts of data fast and still cope with the insert rate that you mention. Buy as the best your project can afford. Go for more spindles rather than big HDD. Go for a number of CPUs (be concious of thread count if you aer going to use SQL Server 2012) that you can afford to license. Use SQL Server Enterprise Edition. We have no idea what sort of data you are working with so whether it is possible to achieve the rates that you need is a little debatable. If the inserts are small amounts of data then its wholly possible, if you are working with large rows or XML then you may find it difficult to achieve. Pay close attention to indexing, this can have considerable affects on insert actions. The choice of one database or another depending on the data being handled will be taken by some process other than SQL Server. You will need to place that logic into your application or whatever import solution you are going to use. If you mean that all data will be placed in one database and then it will be queried to identify data that is within Alert parameters then that can be achieved with a number of options. Something like Service Broker may be applicable.
9 People are following this question.
