BIG And Rapidly Growing Database, Inexperienced Tech Guy... Me!

We're a very small company, I’m the “technical” director who has evolved enough skill in a wide variety of tasks (network setup, machine config, email systems, html, asp, database...) and then one day you notice that parts of the system are starting to get way more complex and troublesome than the layman knowledge you have can cope with... Well, I think I’ve got to that point and I need some outside help to get our system to the next level!

OK, some rough details to start with. We run a small but fast-growing vehicle tracking system that sends back a LOT of data via GPRS to our SQL 2000 Enterprise server hosted on a dedicated server in London. The physical machine is a P4 3.2Ghz Dual-core Dell rackmount with 2GB RAM and 2 x 76GB SCSI disks in a RAID 1 array. This is partitioned into a 15GB C: partition and a 51GB D: partition. The system paging file is set to be 1536MB and is on the C: partition. The server is used for everything we do... it runs Smartermail email server (only about 5 or 6 domains and a few users, hardly used at all), SQL server as mentioned, web server & the proxy software that receives incoming data from our tracking devices.

There are 9 or 10 active databases on the SQL server. 8 of them take up less than a gigabyte between them and are sparingly used. The main “active” database on the SQL server is the tracking system – and this is big... As our tracking devices send in data every 10 – 30 seconds, the database is hit with hundreds of thousands of events per day. On a weekday, some half a million rows of data are written to the main “events” table on the database. Over 7 days from 26th November to 2nd December, almost exactly 3 million rows of data were written to the events table. We undertake to hold 3 months or so of data “live” for our customers and I periodically archive data off. I’ve been too busy to archive recently and the database is holding data on the events table going back to July 1st. The physical .mdf file is just under 30GB on partition d: at present. The plan is to drop the active data stored to only 1 – 2 months, but this still leaves a 12GB .mdf file.

The worrying thing with this is that this is only 700 or so devices writing to us at present... we aim to have thousands out there soon! We are looking into how we can hugely improve system performance and look to the future. Our hosting company is recommending VMWare virtual servers and SAN storage, but I’m not entirely sure that is the best way forward.

Our non-tech MD thinks the way forward is to have one database per customer and can't understand when I tell him I think that's bad as it will create all the system tables and bits & pieces for EVERY customer if we do that, right? Also it would be a nightmare to add a new column to a table as I'd have to update every single version of the database too... I want to avoid this unless I'm missing something and this is actually the best way to go forward?

I've had someone mention horizontal partitioning to me? not sure what implications this has to coding and table naming? Or is it all one big database spread among separate servers?

Currently our server is drowning on disk access and it's only going to get worse... any suggestions or links to reading online that I can do would be great, thanks!

Carl