Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Let's say I have a table (let's call it BigTable) which could experience 5,000,000 INSERTS per day (with possibly just as many SELECTs). Each row inserted is about 50kb.

These daily INSERTs are split across 5 clients equally (the table has a FK called ClientID). There is never a need to SELECT or JOIN data across multiple clients.

I am worried about the database performance as this table grows, so I have come up with three solutions.

SOLUTION 1:

  • Partition BigTable by ClientID
  • Store each partition on a separate hard disk on the server (using Azure blog storage).
  • Partition all data which is 1 month old (archive data, yet still need to be queryable) into another set of READONLY partitions.

Essentially this means the following partitions on their own storage devices:

  • Primary (all data excluding BigTable)
  • ClientA's BigTable (5,000,000 rows per day / 5 clients x 30 days = 30,000,000 rows)
  • ClientB's BigTable (30,000,000 rows)
  • ClientC's BigTable (30,000,000 rows)
  • ClientD's BigTable (30,000,000 rows)
  • ClientE's BigTable (30,000,000 rows)
  • ClientA's BigTable archive
  • ClientB's BigTable archive
  • ClientC's BigTable archive
  • ClientD's BigTable archive
  • ClientE's BigTable archive

The number of rows in the archive tables will be (5,000,000) x (age of DB in days) - (30,000,000). This is still a huge table, but will only be used to drawing up the odd report.

SQL Server will be hosted on a 14GB, 8core Azure VM.

SOLUTION 2:

The other option is to host separate databases for each client. This means each will have it's own dedicated SQL Server machine. Partitioning will still happen for archive data.

This option is not optimal because of the physical separation of the data. Having to manage updates to multiple databases could be very problematic. Having separate database connections for each client will also be a consideration for the developers.

Could anyone perhaps advise on these options?

SOLUTION 3:

Archive data into a faster database platform. I don't know much about this, but perhaps a NoSQL database could handle billions of records much better than SQL Server?

share|improve this question
    
Third solution added –  davenewza Feb 18 '13 at 16:36
    
What is the primary use case for your data? As in, do you want to emphasize inserts (OLTP) or reading/reporting (OLAP)? –  GalacticCowboy Feb 18 '13 at 16:47
    
@GalacticCowboy: Both. I need to optimise the table for INSERTs (like I said, 5,000,000 per day) but also for reading. The 'archived' data does not need to be quickly accessible as this will be used mainly for reports which can be generated during a batch. Otherwise all recent data needs to be very available. –  davenewza Feb 18 '13 at 18:56

1 Answer 1

up vote 5 down vote accepted

I would go with Option 2..

You most certainly don't need a dedicated SQL Server machine for each client, you don't even need and dedicated Instance. I don't know you think that is the case.

My primary reason for this is that when the time comes that you want to scale horizontally (more servers) this is going to best position you to do that.

I don't understand why you think that to "manage updates to multiple databases could be very problematic". Dealing with different Db connections is trivial, i don't understand this concern either. All of this is particular true because "There is never a need to SELECT or JOIN data across multiple clients."

Additional Note : You can still partition the individual Client DBs (by date or something) should you choose\need to do so.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.