MSSQL database physical design: RAID or spreading filegroups across different physical drives? [on hold]
I'm going to build a Microsoft SQL server database which could end up being more than 40 TB in size.
Which of the blow 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 ...
We have a server with the following specs:
cpu: 2 x 12 cores
memory: 256GB
storage:
16 x SSD NVMe PCIe 6TB,
nominal read IOP (4k) 600k per drive,
nominal transfer rate 3000 MB/s per drive
OS:
linux or win server (not decided yet)
ssd performance raid database sql-server
asked Jan 10 at 8:53
Demo80Demo80
11
put on hold as off-topic by grawity, Twisty Impersonator, music2myear, LotPings, Joe Taylor Jan 15 at 11:25
- This question does not appear to be about computer software or computer hardware within the scope defined in the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
I'm going to build a Microsoft SQL server database which could end up being more than 40 TB in size.
Which of the blow 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 ...
We have a server with the following specs:
cpu: 2 x 12 cores
memory: 256GB
storage:
16 x SSD NVMe PCIe 6TB,
nominal read IOP (4k) 600k per drive,
nominal transfer rate 3000 MB/s per drive
OS:
linux or win server (not decided yet)
ssd performance raid database sql-server
asked Jan 10 at 8:53
Demo80Demo80
11
put on hold as off-topic by grawity, Twisty Impersonator, music2myear, LotPings, Joe Taylor Jan 15 at 11:25
- This question does not appear to be about computer software or computer hardware within the scope defined in the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
I'm going to build a Microsoft SQL server database which could end up being more than 40 TB in size.
Which of the blow 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 ...
We have a server with the following specs:
cpu: 2 x 12 cores
memory: 256GB
storage:
16 x SSD NVMe PCIe 6TB,
nominal read IOP (4k) 600k per drive,
nominal transfer rate 3000 MB/s per drive
OS:
linux or win server (not decided yet)
ssd performance raid database sql-server
asked Jan 10 at 8:53
Demo80Demo80
11
I'm going to build a Microsoft SQL server database which could end up being more than 40 TB in size.
Which of the blow 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 ...
We have a server with the following specs:
cpu: 2 x 12 cores
memory: 256GB
storage:
16 x SSD NVMe PCIe 6TB,
nominal read IOP (4k) 600k per drive,
nominal transfer rate 3000 MB/s per drive
OS:
linux or win server (not decided yet)
ssd performance raid database sql-server
ssd performance raid database sql-server
asked Jan 10 at 8:53
Demo80Demo80
11
asked Jan 10 at 8:53
Demo80Demo80
11
asked Jan 10 at 8:53
Demo80Demo80
11
asked Jan 10 at 8:53
Demo80Demo80
11
asked Jan 10 at 8:53
Demo80Demo80
11
11
put on hold as off-topic by grawity, Twisty Impersonator, music2myear, LotPings, Joe Taylor Jan 15 at 11:25
- This question does not appear to be about computer software or computer hardware within the scope defined in the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.
put on hold as off-topic by grawity, Twisty Impersonator, music2myear, LotPings, Joe Taylor Jan 15 at 11:25
- This question does not appear to be about computer software or computer hardware within the scope defined in the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
This will likely be closed shorty on "to broad" and "belongs elsewhere" counts, but a couple of quick pointers to help improve the question before that happens:
- This probably belongs on dba.stackexchange.com, or possibly serverfault.com, rather than here.
- You will need to be more specific about your database. What groups of data are you expecting? Do they have different performance needs (some need fast active use, some are long-term audit storage etc.)? What "given indexing architecture" are you implementing?
- Scenario 2 (using RAID) is not mutually exclusive to the other options. For production use you are likely to need RAID (1+, not RAID0) on all the volumes to allow for device failure even with shiny new high-tech drives. Except maybe if you have multi-server redundancy & fail-over configured, but even then you probably want node-level redundancy to reduce the need for such fail-over.
answered Jan 15 at 11:23
David SpillettDavid Spillett
21.8k4062
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This will likely be closed shorty on "to broad" and "belongs elsewhere" counts, but a couple of quick pointers to help improve the question before that happens:
- This probably belongs on dba.stackexchange.com, or possibly serverfault.com, rather than here.
- You will need to be more specific about your database. What groups of data are you expecting? Do they have different performance needs (some need fast active use, some are long-term audit storage etc.)? What "given indexing architecture" are you implementing?
- Scenario 2 (using RAID) is not mutually exclusive to the other options. For production use you are likely to need RAID (1+, not RAID0) on all the volumes to allow for device failure even with shiny new high-tech drives. Except maybe if you have multi-server redundancy & fail-over configured, but even then you probably want node-level redundancy to reduce the need for such fail-over.
answered Jan 15 at 11:23
David SpillettDavid Spillett
21.8k4062
add a comment |
This will likely be closed shorty on "to broad" and "belongs elsewhere" counts, but a couple of quick pointers to help improve the question before that happens:
- This probably belongs on dba.stackexchange.com, or possibly serverfault.com, rather than here.
- You will need to be more specific about your database. What groups of data are you expecting? Do they have different performance needs (some need fast active use, some are long-term audit storage etc.)? What "given indexing architecture" are you implementing?
- Scenario 2 (using RAID) is not mutually exclusive to the other options. For production use you are likely to need RAID (1+, not RAID0) on all the volumes to allow for device failure even with shiny new high-tech drives. Except maybe if you have multi-server redundancy & fail-over configured, but even then you probably want node-level redundancy to reduce the need for such fail-over.
answered Jan 15 at 11:23
David SpillettDavid Spillett
21.8k4062
add a comment |
This will likely be closed shorty on "to broad" and "belongs elsewhere" counts, but a couple of quick pointers to help improve the question before that happens:
- This probably belongs on dba.stackexchange.com, or possibly serverfault.com, rather than here.
- You will need to be more specific about your database. What groups of data are you expecting? Do they have different performance needs (some need fast active use, some are long-term audit storage etc.)? What "given indexing architecture" are you implementing?
- Scenario 2 (using RAID) is not mutually exclusive to the other options. For production use you are likely to need RAID (1+, not RAID0) on all the volumes to allow for device failure even with shiny new high-tech drives. Except maybe if you have multi-server redundancy & fail-over configured, but even then you probably want node-level redundancy to reduce the need for such fail-over.
answered Jan 15 at 11:23
David SpillettDavid Spillett
21.8k4062
This will likely be closed shorty on "to broad" and "belongs elsewhere" counts, but a couple of quick pointers to help improve the question before that happens:
- This probably belongs on dba.stackexchange.com, or possibly serverfault.com, rather than here.
- You will need to be more specific about your database. What groups of data are you expecting? Do they have different performance needs (some need fast active use, some are long-term audit storage etc.)? What "given indexing architecture" are you implementing?
- Scenario 2 (using RAID) is not mutually exclusive to the other options. For production use you are likely to need RAID (1+, not RAID0) on all the volumes to allow for device failure even with shiny new high-tech drives. Except maybe if you have multi-server redundancy & fail-over configured, but even then you probably want node-level redundancy to reduce the need for such fail-over.
answered Jan 15 at 11:23
David SpillettDavid Spillett
21.8k4062
answered Jan 15 at 11:23
David SpillettDavid Spillett
21.8k4062
answered Jan 15 at 11:23
David SpillettDavid Spillett
21.8k4062
answered Jan 15 at 11:23
David SpillettDavid Spillett
21.8k4062
21.8k4062
add a comment |
add a comment |
