Database design for handling terrabytes of data

[deeparajesh]

We want a database which have to handle incoming data from 300 devices and the same time any no(around 300) of web clients can query the DB

1.Whether to have a 300 databases (each per device) or

2.Storing more than one device data in same database but having sepearate table for each device.

3.Storing more than one device data in same database using single table differentiate by deviceid.

Reply based on follwing factors
1.Resources required
2.performance
3.Reliability

 

[sguslan]

By device, do you mean the database is used in a real time environment used for data collection from many hardware devices?

 

[deeparajesh]

Device means , a user defined software tool to insert the records into the database.

This software tool will be running in a separate maching and it uses ADO Connections to insert the records.

 

[RickCole]

I wouldn't touch option 1 due to the maintenance issues involved. Depends a little on the volume of data and the design of the tables involved to get the required performance but option 3 would be my starting point.

Rick.

 

[deeparajesh]

Can you suggest if option 3 is best for 300 devices and one device should support 1.2 million records per hour and 10000 records per 30 seconds.

For defn. of device see my previous reply

 

[RickCole]

That is a lot of records you've got on the move here. With that amount of activity going on my gut feeling would be to go for a mix between option 2 and option 1.

300 different databases is still far too high from a maintenance point of view. What I would possible be looking at would be say something like maybe 3 databases with aprox 100 tables per databases each differentiated by deviceid.

Other things you need to consider carefully here which influences your choice is the kind of backup strategy you will be using and what resilience needs to be in place.

Depending on what size your server is there may be also a good argument for having say 3 separate servers to handle say 100 devices each.

Rick.

 

[RickCole]

deeparajesh also tale a look at the following link which may help as some backgrond information.

http://www.microsoft.com/technet/tr...odtechnol/sql/reskit/sql2000/part10/c3861.asp

Rick.

 

[sguslan]

WOW ...
Sounds a lot !

I think you need a lot of planning ahead. Do not rush things without considering all aspects DB administration.

But, I would, use sql2000 distributed partitioned views to help spread the table/tables over a few servers in a few databases.
This complicates application dev a little, but ensures a good level of performance and oh, use some nice hardware, if possible, like SAN based storage...

Good luck

Salim