January 17, 2015 at 12:05 am
Comments posted to this topic are about the item Where Microsoft went wrong with SQL CLR
Best wishes,
Phil Factor
January 17, 2015 at 8:39 am
It may be uncharitable of me but I've had the feeling that Microsoft did not really want to open up SQL Server to extensions that might compete with its own add-ons.
For example, we've been struggling to add some data analysis routines to SQL Server so we can perform some financial modeling within the tables being returned from a function/stored procedure. When the ability to leverage CLR was announced it seemed perfect. Only thing is that the .NET library version supported within SQL Server was 2.0 and the analysis functions were using a much more modern version.
As I continued searching for solutions (and ended up with a R server communicating with SQL Server via polled tables), I realized that what I was trying to do might have been possible in the SS Analysis Services and was therefore competitive. My understanding is that this type of bolt-on library/process is well supported by PostgreSQL and perhaps other DB engines.
January 17, 2015 at 11:04 am
I actually think the CLR has worked well. MS used it internally to add things like HierarchyID to the native product (along with Spatial and other items). It gives them flexibility.
I see more and more people adding specialized assemblies to their instances for specific things. I'm not quite sure why it hasn't caught on more, perhaps because it does require a degree of knowledge to write efficient assemblies, but I suspect it's more that it isn't really needed in many cases.
January 18, 2015 at 8:57 pm
I think Phil's thoughts and concerns are spot on. The specific knowledge required to code, secure, test, optimize, deploy, maintain, and document SQL CLR functionality between SQL devs, system administrators, DBA's, and end users is not trivial. It doesn't seem like Microsoft has spent much effort in promoting and encouraging adoption of SQL CLR.
All of that being said, I've been successfully utilizing SQL CLR for a while now. I did so by purchasing (I'm not endorsed or compensated in any way by the vendor) the SQL# library that Solomon Rutzky[/url] created and I can't gush enough about how easy it is to install and use it. I recommend early adopters think of using it initially for a single specific use case (e.g. regex, date functions, file manipulation, etc) and get comfortable with it. Once you do that, I'd be surprised if you didn't start seeing the myriad of different places you can utilize the library.
I'm doing things with a couple lines of T-SQL code that would have required 100's of lines of code in C# or some other programming language and instead I can utilize the skills and tools that I use every day.
If Microsoft implemented all the features the SQL# library implemented, I'd be using them, but until then, I'm happy that the SQL CLR functionality exists and that there are a few vendors out there who have taken advantage of it.
I've created a couple custom SQL CLR functions in the past (that SQL# wouldn't have been able to satisfy) and I can say, it's not a trivial exercise. (especially the testing part)
January 19, 2015 at 4:59 am
I have thought, oh yes, I could do that as a CLR project, a few times. It does work. However you then have to consider all the developer-y stuff like framework version installed (if your prod server does not have the right one then it is not always straightforward or even possible to add framework versions). Then you get halfway through before realising that actually this particular function will not actually work for whatever reason. Brilliant. Back to a tried and tested, if primitive, method. Cheers for the little journey down a blind alley.
Still, it is definitely good for a few things, but no so good that it is essential IMO.
January 19, 2015 at 6:51 am
To my eyes it's just another tool which I included in my tool box.
When designing stuff, I have the flexibility to have another option at hand (CLR)
If it suits a feature, fine, if not, then the feature will require, anyway, something else to compensate for it which would go through, most of the times, the whole process.
As everything else, it's up to you to determine if the CLR suits what you need.
Using the wrong tool at the wrong place will scale difficulty / maintenance etc.
Beside, Oracle already had java at that time while Microsoft had nothing. Somewhat a feature war between those 2 giants.
Finally... isn't it the marketing purpose to "smoke and mirrors" stuff ?
January 19, 2015 at 11:23 am
Well, I actually happen to be quite happy with SQLCLR. Mostly for the fact that it exists and allows us to accomplish stuff that either cannot happen otherwise, or would take an enormous amount of effort. Yes, the feature can certainly (and will hopefully be) improved. But that is the nature of software anyway: always room for improvement / a "living document" if you will.
Does it take extra effort to get a SQLCLR project into Production? Yes, there are some extra steps and maybe a developer needs to review it, etc. But, boo hoo. Seriously. There are times when a project needs to get done and developer resources are already allocated elsewhere. There are times when a project can go either way, and someone is sure to provide the to-be-expected mantra of "you shouldn't have business logic in the database". Sorry, but I have an export job to create and can do it in SSIS, BCP, stand-alone .NET app, or SQLCLR. SSIS and BCP could not handle the requirements. Stand-alone .NET app would require getting a developer involved and setting up a new project and then scheduling that to run. Or I could just do it in SQLCLR and have it seemlessly integrated with the other DB-specific aspects of the project. Long-term maintenance, especially after the developer who created either one of those leaves? Not nearly as different as everyone seems to think. It doesn't matter if it is SQLCLR or a console app. It will be code that is buried somewhere and nobody really knows what it is or what server it was placed on, etc.
Any "failure" of SQLCLR mostly (but not entirely) comes down to false expectations, the prevalence of misinformation, and a general lack of quality educational material. Some people are just expecting too much from it, just as some folks expect it to be a "security risk" or a "performance problem". All of that is bunk. It is a tool, just like all of the other features, that has its place, can do incredibly great things, and can just as easily be used inappropriately and cause a slew of negative effects on the system. And I am doing what I can to improve the quality of reference material via the Stairway to SQLCLR[/url] series.
From the editorial:
but we don't want a Do-It-Yourself kit to achieve this
Actually, yes we do. We most certainly do. Not that built-in RegEx functionality wouldn't be better, but the ability to create any 1 of millions of functions that Microsoft has no interest in means that we can actually get stuff much sooner than by creating a Connect ticket and getting it voted on 400 times. Microsoft has their priorities on where to allocate developer time, and creating hundreds of new functions is pretty low (and I don't blame them). Yes, maybe they can assume that RegEx is such a hot topic that it is safe to add (it probably would have been a better use of developer time than implementing CONCAT and CHOOSE and a few of the other more recent additions), but would they have added all of the Regular Expression operations? Would they have allowed for capturing a group as a scalar function, or capturing any number of groups as a TVF? Would they have implemented all, or even just several, of the overloads of any of those functions that let you pass in the start index or the count of how many matches to return? Would they allow for passing in any of the RegEx Options ? Most likely not.
What if you need FTP? Microsoft already provides that via the FTP Task in SSIS. Ok, but what if you want/need to use FTPS (not the same thing as SFTP)? Nope. What if it is a large file and the connection and/or process aborts and you don't want to have to start over, especially if the 10 GB file was 90% downloaded when it stopped? FTP does allow for passing in a start index so that you can simply "continue" the download for the remaining 10%, but that option is not available in SSIS. What if you need to use Windows Authentication? Well, according to the FTP Task documentation (and FTP Connection Manager):
The FTP connection manager supports only anonymous authentication and basic authentication. It does not support Windows Authentication.
And what if the FTP task does do what you want but it is just one minor part of the process that you don't want to even deal with SSIS for, and you don't want to call xp_cmdshell to call ftp.exe?
Yes, what Microsoft provided via SQLCLR is certainly not accessible to everyone. But what they have provided allows for much more (to those who can make use of it) than anything else they could have provided that would have been accessible to a larger audience.
And this is where SQL#[/url] fits in. It is a library of a lot of functionality for people who don't want to deal with writing the code and/or managing/deploying said code. And it already has the RegEx and FTP functionality I just mentioned (well, not using Windows Auth for FTP, but that would take an entire 1 line of code to do).
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
January 19, 2015 at 12:26 pm
And yet this "killer" feature, which should be quite mature by now, is not in SQL Azure.
As for the "DIY kit" mentality, well, that's where the developer community and the Internet comes in. As Solomon suggests, pimping SQL Server to the nines with esoteric functions is probably not the best use of Microsoft resources. Developers like Solomon create CLR functions that people want and share their knowledge online. Meanwhile Microsoft focuses on making SQL Server a better-functioning product.
Jay Bienvenu | http://bienv.com | http://twitter.com/jbnv
January 19, 2015 at 12:36 pm
@Solomon Rutzky
I'm happy with SQL CLR too. My editorial was more to speculate as to why it wasn't adopted with the enthusiasm that Microsoft initially expected. I suspect that the predicament of the developer for whom CLR is a good solution depends on the IT department and the architectural/strategic rules in place. I suspect that few developers are actually encouraged to hand-cut a CLR solution, despite the fact that in the hands of an expert it provides an excellent solution. For most routine jobs that are beyond TSQL, such as providing a Regex library, it actually makes the most sense to reach for SQL# for most of the routine stuff, because I suspect that most shops will prefer a supported third-party product with a good reputation, to something that is DIY. Much as I like recommending SQL# though, I'd have thought that most of the functions you've got there are so generally useful that they should have been in the product, provided as a CLR extension to the SQL standard, just in the same way as spatial/geometry is.
Best wishes,
Phil Factor
January 19, 2015 at 12:48 pm
jbnv (1/19/2015)
And yet this "killer" feature, which should be quite mature by now, is not in SQL Azure.
Hi there, Jay. Actually, this was true until very recently. The new version -- V12, currently in "preview" -- does support SQLCLR with the following restrictions:
1) Only "SAFE" assemblies are allowed
2) Assemblies must be created from the hex digits (i.e. "assembly bits" as it is called in the MSDN CREATE ASSEMBLY page). Meaning "FROM 0x4D907E...." instead of "FROM 'path\to\some.DLL'".
You can read more about it here:
http://azure.microsoft.com/en-us/documentation/articles/sql-database-preview-whats-new/
As for the "DIY kit" mentality, well, that's where the developer community and the Internet comes in. As Solomon suggests, pimping SQL Server to the nines with esoteric functions is probably not the best use of Microsoft resources. Developers like Solomon create CLR functions that people want and share their knowledge online. Meanwhile Microsoft focuses on making SQL Server a better-functioning product.
This is exactly what I was trying to get across. I would much rather the Microsoft developers spend their time working on major features like In Memory OLTP, etc than this minutia.
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
January 19, 2015 at 1:16 pm
Phil Factor (1/19/2015)
@Solomon RutzkyI'm happy with SQL CLR too. My editorial was more to speculate as to why it wasn't adopted with the enthusiasm that Microsoft initially expected. I suspect that the predicament of the developer for whom CLR is a good solution depends on the IT department and the architectural/strategic rules in place. I suspect that few developers are actually encouraged to hand-cut a CLR solution, despite the fact that in the hands of an expert it provides an excellent solution. For most routine jobs that are beyond TSQL, such as providing a Regex library, it actually makes the most sense to reach for SQL# for most of the routine stuff, because I suspect that most shops will prefer a supported third-party product with a good reputation, to something that is DIY. Much as I like recommending SQL# though, I'd have thought that most of the functions you've got there are so generally useful that they should have been in the product, provided as a CLR extension to the SQL standard, just in the same way as spatial/geometry is.
Ok, thanks for clarifying this a bit. And to a degree I also feel that much of what I have added to SQL# should be base functionality, just like it is for PostgreSQL and MySQL (and in fact, I got some of my ideas from those 2 systems 😉 ).
Ideally (at least in my biased view), Microsoft would take the easy road (I assume it would be easy enough) and let me integrate SQL# into SQL Server, just like they have done with the 3 CLR types (spatial, geometry, and hierarchyID) as well as some of the new functions (FORMAT, etc). Then I would at least have access to the "real" API that allows for overloading so there can be a different set of input params when not using truly optional ones. And then they still wouldn't be wasting any developer time on it (outside of paying me 😀 ).
But as far as adoption is concerned, I am actually not terribly surprised. This feature is at a crossroads that most people are not at. Most developers don't "get" the database side of things (well, not as much as they think they do) and a lot of the nuances of living in SQL Server's world (yes, yes, there are plenty that do and no disrespect to the general population, but .....). At the same time, most DBA's / DBE's aren't accomplished enough in C# to do much more than a simple RegEx or whatever, if even that much. And that is all fine on both sides. This just happens to be a feature that naturally appeals to a subset of people: those with the overlapping skills of .NET and T-SQL. This is a large part of why I started SQL#, not just because I wanted to use those functions myself, but also knowing that most database folks wouldn't be doing the .NET stuff no matter what, and for a variety of reasons.
Similary, I really like Analysis Services (SSAS) and think it has great potential, but how many people really understand that side of things enough to get a lot out of it? I don't and haven't met anyone who does, but if they ever figure out how to add another hour or two to a day, then I will learn it. Well, after Service Broker, PowerShell, and .....
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
January 19, 2015 at 3:33 pm
I think SQLCLR hit a cultural barrier. DBAs were hesitant and perceived it to be a Trojan horse to allow all sorts of sins to be inflicted on the DB.
Developers perceived it to be yet another case of DBAs insisting that certain things be under their control.
Solomon's Stairways to SQLCLR series highlights that DBA fears are largely unfounded. Host Protection Attributes exist to do just that, protect the host which in this case is SQL Server.
In many cases things that could have been done in SQLCLR were put in the application instead and justified with the perception that it would be more appropriate due to scale ability concerns.
My early frustration was that the VS shell necessary to support SSIS/SSAS/SSRS did not include what was greatly helpful to allow a DBA to develop SQLCLR features. You could hand crank everything but that was only for hard core coders.
I do feel that multi-parameter CLR Aggregates could have been better supported and greater emphasis put on examples for SQLCLR Aggregates.
As to the other features. I'm still not clear on precisely how spatial indexes work with regard to the 3 different levels each with a high/medium/low setting.
Service Broker sounded great right up to the point where I found the conversation had to take place between two DB servers. It would be great if it had come with the ability of an app to talk to an instance of SQL Server in a guaranteed ordered delivery queue.
Notification services still sounds like a good idea but whether it is more complicated than would be required is still a matter for debate.
January 20, 2015 at 3:28 am
I agree with the OP.
Why does code need to be maintained as .NET assemblies by parties that likely are no stakeholder?
Why not create a type/class of stored procedure that uses a simplified language to provide a more natural interface to SQL Server then C# or other .NET languages do?
Having access to the .NET library from such a language would be great and the compiler for the language would can be integrated in SQL Server itself.
January 20, 2015 at 8:37 am
peter-757102 (1/20/2015)
Why does code need to be maintained as .NET assemblies by parties that likely are no stakeholder?
So who would create a library and not be a stakeholder? Just curious.
Why not create a type/class of stored procedure that uses a simplified language to provide a more natural interface to SQL Server then C# or other .NET languages do?
Having access to the .NET library from such a language would be great and the compiler for the language would can be integrated in SQL Server itself.
Why not create a new language? I think that would be more work for less benefit.
Less benefit due to being a constrained language. SQLCLR is constrained enough just to be workable within SQL Server. It would be a far less useful language/integration if it did less than what it can do now.
More work for Microsoft due to being yet another language for MS to come up with and maintain. That requires more developer resources working on a completely different, non-overlapping feature (as opposed to modifying .NET stuff). It requires more documentation / training material to be produced. More marketing material. Instead, they just provided an API between SQL Server and the CLR and created some classes in .NET (the SqlType namespace: SqlString, SqlInt32, etc) to assist that API. And they created the Host Protection attributes to allow hosts other than the Windows OS to declare what is not safe within the context of that particular host.
More work for all of us in order to learn yet another language. At least with using .NET you can use any .NET language (C#, VB.Net, F#, J#, and so on...). This is a transferable skill in both directions: people who already know at least one .NET language can apply that skill here with only needing to learn the nuance of working within the SQL Server environment. Those who only know T-SQL will have to learn a new language anyway, but due to it being .NET that skill can be used to better understand the app code that is interacting with their database. To me that is a win-win situation.
On the other hand, I do not relish the thought of spending time learning a new language. Time is the most valuable resource we have and if I had any of it to learn a language, I would:
1) Improve my C# skills / knowledge
2) Learn PowerShell
3) Learn MDX
In fact, I greatly despise the thought of another language as I have learned too many (and forgotten several) of them already over the past 30 years:
1) Basic (on Apple II)
2) Basic (on TRS-80)
3) Basic (on TI-99/4a)
4) GW-BASIC (PCs)
5) PL/I (for college)
6) JCL (for college in order to execute the silly PL/I)
7) Assembler (Mainframe -- 370, I think; for college)
8) C
9) Ksh (Korne Shell--Unix shell scripting)
10) Bash (Born-Again Shell--Unix shell scripting)
11) JavaScript
12) PHP
13) VBscript 6 (for ASP pages)
14) IBM Net.Data (an abomination of a language used only in IBM Net.Commerce server)
15) SQL (learned on DB/2)
16) PL/SQL (for Oracle)
17) T-SQL (for Microsoft SQL Server)
18) whatever scripting language InfoReports used (report writer from PLATINUM software)
19) PL/pgSQL (for PostgreSQL)
20) HTML / XHTML
21) XML
22) IBM DB/2 stored procedures
23) MySQL SQL dialect
24) XBasic (used in Alpha5 from Alpha Software: http://wiki.alphasoftware.com/Contents+-+Learning+Xbasic )
25) DOS Batch/CMD scripting (yes, it actually can do quite a bit: http://www.dostips.com/ )
26) CSS
And there are several that I started to learn but didn't get very far:
JAVA, Perl, Informix stored procedures, XAML (for SilverLight), etc.
And I can't be the only one to have a list like this 😉
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
January 20, 2015 at 9:08 am
Solomon Rutzky (1/20/2015)
peter-757102 (1/20/2015)
Why does code need to be maintained as .NET assemblies by parties that likely are no stakeholder?So who would create a library and not be a stakeholder? Just curious.
I meant that when a DBA wants those extra .NET CLR tools to work with in stored procedures, the developers that can build the .NET CLR tools have no direct use for it and are not much invested (assuming they work with the stored procedures).
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply