Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Best way to store photos Expand / Collapse
Author
Message
Posted Tuesday, December 4, 2012 7:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 30, 2013 9:54 PM
Points: 44, Visits: 158
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
Posted Tuesday, December 4, 2012 7:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 13,327, Visits: 12,820
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1392491
Posted Tuesday, December 4, 2012 7:58 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
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 »

Add to briefcase

Permissions Expand / Collapse