Is it a good idea to store audio files (mp3, wav) in SQL Server as BLOBs? One of the merits appears to be saving Hard disk space. Does this have any impact on scalability or performance?
+5
A:
No, it's not a good idea :) Variable-sized fields (such as TEXT and BLOB) have all sorts of performance impacts. A better approach is store the files on disk and just maintain a reference to them in a DB table. Nowadays, hard drive space is so cheap (especially compared to CPU and I/O performance) that it shouldn't even be a concern.
D Carney
2010-01-15 15:09:12
My app stores a large number of audio files and we are facing hard disk shortage issues. Is the best practice to have a separate "file server". How do online photo sites, online file sharing sites store huge files?
2010-01-15 15:13:49
Online sites of any size will be using enterprise level storage solutions, which definately do not come under the bracket of 'cheap' but can provide incredibly performant and large storage facilities.
Andrew
2010-01-15 15:23:13
Can you recommend any enterprise level storage solutions?
2010-01-15 15:42:00
Well, audio is different than most data in that I'm guessing you'll be streaming it to users (as opposed to having them download it ASAP). Regardless, you'll need to take into consideration things such as the number of concurrent connections you want to support, the degree of caching you can expect/hope for, the throughput you want, etc. I don't think there's any easy answer.I recommend that you use Google to track down presentations made by a number of large businesses (such as Flickr or Twitter) to see how they do it (better yet, find a company that does what you're trying to do).
D Carney
2010-01-19 05:04:23
That said, if all you want is an enterprise DB (and are willing to pay for it, including the setup/maintenance) then look into DB2, Oracle, etc.
D Carney
2010-01-19 05:05:38
+1
A:
I think it depends a lot on how your application is designed. If you are going to be retrieving the audio files often (or updating them) it could be a serious performance hit as the audio files can be quite large. Why wouldn't you put them on a shared network location instead and just store the path to the file in the database? Hard drive space is cheap, bandwidth and SQL performance isn't. Unless you have a very good reason to keep them in the database (and I don't think saving hard drive space is it) you are better off storing them locally or on a shared path.
TLiebe
2010-01-15 15:13:29
I'm not sure how online photo sites, etc. store binary data but I'd be shocked if they kept it all in the database. Whether you store the audio files in the database or somewhere else, they are still going to take up roughly the same amount of space but the database just isn't the place to put them. Add a hard drive, move the app to a different server or keep the audio files on a completely different server and then just store the path in the database. You're going to kill your database performance if it's spending all it's time retrieving large audio files.
TLiebe
2010-01-15 15:26:25
A:
If you have the option of upgrading to SQL 2008 (i.e. if you're licensed for free upgrades) then FILESTREAM is the best choice - it puts "links" in the column but uses the regular file system to actually store the data.
In SQL 2005 or earlier, I wouldn't do it. I fail to see how it's going to save hard disk space, either; 5 megs is 5 megs whether it's a file on disk or sitting in a database column somewhere; the latter just makes it harder to get to, and as others have mentioned can have a negative effect on your overall DB performance. I guess there's some minor file system overhead like minimum cluster sizes, but for large files (especially audio) the effect is negligible.
Aaronaught
2010-01-15 15:22:40