CLR Assemblies in Azure SQL Database

I know a lot of DBAs that are adamant about the exclusion of CLR on their databases. There is no subtlety or nuance or discussion with the development team about the possibilities of a given solution actually running better in compiled CLR code. They just trot out the DBA's favorite word... NO. Prior to v12, that was also Microsoft's stance on Azure SQL Database. Not now.

Amongst the long list of supported T-SQL features is a very short phrase: Safe binary bit CLR assemblies. Now, before you get too excited, within the not very long list of unsupported T-SQL features is another phrase: CLR assemblies. So which is it? Are they supported or aren’t they?

Before we get there, I want to take a little side-track. Why won’t you use CLR? Maybe I’m weird as a DBA. I came out of development. I think of developers as my friends and co-workers, not the enemy (although, my friends and co-works can be a little pig-headed and ignorant at times). I not only have faith that their intentions are good, but I have a long held understanding that they actually know what they’re doing. When you couple this with the fact that SQL Server, and more specifically, T-SQL can be quite problematic with certain types code behaviors, I’m more than ready to recognize that CLR is a viable option. I’ve only seen it in a production environment once, but it was chugging away doing great stuff. You really should consider it for those unique problems that T-SQL doesn’t solve well, like adjacency lists, you know Hierarchies. Don’t believe me that it doesn’t work well with those, I’ll let Adam Machanic explain it.

Now, back to Azure.

You can add CLR to your Azure SQL Database, if you’re running v12. But, that has to be both Safe, an important phrase in CLR, and binary in order for you to make this happen. It doesn’t support any other assembly types. Remember, this is a database on a platform you’re working, still SQL Server, but not a server instance. Just a database. When speaking about CLR, what does Safe mean? There are three levels of restrictions on CLR code, UNSAFE, EXTERNAL_ACCESS, SAFE. I’m not going to detail all the limits placed on each of these types. Suffice to say that SAFE is the most restrictive. You get to run the code within the CLR assembly, but, it can’t call any kind of external process and can only run within your database. In short, it completely makes sense based on how Azure SQL Database works.

Next up, it has to be binary. The shortest possible explanation for that is, that you can’t upload a file. You don’t have access to a server. So, in order to create the assembly, you have to have converted the DLL that you created for doing your CLR processing into binary. There are a number of different ways to get this done documented all over the place. Here’s one in PowerShell. Here’s one in T-SQL.

From there everything is straight forward. You have to create the assembly:

CREATE ASSEMBLY MyAssemblyNameAUTHORIZATION dboFROM 0x0WITH PERMISSION_SET = SAFE;GO

In this example the binary is just representative.

Then, you have to create a function that will consume the assembly. Here’s an example from Adam’s scripts:

CREATE FUNCTION [dbo].[hierarchy_enqueue_inner](    @id [int],    @payload [nvarchar](4000))RETURNS [int]AS EXTERNAL NAME [hierarchy_navigator].[UserDefinedFunctions].[hierarchy_enqueue_inner]GO

With that you can now begin to use the T-SQL to make calls to the CLR code. Oh, let’s not forget, that we’re running within Azure SQL Database.

Back on Adam’s script. With his permission, I downloaded a copy of the code and ran it on my v12 database. Everything worked pretty flawlessly. Well, mostly. I didn’t end up with a parallel plan unlike Adam. I’m putting it down to differences in how the query optimizer behaves in Azure. You can check out the properties of the plan:

It says that the EstimatedDOPIsOne is the NonParallelPlanReason, which is a little shocking when you note that the Estimated Subtree Cost is 987789. Exploring parallelism and its behavior within Azure SQL Database is another adventure for another day.

Conclusion

While many of you are not running CLR within your SQL Server, some of you have identified useful functions where CLR performs better than SQL Server. You need that CLR code. Now, as long as you’re able to make it run under the SAFE restriction, you can create your assembly within Azure SQL Database and it will work, just like Adam’s code worked for me. This adds yet another level of functionality to the Platform as a Service offering for running relational storage within Azure.