Handling Images and file attachments in a Content Management System

Question

Assumptions: Microsoft stack (ASP.NET; SQL Server).

Some content management systems handle user-generated content (images, file attachments) by storing it in the file system. Others store these items in the back end database.

Some examples of both:

• In the filesystem: Community Server, Graffiti CMS
• In the database: Microsoft Sharepoint

I can see pros and cons of each approach.

In the filesystem

• Lightweight
• Avoids bloating the database
• Backup and restore potentially simpler

In the Database

• All content together in one repository (the database)
• Complete separation of concerns (content vs format)
• Easier deployment of web site (e.g. directly from Subversion repository)

What's the best approach, and why? What are the pros and cons of keeping user files in the database? Is there another approach?

I'm making this question Community Wiki because it is somewhat subjective.

Answer 1

I picked the file system because it made editing of documents in place easier, that is when the user edits a file or document it can be saved in the location it is loaded from with no intervention by the program or user.

Answer 2

IMO, as of right now with the current functionality available in databases, the file system is the better choice.

1. The file system has no limit on the size of the files and with content this could easily be files larger than 2 GB.
2. It makes the database size much smaller which means less pressure on memory.
3. You can design your system to use UNCs and NASs or even cloud storage where as you cannot do this with FILESTREAM.

The biggest downside with using the file system is the potential for orphaning files and keeping the database information on files in sync with the actual files on disk. Admittedly, this is a huge issue but until solutions like FILESTREAM are more flexible, it is the price you have to pay.

Answer 3

If you are using SQL Server 2008 or higher, you can use the FileStream functionality to get the best of both worlds. That is, you can access documents from the database (for queries, etc), but still have access to the file via the file system (using SMB). More details here.

Erick