May 6, 2013 at 10:56 pm
Comments posted to this topic are about the item Deploying CLR Assemblies with T-SQL
May 7, 2013 at 7:26 am
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...
May 7, 2013 at 7:30 am
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. 🙂
May 8, 2013 at 4:41 am
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...
May 9, 2013 at 5:36 am
Wonderful.
Share a website with you ,
Believe you will love it.
We accept any form of payment.
May 10, 2013 at 9:06 pm
Wonderful.
Share a website with you ,
Believe you will love it.
We accept any form of payment.
May 10, 2013 at 9:06 pm
Wonderful.
Share a website with you ,
Believe you will love it.
We accept any form of payment.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply