Why do we need secondary data files in SQL Server?

Question

I always ignore this option when creating a new database on SQL Server 2005, simply because we can ignore something that we do not understand and leave it as it is. (I'm not so into DBA)

so now I am curious what it is about.

From your experience, when do you think we need to add secondary data files to my database and why do we need it?

Answer 1

Lots of cases where this can be useful - to start, for availability reasons it's always best to keep only system data in your primary data file (with Sql2k5 and up, so long as the primary data file is available, the database can be brought online, allowing you to repair/restore/etc. non-system data while having as much online as possible). Some other cases to use secondary file(s):

1. Partitioning data across multiple LUNs
2. Allowing partial/filegroup backup/restores
3. Segmenting your different read/write access types across different LUNs (i.e. sequential vs. random)