Pro & Cons of storing files(pictures) in a SQL Server for a website

Question

I'm creating an Asp.Net MVC website.

I've in the past, for heavy application, multiple layer application, used the database to store files.

But now I'm questioning myself, is this a good idea for a website? In a performance view?

It has several pros to me:

• Allows me to control easily if the connected user has the right to display the image(Required for my project)
• Permits to be sure that we have consistent data(otherwise we can have an existing file but no info in the database and the opposite
• I need a fail-over webserver, and those files will be imported from a third server, so if those files are in the database, I only need to have a working ASP.Net website and a replicated database on the failover server, no need to sync files.

But it has some cons too:

• There SOME big files(it's a minority, but it will happen), like 100-200MB, and I'm not sure it's good to have this kind of file in a database?(it's more like a question ;) )
• I'm not sure that it will have good performances?

What do you think? Is this reasonable? I searched on the Internet, but I didn't found some kinds of arguments for website. My question is mostly about FILESTREAM VS FILESYSTEM, I'm sure that FileStream is slower, but a lot? Because if it's only some percent, the gain of functionality worth it.

Answer 1

If the files are integral and actively changing part of the system and them have to be backed up along with the other data - you can store them inside the DB, but try to use the FILESTREAM fields if you use sql server 2005+ and your files are big enough - say 500k+

If the files are static content, you can store them outside with only pointers in DB. This not prevents to take into account all your custom permissions machine.

Storing and working with files inside DB is usually slower, than in filesystem, but all depends on your needs.