SSWUGtv
With Stephen Wynkoop
In this episode Steve takes on the difficult task of cleaning data. Do you clean it first and then bring it in? Do you bring it in and then cleanse it. How about transforming data to fit your schema. Watch as Steve interviews key players in the data cleansing world.
Watch the Show
CLR in Azure
Lots of feedback on the CLR in general, and some regarding the CLR in SQL Azure. First, let me clarify that SQL Azure has no support at this time for CRL objects. The keywords to create CLR objects are not supported, regardless of using a hex representation of the binary, or pointing to a file located on the server.
Why this is the case I would have to guess. I know that on my own systems there are memory constraints when using the CLR. I can imagine the kind of problems you might have with a multi-tenanted system sharing limited resources.
Here’s some reader feedback:
Mark writes:
Been reading (with great interest) your comments in the SSWUG newsletters for some time. Thoroughly enjoy – so thank you
The most recent “CLR” commentary is particularly noteworthy.
While I have used CLR in a few places, by and large, I am not a huge fan. That was mainly due to the headaches and performance (memory) problems in the early days, and, the need to be able to support and manage on remote systems using various versions of SQL Server.
So, it came as no surprise that CLR went the same way as other features for a lot of hosted or cloud based systems. Bottom line, there have always been challenges with access to disk based systems if they are not on-premise, and a cloud based version of SQL is not going to change that, and CLR is not the only victim.
What I do find surprising is that Microsoft has not come up with a way that it can be coded essentially in-line if all the .Net framework is in place etc… (even if that means a privileged status of secretly writing to the tempdb folder).
At some point we need to decide if SQL is a coding platform, or, simply a database. Seems that in trying to keep up with other suppliers, Microsoft sometimes introduces features into the SQL platform that might best be packaged separately and therefore easier to configure, to license, to manage, or to combine various elements (feature sets) according to corporate need (and budget).
Vladimir writes:
CLR code does not have to live on the disk in file to be deployed, as your comments suggest.
We do not deploy them as files anyway but use binary value for DLL, script created by build process, similarly to what Visual Studio does in the background. Profile CLR deployment and you will see.
Editor Note: It would look something like this:
declare @assemblyBits varbinary(max);
select @assemblyBits = 0x4D5A90000300000004000000FFFF0000B80...
-- Create (or alter) assembly:
create assembly MyAssembly
from @assemblyBits
with permission_set = safe;
end
This works wonderfully and should work for Azure deployment.
So if CLR is not supported in Azure, then it is not true reason.
We write ERP software and as it relates to Azure, have no plans to support it, as customers we have cannot afford network instability to run their factories. LAN is still so much more secure and stable compare to cloud.
Editor Note: SQL Azure does not support the CREATE ASSEMBLY command.
Maurice Writes:
Probably Microsoft doesn’t want to have the CLR into SQL Azure because it is a multi-tenant database solution with a lot a rights restriction, and those rights are required to run the CLR. Also Microsoft wouldn’t like that the CLR could be leveraged as a tool to open doors from a security standpoint. Security risks or potential security breach doesn’t sells very well cloud solutions. An assembly could increase the surface area exposure of SQL Server, may create resource problem.
Anyway from a marketing standpoint SQL Azure is not a development functionality proposition, it is an infrastructure management solution. Both propositions goes against one another, it is harder to have both at the same time.
Really a much better good solution would simply to improve the T-SQL engine (ex: for performance in procedural code constructs, adding regular expression, enhancing user-defined function and supplying some other functions) so we would rely much less on CLR alternatives.
Mike Writes:
I’m worried you’ve got it backwards. Azure isn’t a reason to not use CLR extensions in SQL Server. CLR extensions in SQL Server are another reason to not use Azure!
There are many other reasons. You point out that Azure doesn’t give the SQL Server process (and its extensions) access to disk on the server. That’s true; but Azure also doesn’t give the user access to the server. If Azure goes down or becomes inaccessible, there’s no way for the customer to remedy the problem. Their fate is out of their hands, and they’re still on the hook to their customers. Sure, the SLA says that won’t happen too often, and if it does, they get a few bucks (literally) refunded.
Steven Writes::
I have been in this business since 1970 and if there is one thing that I have always tried to do is stay within the ANSI Standards and provide Keep it Simple Stupid (KISS) code.
Do you have a different opinion? Share it with us by sending an Email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Tips for using SQL Server 2008 R2 database settings
In this article, you can find some useful databases settings tips.
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008
Written by Townsend Security
Simplify encryption and key management on … (read more)