Solved

Uploaded File Storage Options/Comparisons

Posted on 2006-07-19
4
205 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
  • 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

708 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

12 Experts available now in Live!

Get 1:1 Help Now