Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Uploaded File Storage Options/Comparisons

Posted on 2006-07-19
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

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 (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
Microsoft will be releasing the Windows 10 Creators Update in just a matter of weeks. Are you prepared? Follow these steps to ensure everything goes smoothly and you don't lose valuable data on your PC.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

609 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