Best way to store photos

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Best way to store photos

Best way to store photos

Rate Topic

Display Mode

Topic Options

Author

Message

dubem1-878067

Posted Tuesday, December 04, 2012 7:26 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 10:08 AM
Points: 43, Visits: 151

Hello

our app is used for plant inspection. Each inspection file will have about 30 photos. We resize photos so each weights about 60 k
We evaluate 25 000 inspection file per year.
So 45 gigs of photos in the database after one year and 90 gigs after two years
How to manage this efficiently in SQL Server? (performance...backup..etc)

thanks

Martin

Post #1392478

Sean Lange

Posted Tuesday, December 04, 2012 7:43 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

There are basically 3 ways to handle storing binaries with sql.

1) Store the files on disc in a folder and then store the file name in sql. You basically just use sql as a way to track which file belongs to a given row in the table.

2) Store the actual file in a varbinary(max) column. This can be a bit unwieldy as you get large numbers of files due to page and index fragmentation. You also have to be careful not to select that column with every day queries. I have done this in the past by having a table that holds nothing but the image, an identity and a foreign key.

3) Filestream. Not sure this is what you want given the small size of the files you are working with. You read more about this option here. http://msdn.microsoft.com/en-us/library/bb933993%28v=sql.105%29.aspx

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1392491

DiverKas

Posted Tuesday, December 04, 2012 7:58 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:24 AM
Points: 237, Visits: 413

I would recommend the file system and store the path in the SQL Server. Ultimate flexibility and easiest to code.

Post #1392507

« Prev Topic | Next Topic »

Permissions