Hello,

I have a question regarding the best way to design my application when it comes to storing files.

If I use a web server then I have to worry about sercurity risk, if I use a database then I am told it will put more stress on the server.

I am thinking about going with the database, but what are the advantages of using one instead of the web server?

Thanks,

xyz789

How big are the files?  Are they binary?  How would you store them in the database, as text?

I think life will be much easier if you store them as files on the web server.  Security needn't be an issue, just put them into a directory that no one has access to except the userid that your app runs under (ie, use impersonation to run run as a system account, change the ACL on the folder where the files are to go so only the system account has access to the folder)

Thank you dbland07666 for responding back,

Well the files shouldn't go over 4 mb, and yes I would save them as binary using a file handler to interrupt them back to the user when viewing.

Actually, I have the code working to store files to a database, I am just trying to understand the difference between using a web server and a database.

Why would one way be better then the other? 

I'm just a little wary when you give write permission to the web server because someone could save a file to the site that would redirect the application to whatever or wherever. Even if we use authentication, there is still the fact that someone could possibly hi-jack site. 

With the database there is 3 columns required for input, the fileId, fileName, and fileBytes. With the web server there is just 1 folder that can hold a file up to 4 mb, so you are at greater security risk using a web server. Especially if I increase the file storage to something greater than 4 mega bites like to 10 per say.

What version of SQL server are you running?  2005 has great improvements in this area, allowing storing binary data without the performance issues of SQL 2000.

Well, I'm no expert in this -- sounds like you're uploading pictures.  I really don't feel comfortable giving the pros and cons in any depth but I would say that you can secure the web server pretty easily.

I imagine that the photo sharing sites use a database, but I don't know.

 If volumes were small I would go with the files, just because it seems easier to me

Does your environment use multiple web servers (web farm or web cluster)? If so do you replicate the files to all servers? I had issues where the files wouldn't replicate fast enough so certain requests for the same file returned different results.

I switched to a database to avoid this issue.

Hello sswanner1,

Yes I am using SQL 2005 with the data type Varbinary(max). So far it is working great!

What were your experiences working with a web server vs. the database?

 Do you have a preference when it comes to storing a lot of files?

Thanks,

xyz789

Hello Bobby S,

We do use multiple web servers, and this is good to know if I decide to copy files to all our servers. 

Thanks to you now I will keep the database. Have a great day!

Regards,

xyz789

Thank you David for the advice!

xyz789

xyz789,

It really depends on what the files are for and how they will be referenced.  I was building a helpdesk application where the files were directly related to a primary key of a table, so it made sense to just store the file in the DB.  Also, transferring the application is extremely easy, because all you have to do is move the database, instead of worrying about transferring directories and folder permissions, etc...  The biggest reason NOT to use the DB is for performance reasons, but 2005 really did a good job of eliminating that problem.

I am curious to know now what type of performance issues could I expect if we did use SQL 2000?

If you don't mind sharing your experiences with me.

Thanks sswanner1

Here's an article that discusses how BLOB's are stored in SQL 2000 server.
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx?mfr=true

Here's an article relating to how SQL server 2005 deals with large binary objects and how better performance is achieved
http://msdn2.microsoft.com/en-us/library/ms189087.aspx

Glad to be of assistance =)

by any means it all depends on your application business needs

 Storing Data in the Database vs. Storing it in the File System, it better to use file system than database for the following reasons

• It requires less work - storing and retrieving binary data stored within the database involves a bit more code than when working with the data through the file system. It's also easier to update the binary data - no need for talking to the database, just overwrite the file!
• The URL to the files is more straightforward - as we'll see in this article, in order to provide access to binary data stored within a database, we need to create another ASP.NET page that will return the data. This page is typically passed a unique identifier for the record in the database whose binary data is to be returned. The net result is that to access the binary data - say an uploaded image - the URL would look something like http://www.yourserver.com/ShowImage.aspx?ID=4352, whereas if the image were stored directly on the file system, the URL would be more straightforward, such as: http://www.yourserver.com/UploadedImages/Sam.jpg.
• Better tool support for displaying images - if you're using ASP.NET 2.0, the ImageField can be used in the GridView or DetailsView to display an image given the path to the image from the database. The ImageField, unfortunately, will not display image data directly from the database (since it requires an external page to query and return that data).
• Performance - since the binary files are stored on the web server's file system rather than on the database, the application is accessing less data from the database, reducing the demand on the database and lessening the network congestion between the web and database server.

i get these information  from this link please check for more details http://aspnet.4guysfromrolla.com/articles/120606-1.aspx