February 6, 2009 at 11:30 pm
Jeff Moden (2/6/2009)
Jonathan Kehayias (2/6/2009)
so I did it the right way, rather than the easy wayBWAA-HAA!!! Man, I'm right there with you on that one! Now, all we have to do is convince the rest of the world, especially the managers that want it real bad, to give people just a little more time so they don't get it that way... real bad.
Good talking with you again, Jonathan. Kudos on the article.
I've got some more coming that it will be fun to see your comments on. All along this same line of processing, but its always interesting to see what you come up with for TSQL equivalents to the SQLCLR. As you say, the places I can write CLR to beat TSQL has been limited, and some of your tricks like appending a '/' at the end of a URL in TSQL to make domain name extraction lightening fast are very interesting and something to learn from.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
February 7, 2009 at 1:06 am
This solution is based on the premise that security is important enough to take the time to implement. If time is more important than preventing unrestricted access to the OS as the SQL Service account with xp_cmdshell, then this solution probably isn't for you.
There is nothing on the machine other than SQL Server databases protected by failover or log shipping. So what if the machine crashes; that's just a blip. If a hacker has ever been able to copy an .mdf file from a machine to outside the firewall because xp_cmdshell was turned on, I would like to read about it. Security isn't about the OS; it is about the company's data.
Data security principle -- developers make the access to the data visible to the administrators who are responsible for the security of production data, after the developers go on to other projects, by writing simple, compact T-SQL, tested for performance. What the application does with the data should not be hard to see inside a pile of clever code-- that is a data security issue. It's not about how easy or hard it is for the developer to write the code; it is about how the code is maintained and explained two years later.
I'm talking about database programming for business applications. I suppose UI considerations are primary for other applications.
My SQL Server policy is xp_cmdshell on and CLR off -- unless I hear of theft of an .mdf that was accomplished using xp_cmdshell.
And thanks to someone for fixing the window that displays code. It's normal width now.
_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
February 7, 2009 at 4:04 am
That was it and I agree... someone like you wouldn't pass a bad path.
Remember the advice in 'The C Programming Language by Dennis M. Richie and Brian W. Kernighan' to always put in error handling even when an error couldn't possibly happen? I've lost count of the number of times I've thanked those guys for that advice. I've seen the error 'this cannot possibly happen' more times than I'd like to admit.
I'm not going to back down over my gentle advice that the CLR must react properly to all possible inputs. After all, it was because people neglected this that SQL Injection became possible. If a routine is for your own private use, then bless you, but if it is published on the internet then prepare for a comment by Phil.
Oh and another thing; it was particularly the use of the Asymmetric key that I was so pleased about in my original comment. It is sooo important.
Thanks for the update. I've now trashed my old CLR Directory routine, and adopted yours instead.
I don't yet see CLR as being a simple substitute for OLE automation. OLE is much quicker to lash together and maintain, if you have good templates and test harnesses. I see CLR more as a way of providing extensions to the SQL Server system to handle complex data. Now that is really exciting.......
Best wishes,
Phil Factor
February 7, 2009 at 6:25 am
Great article Jonathan!
February 7, 2009 at 7:25 am
Jeff Moden (2/6/2009)
.... One of my major points was that someone posted some a link to some pretty bad T-SQL and the race between that
and a CLR and a test that probably wasn't even done correctly.....
Boy, am I glad to be "someone" now.....
I was perfectly aware that the T-SQL code is not the best. The idea of providing that link was to show the reasoning behind Andy's article on possible applications for SQLCLR. He explicitly wrote that the code is not the best.. I hope people read!
Besides that, it shows with plenty-o-external-links the process on creating a CLR, informative for anyone..
Yes, yes, mea culpa on trying to show everyone bad coding and trying to point that "people don't go CLR crazy". If someone just takes any code thrown at them without even thinking on the consequences of applying it in their environment, they have no business being a DBA/Developer.
At least it sparked a series of very informative discussion between you and Jonathan, with your always excellent code samples 🙂
Cheers
February 7, 2009 at 7:36 am
Phil Factor (2/7/2009)
I see CLR more as a way of providing extensions to the SQL Server system to handle complex data. Now that is really exciting.......
For what it's worth, that's more of what I saw CLRs being used for instead of a convenient method for people who don't know T-SQL to still write code against a database. Regex, the things you wrote about, and, if you want to skip SSIS, file handling are a couple of great places for properly written CLRs.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2009 at 8:06 am
Richard M (2/7/2009)
Boy, am I glad to be "someone" now.....
More than that, I've got a pretty healthy respect for you because of the way you just responded and have responded in the past. It wasn't my intent to make it personal and, thank goodness, you didn't take it personal. Thank you, Sir!
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2009 at 8:27 am
Jonathan Kehayias (2/6/2009)
I've got some more coming that it will be fun to see your comments on. All along this same line of processing, but its always interesting to see what you come up with for TSQL equivalents to the SQLCLR. As you say, the places I can write CLR to beat TSQL has been limited, and some of your tricks like appending a '/' at the end of a URL in TSQL to make domain name extraction lightening fast are very interesting and something to learn from.
Knowing your abilities, there will undoubtedly be things where T-SQL and I just don't stand a chance, but I sure do look forward to trying. I always look forward to anything written by Jonathan Kehayias. And, like Phil, I'm tickled that, if our group at work does come up with the need for a viable CLR, we now have a great template to follow that considers the right kind of security that the Systems DBA's are likely to buy. Thank you, Sir, and very well done!
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2010 at 12:10 pm
Hey there - thanks for the article; it was just what I was looking for.
I've decided to deploy my CLR via VS2010, using a VB SQL CLR project. However, my code requires system.directoryservices. My directoryservices code works fine in a normal vb module where I can add the reference to system.directory services, however, in my VB SQL CLR module, I don't have the ability to do so. I'm suing the CLR project for the "auto deployment" scenario you speak of.
Do you have any recommendation on how to 1) import system.directoryservices into my CLR project, or 2) compile my code and "manually" add it as a clr?
Thanks man.
Thor (Hammer of God)
January 28, 2010 at 8:00 am
Thank you for the article, i was able to implement a simple SQLCLR assebly, It just does basic file operations , the function receives the Source path and Destination path from the calling procedure and the SQLCLR function ( written in C#) only copies the file to the destination path.
Everything was excelled when deployed, the apps showed a big boost in performance..
Suddenly after about 3hr, the SQLCLR function start throwing .NET exceptions. :
" An error occurred in the Microsoft .NET Framework while trying to load assembly id 65539. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues.
System.IO.FileLoadException: Could not load file or assembly 'FileCopy' "
The fuction was called extensively ( about 10 call evey 10 secs ).
Could someone please comment on this, about why this happened. I thought .NET takes care of memory leaks on its own, and this does not need to be handeled in the code.
Any comments on this will be greatly appreiciated.
Thanks in Advance!!
January 28, 2010 at 9:12 am
What is your C# code for the function? I am guessing that this is a 32 bit SQL Server? It may not have anything to do with a memory leak, or failure to deallocate the memory used, but instead with VAS fragmentation over time when coupled with other VAS consumers in your environment, something very specific to 32 bit servers where the VAS is limited and SQL Server only takes a default reservation of 384MB (which is very small) for running VAS consumers like SQL CLR. We need more information like the C# code, whether SQL is 32 or 64 bit, and the amount of physical ram installed on the server.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
January 28, 2010 at 9:38 am
Hi Jonathan
Thanks for replying back..
Server is Win Server 2003 R2 , 64 bit edition,
32 GB RAM installed,
Microsoft SQL Server 2005 - 9.00.4229.00 (X64)
Jul 29 2009 14:59:51
Standard Edition (64-bit) on Windows NT 5.2
(Build 3790: Service Pack 2)
C# code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 udf_doFilecopy(SqlString sSrc, SqlString sDest)
{
try
{
if (File.Exists(sDest.Value) == false && File.Exists(sSrc.Value))
{
FileInfo fileInfo = new FileInfo(sDest.Value)
if (Directory.Exists(fileInfo.DirectoryName))
{
File.Copy(sSrc.Value, sDest.Value);
return (0);
}
else
{
Directory.CreateDirectory(fileInfo.DirectoryName);
File.Copy(sSrc.Value, sDest.Value);
return (0);
}
}
else if (File.Exists(sDest.Value) == true)
return (1);
else if (File.Exists(sSrc.Value) == false)
return (2);
else
return (3);
}
catch (Exception e)
{
return (-1);
}
}
January 28, 2010 at 10:07 am
What is Max Server Memory configured to on the server and is it using Lock Pages in Memory?
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
January 28, 2010 at 10:22 am
Max Memory for SQL: 20GB
Lock pages in memory is enabled in for the user with which the SQL runs
January 29, 2010 at 6:17 am
just a sanity check, have you verified in the sql server logs that SQL server is using locked pages. Check the instance startup text in the log to verify lock pages is in force
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 46 through 60 (of 168 total)
You must be logged in to reply to this topic. Login to reply