Search code examples
sql-serversqlclrtsqlt

What is the purpose and risks of enabling SQL CLR?

We are looking to implement unit tests using the tSQLt test framework. It has got a pre-requisite that the SQL CLR must be enabled using this command:

EXEC sp_configure 'clr enabled', 1; RECONFIGURE;

I am curious to know what is the purpose of SQL CLR and the risks of enabling this in production environment?

Solution

  • PURPOSE

    SQLCLR allows one to do things that either:

    1. can't be done in T-SQL, or
    2. can't be done as efficiently as in T-SQL

    There are plenty of things that can be done in both, and for which T-SQL is actually much better at. In those cases it is an inappropriate use of SQLCLR to do those things so it is best to research first to make sure that the operation cannot be done in T-SQL, or would definitely be slower.

    For example of performance, T-SQL Scalar UDFs prevent parallel execution plans. But SQLCLR scalar UDFs, as long as there is no data access and that they are marked as IsDeterministic=true, do not prevent parallel execution plans.

    For more details on what SQLCLR is and is not, please see the first article in the Stairway to SQLCLR series that I am writing for SQL Server Central:

    Stairway to SQLCLR Level 1: What is SQLCLR?

    Or, to get a sense of what can be done in SQLCLR, please see my SQL# project, which is a library of over 320 stored procedures and functions, many of which are in the Free version, and many of which work in SAFE mode: SQLsharp.com.

    RISKS

    The risks vary based on the PERMISSION_SET (i.e. SAFE, EXTERNAL_ACCESS, and UNSAFE) that the Assembly is marked as, and what is being done. It is possible to do things in an UNSAFE Assembly that cannot be done in regular T-SQL (except that many of those dangerous things can already be done via some extended stored procedures, xp_cmdshell, and the OLE Automatic procedures -- sp_OA* ). An Assembly marked as SAFE cannot reach outside of the database, so generally quite safe, BUT you can still lock up the system via a Regular Expression that exposes "catastrophic backtracking" (of course, this can be mitigated starting in .NET Framework 4.5, so SQL Server 2012 and newer, by setting a max time limit on the RegEx operation). An Assembly marked as UNSAFE can write to static variables, which in the context of the shared App Domain model used by SQLCLR, allows for shared memory between Sessions. This can allow for caching, but when not used properly, easily leads to race conditions.

    TESTING

    As for tSQLt, I do not believe that you are required to use the SQLCLR component. I thought I saw that it just enabled some extended functionality. Either way, the source code is available on GitHub so you can check it out to see what it is doing. It has been a while since I looked at it, but from what I remember, it should not present much of a risk for the little that it is doing (especially in a Dev / QA environment).

    Another option that doesn't use SQLCLR is DbFit. I have always prefered DbFit as it is completely external to the DB. It is based on the FitNesse framework, written in Java, and you manage the tests via wiki-style pages. It, by default, wraps the tests in a Transaction and rolls everything back when the test is finished (i.e. clean-up). It is worth taking a look at.

    Download: DbFit project on GitHub
    Tutorial: Using the DbFit Framework for Data Warehouse Regression Testing