For me, this is a question of simplicity-of-design vs performance-and-scalability.
If I am designing a non-critical database that won't see a huge amount of traffic, then I typically would go with a single file per database since it is easy to configure and maintain.
However, if your recovery point objective is to be able to recover the database to within several minutes of any failure, and your recovery time objectives are to be able to recover within as short a time as possible, I would carefully plan the filegroups and associated files as:
- The main filegroup should only contain the system tables. This filegroup can then be very quickly restored or replaced if necessary without affecting business data.
- A primary filegroup to store business critical data that can be restored quickly enough to meet the recovery time objective and get the business back up-and-running ASAP. I would create as many files for this filegroup as I have separate and distinct drive subsystems on high-speed devices. For instance, if I had 3 separate SSD raid arrays, I might create 3 files to spread the load across the arrays evenly.
- A secondary filegroup to store historical yet business relevant data that is not critical to get the business up-and-running and yet still needs to be in the database for some reason. Typically this would be on one or more files, depending on performance and cost requirements.
If there is a requirement to store many file-based pieces of data, for instance images or documents, with relationships to data stored in SQL Server, I would use
FILESTREAM, which requires its own filegroup.