Storing Files in the Database
January 7th, 2009 by Dario Solera | Filed under Community, Development, Software Design.Classic question: does it make sense to store files in a database?
Classic answer: no.
Pragmatic answer: it depends.
For the SQL Server and MySQL data providers I’m writing for ScrewTurn Wiki, I decided to store files directly in the database, for the following reasons:
- if you are using a database for the rest of the data, it makes sense to store everyting in it so you simplify backups and management
- storing everything in the database (including providers’ DLLs!) and disabling the cache (or using an appropriate cache provider), you can scale ScrewTurn Wiki using a server farm (not tested yet, but it should work)
- given that users most likely upload files via the browser, they won’t likely upload files bigger than a few megabytes – the providers, anyway, limit the file size to 50 MB
- a big file upload will hit ASP.NET maxRequestLength limit before the database limit
I have no idea whatsoever about the throughput to and from the database, I’ll have to do some tests when the providers will be complete (in a few hours, probably).
On a side note, I’m using varbinary(max) for SQL Server and blob for MySQL, thus requiring SQL Server 2005/2008 and MySQL 5.
New Posts
Dario,
I’m hoping you would make this an optional thing for the SQL Data providers. A couple of my clients connect the wiki up their main file shares and love the configuration, they also still run SQL Server 2000. I agree with your points but making it optional would be a nice way to not break existing configurations.
Of course the integrated (file-based) providers are still supported and are the default choice.
How can i do it myself?
Is there something that i can download?
hi,
which one has better performance, file storage or SQL providerrs?
Probably the file-based provider.
Erk! So many reasons why this is bad news. Database performance will be affected, files in the db can’t be indexed without extraction, larger db to backup, etc etc. Not to mention webclusters should be using a shared/central storage area for this kind of thing.
As I said, you’re not forced to use a SQL-based file storage provider. You can mix-and-match providers as you like.