Assuming VLDB is split into multiple file groups, each having its own file and on a separate disk.
Now let's say there is a disk crash bringing down one of the file groups – then will the other parts of the database be accessible?
Is it good practice to assign multiple disks to 1 filegroup OR create multiple file groups with independent disks?
Best Answer
It will depend on the predicate of the query you write against the partitioned table. If you filter on the column that is used as the basis of the partition function and scheme and that filter restricts the data to come from filegroups in the partition that are still online, your query can return results.
This is what makes partial database restores possible; you can restore the
PRIMARYfilegroup followed by read-write filegroups to bring 'current' data for your VLDB online and then restore the read-only filegroups.As for what's the better disk-to-filegroup strategy, it'll depend on whether you're using older rotational or newer SSD media, whether the VLDB is for an OLTP application that is IOPS-heavy or a OLAP data warehouse where throughput when scanning tables is paramount, and whether the storage is local to the host or attached via SAN. Ultimately, you'll want to try different configurations, collaborating with your hardware support team and vendor, using benchmarks like from TPC and your application's typical and peak workloads.
For a deeper dive into the methods and strategy, Microsoft's series of documents describing their Fast Track Data Warehouse architecture over the years are a good resource.