Solved

Uploaded File Storage Options/Comparisons

Posted on 2006-07-19
4
216 Views
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.
0
Comment
Question by:mandhjo
[X]
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
4 Comments
 
LVL 19

Expert Comment

by:nschafer
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
Pros:
   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.


Cons:
  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.



 
   

Neal.
0
 
LVL 4

Author Comment

by:mandhjo
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.
0
 
LVL 19

Accepted Solution

by:
nschafer earned 250 total points
ID: 17141918
mandhjo,

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.


Neal.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

By default, Carbonite Server Backup manages your encryption key for you using Advanced Encryption Standard (AES) 128-bit encryption. If you choose to manage your private encryption key, your backups will be encrypted using AES 256-bit encryption.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

717 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