Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to use code that I developed in C# CLR to be used in all of the databases on the system so that I don't have to set each to trustworthy and turn CLR on and keep a bunch of the same code inside each one.

Is there a best way to do this from an administrative and security standpoint? The CLR functions are very basic like string breakers, email validation, url en/decode, base64, and etc. I would like only the dbo schema in each database to be able to access the functions.

  1. Is there any simple way to do this?
  2. Also I am not clear if the CLR dll is embedded and if I move the database, it tags along, or do I have to move the dll as well.

Thanks

share|improve this question

migrated from stackoverflow.com Aug 14 '12 at 16:05

This question came from our site for professional and enthusiast programmers.

1  
OK, sounds good. As long as I don't hear crickets from the dead silence about the question. Always had luck at stackoverflow –  Alex Erwin Aug 14 '12 at 16:22
1  
dba.se is less frenzied than SO, but I'm confident you'll get good attention on a question like this within a day or so. Are you happy to be patient that long? –  Jack Douglas Aug 14 '12 at 16:37
    
Lol, patience is a virtue. I have a fair amount of it, and the question itself, I would think, MS would address. What if you are a SQL Server host that wants to expose some useful functions but not have your server open to CLR in full force? –  Alex Erwin Aug 14 '12 at 17:11
add comment

2 Answers 2

up vote 7 down vote accepted

At our company we have that exact setup. When you create a CLR assembly a binary representation of the assembly is stored within the database that you create it in. This enables you to take it with you (and even script it out) should you move the database at any point in time.

A couple of months back our data center got flooded - filling several servers full of water. When I rebuilt them I only used the backups of the db that had been taken the night before. So far we have had no issues.. (touch wood!)

I am not sure if this is the right thing to do from a security perspective but the way we grant access to the CLR procs etc is to create a role within the shared database, and then add users from other databases to that role. The role is then granted execute on the CLR procs.

There may be access issues if the CLR is trying to do things like access resources outside of the database that it is contained within but you can set the permission on the assembly when you create it. The below link has a lot more information regarding permissions than I can explain here though:

http://msdn.microsoft.com/en-us/library/ms345101.aspx

I hope that this helps you.

share|improve this answer
add comment

The assembly binary is stored as a blob in the database, so it's carried wherever the database goes. CLR is only enabled on the instance -- there are no database-specific settings for that.

In any event, why are you trying to do this?

(I'm not trying to be argumentative; I just want to hear the motives involved, because perhaps the problem could be solved in a different way that meets your needs.)


There's no way to do this easily, except to put the assembly in a shared database.

That said, I would think it's advantageous to embrace the database-centric architecture, unless there's a particular situation that has very compelling reasons to centralize. The reason why is that putting the assembly (or anything for that matter) outside the database creates a dependency in your environment. This is precisely the opposite approach Microsoft is building towards with Contained Databases starting in SQL Server 2012.

  • When you start needing to use features like replication or clustering, this dependency can potentially add a tremendous amount of complexity to the deployment, but also to troubleshooting, and failover procedures.

  • This architecture is much less obvious to people unfamiliar with the system (i.e., it's less self-discoverable, and less self-documenting).

  • If you end up requiring different security in different databases, or anything that involves variation, you're in a world of hurt.

  • If these databases get deployed to customers (sounds like they won't be, but I'll say this for completeness), this adds complexity to the deployment procedure, maintenance, and troubleshooting.

  • Since all databases would share this code, if any bugs are introduced (or fixed!), this could potentially break all the applications that rely on the databases. Comprehensive unit testing would be an absolute must.

If you have several databases that need the same functionality, there are other ways of reducing the amount of duplication involved, which I assume is the point of the exercise. Even a fairly complex CLR assembly won't take up much physical storage space compared to the data in the database itself (almost always), so I don't see that as a valid argument unless you have literally thousands of tiny databases that need this assembly.

What you could do is modify other parts of the deployment procedure for these databases to reduce the source duplication. For example, build and deploy the assembly from the common location of the CLR code in source control. Or, create a script that deploys the same assembly to the databases. Automate this part of things as much as possible, and it won't be a big deal.

I agree that what I'm suggesting is a tradeoff, because there will still be some duplication, but that has to be balanced with the negatives involved with implementing an architecture that doesn't follow the prescribed standard. Only you can decide what's right for your environment.

share|improve this answer
    
I see your point with possibly breaking things, however it is to simplify roll outs of code. No army of developers here. Just me. I do however have others using the database, so I need to make sure the functions are inaccessible unless used from stored procedures I define. –  Alex Erwin Aug 15 '12 at 9:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.