I'm going to build a Microsoft SQL server database which could end up being more than 40 TB in size. There are some quite large tables in the DB with more about 1010 .. 11 records. Every table has typical primary key index. Also most of the tables have composite non-clustered indexes too (B-tree). In a few cases we have defined columnstore indexes.
Which of the below physical designs of the server will result in better performance1 and db management2 overtime?
Scenario 1. create one (or more) filegroup per physical drive (we only have SSD NVMe drives), and spread the heavily used tables and indexes across different drives.
Scenario 2. Bundle all drives using RAID 10 and create a single large volume. Data will be striped on all disks by the OS. In this scenario, data file (.mdf), log file (.ldf) and tempdb are all stored on a large RAID 10 volume.
Scenario 3. Partition tables/indexes and store each partition on different physical drive. I should mention that it wold be close to impossible to distribute data uniformly across all partitions because of the type of data we are dealing with.
If you think none of the above are ideal solutions; I'd love to know what would you recommend.
Many Thanks.
[1] by performance I mean lower retrieval time and higher IOPs, for a given indexing architecture
[2] by db management I mean add/remove/update data, keeping the performance, redundancy, availability ..