May 20, 2009 at 2:08 pm
I've been trying to do some research in regard to storing large quantities of image data directly in SQL Server 2005. I understand that the image data type allows for the storage of image data as a binary value, and I understand how SQL Server 2008 is using varbinary(max) to accomplish the same task.
The company I work for currently uses various folders on the file system to house image data for products we sell, and these pictures are named based on the primary key of the product in question, e.g. 123.jpg. As a picture is uploaded, the photo may be resized in a variety of ways and restored in different folders that gives various images for various purposes, e.g. a thumbnail, small, medium, or large photo. This method allows for straightforward storage of the images, and interfaces with SQL Server only insofar as the images are named after the PK of a table in the database.
My question is, how does SQL compare with a file system in regard to storing image data and serving it back up to a website as a user browses products on an e-commerce site?
None of these photos will be even near a 1GB size, and I'm probably talking about 100K photos at any given time.
I imagine creating a table that has something like a field for each possible image size.
Does anyone have experience doing this in SQL Server 2005? If so, how has it gone? Also, has anyone not done this in 2005 but found that enhancements in 2008 allow for greater performance and flexibility in storing and serving up image data?
As a further piece of the picture, we plan on using .NET to write the code for the website, per se, and plan on using this framework to access the database layer.
Thank you!
May 20, 2009 at 2:12 pm
You might want to look at SQL Server 2008 with the varbinary data type and the filestream filegroup (?).
May 20, 2009 at 3:40 pm
I also would recommend looking at SQL Server 2008 and filestreams.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 20, 2009 at 5:28 pm
I don't know many details of how the application was built, but we recently implemented a custom built application containing about 500,000 document images using Sharepoint to hold the image files, another custom database with the index information for locating the documents, and with a custom web app to do searches and bring back the documents to the end users. The end users do not directly access Sharepoint; it's just a container for the documents. It has about one hundred full time users and they seem satisfied with the performance of the application. Of course, Sharepoint uses a SQL Server database to actually store the images.
May 21, 2009 at 6:46 am
Actually the files themselves in Sharepoint are stored on the file system. The meta data about the files is stored in Sharepoint. Restores of a Sharepoint system can be entertaining because of this disconnect.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 21, 2009 at 2:34 pm
Grant Fritchey (5/21/2009)
Actually the files themselves in Sharepoint are stored on the file system. The meta data about the files is stored in Sharepoint. Restores of a Sharepoint system can be entertaining because of this disconnect.
The may be some later version of Sharepoint that does this, but in the version we are using, all content is stored in the database. Database size is 54 GB for the content database.
May 22, 2009 at 6:04 am
Really? I'm sorry if I put out bad info. Maybe it's a setting during the install because we've been dealing with file systems seperate from the data store for a couple of versions now.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2009 at 8:03 am
Grant Fritchey (5/22/2009)
Really? I'm sorry if I put out bad info. Maybe it's a setting during the install because we've been dealing with file systems seperate from the data store for a couple of versions now.
It looks like you may actually be able to do both, according to the info in the excerpt from the white paper below. I used info from this paper as a guide to sizing the database server hardware for the Sharepoint databases.
"Optimizing Content Database Performance
SharePoint Content databases contain the bulk of all content in the site collections that comprise the portal instance. In WSS v3.0, not only is all of the content metadata stored in the database, but the binary files themselves related to document library records are also stored in the database. That statement should be qualified with the knowledge that a hotfix (and SP1) is available that will allow the externalization of content. However the API is rudimentary and should be avoided in most circumstances."
Scaling SharePoint 2007: Storage Architecture, Russ Houberg, April 11, 2008
Available as PDF on this link:
May 22, 2009 at 8:50 am
Now I'm second guessing what we're doing. If we're storing files directly in the DB, I'd expect it to be fairly large since we've got a pretty extensive Sharepoint installation, but I don't think it's all that big. I'm checking with our administrators now to get more details. Thanks for the information.
BTW, have you seen this white paper from Redmond Magazine?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2009 at 9:40 am
I've actually operated a database specifically for storing images, and SQL 2000 stank at it, SQL 2005 was okay at it, and SQL 2008's filestream is great at it. With 2000 or 2005, I'd store pointers in the database and store the files in the file system, and just make darn sure the files can't be moved without updating the database (which can be done through controlling permissions correctly).
Storing them as varbinary or image in the database ends up with horrible performance every time I've tried it. It does work, but it's not good at it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply