Theo Ekelmans (2/25/2015)
By "But only to find out that .NET SQL CLR is "some what limited" in the things you can use." i ment that i got errors when i tried to use this construct in the CLRManagementScope scope = new ManagementScope("\\\\.\\ROOT\\cimv2");
ObjectQuery query = new ObjectQuery("SELECT * FROM Win32_Volume where Drivetype = 3");
ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);
ManagementObjectCollection queryCollection = searcher.Get();
It seems that ManagementObjectSearcher is .NET 2 based an not allowed in a CLR.
It would have solved my performance problem, because if i use it in my console app, it rips through all 139 mountpoints in 500 ms flat; hundreds of times faster then using the perfmon counter workaround.
Since this runs really reliable in an command shell i'm tempted to get the data using xp_cmdshell and a 6 KB console app.
Ok, so here's the dealio:
.NET 2.0 has nothing to do with it. The problem with your original plan is that those classes (ManagementScope, ManagementObjectSearcher , etc.) are in the System.Management DLL, and that is not in the Supported .NET Framework Libraries list. So you have two options:
Manually load System.Management.dll via CREATE ASSEMBLY. This will require you to most likely create it as UNSAFE, which in turn will require that your custom assembly be created as UNSAFE. And that will require you to also set the database to TRUSTWORTHY ON in order to get the Microsoft DLL to load as UNSAFE. Since you don't know what exactly is going on in the Microsoft DLL, it could be doing things that are fine for a single-threaded process but exhibit odd behavior in a shared environment such as SQLCLR. This is due to there being a single, shared App Domain (per database, per owner) in SQL Server. If the code was not expecting multiple users/processes (i.e. Sessions / SPIDS) to share static, class-level variables then there might be issues. So it just requires a bit of extra testing, but can certainly be done. I would recommend using a non-user-facing instance dedicated to internal maintenance jobs. I think SQL Server Express is great for this. I don't think it comes with SQL Agent, but it can be connected to via a Linked Server from an instance that is Standard or Enterprise and a SQL Agent job on one of those could exec a proc on the Express instance over a Linked Server rather easily. This set up would isolate any risk of instability of the UNSAFE code. HOWEVER, this won't work if System.Management is a "mixed" Assembly and not "pure" MSIL. And even if it is today, Microsoft might change it to "mixed" in the future which will mean that it won't be loadable into SQL Server and you will be forced to recode this functionality. That might never happen, but it could, and I have seen one question on Stack Overflow due to someone experiencing this very thing. The next Level of my Stairway to SQLCLR series covers all of this (should be published in 2 weeks).Use xp_cmdshell to run your console app. Using this method I would suggest formatting the output of the console app to be in XML. This should allow you to grab it from the result set ouptut of xp_cmdshell and then easily parse it using the built-in XML functions. I don't know if there is a maximum size of output that xp_cmdshell can handle, but if there is and this is too much, just have the console app save the XML to a file. And to make things a little safer, use the .NET method for generating a temp file name (in the File class I believe) and return that as output from the console app. Grab that temp file name via the result set output and then open that in your proc and get the XML. Of course, getting the file will require creating a SQLCLR function to File.ReadAllLines() or something like that, but that is supported and only requires EXTERNAL_ACCESS for the assembly. And since you can sign the assembly, you can create an Asymmetric Key from that DLL, a Login based on that Key, and then grant that Login the "EXTERNAL ACCESS ASSEMBLY" permission, all of which will allow you to set the Assembly to EXTERNAL_ACCESS without having to set the database to TRUSTWORTHY ON.I hope this helps.
Take care,
Solomon...