CLR Stored Procedure deployment advice

[guest2013-1]

I'd like it for a CLR function I wrote to be available to every database on a specific instance.

Any advice on how I would go about doing this? Obviously I want to avoid any security issues and (of course) the labor of deploying the DLL to each and every db that will be using it.

 

[kripstoe]

How complex is the CLR? Attempted this once, but we decided against it because of all the hoops that we had to jump through to make it work. Was too risky for or prod environment.

 

[guest2013-1]

It sends a MSMQ message to a queue you specify to give us the ability to update records in several systems with a trigger on SQL

Then MSMQ triggers, fires a console application that does some footwork and broadcasts the change to another SQL instance

The actual CLR doesn't do anything special except add table structure/originating database info and puts it on the queue.

The console app that fires later does most of the "heavy" lifting by applying the table structure template to the data sent through to send it through to the waiting stored procedure for processing.

It's unfortunate I have to launch this DLL per database though, because the way I made the console app is to get all DB's with the same recipient stored procedure in an instance, and fire the stored procedure for each while sending them the data as parameters to process.

This enables us to only have a queue per SQL instance instead of a queue per database, and I use User-Defined Table values as a template to apply what data I want from the trigger that fired without requiring the programmer to specify records. That way we modify the recipient stored procedure and the UDTV and don't worry about updating triggers or select statements and crap.

Still a work in progress though, proof of concept

 

[kyleiscoding]

SQL .NET assemblies

I'd like it for a CLR function I wrote to be available to every database on a specific instance.

Any advice on how I would go about doing this? Obviously I want to avoid any security issues and (of course) the labor of deploying the DLL to each and every db that will be using it.

Hi AcidRaZor,

I've developed several C# DLL's for use in both SQL stored procs and functions.

As for the security you will be fine if you restrict the access the dll has in the system. If its unrestricted you are giving it access to the OS and file system which I dont recommend unless you're 100% sure it needs to operate on that level.
Restricting the access to the function for the users that require access (I'd never recommend admin access to the SQL user that can access the DLL. This is because I would not allow the application using this to have admin rights in the SQL instance.)
The DLL's contained methods/subs should be internal only except for the methods that recieve, and return the data.

The next step is really to make sure no one gets thier hands on the DLL or the project.

With the security out of the way we can talk about the deployment.
Its quick and easy, and can be done covering many SQL instances through SSMS by clicking on programmibility and then right clicking on assemblies, add, and then navigate to the dll (on the computer youre working on as this is done remotely) and upload.

Then its just to create the SP's that reference the DLL and youre good to go.


I'm going to say this once. The performance boost you get from ie. string manipulation from SQL CLR Functions and SP's is WELL WORTH YOUR TIME. SQL varchars are not managed using a garbage colector or the string intern pool.

If you do one thing today read this link.
http://www.simple-talk.com/sql/t-sql-programming/clr-performance-testing/




The below link is a simple and easy to use way to get this working.
http://blog.sqlauthority.com/tag/clr/

Good luck with it.

 

[guest2013-1]

Thanks for the reply. If you read up on more things, you'd find that, by default, a CLR stored procedure has access to the file system of an OS, hence why most people use it. But it doesn't give you full access to the OS. That is why I need to add System.Messaging and load the CLR as UNSAFE in the database.

I've decided not to bother with deploying the CLR per instance, but per database. I need to have the connection, in context, determine which catalog it's coming from, and I won't be able to do it if it's running from somewhere like, master, for example. It's just so that I can automatically add the instance/catalog information to the XML message I send through to MSMQ.

I'm not too worried about Security though, the trigger (aka, system itself) will fire the CLR, and I don't expose methods that's not necessary. ie, All methods accessing the OS in some way (of which there is only really the "send to MSMQ" bit) is private, and I only expose the public method that takes specific parameters and from there on does it's thing.

So someone has to have access to, not only the code, but also our sql server instances (somehow) to do anything malicious.

i might be wrong though, I haven't picked up my "black hat" in a while