Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deploying CLR Assemblies with T-SQL Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 10:56 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 57, Visits: 449
Comments posted to this topic are about the item Deploying CLR Assemblies with T-SQL
Post #1449975
Posted Tuesday, May 7, 2013 7:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:08 PM
Points: 40, Visits: 938
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...




Post #1450131
Posted Tuesday, May 7, 2013 7:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 10, 2014 6:19 AM
Points: 262, Visits: 919
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. :)
Post #1450134
Posted Wednesday, May 8, 2013 4:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 7:26 AM
Points: 367, 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...
Post #1450489
Posted Thursday, May 9, 2013 5:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 9, 2013 5:35 AM
Points: 3, Visits: 0
Wonderful.

Share a website with you ,

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

Believe you will love it.

We accept any form of payment.
Post #1451043
Posted Friday, May 10, 2013 9:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:05 PM
Points: 3, Visits: 0


Wonderful.

Share a website with you ,

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

Believe you will love it.

We accept any form of payment.
Post #1451802
Posted Friday, May 10, 2013 9:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:05 PM
Points: 3, Visits: 0


Wonderful.

Share a website with you ,

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

Believe you will love it.

We accept any form of payment.
Post #1451803
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse