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.
- 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(5,000,000 rows per day / 5 clients x 30 days = 30,000,000 rows)
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.
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?
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?