Home About Us SQL Interview Book Contact Us RSS
Articles
Tools
Code Snippets
Links
Tips & Tricks
FAQ
Resources
Articles
Code Snippets
Links
FAQ
Resources

Saravana Kumar
Vinod Kumar


Where to store Images? 

The fundamental Pros of storing the images in SQL Server are pretty simple: You gain all the "ACID" properties of a database system. This is something we donot intent to capitalize fundamentally. But the application demands have made us think so and we have unknowingly have used the database a store for binary data store such as images. As said earlier, the images are backed up with the data in a coordinated fashion. All "pointers" between your structured data and the images are maintained. No "broken link" problems, no out of sync problems, no multiple namespaces, etc. If you store the data in the database and the images in the file system then you have multiple backups and various ways for the database and image information to end up out of sync. In SQL Server you can store upto a max of 2GB of data in the binary data column. To pictorially represent the same I would show the same as below.

Plus, you have to manage two different security environments. The big negative of storing images in SQL Server is performance. There are three issues here. The first is that SQL Server breaks images up into chunks that fit on database pages. This makes reassembling the images slower than if they are stored, without additional internal structure, in a file. It also makes it impossible to use the operating system's built-in facilities to transmit a file directly from disk out over a communications link in kernel mode. So, from the standpoint of serving the image out onto the web it is definitely much slower. Second, images stored in SQL Server are returned to the application via the TDS protocol and the data access APIs. Again, these are not optimal for image processing and impose overhead that doesn't exist with a file. Third, most applications that process images read and write them from the file system. So, if the image is stored in SQL Server then you have to read the image out of the server, write it to a temporary file, then invoke the image processing software against the temporary file. It is these performance issues that lead many people to store the images outside the database itself. The real suitability of storing images inside SQL Server versus in the file system comes down to the analysis of the application itself. For example, if you have an HR application and one of the pieces of information that you store about an employee is the picture that is on their id card then I think you should store that image inside the database. Why? Well, you don't access it very often. You aren't serving it up to the web constantly. It's always accessed in conjunction with other employee data. You need to protect access to the images under your HR policies. The performance hit is thus not a significant factor when compared with the application and operational issues.

Now I did explain the same of how we can capitalize on the database capabilities to our advantage, but in my image above you can see that there is some thing very unusual. The link between the Web Server and the Database is high on traffic too. That's why the link is thick. Now there are performance penalties too for using images from database. Now SQL Server's network packets are as 4K packets. Even though thsi can be changed, these aren't in most scenario. I would suggest you look into the same considering the intranet bandwidth you are operating in. These sometimes can be benificial for you. Hence if we have huge images stored in the database then we incur this extra overhead and these are sent as 4K packets to the caller (here web server). Then there will be another handshake or the protocol validation for successful packet delivery etc. And now it becomes more of how we can optimize packets sent and more of how we can minimize this. I would suggest the following architecture for using images generally.

If I had a server whose primary function was to serve up images all day, then I'd store them in the file system. And hence now if you were to look at the WebServer to database interaction thickness it is normal :). And the focus has changed for the web server to get the images from filesystem. Now if you were to ask me on the security fronts, well you have all the security feature of the OS running. With EFS for encryption and the ACL and DACL's we can administer who can actually see the images stored in the local system. One system that runs counter to conventional wisdom is the TerraServer (http://terraserver-usa.com/). You can get details of how it works from http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-99-29. Even though it serves up images all day long the images are stored in SQL Server. This was done for a few reasons, chief among them to show that SQL Server was capable of hosting such an application. Terraserver has been operational since the summer of 1998, at times serving up several million images per day.