We have a ASP.NET MVC project with AngularJS and Entity Framework database-first, a news portal already in production for about a year. The project is mainly focused on providing content for users (articles, news and etc) and of course there is a lot of images. Currently we are storing the images in the File System and in SQL Server we store only the path.
The site today has something like 2000+ images (every day a lot of new images are uploaded so this number can grow very fast). The size of the images are on the average of 1 MB (but can also be larger).
Our client has requested (really don’t know the reasons..) for us to start saving the images inside the database, using SQL Server’s FILESTREAM. As a company, we had never developed any project using this technology. We already know that storing BLOB’s (as VARCHAR(MAX)) is a really bad approach but what about FILESTREAM? Is it really worth it?
The main objectives are:
- FILESTREAM will offer better performance over time?
- What about the way to manage (save, retrieve) images? Is it a lot of different than using System.IO.File ?
- What about EntityFramework 5? Is it compatible? (Couldn’t really find actual answers for this on the internet)
The user can upload up to 10mb. Also, we have plans to let them also upload files (.pdf, .doc, .xls, even .mp3 max 10mb)
5
Since you’re talking about images in a context of a news portal, I imagine that those images are small JPEGs, not RAW files which can easily be 30 MB in size. In that case, FILESTREAM won’t be recommended, because its primary purpose is to host large files within the database.
For smaller objects (less than 1MB), storing
varbinary(max)BLOBs in the database can provide better streaming performance.
Source: Best Practices on FILESTREAM implementations
FILESTREAM has one major benefit: you can store very large blobs of data without being limited by the amount of memory on your server. By storing this data within the database instead of plain file system, you make it possible to unify data management. For instance, system administrators can define the backup strategy once for the database, and don’t have to create a duplicate backup strategy for plain files.
But the benefit stops here. In other words, you get performance nearly identical to what you can get with plain files and the benefit of a single strategy, but there is nothing more there.
If you don’t need this unified management of data, don’t use FILESTREAM. Unfortunately, FILESTREAM is overrated in Microsoft community, leading many people to use it in projects which don’t need it.
FILESTREAM will offer better performance over time?
One of the benefits I can see is that you won’t need to query the path of the file any longer. Aside this, you won’t see a benefit of using FILESTREAM compared to direct access to a file, because you simply add a layer of complexity.
Beware of the exact location of your actual files and the possible location of FILESTREAMed files: if currently, the files are stored locally, and in FILESTREAM, they will be moved to the machines hosing SQL Server, you’ll have to consider the bandwidth usage between your app servers and your database servers.
What about the way to manage (save, retrieve) images? Is it a lot of different than using System.IO.File ?
No. Accessing files is really easy and similar to the way you access an ordinary file. You can easily find the examples on MSDN.
What about EntityFramework 5? Is it compatible? (Couldn’t really find actual answers for this on the internet)
It wasn’t supported yet in 2011. I don’t know if things changed since then, but in all cases, you’ll probably circumvent Entity Framework if you need to access data stored in a FILESTREAM (which won’t be difficult either).
4