SQL 2014 CLR Migration to SQL 2019

MS Techie 2,681 Reputation points
2021-02-25T10:27:49.803+00:00

Hi,

I am planning to migrate my CLR procedures from SQL 2014 to SQL 2019.

Is SQL 2019 backward compatible to host CLR written for SQL 2014 ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,111 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
505 questions
{count} votes

Accepted answer
  1. CarrinWu-MSFT 6,866 Reputation points
    2021-02-26T05:56:18.207+00:00

    Hi @MS Techie ,

    Yes, SQL Server 2019 is backward compatible to host CLR written for SQL 2014. And you can change the compatibility level, see below:
    72356-untitled-picture.png
    The CLR integration feature is off by default in Microsoft SQL Server, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration using Transact-SQL, use the clr enabled option of the sp_configure stored procedure as shown:

    sp_configure 'clr enabled', 1    
    GO    
    RECONFIGURE    
    GO  
    

    Best regards,
    Carrin


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 103.9K Reputation points MVP
    2021-02-25T23:06:12.867+00:00

    Yes, but you should be aware of that starting with SQL 2017 there is something known as CLR Strict Security. (Actually, it is available on SQL 2014 as well, but only under a trace flag.)

    With this setting, all assemblies are considered unsafe. (Because the mechanism in .NET SQL Server originally relied on is no longer a security boundary.) If you already have unsafe assemblies, you know which dance to get through.

    There is a new mechanism to trust an assembly, though. You can can enter a hash with sp_add_trusted_assembly. You can get this hash by running hashbytes('SHA2_512') on what is in sys.assembly_files for your existing assemblies.

    You can escape it by saying

    sp_configure 'clr strict security', 0
    RECONFIGURE
    

    But it is nothing I recommend.