December 4, 2012 at 7:26 am
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
December 4, 2012 at 7:43 am
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 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/
December 4, 2012 at 7:58 am
I would recommend the file system and store the path in the SQL Server. Ultimate flexibility and easiest to code.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply