Storing a file in a database as opposed to the file system?

Question

Generally, how bad of a performance hit is storing a file in a database (specifically mssql) as opposed to the file system? I can't come up with a reason outside of application portability that I would want to store my files as varbinaries in SQL Server.

Solution 1

Have a look at this answer:

Storing Images in DB - Yea or Nay?

Essentially, the space and performance hit can be quite big, depending on the number of users. Also, keep in mind that Web servers are cheap and you can easily add more to balance the load, whereas the database is the most expensive and hardest to scale part of a web architecture usually.

There are some opposite examples (e.g., Microsoft Sharepoint), but usually, storing files in the database is not a good idea.

Unless possibly you write desktop apps and/or know roughly how many users you will ever have, but on something as random and unexpectable like a public web site, you may pay a high price for storing files in the database.

Solution 2

If you can move to SQL Server 2008, you can take advantage of the FILESTREAM support which gives you the best of both - the files are stored in the filesystem, but the database integration is much better than just storing a filepath in a varchar field. Your query can return a standard .NET file stream, which makes the integration a lot simpler.

Getting Started with FILESTREAM Storage

Solution 3

I'd say, it depends on your situation. For example, I work in local government, and we have lots of images like mugshots, etc. We don't have a high number of users, but we need to have good security and auditing around the data. The database is a better solution for us since it makes this easier and we aren't going to run into scaling problems.

Solution 4

What's the question here?

Modern DBMS SQL2008 have a variety of ways of dealing with BLOBs which aren't just sticking in them in a table. There are pros and cons, of course, and you might need to think about it a little deeper.

This is an interesting paper, by the late (?) Jim Gray

To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem

Solution 5

In my own experience, it is always better to store files as files. The reason is that the filesystem is optimised for file storeage, whereas a database is not. Of course, there are some exceptions (e.g. the much heralded next-gen MS filesystem is supposed to be built on top of SQL server), but in general that's my rule.

View more solutions

Comments

[Dave Van den Eynde]

I don't see anywhere mentioned that this "file" needs to be written to disk and read again.

[Dave Van den Eynde]

I'm having some reservations here. Specifically the scalability and availability side of things: how do you control where those "blobs" are stored?

[Jon Limjap]

This is an implicit task when images have to be displayed afterward, especially when stored in different formats or in scenarios where they can't be held in memory for lenghty periods of time, by virtue of sheer size.

[Michal Sznajder]

Scalability and availability seem to have been pretty well thought through - see this whitepaper: msdn.microsoft.com/en-us/library/cc949109.aspx

[Sven Grosen]

One caveat to this is that you have to use integrated security (i.e. Windows authentication) when connecting to the database: blogs.msdn.com/b/psssql/archive/2008/04/10/…

[ubiquibacon]

What do you consider a small or large file?