Thursday, February 14, 2013

One can discuss if SQLCLR is an important feature or not. My opinion? Yes, it might be useful in some specific scenarios BUT I still say that the Data is the most valuable part of any system (or business) and you'll want it to be as much controlled, secured and closed as possible. And I'm a .NET enthusiastic...

However, If you do need to use SQLCLR be aware that there is a limited list of supported assemblies that you can use. Yes, you can load any assembly that you want, BUT, if it's not in the approved list, "you are now shifting the responsibility of managing those assemblies to *yourself*, the SQL Server application programmer and/or DBA". And this is no problem if it's your assembly, after all, it's your code. But you also become responsible for some of the Microsoft’s Assemblies not in the supported list! Did you know that System.DirectoryServices.dll is not on that list?

One other important issue has to do with the versioning. Quoting Microsoft, "servicing or upgrading libraries in the GAC does not update those assemblies inside SQL Server. If an assembly exists both in a SQL Server database and in the GAC, the two copies of the assembly must exactly match. If they do not match, an error will occur when the assembly is used by SQL Server CLR integration." And by "must exactly match", it includes MVIDs.

"When the CLR loads an assembly, the CLR verifies that the same assembly is in the GAC. If the same assembly is in the GAC, the CLR verifies that the Module Version IDs (MVIDs) of these assemblies match. (...) When an assembly is recompiled, the MVID of the assembly changes. Therefore, if you update the .NET Framework, the .NET Framework assemblies have different MVIDs because those assemblies are recompiled. Additionally, if you update your own assembly, the assembly is recompiled. Therefore, the assembly also has a different MVID."

So, final conclusion: what this means is that if you're using unsupported assemblies "machine widely", any update in the GAC must be aware that it might "break" something if also used inside SQLServer.