Return image from file system as if it were in a blob column

  • 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?

    Jared
    CE - Microsoft

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

    Jared
    CE - Microsoft

  • 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

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

    Jared
    CE - Microsoft

  • What about just sending the path to the app and letting the app worry about fetching the image?

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • That is the final product. This clr is the interim while we slowly move millions of images out of the database.

    Jared
    CE - Microsoft

  • 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)[/url], 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#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • 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)[/url], 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();

    }

    }

    Jared
    CE - Microsoft

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply