Uploaded File Storage Options/Comparisons

Posted on 2006-07-19
Last Modified: 2013-11-15
Hello all.  I haven't been around EE for a while, but was active a few years ago.  Anyway, I've done some searching on the following and I felt it would be good to have one discussion that covered the pros and cons of different approaches for storage of files uploaded to a web server.  So, here's the situation...

1.  All users who upload/view files are registered.
2.  Users can upload files which can be accessed by themselves and other registered members (no public access to the files).
3.  Directory level security is not possible, ie, restrict access to a directory by user account

Question: What is the best approach for storing the files?  The two that I understand are (1) store the files in a directory or (2) store the files in a database in binary format.

What are the pros and cons (good points and bad points) of each approach?  What is most widely used?

Thanks in advance.  Ultimately, I'll be implementing this in ASP.NET 2.0, but I believe the audience to discuss this is much wider than that subset of developers/experts.

Thanks.  I'm looking forward to helpful comments/answers.
Question by:mandhjo
  • 2
LVL 19

Expert Comment

ID: 17141648
Hi mandhjo,

Generally I avoid storing files in the database.  I do however frequently store file information including the path to files in a database.  I do this so that I have greater flexibility in the storage locations of my files.  I prefer to keep the database as small as possible and let the file system handle files.

But to address your question as fully as possible:

Storing Files in the Database
   Security.  - You must be authenticated to the server to access the files, since you specified that directory level security is not possible, this provides some protection against those that have physical access to the server.

  Backup - I'm actually not 100% sure about this, but I belive that when you backup the database it will backup the embeded files.  I haven't tested this, but can see no reason why this would not be the case.   This could also be considered a CON though as this will increase the size and time of your database backup.

  Replication - You can replicate the database, including the files to another SQL server.  This gives you a very nice safety net if something happens to your database or file system.  You could of course do this seperately for the files and database using the file system for the files, but in my opinion it would be difficult to guarantee that they stay in sync.

  Portability - You can move the database to another server or network and all of the files move with it.  You don't have to worry about moving the files seperatly.

  Database Size - The database size will be increased by the size of the image files.  If you are storing large numbers of files this could become an issue

  Backup - As mentioned in the PRO's section the larger database size could cause backup time and media to be difficult to maintain.

  Flexibility - I could be wrong here and will be happy if someone points out that I am, but in my understanding it is not possible to split a database between multiple storage units.  If you have a large number of files (or even a few very large files) this could cause problems with space on the SQL server.  An example here is one system I operate has a bit over 4 TB of image files currently.  Those image files are split up over several RAIDs and have Backups on other systems which also use multiple RAIDs.  Each RAID constitues its own file system therefor in a Windows Environment a seperate drive letter (we actually use linux for these systems but the point is the same).  None of these is the same as the SQL server.  This allows me a great deal of flexibility in storing files and choices of hardware (for example I don't need to purchase a single 4+TB server, or an expensive SAN to store these images)

  Performance - I've heard a lot on both sides of this issue and I haven't run any significant testing of my own so here I'm more parroting the opinions of others than what I have solidly tested.  That being said the concensus of the articles I have read on this issue appear to state that having the SQL server process the data for the images provides a performance hit.  How big of a hit is dependant on many things:  The number, type and size of files being stored.  The relative performance of the computer storing the files in a file system method vs the performance of the SQL server.  The utilization of the SQL server.   I'm sure I've left out lots of variables that help determine performance.

  Ease of Use - It is generally considered easier to access files that are stored in a file system rather than to have to stream them out from the SQL server.  This is a bit more subjective though.  I don't think that it is all that difficult to stream a file from the SQL server and frankly often stream files even when they are on the file system, but if the images are being used in web pages it is certainly easier to use them in the case of something like an <img> tag.



Author Comment

ID: 17141741
Thanks, Neal.  I appreciate your very thorough comments and will reward you.  I'd like to leave the question unanswered at this time so as to peak the curiousity of other experts.

One additional comment, the files aren't limited to images...could be anything (which I recognize brings up additional concerns from a hacking perspective).

One follow up question, how would you go about "securing" the file locations?  One potential solution would be to:

1  have one big fat directory
2  turn off browsing/listing
3  have the default doc re-direct them elsewhere
4  hash up the file names and only serve up the ones that the user has access to (using the db data)

Can you think of another solution or add depth to what I've suggested above?  Thanks.
LVL 19

Accepted Solution

nschafer earned 250 total points
ID: 17141918

I store the files in a location that is not accessable from the Internet.  The users can get a list of available files from a database table, or you could use the File System Object to get a list of files.  I think that database method is a bit more efficient.  When the user chooses to download a file I can either use the File System object to put a copy of that in a location available to the web and redirect his browser there, or as I usually do, stream the file from it's location to the user using the ADO Stream object.

By storing the list of files in a database rather than listing them with the File System Object, you can also specify what users have access to what files and only show the users the files they have access to.


Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System ( introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
How to fix error ""Failed to validate the vCentre certificate. Either install or verify the certificate by using the vSphere Data Protection Configuration utility" when you are trying to connect to VDP instance from Vcenter.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now