1

I am working as database developer (intern), and I have to find out the best way to store image files in SQL Server 2014. Could anyone suggest the best solution?

I have found that if the file size is less than 256KB, it's better to store it in the database; if it exceeds 1MB then it's better to store in filestream, and just store the path in the database.

My research is still on to find the way which is better in terms of performance, security. If you have any idea please share.

  • 2
    What does "best" mean? Also, this debate has happened literally hundreds of times. Have you done any research of previous conversations here and elsewhere? Do you have an interesting aspect of your problem that isn't covered in those previous conversations? Re-hashing a debate that has no clear, right answer is not a very good fit for Q & A. – Aaron Bertrand Mar 6 '16 at 23:44
  • Hi Aaron, I am new to all this. But I have found that if file size is less than 256k then its better to store in db but if exceeds 1MB then its better to store in filestream and just store the path on db. My research is still on to find the way which is better in terms of performance, security. If you have any idea please share. Thanks. – Bijendra Yadav Mar 6 '16 at 23:56
  • Here is an SO thread which explain whether to go for blob or not. stackoverflow.com/questions/5613898/… – JSapkota Mar 7 '16 at 3:47
  • Have you looked at FileTable (msdn.microsoft.com/en-us/library/ff929144.aspx)? – Dan Guzman Mar 7 '16 at 4:03
2

I've dealt with this very issue for a number of years at the hospital where I work. For us, it really doesn't come down to size of the individual file so much, but overall size of ALL the files stored. What's disappointed me most about storing images in a database is that customer's poor forecasting for future growth. We have systems where SQL Server image databases are small but have terabytes of image data on a file share. And we have images stored in SQL where the overall size of the database is contained--like thumbnail images for a finite set of employees.

What you've mentioned with 256K versus1 MB has merit in a pure effectiveness and efficiency (in a technical vacuum apart from reality) but I've found that SQL works well enough even if you depart from this model. Even if the customer would tell you that they'd spare no expense for disk storage for SQL Server, it will cost you greatly if you're tasked with backing up databases that are terabytes in size.

For me it comes down to how much do you trust your customer or vendor of the software. Regardless of what you choose--only store the images within SQL if you:

A. Really trust your customer/software vendor

B. Can ensure a limited/finite scope of both images and size of the image data

C. If the trust level is low yet the customer/management insists on storing images in SQL, ensure they have their own SQL Server and pay continually for disk increases for databases and backups-- to isolate the explosion as much as possible

That said, as DBA I really like the idea of being able to store images if I should need to. Technically, there is not really an overreaching "best" answer. And quite often the "trust" just isn't there for me to believe that the customer/vendor knows what they're talking about enough to store images on SQL--but occasionally they do.

  • Sometimes - for example, in the healthcare realm where there are HIPAA concerns - there is an added convenience of storing files/images within the DB itself. Since it's common practice for healthcare db's to utilize encryption for data-at-rest, storing the files within the db itself means that the data is already encrypted (and presumably secure, though that's a different discussion) and does not require separate hard disk encryption to store the files. – Shoeless Mar 7 '16 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.