Table scaling with partitions or with separate databases?

Question

Hypothesis: I have a table (named 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 blob 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, 8 core 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?

Answer 1

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.