Mass data store with SQL Server

Question

We need management 10,000 GPS devices, each GPS device upload a GPS data every 30 seconds, these data need to store in the database (SQL Server 2005).

Each GPS device daily data quantity is: 24 * 60 * 2 = 2,880 10 000

10,000 GPS devices daily data quantity is: 10000 * 2880 = 28,800,000

Each GPS data approximately 160Byte, the amount of data per day is: 28,800,000 * 160 = 4.29GB

We need hold at least 3 months of GPS data in the database,

My question is:

1, whether SQL Server 2005 can support such a large amount of data store?

2, How to plan data table? (all GPS data storage in one table? Daily table? Each GPS device with a GPS data table?)

---

The GPS data:

GPSID varchar(21),
RecvTime datetime,
GPSTime datetime,
IsValid bit,
IsNavi bit,
Lng float,
Lat float,
Alt float,
Spd smallint,
Head smallint,
PulseValue bigint,
Oil float,
TSW1 bigint,
TSW1Mask bigint,
TSW2 bigint,
TSW2Mask,
BSW bigint,
StateText varchar(200),
PosText varchar(200),
UploadType tinyint

---

I tend to use partition table. However, how to set the boundaries?

Answer 1

1. whether SQL Server 2005 can support such a large amount of data store?

Yes, Sql Server has been shown to be able to store peta bytes of information.

2, How to plan data table? (all GPS data storage in one table? Daily table? Each GPS device with a GPS data table?)

There is no need to separate the data across table. That will make data retrieval much more cumbersome in the future. You want to look at table partitioning.

http://blogs.techrepublic.com.com/datacenter/?p=139

Answer 2

Yes, SQL Server 2005 can hold that amount of data.

To be able to answer the last question we would really need to know how you were going to use the data. For instance, if you wanted to do queries on all of the data, putting it in daily tables would be a problem. You also have the problem of how would you handle the daily switch of tables?

I would initially be thinking about keeping the data in a single table that uses table partitioning and putting the historical partitions into filegroups that sit on fast disk for reads. This will make querying the data faster.