If you’re unfamiliar with SQL Common Language Runtime (CLR) objects, I’m happy for you. They’re not the worst, but it’s a steep learning curve for someone who ended up in a position they probably weren’t completely prepared for. And as my DevOps roomie likes to put it, “It feels icky.” Sometimes.
Since taking a technical position in January, SQL Server CLR objects have been the bane. Of my. Existence. At this point, we’ve sort of worked out a symbiotic relationship. However, in the beginning, I lived in constant fear of their existence. They were scary, uncharted territory I knew literally nothing about, and neither did anyone else on my team. I’m not here to toot my horn or jump on the rant train. Rather, I’d like to share my experience and understanding with hope that some other terrified junior DBA finds it helpful.
For those with a bit more experience under their belts, I profusely apologize for the rough explanation. Any correction or critique wouldn’t go unappreciated.
——————————————————————-
Roughly, Common Language Runtime works with .NET Framework to process managed code. Well…what the fuck is .NET Framework and managed code, right? If you’ve ever played RPGs, allow me a comparison to Common Tongue languages. Your Teir’Dal (dark elf or drow for you non-EverQuesters) speaks Elvish, but your Iksar buddy speaks Lizardman. How are you supposed to get anything done?! Common Tongue.
Common Language Runtime (very) basically uses .Net Framework as a translator for code. When code is compiled in a language .Net Framework can work with, it’s managed by CLR while running (or during its runtime). The code is translated into an intermediate language that can be used to communicate with objects written in other languages.
CLR manages a ton of stuff outside of this process, and it’s not exclusive to SQL (duh). However, for the sake of simplicity, from here on it will be! CLR objects allow commands inside a SQL Server environment that may not be easily achievable using T-SQL script, or may provide better performance. For example, my team uses CLR objects to employ regular expressions (regex) and access files and directories.
Sure, sure, there are options outside of CLR & managed code to do things like that. Public declaration: I KNOW! And I’m working on it. One hurdle at a time, folks. Anyway, in order for these objects to actually work, there are a couple of steps that must be take:
1. Managed code is complied into a module to deploy in SQL Server; in my experience, these are written in C# or Visual Basic. The result is a Dynamic Link Library (DLL) file. This is where our nifty translator gets it’s code information.
2. CLR is normally disabled on a SQL server for security reasons. I (maybe) won’t go into detail, but here’s a nifty link.
3. An assembly must be created in the database to utilize the DLL using the CREATE ASSEMBLY syntax.
4. Functions can subsequently be created, referencing the assemblies to do nifty things (like split data, write & move files).
5. Database ‘Trustworthy’ and owner settings may need to be changed.
The end result is functionality that can be called to make life easier. As indicated in the title, I’m aiming for a multi-post exposition. In the next one, I’ll get into compiling DLLs using Visual Studio, and expand on the uses mentioned above.
Sources for additional information:
TechTarget – Common Language Runtime (CLR)
Microsoft – Common Language Runtime