SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deploying CLR Assemblies with T-SQL


Deploying CLR Assemblies with T-SQL

Author
Message
David Rueter
David Rueter
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 574
Comments posted to this topic are about the item Deploying CLR Assemblies with T-SQL
tskelley
tskelley
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 1177
Let me be the first to say, "You are da man!"

We have always avoided CLR like the plague, mainly due to deployment issues. We keep a delta of all SQL changes, and just did not ever want to include CLR integration in the deployment mix.

Assuming everything works okay, this method will be "worth its weight in gold". Thanks for giving us a "go to" should the CLR need arise, which is actually on the drawing board...



Mike Dougherty-384281
Mike Dougherty-384281
Mr or Mrs. 500
Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)Mr or Mrs. 500 (552 reputation)

Group: General Forum Members
Points: 552 Visits: 944
tskelley (5/7/2013)
Let me be the first to say, "You are da man!"


Let me be the first to say... ^^ what he said. Smile
Rob Ashton
Rob Ashton
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 133
Hi David,

It looks like you've done a lot of research and worked hard to get the solution you've presented, always good to see someone pushing the boundaries!

Having said that, I have some reservations about the principle behind what you've shown us.

Who vets the incoming code strings to ensure that no code is defined that can cause the .NET compiler to use arbitrarily large amounts of system resources during compilation that should be available to the SQL Server instance?

Alternatively, who vets the code strings to ensure that their compilation time is low enough that xp_cmdshell is exposed for only a short period of time to prevent someone else from using it to execute something of their own with xp_cmdshell against the host machine?

Who vets the code strings to make sure that the resulting functions and SProcs cannot be used to perform arbitrary malicious operations on the host machine? Something as simple as a CLR-based RegEx search function (which can be marked as SAFE, ironically) can be used by an unknowlegeable user with a badly written RegEx to consume all of the RAM on a host and cripple a SQL Server instance...

How are code changes managed over time and can changes be easily rolled back if found to cause a problem? Is the database now responsible for version control of its own code or are they placed elsewhere?

Finally, by placing code definitions, or at least their compilation mechanism, inside the database, aren't we moving away from the point of a relational database, namely that of maintaining data and the relationships inside it?

I guess I'm struggling to see how this process is useful when placed next to solutions like Microsoft's new SQL Server Data Tools that provide you with a simple way to manage your database schema, including all CLR code, via a code project that can live in any code repository you can connect Visual Studio to (TFS, GitHub and Subversion are the first three to pop into my head, but there are plenty more). SSDT automatically handles CLR code compilation, and can be configured to automatically handle CLR code signing, automatic generation of the T-SQL wrapper code needed to add the CLR assemblies and their methods and types to the database, and even automatic deployment of all schema objects and CLR code directly to the database (for less adventurous shops, it can alternatively generate a self-contained file called a .DACPAC that is used to deploy changes, or even just a plain old T-SQL script a DBA can execute in SSMS).

Having said all that, I've just checked SSC for any articles to take someone through using SSDT, and didn't find one that starts from first principles, so there's an idea I might have to follow up on, hmmm...
ccmalls26
ccmalls26
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 0
Wonderful.

Share a website with you ,

( http://www.ccmalls.net/ )

Believe you will love it.

We accept any form of payment.
ccmalls29
ccmalls29
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 0
Wonderful.

Share a website with you ,

( http://www.ccmalls.net/ )

Believe you will love it.

We accept any form of payment.
ccmalls29
ccmalls29
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 0
Wonderful.

Share a website with you ,

( http://www.ccmalls.net/ )

Believe you will love it.

We accept any form of payment.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search