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 ««12

Best way to store images Expand / Collapse
What is best way to store images
Poll ResultsVotes
Store them in binary type in SQL SERVER
 
12.5%
2
Copy them in a folder and only store their 'names' in SQL SERVER
 
87.5%
14
Member Votes: 16, Anonymous Votes: 0. You don't have permission to vote within this poll.
Author
Message
Posted Thursday, February 13, 2014 12:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 9:14 PM
Points: 7, Visits: 27
Jeff Moden (3/11/2013)
I'd have to include FileStream in the same bucket as the "File Path Reference" approach with some reservations. To be sure, I've not used FileStream, yet (too much on my plate for now), but I like what I see in the documentation. One of the things that would be important to me is that other applications should be able to save image in the data containers that FileStream uses without having to go through anything having to do with SQL Server or FileStream. For example, I'd like folks to be able to get to the files using only, say, Windows Explorer and be able to open up a PDF or a JPG or whatever just by double-clicking on it. I also need to be able to "update" the content of a file simply by replacing it through something as simple as the Command Prompt. If that can't be done in FileStream data containers directly (and I haven't found such explicit information in the documentation, yet), then file saving Filestream wouldn't work so well for the things I need to do.


Right now, I am saving file in a table and I know it will suck when the image file size grows bigger. Thus, I am trying to find another way for storing image in sql server. And I am very interested in your file path reference approach. You mentioned the documentation and I wonder could you please recommend that documentation for me? so I can know more about this kind of file saving method. Thanks you so much.
Post #1541043
Posted Monday, February 17, 2014 7:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 6:57 AM
Points: 63, Visits: 364
aframiller326 (2/13/2014)
Jeff Moden (3/11/2013)
I'd have to include FileStream in the same bucket as the "File Path Reference" approach with some reservations. To be sure, I've not used FileStream, yet (too much on my plate for now), but I like what I see in the documentation. One of the things that would be important to me is that other applications should be able to save image in the data containers that FileStream uses without having to go through anything having to do with SQL Server or FileStream. For example, I'd like folks to be able to get to the files using only, say, Windows Explorer and be able to open up a PDF or a JPG or whatever just by double-clicking on it. I also need to be able to "update" the content of a file simply by replacing it through something as simple as the Command Prompt. If that can't be done in FileStream data containers directly (and I haven't found such explicit information in the documentation, yet), then file saving Filestream wouldn't work so well for the things I need to do.


Right now, I am saving file in a table and I know it will suck when the image file size grows bigger. Thus, I am trying to find another way for storing image in sql server. And I am very interested in your file path reference approach. You mentioned the documentation and I wonder could you please recommend that documentation for me? so I can know more about this kind of file saving method. Thanks you so much.


I believe this is the documentation referred to:

http://technet.microsoft.com/en-us/library/bb933993(v=sql.105).aspx
Post #1542104
Posted Monday, February 17, 2014 11:17 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 4:56 PM
Points: 8,271, Visits: 8,717
I'm inclined to go with an "It Depends" answer. In the past I've worked a lot with binary files outside the database, in filestore, and it's been fine; sound files, image files, movie files, executable files, library files - all handled much the same way. Small files and large files - why treat them diferently.

But I can see cases where I would want to have binary data involved in transactional consistency with other data; that's something I've worked around in the past (using multi-phase transactions, which I generally don't like) but I can see that filestore data might provide a better handle on that. Of course the idea of manipulating some forms of binary data within the database is a bit odd - DML is not really well suited to image processing, for example; writing XPs to do that sort of thing is not much fun, but maybe the ability to use CLI has made that easier.

The point Jeff makes about people messing around with filestore so that the database references to files are broken is a valid one - it is essential to have the discipline in place that ensures that doesn't happen. Of course if the data is held both inside and outside the database that's still just as important, since it is important to keep the two copies in step, and doing that would mean keeping track of which file corresponds to which blob (or set of blobs) in the database, so I guess if I wanted the stuff in the database I wouldn't want it outside too, there's too much to go wrong, except that I'd probably want to pull it out now and again in order to use standard tools to update it rather than having to reinvent the wheel (and that of course is entirely analagous to letting an accountancy application pull data out and update some of it before putting it back). Of course it's also valid in some cases to have references in the database to files that are absent - it's sometimes contractually essential in a system where one is handling licensed accessed binary material that the material not licensed for use in a country is not held on the servers in that country, but highly desirable that all material is referenced in the databases on those machines so that when a license becomes avaialble (after the usual silly bureaucratic delays created by content-provider obduracy) it can be made available immediately without requiring database upgrades on a subset of machines; that too, of course, requires some pretty solid displine in the approach to file distribution.

On balance, I would usually just put references to external files in the database; but I imagine there would be cases where I would want the files to be inside the database and have them outside only transiently (just as for any other data in the database), provided I could cope with the backup and recovery problems entailed by having a lot of infrequently updated data in the DB.


Tom
Post #1542231
Posted Monday, February 17, 2014 9:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 35,951, Visits: 30,235
aframiller326 (2/13/2014)
Jeff Moden (3/11/2013)
I'd have to include FileStream in the same bucket as the "File Path Reference" approach with some reservations. To be sure, I've not used FileStream, yet (too much on my plate for now), but I like what I see in the documentation. One of the things that would be important to me is that other applications should be able to save image in the data containers that FileStream uses without having to go through anything having to do with SQL Server or FileStream. For example, I'd like folks to be able to get to the files using only, say, Windows Explorer and be able to open up a PDF or a JPG or whatever just by double-clicking on it. I also need to be able to "update" the content of a file simply by replacing it through something as simple as the Command Prompt. If that can't be done in FileStream data containers directly (and I haven't found such explicit information in the documentation, yet), then file saving Filestream wouldn't work so well for the things I need to do.


Right now, I am saving file in a table and I know it will suck when the image file size grows bigger. Thus, I am trying to find another way for storing image in sql server. And I am very interested in your file path reference approach. You mentioned the documentation and I wonder could you please recommend that documentation for me? so I can know more about this kind of file saving method. Thanks you so much.


Sorry for the late reply. The link behind the bold lettering in my previous reply apparently didn't work for click-ability. Here's the plain link for the "file path reference approach".
http://www.rasteredge.com/how-to/vb-net-imaging/open-save-file/


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1542350
Posted Tuesday, February 18, 2014 4:00 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 4:56 PM
Points: 8,271, Visits: 8,717
Jeff Moden (2/17/2014)
Here's the plain link for the "file path reference approach".
http://www.rasteredge.com/how-to/vb-net-imaging/open-save-file/

Ugh!

Couldn't you point to something that doesn't reference that abominable language VB? Can't RasterEdge Image SDK for .NET be used when programming in other languages which support .NET?

Also, if I do a google search for "RasterEdge Image SDK for .NET" the first non-advert hit is on soft112.com and that's a site which McAfee suggests you don't visit. So probably if anyone wants to download the SDK they should get it from rasteredge.com, not from google's first hit.


Tom
Post #1542453
Posted Tuesday, February 18, 2014 4:47 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 1,887, Visits: 1,177
Something to be aware of (which has always prevented me from using FILESTREAM) is that a database cannot be mirrored if it contains a FILESTREAM filegroup. There are a number of other restrictions to take into account:

http://technet.microsoft.com/en-us/library/bb895334.aspx

Regards
Lempster
Post #1542484
Posted Tuesday, February 18, 2014 2:29 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 845, Visits: 2,331
TomThomson (2/17/2014)

But I can see cases where I would want to have binary data involved in transactional consistency with other data;


To me the critical question is: Do changes/adds/deletes to the binary data require ACID compliance? If so, thou shalt put them in the database. If not, you have choices to think about.

If you use "files on file store, link in the database" (whether FILESTREAM or custom coding), I would recommend a regular process that checks validity - i.e. something you can run when you run DBCC CHECKDB to see if the links still lead to files.
Post #1542750
Posted Tuesday, February 18, 2014 9:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 35,951, Visits: 30,235
To me, the critical question is, is the data important whether it changes or not? If so, put it in the database because no one will protect the data better than a DBA. It's going to take disk space in one place or another. It might as well be in a safe place.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1542847
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse