Risk Of Enabling CLR

  • Denby

    Hall of Fame

    Points: 3120

    Heys guys, just want to know if there is any potential risks with enabling CLR in SQL server.

    Developers wanted to utilize this feature in prod.

    Also, is the anything i should be monitoring from a DBA's perpective if feature is enable.

    thanks

  • Jack Corbett

    SSC Guru

    Points: 184381

    The first risk is poorly performing code. Why do the developers want to use the CLR? There have been a couple of threads here on SSC where there is a set-based solution that outperforms a CLR solution in areas where most would have thought CLR code would have been faster.

    Otherwise, I don't think there are too many other "risks" if you do not allow assemblies with UNSAFE permissions.

    There are 4 DMV's for CLR functions that you can use as well. Lookup DMVs in BOL and there is a link to them.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    Enabling CLR doesn't do anything in reality, it just allows CLR to work if it exists in the database. You can create assemblies in SQL even with CLR disabled, they just won't run. You must be on 90 compatibility mode before you can use CLR inside of SQL Server. Things to monitor? The list grows every day it seems. Start with:

    What do they want to do in CLR that can't be done in TSQL?

    Most often the application of CLR is being misplaced, and a suitable TSQL implementation exists or can be created to prevent needing CLR.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Denby

    Hall of Fame

    Points: 3120

    Ok, thanks guys appreciate the inputs

  • Jeff Moden

    SSC Guru

    Points: 996830

    Denby (7/21/2008)


    Also, is the anything i should be monitoring from a DBA's perpective if feature is enable.

    Absolutely! First, I'd make bloody sure that the source code was in a versioning tool. Next thing I'd do is review the source code to make sure there's nothing really bad in the code that may bring the server to it's knees. The other reason for the source code review is to check and see if someone is just using CLR's because the can't figure it out in T-SQL... there's that overhead thing that Jack was talking about above. ๐Ÿ™‚

    Personnally... I won't turn the damned things on. Except for some RegEx functionality, I've been able to do what most folks turn to a CLR for in T-SQL... and beat the CLR for performance, to boot.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ianT

    SSCrazy

    Points: 2956

    Had a CLR procedure cause random server crashes in production. Only able to replicate the error in Dev after a lot of work and never realy fixed.

    If you can, avoid.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720436

    I don't like the idea of CLR code, but there have been a few people that have found it quite handy. I tend to side with those above and I'd want to know more about what exactly developers want to do with it that they can't do another way.

  • SQLRockstar

    SSCertifiable

    Points: 6219

    hi denby,

    we try to limit the use of CLR here, simply because it does not seem to be necessary. however, one caveat that i have seen has to do with memory. although I have been told that the CLR memory space will not affect the mssql memory space, what I have found is that periodically the CLR ememory space becomes so fragmented that a bounce of the service is necessary.

    so, while you may be told that you do not have to worry about CLR bringing a server down, the fact is that if someone is using CLR and they rely on CLR for production, it may be necessary to bounce the services anyway. this results in a outage, plain and simple.

    if possible, sit doen with the developers and ask questions, and explain some of the risks. chances are once they know the risks, they may think twice about using CLR.

    ----------------------
    https://thomaslarock.com

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    Thomas LaRock (8/18/2008)


    hi denby,

    we try to limit the use of CLR here, simply because it does not seem to be necessary. however, one caveat that i have seen has to do with memory. although I have been told that the CLR memory space will not affect the mssql memory space, what I have found is that periodically the CLR ememory space becomes so fragmented that a bounce of the service is necessary.

    so, while you may be told that you do not have to worry about CLR bringing a server down, the fact is that if someone is using CLR and they rely on CLR for production, it may be necessary to bounce the services anyway. this results in a outage, plain and simple.

    if possible, sit doen with the developers and ask questions, and explain some of the risks. chances are once they know the risks, they may think twice about using CLR.

    This points to either a problem in your server configuration or a problem with your CLR code where it is not allowing the GC to activate and free the memory properly. I have had CLR running for many months with no appdomain unloads or server restarts necessary on any server that is running it. In a worst case scenario, you shouldn't have to restart the service, as a DBCC FREESYSTEMCACHE call will clear the CLR stack from its MTL allocations.

    CLR runs in a very limited and finite memory space in SQL Server called the MemToLeave area. This is generally only allocated 256MB of memory at startup, depending on a number of factors to include how much memory your server has, network packet size, and various other configuration options. It isn't very hard for SQL Server to bump into MTL memory pressure without CLR running let alone with CLR running. I'd recommend that you investigate what is actually causing your problem in the sys.dm_os_memory_clerks and see where your memory is actually being allocated. XP's also work in the MTL area, which is why some tools like litespeed used to create MTL memory pressure. There is a lot more to play here than CLR causing a problem.

    The above also applies to the post about CLR crashing a SQL Server. By design CLR won't do this unless your wrote code that leaks pointers in UNSAFE registered assemblies. If you build appropriate CLR code for use in SQL only for Instances where CLR is appropriate, and I admit that these are extremely limited, then you shouldn't have problems with running CLR in SQL. 99% of the CLR code that I have seen on this forum, as well as the others that I answer on is misplaced, and belongs in an application layer outside of SQL Server. 99% of String manipulations being done in CLR can be done in TSQL much faster, as Jeff says, regular expressions replacements and matching seem to be the only two that outperform a solid TSQL solution.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • SQLRockstar

    SSCertifiable

    Points: 6219

    Thanks Jonathon, I will try the DBCC FREESYSTEMCACHE the next time we have this issue.

    It doesn't happen often, and I am familiar with the MemToLeave area, and the various things that eat up space in there, including Litespeed. However, I had not thought about all the connections until reading your post, thanks.

    In most cases, the CLR works fine, but every now and then there seems to be a slight bump in the road.

    ----------------------
    https://thomaslarock.com

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    You can try to add the -g384 or -g512 startup parameter to see if it is a memory leak causing the problems. I have one CLR object that interacts with third party application webservice that has a poor API and returns large datasets back. I had to increase the MTL allocation size to prevent having appdomain unloads occur from memory pressure.

    If you are truly leaking memory and preventing the GC from cleaning out objects, adding the startup option won't solve the problem, it will just provide more memory to be leaked.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • SQLRockstar

    SSCertifiable

    Points: 6219

    i have used that -g flag previously, but as you said it does not solve the real issue, it just delays it from happening as frequently.

    ----------------------
    https://thomaslarock.com

  • DC-SQLServer

    SSC Veteran

    Points: 281

    To be clear there is no security issue, SQLCLR code canโ€™t do anything more in a database than an equivalent T-SQL code module running under the same security context. Ignorantly prohibiting CLR also prevents SSISDB deployment, which drastically improves security via less RDP accounts, FileSystem management and less rights needs, Backup inheritance inside the maintenance plans, Full Package Encryption via TDE, to say nothing of SSIS package deployments and maintenance via the environment section of the SSISDB and lack of many C# functions in SSIS which require CLR.

    http://www.codemag.com/article/0603031

    This is currently the most ignorant security claim in all of SQL Server, which can prohibit the flagship ETL deployment model, the SSISDB (requires CLR), because some 3rd party security tool which also incorrectly flags SQL Server collation coefficients via 2000, or simply a severely misinformed DBA. **CLR is not a security risk**, it actually has enhancements to security in multiple regards. Ask your DBA if he cares so much about security why Mixed Mode Authentication is set on and TDE is not enabled and they have non-existent auditing.

    To enable CLR simply run

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    sp_configure 'clr enabled', 1;

    GO

    RECONFIGURE;

    GO

  • Jonathan Kehayias

    One Orange Chip

    Points: 26778

    You do realize you replied to a post that is 7 years old and completely predates SSISDB in SQL 2012?

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • DC-SQLServer

    SSC Veteran

    Points: 281

    Yes, and ignorant people are still keeping it disabled in heavy SSIS shops.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply