Sql-server – Splitting a database into multiple databases

sql-server-2008

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

to this:

  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.

Best Answer

If the problem is this single table getting huge, you should consider partitioning it (by date, since your data seems to be time-based).

This way the SQL engine query planner may be able to ignore some partitions (if they contain data too old to appear in your query results) when querying and thus perform better.

Additionally, partitionning will be handled transparently by your database engine, so you have nothing to change in your code.

Another advantage would be that you can imagine to move the 'old' partitions, containing data that is rarely accessed, to slower (and cheaper) storage.

You can also set up your indexes to be partitioned so you can only rebuild a part of an index: http://msdn.microsoft.com/en-us/library/ms187526.aspx

Index partitioning is a different feature from table partitioning, it can avoid you to have to rebuild an entire index if it's not needed.

Related Question