SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13858 Visits: 3697
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218231 Visits: 41995
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13858 Visits: 3697
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35195 Visits: 16671
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
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13858 Visits: 3697
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Sean Pearce
Sean Pearce
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4136 Visits: 3436
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
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13858 Visits: 3697
That is the final product. This clr is the interim while we slowly move millions of images out of the database.

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Solomon Rutzky
Solomon Rutzky
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3378 Visits: 3029
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/
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13858 Visits: 3697
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search