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.

more ▼

asked Nov 17, 2012 at 06:58 PM in Default

avatar image

0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Nov 18, 2012 at 09:38 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 17, 2012 at 06:58 PM

Seen: 708 times

Last Updated: Nov 18, 2012 at 09:38 AM

Copyright 2018 Redgate Software. Privacy Policy