January 21, 2014 at 8:59 am
Hello. We have been tasked with a requirement to store .jpg files in the database for 2 or 3 tables.
At this time a size limit has not been set. It is my understanding that the goal will be for a high quality image that would display no larger than maybe a 5X6" or 4X4". These will be primarily headshots of people. I would like to advise them as to a reasonable limit. Note: the images will be used on a SharePoint 2010 site.
Each table will only have a maximum of 15,000 records.
The image files will be picked up from one file system and moved to the database via an SSIS package.
Since I have not worked with storing images previously I thought it best to ask for some advice.
Within SQL Server 2008 R2 Enterprise Edition what are my data type options for storing this type of image?
What are the considerations for choosing one data type over the other?
Note2: database will be upgrade to SQL Server 2012 in the near future
Thank you in advance for your recommendations
January 21, 2014 at 9:51 am
Although I've never had the opportunity to store .JPGs in a database the construct you are looking for is FILESTREAM. I'd suggest you read up on it in BOL.
One the applications I previously supported used a lot of .JPG files, roughly 1.6m files. These .JPG files were stored on a fileserver and the UNC location and file name was stored in the database. The biggest concern I'd have is the storage overhead keeping .JPGs in the database.
Just saying....
Good luck.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
January 21, 2014 at 10:10 am
Another option is to store the images in a varbinary(max) column. There are advantages and disadvantages to any of these options.
Kurt mentioned something about storage. Honestly, the storage is going to happen regardless of how you handle the images. The question is where is that storage going to be consumed.
One challenge of storing the unc path is that the folder and database can (and usually will) get out of synch. Somebody will accidentally remove a file or rename a file and suddenly your database can't find the image.
You might want to read up on the three options suggested and determine what is best in your situation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 21, 2014 at 10:42 am
Sean Lange (1/21/2014)
Another option is to store the images in a varbinary(max) column. There are advantages and disadvantages to any of these options.Kurt mentioned something about storage. Honestly, the storage is going to happen regardless of how you handle the images. The question is where is that storage going to be consumed.
One challenge of storing the unc path is that the folder and database can (and usually will) get out of synch. Somebody will accidentally remove a file or rename a file and suddenly your database can't find the image.
You might want to read up on the three options suggested and determine what is best in your situation.
Yes, I agree that there is a chance the unc path/name in the database can get out of sync, but if you are dealing with a production database then no one should be meddling with the image files in the first place.
I had the task of moving the images on the file server to another server. It was a bit of a challenge but what I did was used the location in the database to build a script (dos command) and xcopied the files from their location to an external drive. I then was able to revise that same script to move to the new file server. The best part to that was I had 100% recovery rate. Total number of files moved was just over 1.5m. It took about 2 weeks to complete the project.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
January 21, 2014 at 10:53 am
Kurt W. Zimmerman (1/21/2014)
Sean Lange (1/21/2014)
Another option is to store the images in a varbinary(max) column. There are advantages and disadvantages to any of these options.Kurt mentioned something about storage. Honestly, the storage is going to happen regardless of how you handle the images. The question is where is that storage going to be consumed.
One challenge of storing the unc path is that the folder and database can (and usually will) get out of synch. Somebody will accidentally remove a file or rename a file and suddenly your database can't find the image.
You might want to read up on the three options suggested and determine what is best in your situation.
Yes, I agree that there is a chance the unc path/name in the database can get out of sync, but if you are dealing with a production database then no one should be meddling with the image files in the first place.
I had the task of moving the images on the file server to another server. It was a bit of a challenge but what I did was used the location in the database to build a script (dos command) and xcopied the files from their location to an external drive. I then was able to revise that same script to move to the new file server. The best part to that was I had 100% recovery rate. Total number of files moved was just over 1.5m. It took about 2 weeks to complete the project.
Kurt
I worked with a system several years ago that stored the images like that and it worked well.
Of the three choices that one is probably the easiest and quickest to implement. Given that the size of files as indicated is going to be pretty small I would think that FILESTREAM is not the best choice. It works best on files that are larger.
If this was my system, based on the details provided, I would most likely go with unc storage.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 21, 2014 at 11:32 am
Storing any binary component in a database (documents, pdfs, images, whatever) is usually only done when the stored component must stay in lockstep with a database restore/recovery, particularly for point in time.
For example, if 10 new rows go into the database and they're lost during a point in time recovery, does having the images exist in the file system harm anything? If you're storing document versions, for example for a court system, do modifications to associated data have to stay in perfect sync with the file in question? If you're filling out court forms and you have a before/after switch and very tight timelines, this might be a possible item. I worked on one system that absolutely had to do this with sound components. Long story with flakey hardware.
Under most circumstances, BLOB fields such as images should be stored external to the database with the database having a reference to its location for front end usage/display. Storage and manipulation of these items has improved tremendously, but it's still good practice.
If you are going to proceed with this process, however, I recommend you make sure you have a separate filegroup for your BLOBs (personal preference is one per BLOB for clarity of who's offending what) and it's easy enough to store them separate from the rest of the data with a switch during table creation. This will significantly ease maintenance and help with bloat. Additionally, you'll want to review expected usage. If they are basically insert once and never touch again, no biggie. If there's a lot of churn in your blobs (constantly updating headshots with overwrite on the old ones, etc) you'll want to explore maintenance and space recovery for blobs in whatever version you're in. It can be finicky and doesn't follow all the normal rules.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 21, 2014 at 11:47 am
Gentlemen, much thanks to all of you.
I will read up on the FILESTREAM / and storing BLOBS outside of the database.
Will also test using varbinary(max) .
The images should not change very often but the plan at this point is that the data will be refreshed daily by truncating the tables and reinserting all records from the source.
Based on that method of refresh I am thinking that storing the images on a file server with the database having the reference to the filename and location may be a better solution.
That way just for images the refresh could be controlled to only replace if the filename has changed for the image that corresponds to the record.
January 22, 2014 at 3:06 pm
ELLEN-610393 (1/21/2014)
Gentlemen, much thanks to all of you.I will read up on the FILESTREAM / and storing BLOBS outside of the database.
Will also test using varbinary(max) .
The images should not change very often but the plan at this point is that the data will be refreshed daily by truncating the tables and reinserting all records from the source.
Based on that method of refresh I am thinking that storing the images on a file server with the database having the reference to the filename and location may be a better solution.
That way just for images the refresh could be controlled to only replace if the filename has changed for the image that corresponds to the record.
You said you were moving soon to SQL 2012. Take a look at this article before you do: Options for Storing Blobs (SQL Server). You may benefit from file tables. I have never used them for storing images but know that you can.
-- Itzik Ben-Gan 2001
January 22, 2014 at 3:25 pm
Why don't you just store the files in SharePoint?
January 24, 2014 at 5:55 pm
I used to think that storing files on a file system and providing the path in the database was the best thing. After the mess I found on our phone system (recorded calls), I no longer think that. Disk space is disk space but no one will take better care of data than a good DBA.
To be sure, I'm currently still stuck on 2005 so I've not had the chance to play with FileStream. If that prevents anyone else from deleting files (like you can't delete an MDF file being used by an active instance of SQL Server) then that's a bonus but I still like the idea of having multiple blobs in a file instead of just one "picture" or "call recording" per file.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply