----------------------------------------------------------- New Message on BDOTNET
----------------------------------------------------------- From: VinodK [TLabs, SCT] Message 1 in Discussion Hi all, Some momets to share with all. Last UG meeting I had someone asking me if Images can be stored in the database or not. Is it advisable etc etc ... I did give a explanation to them in person but also thought that it would be better if the whole UG understands this requirement ... This is a common requiremnet in the real-world... So I would just walk you through some of the pros and cons of using them ... The Pros of storing the images in SQL Server are pretty simple: You gain all the "ACID" properties of a database system. 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. 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. 1. 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. 2. 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. 3. 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. On the contrary, if I had a server whose primary function was to serve up images all day, then I'd store them in the file system. But having said so much here, I would have to resort to the database archietecture if I were to be using a client-server app. I think the choice is obvious because you donot get a place to store or replicate them. But in a web archietecture you can always maximize on the web servers space and capabilities. I just caught up with one classical example to illustrate that SQL Server is indeed capable of storing and running on image datatypes. 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 I had one more suggestion to offer in case you were to use Image datatypes. You can separate the Image datatype tables into a separate filegroup and try to maximize on the IO operation by placing them on a separate disk ... These are some of the performance considerations to attain parallism in SQL Server. Whew, So much for a image datatype ... :) ... I think I would upload this as a FAQ to my site ... :) ... Iam open to views from others on the same ... i.e. if I've missed anything ... Regards, Vinod Kumar Microsoft India Community Star www.extremeexperts.com ----------------------------------------------------------- To stop getting this e-mail, or change how often it arrives, go to your E-mail Settings. http://groups.msn.com/BDotNet/_emailsettings.msnw Need help? If you've forgotten your password, please go to Passport Member Services. http://groups.msn.com/_passportredir.msnw?ppmprop=help For other questions or feedback, go to our Contact Us page. http://groups.msn.com/contact If you do not want to receive future e-mail from this MSN group, or if you received this message by mistake, please click the "Remove" link below. On the pre-addressed e-mail message that opens, simply click "Send". Your e-mail address will be deleted from this group's mailing list. mailto:[EMAIL PROTECTED]
