Future Plan: Move all images out of the database to an smb file share and only store the path within the database.
I used to think that was the best thing to do.
Our phone system records each call and converts it to a .WAV file. As stupid as it might sound (and it is), the file is saved as a separate file and the path to the file is saved in a CallRecording table AND the .WAV file is ALSO saved in the CallRecording table as a VARBINARY(MAX).
Obviously, that's an absolutely insane and totally unnecessary duplication of data so I set out to fix the problem. In the process of trying to resolve that duplication, I compared the file-paths stored in the table to the files and, boy was I ever surprised. Almost 15% of all the calls had been lost (file was missing) and more than 25% had been moved to other places without updating the file-path in the database. I also found out that around half of the files had NEVER been backed up to tape and some of the content of the files had been wiped clean (supposedly by accident).
That's a hell of a lot of screwed up data if you consider that I have 4 years of call recordings that I have to keep for irresistable legal requirements and that I have almost a million calls' worth of data that I'm supposed to have. The great thing is, the call recordings in the database are 100% there. Not a one has been lost.
So, lesson learned. If you want to keep graphics, documents, call recordings, or other blob information 100% safe and intact with all the proper backups, etc, ad nauseum, keep them in the database where people who are truly concerned with the proper preservation of data can take care of it.
In other words, store the data in a database where a qualified DBA is sure to take good care of it.
What about the size of backups? Again, it boils down to that qualified DBA doing his/her job properly. My phone system database isn't the biggest thing, by any means, and would be considered to be relatively small by many standards. It's 280GB the last time I looked but 270GB of that is 100% static call recording data. Right now, we back the whole bloody thing up every night along with Point-in-Time log files every 15 minutes. It takes 5 hours to back it up! I'm in the process of partitioning the table by month (1 FileGroup per month, 1 file per FileGroup) and I'm setting all but the current month to "Read-Only", which really makes "Piece-Meal" restores easy if it ever comes to that as well as making it highly unlikely that anyone will accidently delete any of the calls.
Don't forget that, even if you have the Standard Edition, it is possible to partition tables even though "Partitioned Tables" isn't available there.
I've never had the chance to study the ramifications of storing blobs in files v.s. storing blobs in the database before. I used to be all for the store-in-files method before I did this study and, after the study I have recently completed, I am now convinced that the best place to store the blob data (provided it's less than the MAX datatype being used and it's NOT XML data unless you need that for audit purposes) is where the pro's of data can manage it. Long live DBAs!!!
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs