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.
Best Answer
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.