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

Return image from file system as if it were in a blob column Expand / Collapse
Author
Message
Posted Monday, November 4, 2013 12:11 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
I have been tasked with writing a CLR proc (my first) to retrieve an image from the file system.

Background: Currently, all images are stored in an IMAGE column. The current stored procedure simply returns that column to the application.

Future Plan: Move all images out of the database to an smb file share and only store the path within the database.

In the meantime: Alter the current procedure to see if a file path exists for the requested image. If it does, we will pass the path into a clr function/procedure to go to the file system and grab the image.

So... Any thoughts on how to do this or where I should start?


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1511236
Posted Monday, November 4, 2013 5:53 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
SQLKnowItAll (11/4/2013)
Future Plan: Move all images out of the database to an smb file share and only store the path within the database.


I used to think that was the best thing to do.

Our phone system records each call and converts it to a .WAV file. As stupid as it might sound (and it is), the file is saved as a separate file and the path to the file is saved in a CallRecording table AND the .WAV file is ALSO saved in the CallRecording table as a VARBINARY(MAX).

Obviously, that's an absolutely insane and totally unnecessary duplication of data so I set out to fix the problem. In the process of trying to resolve that duplication, I compared the file-paths stored in the table to the files and, boy was I ever surprised. Almost 15% of all the calls had been lost (file was missing) and more than 25% had been moved to other places without updating the file-path in the database. I also found out that around half of the files had NEVER been backed up to tape and some of the content of the files had been wiped clean (supposedly by accident).

That's a hell of a lot of screwed up data if you consider that I have 4 years of call recordings that I have to keep for irresistable legal requirements and that I have almost a million calls' worth of data that I'm supposed to have. The great thing is, the call recordings in the database are 100% there. Not a one has been lost.

So, lesson learned. If you want to keep graphics, documents, call recordings, or other blob information 100% safe and intact with all the proper backups, etc, ad nauseum, keep them in the database where people who are truly concerned with the proper preservation of data can take care of it.

In other words, store the data in a database where a qualified DBA is sure to take good care of it.

What about the size of backups? Again, it boils down to that qualified DBA doing his/her job properly. My phone system database isn't the biggest thing, by any means, and would be considered to be relatively small by many standards. It's 280GB the last time I looked but 270GB of that is 100% static call recording data. Right now, we back the whole bloody thing up every night along with Point-in-Time log files every 15 minutes. It takes 5 hours to back it up! I'm in the process of partitioning the table by month (1 FileGroup per month, 1 file per FileGroup) and I'm setting all but the current month to "Read-Only", which really makes "Piece-Meal" restores easy if it ever comes to that as well as making it highly unlikely that anyone will accidently delete any of the calls.

Don't forget that, even if you have the Standard Edition, it is possible to partition tables even though "Partitioned Tables" isn't available there.

I've never had the chance to study the ramifications of storing blobs in files v.s. storing blobs in the database before. I used to be all for the store-in-files method before I did this study and, after the study I have recently completed, I am now convinced that the best place to store the blob data (provided it's less than the MAX datatype being used and it's NOT XML data unless you need that for audit purposes) is where the pro's of data can manage it. Long live DBAs!!!



--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."

(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 #1511332
Posted Tuesday, November 5, 2013 5:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
I agree with you in your situation. However, we have terabytes of images and the business loves to keep them for 10 years (even though they don't go back to them with the application). We have poor man's partitioning going on with 1 read-write database, several 100GB read-only, and a view that ties them all together. I am confident in our backups of the file share.

Our issue does not have to do with space, it is a number of things outside of that. One is the speed at which the images are retrieved. We also have several "customers" that scrape our databases. Finally, it is licensing for SQL Server 2012 and the box itself.

The CLR is just an interim while the developers create their own application layer to take the retrieved file path and grab the image. Their resources are not able to do it now, but we need to get these images out and virtualize the server before our true up.

I also look at this as a chance to learn clr and C# a bit more for myself.


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1511450
Posted Tuesday, November 5, 2013 6:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 5,485, Visits: 10,323
Jared

I know you've posted in the SQL Server 2005 forum, but if you have access to SQL Server 2008 and above, it's worth considering Filestream. There's a very thorough paper on it written by Paul Randal that you can find on the web.

John
Post #1511452
Posted Tuesday, November 5, 2013 6:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
CRAP! I didn't even realize this was 2005, I just looked for CLR. I feel like a newbie! We looked into filestream and the higher ups decided against it for now. Don't remember why...

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1511455
Posted Wednesday, November 6, 2013 3:46 AM


SSC Eights!

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

Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 996, Visits: 3,089
What about just sending the path to the app and letting the app worry about fetching the image?




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1511777
Posted Wednesday, November 6, 2013 4:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
That is the final product. This clr is the interim while we slowly move millions of images out of the database.

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1511789
Posted Wednesday, January 8, 2014 8:16 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, December 14, 2014 10:06 PM
Points: 379, Visits: 1,991
SQLKnowItAll (11/6/2013)
That is the final product. This clr is the interim while we slowly move millions of images out of the database.


Hey Jared. Did you ever get your answer to this? It has been 2 months so I would like to think you have, but figured I should ask just in case.

I have written a library of SQLCLR functions, SQL# (SQLsharp), some of which do what you are asking about (reading / writing binary data to / from disk). While a large number of functions are available for free, the FileSystem ones are only in the Full (i.e. paid-for) version. It sounds like your need is (or I guess, was) more temporary so maybe not worth buying a tool, but I thought I would mention just in case.

Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #1529190
Posted Thursday, January 9, 2014 6:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
Solomon Rutzky (1/8/2014)
SQLKnowItAll (11/6/2013)
That is the final product. This clr is the interim while we slowly move millions of images out of the database.


Hey Jared. Did you ever get your answer to this? It has been 2 months so I would like to think you have, but figured I should ask just in case.

I have written a library of SQLCLR functions, SQL# (SQLsharp), some of which do what you are asking about (reading / writing binary data to / from disk). While a large number of functions are available for free, the FileSystem ones are only in the Full (i.e. paid-for) version. It sounds like your need is (or I guess, was) more temporary so maybe not worth buying a tool, but I thought I would mention just in case.

Take care,
Solomon...
Thanks for your response. I will check those out. Here is the code I finally used:
using System;
using System.Data;
using System.IO;
using Microsoft.SqlServer.Server;


public partial class ClrGetImageFile
{
private const string ConnectionString = "context connection=true";

[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetImageFile(string filePath)
{
SqlPipe pipe = SqlContext.Pipe;
byte[] imageFile;

// Create the record and specify the metadata for the columns.
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("col1", SqlDbType.VarBinary, SqlMetaData.Max));

// Mark the begining of the result-set.
SqlContext.Pipe.SendResultsStart(record);

imageFile = File.ReadAllBytes(filePath);
record.SetSqlBinary(0,imageFile);

// Send the row back to the client.
SqlContext.Pipe.SendResultsRow(record);

// Mark the end of the result-set.
pipe.SendResultsEnd();
}

}



Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1529301
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse