January 25, 2008 at 11:53 am
You can do all logical operations in just NOT and OR (maybe NOT and AND, I don't remember) though I'm not sure about bitwise.
You can do things like shifting with * and / (2^n) but it isn't as easy. This is exactly what makes one tool for one job more appropriate than another.
I think there's a danger that since so many people abuse CLR, a lot of people simply write off use of CLR altogether unless there is absolutely no other way of doing it such as networking or I/O. In reality it has advantages in certain areas as I have mentioned even if it is possible to do it in T-SQL, though in the majority of cases T-SQL is more appropriate as most database operations, of course, involve manipulating data.
Another example could be something like storing images in a database and using these procedures to analyse the image to produce results or prepare for queries using existing libraries (not necessarily part of the .net api but if you already have a library that does something such as an image manipulation library, why reinvent the wheel), though this would probably be better placed in the application, depending on the needs.
February 13, 2008 at 11:12 pm
In my workplace we have recently started replacing xp_cmdshell calls with specialized calls to database objects (sprocs/UDFs) that perform file system manipulation tasks, eg. file deletion, directory listing, detection of network share used space etc.
In addition to it being a security risk because of its highly non-specialized nature, xp_cmdshell is also very clumsy. Picture, for example, a situation where one wants to list the contents of a certain directory and extract the summed size of a group of files. With xp_cmdshell it is at least several lines of code PLUS one or more temporary objects (table variables/temp tables) to store intermediate data. With the right SQL CLR object it is ONE line of code! This constitutes a performance benefit and adds to the ease of development and maintenability of the code. Not to mention that the possibility of catastrophic human error or malicious intent is also reduced. With "xp_cmdshell" one can with one wide swipe delete a large part of the filesystem, introduce a virus etc. With highly specialized calls to CLR objects, the possibility for such disasters is largely mitigated and there is tighter control.
It has always struck me that the one professional group most resistant to CLR integration within SQL Server tends to be the one that stands to gain most from it... 😉
We DBAs usually do not develop multi-tiered code. We are mostly interested in quick and dirty solutions using SQL to give us information on our environmental infrastructure: file system, network shares etc.
A developer working on a sophisticated multi-tiered application would likely put such code in the business layer and would NOT involve SQL. Since we DBAs live and work essentially exclusively on the database end, we do not often have the luxury of a business tier. We develop exclusively DATABASE objects (sprocs etc.) for our day-to-day work. This is where SQL CLR can come to be a valuable tool for us for anything that cannot be evaluated using the set-based logic of T-SQL.
Some aspects that can benefit from the CLR (apart from regular expressions and file-system operations):
- custom aggregates: who out there would not love an aggregate like SUM for concatenating strings on a table column?
- registry information: reporting on registry info from many servers
- active directory info: reporting on which SEC groups a windows acct is a member of
- System log info: reporting on recent errors from multiple servers
The possibilities are endless... 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 14, 2008 at 6:11 am
While I certainly agree that one should use the right tool for the right job, I'll also be the first to disagree about the shorter the code, the faster it runs. In the procedureal world, that may be true... in the set based world, it's usually not true. There are, of course, contrary exceptions to both worlds.
--Jeff Moden
February 14, 2008 at 7:56 am
Marios Philippopoulos (2/13/2008)
It has always struck me that the one professional group most resistant to CLR integration within SQL Server tends to be the one that stands to gain most from it... 😉
We DBAs usually do not develop multi-tiered code. We are mostly interested in quick and dirty solutions using SQL to give us information on our environmental infrastructure: file system, network shares etc.
A developer working on a sophisticated multi-tiered application would likely put such code in the business layer and would NOT involve SQL. Since we DBAs live and work essentially exclusively on the database end, we do not often have the luxury of a business tier. We develop exclusively DATABASE objects (sprocs etc.) for our day-to-day work. This is where SQL CLR can come to be a valuable tool for us for anything that cannot be evaluated using the set-based logic of T-SQL.
Some aspects that can benefit from the CLR (apart from regular expressions and file-system operations):
- custom aggregates: who out there would not love an aggregate like SUM for concatenating strings on a table column?
- registry information: reporting on registry info from many servers
- active directory info: reporting on which SEC groups a windows acct is a member of
- System log info: reporting on recent errors from multiple servers
The possibilities are endless... 🙂
I have to take you to task on a few things.. First - let's start by saying I am both a developer and a DBA (or at least - I play on on TV), and am not averse to using CLR when it's appropriate.
I'm however averse to making SQL Server the automation tool to run other things, like - file maintenance, Active directory security, etc.... There are good reasons that SQL server does and should have some amount of insulation between the data INSIDE and the stuff outside: because of that, it will never be as "convenient", useful, etc... to do those thing from tools internal to SQL Server. It's like asking a Web application to start doing the same kind of maintenance? Why don't we ask a backup tool to run FTP jobs? In all cases - the answer is - you're using the wrong hammer on the wrong nail. And - like it or not - CLR is no more intrinsically secure, or prone to preventing you from deleting large swaths of your directory. The fact that you open a door in means you have to now continue to watch that door: CLR or no, bad code is bad code, and will still rain fire down on your day.
Also - like it or not - the CLR objects in SQL Server are SQL objects just like any other SQL Object. If you see a conceptual difference between CLR objects as a "business layer" in SQL Server, and yet think of stored procedures as part of the Data Layer - stop, because the distinction isn't there. I personally think that both are often in the Business layer for all of those apps not big enough to justify having separate DAL/BL.
One of the reasons I'm not thrilled with the CLR in 2005 for most things is that the implementation given to me is one that is in most cases not as performant as the "native" solution. If you spend some time reading through some of the threads on here - you'll see there's been a fair amount of testing on a lot of applications of CLR and in almost every case - the set-based version wipes the floor with CLR. And it's not necessarily the CLR code's fault - it runs FASTER and returns stuff faster when OUTSIDE SQL Server. If you want more CLR in SQL Server - then make the components we have perform as well as the native components.
Why? Because that DBA who's "holding up" (I mean - the nerve of him) that wonderfully complex n-tier project, demanding performance, etc... has 100 OTHER, wonderfully complex n-tiered projects in production, who all want THEIR data, yesterday.
Finally - I sure do hope that your DBA's are in fact involved in your n-tiered development. I really do, because if they're not - your app will hit production like a ton of bricks, and everyone will wonder why the wonderful 62 layer of abstraction squeal like a stuck pig dealing with anything over 10,000 rows. Been there, done that (from both sides of the fence) - got the scars to remind myself not to do that again.
Quick and dirty belongs on your dev box. Leave it there.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 14, 2008 at 9:10 am
I think the security he was talking about is purely down to xp_cmdshell. Running arbitrary commands with administrative privileges will always be dangerous. Encapsulating the exact command you want in one procedure and just passing it the argument stops arbitrary commands from being run. This is no different in CLR as it is in the old extended stored procedures.
I think that if you do this, it actually increases the separation between the internals and externals. T-SQL is not required to run native commands (via xp_cmdshell - a very broad and insecure extended procedure) and get the result, it asks an external procedure, CLR or not, to do something specific and return the data. This is more of a reflection on xp_cmdshell's security rather than anything else.
The DBMS optimises data manipulation expressions so well that nothing will ever be as fast for data manipulation. T-SQL should be used everywhere possible for this. However, it is not suitable in other places.
My point really is that both methods have their advantages and should be used in the situations that suit them. Just because CLR procedures are commonly abused, it does not mean that they are inherently bad. People just need to be educated in order to use them only in the appropriate situations. Saying that they can never be used well is just a sweeping statement and a fallacy.
February 14, 2008 at 9:10 am
Matt Miller (2/14/2008)
I'm however averse to making SQL Server the automation tool to run other things, like - file maintenance, Active directory security, etc.... There are good reasons that SQL server does and should have some amount of insulation between the data INSIDE and the stuff outside: because of that, it will never be as "convenient", useful, etc... to do those thing from tools internal to SQL Server. It's like asking a Web application to start doing the same kind of maintenance? Why don't we ask a backup tool to run FTP jobs? In all cases - the answer is - you're using the wrong hammer on the wrong nail.
Think of Reporting Services as the "web" app calling SQL CLR objects to report on things such as Active Directory settings, system log critical errors in a sql-server box etc. Which DBA would not love being able to monitor this type of information without having to build a full-fledged Windows/Web app to do the same thing? I see the combination of SSRS and SQL CLR as a match made in heaven for these kinds of problems.
The role of the DBA extends beyond the database realm, and SSRS/SQLCLR can help automate monitoring tasks on the network/OS side that I just cannot fathom any better alternatives to.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply