May 26, 2015 at 4:48 pm
don't understand why people think they need to write a CLR for this
and
Why not use native T-SQL commands?
Don't know that you'd want to, but maybe mass loading a whole bunch of files, something like:
INSERT FileDataTable(FileName, FileData)
SELECT FileName, clr.GetFileData(FileName) FROM FileNameTable
or UPDATE FileTable SET FileData = clr.GetFileData(FileName)
? (I'm not sure this would even be possible - it's been a while since I personally looked into CLR functions?)
Re: the discussion about to FILESTREAM or not to FILESTREAM. What Jeff said:
For those cases where you're not going to violate the 2GB limit, you don't even need FileStream
Why would you want to complicate your life by introducing FILESTREAM if you don't need it? I suspect the scenarios where people are wanting to store > 2GB files are several orders of magnitude smaller than those scenarios where people are storing files documents and images < 2GB in size.
I know some people have an aversion to storing large blobs inside a SQL database, but I've never really understood it; you still need the same about of space for your data, and for the projects I've been involved with (a very small sample, I admit) nobody has yet commented about the performance.
May 26, 2015 at 4:53 pm
Ryan, IMHO Filestream does not complicate life - it provides a dual view on BLOBS (the best of 2 worlds) when BLOB could be stored as a file but database engine is aware of it presence. Unfortunately technology is not a mainstream even in SQL Server community 🙁
May 26, 2015 at 5:21 pm
fregatepllada (5/26/2015)
Ryan, IMHO Filestream does not complicate life - it provides a dual view on BLOBS (the best of 2 worlds) when BLOB could be stored as a file but database engine is aware of it presence. Unfortunately technology is not a mainstream even in SQL Server community 🙁
That's awesome if you need to see the data from both views, but the applications that *I* have worked with don't have that need, so providing it would be adding unnecessary complexity. i realize that it's not a *lot* of complexity, but it nonetheless involves extra configuration and maintenance which my applications don't need (and I'm sure there are many in the same situation as me).
May 26, 2015 at 5:32 pm
I do not claim that this is a next silver bullet, from my experience I simply found this capability more useful comparing with "classical" approach. When it came to your project design decisions obviously it's you call what to use, but there are few options (aka multiple ways to skin the same cat). I found FILESTREAM useful for document management system implementation last year 🙂
May 26, 2015 at 11:19 pm
fregatepllada (5/26/2015)
FileTable does not support transactions,
Sure it does...
while Filestream does, plus in Filestream you have a choice to hide files from Windows 🙂
You can hide the files in Windows with FileTables as well using the same NON_TRANSACTED_ACCESS filestream option you would use when enabling a regular FILESTREAM setup.
FileTables is built atop FILESTREAM technology. You can think of FileTables as an advanced API sitting on top of FILESTREAM technology. There are some tricky differences so FileTables are not a full replacement for stand-alone columns configured as FILESTREAMs but I look at FileTables first then go to the lower-level FILESTREAM second if I need more control.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 26, 2015 at 11:25 pm
mwpowellhtx (5/26/2015)
Orlando Colamatteo (5/26/2015)
Thanks for taking the time to write the article. Given the alternatives I strongly prefer employing a SQLCLR for implementing file system access from T-SQL. However, in this case a custom solution for handling binaries may not be necessary. I see this as a great use case for FileTables. Unlike the C# solution in the article, the native T-SQL solution alluded to and using FILESTREAM directly, no coding is required to bring a binary into the database when using FileTables. FileTables leverage FILESTREAM but with the added advantage of offering us the option of managing files in the database via a UNC path accessible through Windows Explorer or other standard file-system tools.I'm not sure you understand FILESTREAM correctly. I may not either, but as I understand it, the OP's API would still be necessary. The only difference being, FILESTREAM permits large files, stored in the file system to be sure, transparently through the table. They still look and feel like a varbinary(max), but to be sure, the files are landing in a predetermined location. They are also very transactional in nature; that's one of the benefits of FILESTREAM; as contrasted with a RYO file tabling / streaming strategy.
I have researched this area quite a bit so I think you may have misread my comment. I was not offering up FILESTREAM to eliminate the need for the OPs API, I was offering up FileTables as a possible replacement for the OPs API. Even with FILESTREAM the OPs API could go away by leveraging Windows APIs but I do not want to go too far off topic.
See the bolded statement in my original comment above. With FileTables you have the option to access the FILESTREAM directly but it is not compulsory if you do not need transactional data access, i.e. you can get files into the database without ever connecting to the database engine itself.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 26, 2015 at 11:44 pm
Jeff Moden (5/26/2015)
For those cases where you're not going to violate the 2GB limit, you don't even need FileStream to load binaries.
True, but with in-table storage the cost per GB will generally be higher, streaming performance is poor relative to other methods and recovery from fragmentation is difficult. This is not to mention that if your binaries are large you can be blasting your buffer pool moving files into and out of your database whereas with FILESTREAM and FileTables data is streamed outside the buffer pool.
For small binaries or even medium sized binaries that are not handled much once they are stored I think in-table storage makes a lot of sense but there are definitely use cases where the other methods are going to be a much better choice.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 27, 2015 at 12:22 am
Orlando Colamatteo (5/26/2015)
Jeff Moden (5/26/2015)
For those cases where you're not going to violate the 2GB limit, you don't even need FileStream to load binaries.True, but with in-table storage the cost per GB will generally be higher, streaming performance is poor relative to other methods and recovery from fragmentation is difficult. This is not to mention that if your binaries are large you can be blasting your buffer pool moving files into and out of your database whereas with FILESTREAM and FileTables data is streamed outside the buffer pool.
For small binaries or even medium sized binaries that are not handled much once they are stored I think in-table storage makes a lot of sense but there are definitely use cases where the other methods are going to be a much better choice.
Yep... I realize that some of that can be a problem. 😀 Most of the files that I have to import are just several meg of compressed telephone conversation. The problem that I had was that all this stuff was being stored in a table when I got there and it's on a 2005 box that's not going to be upgraded for another year (heh... it WAS on an SQL 7 box). They also included the path to the original file.
When I did the due-diligence to ensure that all the files existed, 10% of more than (at the time) years of recordings were missing (we're now required to keep them forever or until a client leaves us) and the other 10% were corrupt in one form or another. I could write it all out to new files but I decided that the recordings are safer in the database, nicely partitioned (after I got done with it), set to READ-ONLY, and safe. Heh... no one guards data like a DBA.
I'm going to have to dig into FileStream and FileTables soon. We're finally migrating to 2012.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 27, 2015 at 6:46 am
am i seeing a bug in GetBytesFromFile procedure it is not passing the @FileName parameter to the
SqlFileProcessor.FileProcessor.GetBytesFromFile dll
May 27, 2015 at 8:16 am
Lots of different approaches. Thanks for yours.
May 27, 2015 at 8:27 am
Jeff Moden (5/27/2015)
Orlando Colamatteo (5/26/2015)
Jeff Moden (5/26/2015)
For those cases where you're not going to violate the 2GB limit, you don't even need FileStream to load binaries.True, but with in-table storage the cost per GB will generally be higher, streaming performance is poor relative to other methods and recovery from fragmentation is difficult. This is not to mention that if your binaries are large you can be blasting your buffer pool moving files into and out of your database whereas with FILESTREAM and FileTables data is streamed outside the buffer pool.
For small binaries or even medium sized binaries that are not handled much once they are stored I think in-table storage makes a lot of sense but there are definitely use cases where the other methods are going to be a much better choice.
Yep... I realize that some of that can be a problem. 😀 Most of the files that I have to import are just several meg of compressed telephone conversation. The problem that I had was that all this stuff was being stored in a table when I got there and it's on a 2005 box that's not going to be upgraded for another year (heh... it WAS on an SQL 7 box). They also included the path to the original file.
When I did the due-diligence to ensure that all the files existed, 10% of more than (at the time) years of recordings were missing (we're now required to keep them forever or until a client leaves us) and the other 10% were corrupt in one form or another. I could write it all out to new files but I decided that the recordings are safer in the database, nicely partitioned (after I got done with it), set to READ-ONLY, and safe. Heh... no one guards data like a DBA.
I'm going to have to dig into FileStream and FileTables soon. We're finally migrating to 2012.
Wow, something newer than SQL vNextMinus4, exciting 😀 In all seriousness, it seems like the petition we had going to get your company to upgrade so you could have your hands on some newer SQL Server features more regularly finally gained enough traction.
From the information you shared in-table storage very well could continue to be the right choice even with these other options available, especially since it seems to be serving the system well.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 28, 2015 at 12:03 am
Hi Eric. Good topic. I definitely like the concept, but need to mention a few notes regarding SQLCLR best practices:
Until the code in the article is updated to trap errors to ensure proper disposal, I would have to recommend against anyone using this in a Production capacity.
I hope all of this helps. Take care, Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
May 28, 2015 at 12:20 am
sanjarani (5/26/2015)
I used .dll program developed with C# a few years a go (SQL Server 2005) to store binary files, mainly .jpg file.At the time it was said that a special permission was necessary to consume .dll in SQL Server and those permissions would compromise the security of the Server.
Is consuming .dll in SQL Server still a security concern?
Hi sanjarani. No, it isn't a security concern, not now nor was it even back when you were told that it was. The problem is that there is a general lack of understanding around SQLCLR. But SQLCLR is far better than the alternatives of xp_cmdshell, the OLE Automation Procedures, extended stored procedures, and sometimes even OPENROWSET / OPENQUERY.
If you would like to see for yourself how the various aspects of SQLCLR security work, check out the following two articles of mine:
Stairway to SQLCLR Level 3: Security (General and SAFE Assemblies)[/url]
Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies)[/url]
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
May 28, 2015 at 5:22 am
Solomon Rutzky (5/28/2015)
But SQLCLR is far better than the alternatives of xp_cmdshell, the OLE Automation Procedures, extended stored procedures, and sometimes even OPENROWSET / OPENQUERY
Extremely personal and grossly incorrect opinion noted. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 28, 2015 at 7:12 am
Solomon Rutzky (5/28/2015)
Hi Eric. Good topic. I definitely like the concept, but need to mention a few notes regarding SQLCLR best practices:!! Critical Issue !!: You open a FileStream but you don't have any error handling or even a "using" block. This is very dangerous as external resources (i.e. file handles, network handles, etc) can get orphaned if not properly disposed and can be held open at least until the App Domain is unloaded. So, the File.Open() through the File.Read() needs to either be in a try / catch / finally (where you call fs.Dispose() in the finally block), or at least in a "using" block (which is really just short-hand for the compiler doing the try / catch / finally for you). And then you don't need to call fs.Close().
Strongly agree with this, exception handling is a must when dealing with the file system. Regarding need to dispose, maybe you could just "dispose" with all of that (and a few lines of code) by just using the File.ReadAllBytes method instead (if you don't mind the file being locked for reading). Something like (sorry, code written off the top of my head):
public static SqlBytes GetBytesFromFile(string sFilePath)
{
SqlBytes b = null;
try
{
byte[] abytBuffer = System.IO.File.ReadAllBytes(sFilePath);
b = new SqlBytes(abytBuffer);
}
catch (System.IO.IOException ex)
{
// Do something with/about the exception
}
return b;
}
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply