I have a database which contains large amounts of measurements (20+ instruments, each recording 60 measurements per second, each measurement containing 10+ complex values). All measurements are stored in a single table, normalized, and indexes are used to allow filtering by instrument, time and other columns.
Needless to say, every 6 months, the database gets so large that scheduled index maintenance tasks take more than a day to complete. So far, I've had to turn off the db twice, for almost two days, to run scripts which would backup and then delete old data in order to "refresh" it.
My colleagues suggest a (logical, IMHO) modification to physically separate the database into multiple databases, by instrument and time. Furthermore, different clients will access different instruments, so keeping each instrument separated will simplify multi-tenant scenarios (providing backup to customers, migrating to other machines, etc.).
In other words, I would go from this:
Big database - AllMeasurements table
Instrument001 database - Measurements-2012-01 table - Measurements-2012-02 table - Measurements-2012-03 table Instrument002 database - Measurements-2012-01 table - Measurements-2012-02 table - Measurements-2012-03 table
Since my DAL is abstracted through the repository pattern, it would be easy to swap the underlying implementation to switch databases and tables on demand, so coding this shouldn't a problem.
But I am now wondering if this is really the best way?
Does it make sense?
[Edit] I'm using SQL Server 2008, if that's relevant.