SQL 2014 CLR Migration to SQL 2019

MS Techie 2,676 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.
12,690 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.
494 questions
Accepted answer
  1. CarrinWu-MSFT 6,851 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

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.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.