Click here to Skip to main content
15,867,939 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
HI,

I need to save files of different types (images, docs, xls etc.) uploaded from a hosted web application. But the issue is that I can't save those files on the same server on which the web app is hosted, rather I need to save them on the database server. I have come up with two approaches -
1. Saving the files in the SQL Database and retrieve them directly from the database only.
2. Create and host a web/WCF service on the database server to save the files in the database server's hard disk, and later on retrieve the files on client request.

Can anybody suggest me which one will be a better approach.
Posted

Depends.
I do both: some files go in the DB, other go in folders, with links to them going into the DB. Generally, what decides which way I go is size: if the files are large ==> folder. If they are small ==> database.

What you want to avoid is clogging up the DB with huge data transfers: file systems are used to that but the bigger the data, the more memory the DB server has to allocate, the more bandwidth it uses to transfer, and so forth.

If you do store them in a folder, then don't use the original file name: keep that in the DB with the file link, and give the file a temporary name - I use Guids - to avoid problems when you get two users with the same file name.
 
Share this answer
 
Comments
pulak_mj 4-Sep-14 14:30pm    
Thanks OriginalGriff, What my actual concern is that I need to store the files in a different server. Which means that to save or retrieve the files, I need to have a service to do the talking between the web application and the file server, which in turn means that the service will take its own processing time. So in that case also, assuming that the file size will be large, would it be a better idea to store the files in a file system?
OriginalGriff 4-Sep-14 14:36pm    
To be honest, if you are talking about a web based system, it's pretty much irrelevant: the transfer speed will be controlled by factors outside your control - the client bandwidth!
I don't entirely disagree with Griff (who could?), but another option for large files in SQL Server is to store the files in a database using the Filestream data type. The files are then stored in the file system by SQL Server and retrieved using SQL queries.

I always feel uneasy about giving access outside of the DMZ to a web application, except through a database connection. That also keeps my IT guys happy!
 
Share this answer
 
Comments
pulak_mj 4-Sep-14 14:33pm    
Thanks PhilLenoir, I also agree with the fact that it is not a good practice to give access to DMZ to web applications. But I have just one query that how efficient and reliable is the filestream approach? Does it help in saving and retrieving the file faster than the file system approach?
PhilLenoir 4-Sep-14 14:43pm    
Reliable, yes! Efficient: Efficiency goes up as file size goes up. Myself, I wouldn't use it for files smaller than 100MB, although there are plenty out there that do and swear by it. We have large and fast servers and storage here and I find that I'm perfectly happy with varbinary(MAX) for our typical needs where file size rarely exceeds 20MB. Where we have really large data, it's mostly spatial or time series for which we have specialized storage solutions.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900