[sql-server] Storing images in SQL Server?

I have made a small demo site and on it I am storing images within a image column on the sql server. A few questions I have are...

• Is this a bad idea?

• Will it affect performance on my site when it grows?

The alternative would be to store the image on disc and only store the reference to the image in the database. This must be a common dilemma many people have had. I'd welcome some advice and would actually be happy to make a less of a mistake if I could.

Why it can be good to store pictures in the database an not in a catalog on the web server.

You have made an application with lots of pictures stored in a folder on the server, that the client has used for years.

Now they come to you. They server has been destroyed and they need to restore it on a new server. They have no access to the old server anymore. The only backup they have is the database backup.

You have of course the source and can simple deploy it to the new server, install SqlServer and restore the database. But now all the pictures are gone.

If you have saved the pictures in SqlServer everything will work as before.

Just my 2 cents.

I would prefer to store the image in a directory, then store a reference to the image file in the database.

However, if you do store the image in the database, you should partition your database so the image column resides in a separate file.

You can read more about using filegroups here http://msdn.microsoft.com/en-us/library/ms179316.aspx.

Another option was released in 2012 called File tables: https://msdn.microsoft.com/en-us/library/ff929144.aspx

While performance issues are valid the real reasons in practice that you should avoid storing images in a database are for database management reasons. Your database will grow very rapidly and databases cost much more than simple file storage. Database backups and restores are much more expensive and time-consuming than file backup restores. In a pinch, you can restore a smaller database much more quickly than one bloated with images. Compare 1 TB of file storage on Azure to a 1 TB database and you'll see the vast difference in cost.

I fell into this dilemma once, and researched quite a bit on google for opinions. What I found was that indeed many see saving images to disk better for larger images, while mySQL allows for easier access, specially from languages like PHP.

I found a similar question

MySQL BLOB vs File for Storing Small PNG Images?

My final verdict was that for things such as a profile picture, just a small square image that needs to be there per user, mySQL would be better than storing a bunch of thumbs in the hdd, while for photo albums and things like that, folders/image files are better.

Hope it helps

In my experience, storing to the url to the images stored in another location is the best way for a simple project.

When storing images in SQL Server do not use the 'image' datatype, according to MS it is being phased out in new versions of SQL server. Use varbinary(max) instead

https://msdn.microsoft.com/en-us/library/ms187993.aspx