Strategy to handle an SQL Server DB with too many files (BLOBs) in it?
I look after a very similar database, currently 3TB and growing 5GB per day. 


Filestream (2008+) doesn't solve the backup/restore challenge.
Filestream performs better than LOB storage for files >1MB, so says Paul Randal's testing. It's workload dependent at 256KB-1MB and generally worse at &lt;256KB.
A big plus for Filestream in some environments is that it bypasses the buffer pool and uses the Windows system cache instead.
If you put the files on a filesystem, you lose transactional consistency with the database record. You've also added the overhead of backing up millions of individual files, which can be troublesome.


Weigh up the pro's and con's for Filestream and see if it fits in your case. In our case, we've taken a different route and opted for partitioning the database so we can make use of partial availability/piecemeal restore.

One option that wasn't available to us, which you may have, is to mark older/archive filegroups as read-only. The read-only filegroup(s) can then be backed up infrequently.

If your stuck on 2005 Standard (partitioning is an Enterprise edition feature) and you have the option of read-only for history, you could tackle this the old fashioned way.


Split your table. You could consider the active/history route or date based e.g. table per month.
Put the historic data on a read-only filegroup and back it up only when you archive further data. Ensure your users understand that this cuts backup time only. Restore might take a while when you haven't got the partial availability feature.
Create a partitioned view over the tables.


One final option (which we're considering for our 3TB blobber) is to move the file data to a document database or cloud storage (e.g. AmazonS3, Azure BLOB Storage). This does introduce the transactional consistency problem I mentioned earlier but it takes the load away from those very expensive SQL Servers.