0

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?

1

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 PRIMARY filegroup 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.