Migrate your SQL Server workload with CLR integration to AWS

Common language runtime (CLR) integration is an option to host .NET code within SQL Server programmatic objects like stored procedures, functions, and triggers, and adding user-defined data types. Since its introduction in SQL Server 2005, CLR integration has gained popularity within the SQL Server community for its additional flexibility and options to import .NET code components within T-SQL. It provides a managed execution environment which in turn offers a lot of freedom for developers to write managed code for a variety of benefits, such as improved safety and security, cross-language integration, and object lifetime management.

In this post, we look at the multiple options to enable and run CLR integration with different offerings of SQL Server on AWS. We also walk you through steps to import a CLR assembly into Amazon Relational Database Service (Amazon RDS) for SQL Server. After you’re familiar with all of the available options, you will be able to make a fully educated decision as per your use case and requirements.

SQL Server offerings on AWS

There are three main options to run SQL Server on AWS:

• SQL Server on Amazon Elastic Compute Cloud (Amazon EC2), a web service that provides secure, resizable compute capacity in the cloud
• Amazon RDS for SQL Server, which makes it easier to set up, operate, and scale a relational database in the cloud
• Amazon RDS Custom for SQL Server, a managed database service for legacy, custom, and packaged applications that require access to the underlying OS and DB environment

Enable CLR integration

SQL Server offerings on AWS provide both managed and self-managed service experiences. Therefore, there is a difference in the steps involved to change instance-level settings and parameters. In this section, we go through the steps involved in enabling and importing CLR on different SQL Server offerings on AWS.

SQL Server on Amazon EC2

Amazon EC2 provides an underlying infrastructure to host a self-managed SQL Server database instance. It provides full control over the DB instances as well as the operating system, and allows you to use your choice of tools, mechanisms, and procedures for database administration.

To evaluate if SQL Server on Amazon EC2 is a good migration or hosting option for your use case, refer to Amazon EC2 for SQL Server.

To import CLR assemblies into SQL Server running on Amazon EC2, you must first enable CLR integration at the SQL Server instance level. For instructions, refer to CLR Integration – Enabling.

Then you can start importing CLR assemblies to your SQL Server instance with one the following methods:

• Back up and restore the database from the source instance to the SQL Server instance on Amazon EC2 that is already embedded with CLR assemblies
• Import CLR from DDL files created on the source to the target environment
• Create it directly on the target environment via T-SQL

All these methods work similar to what you would do with any other self-managed SQL Server deployment.

Amazon RDS for SQL Server

Amazon RDS for SQL Server is a fully managed AWS service that makes it easier to set up, operate, and scale SQL Server deployments in the cloud.

With Amazon RDS for SQL Server, CLR integration is supported only with PERMISSION_SET=SAFE. However, beginning with SQL Server 2017, Microsoft has enhanced security controls for CLR assemblies by introducing the CLR Strict Security option, which is enabled by default. Due to this change, it started to treat SAFE assemblies as if they were marked UNSAFE.

To use previously created CLR assemblies, you need to disable the CLR Strict Security option. This is neither supported on Amazon RDS for SQL Server nor recommended by Microsoft. Therefore, you can only use CLR integration with Amazon RDS for version SQL Server 2016 and below. For more information, refer to CLR strict security.

If you’re running SQL Server 2016 on Amazon RDS with CLR enabled and planning a major version upgrade to Amazon RDS for SQL Server 2017 or higher, you need to either stop using CLR integration or use other options to run SQL Server on AWS (as explained in this post).

Because Amazon RDS for SQL Server is a fully managed service, you don’t have access to the underlying host operating system or sysadmin access to run procedures like sp_configure. To make configuration changes like enabling CLR integration, you can use a custom parameter group and enable CLR integration for the RDS for SQL Server instance, as shown in the following screenshot. For more information, refer to Working with parameter groups.

After you enable CLR integration for a given RDS instance, there are a few ways to import CLR assemblies and other programmable constructs:

• Restore the CLR-enabled database via native restore from an Amazon Simple Storage Service (Amazon S3) bucket. Restoring the SQL Server database from Amazon S3 is similar to the native SQL Server restore method (refer to Importing and exporting SQL Server databases using native backup and restore).
• Create a CLR with a T-SQL command. This looks the same as what you perform on any other SQL Server deployment. For example:

CREATE ASSEMBLY [DatabaseCLR] FROM 0x4D5A90000300000004000000FFFF0000B800...00 WITH PERMISSION_SET = SAFE;

Amazon RDS Custom for SQL Server

Amazon RDS Custom is a managed database service for applications that require customization of the underlying operating system and database environment. Amazon RDS automation offers the access needed for legacy, packaged, and custom applications.

Because you can have both sysadmin privileges on the DB instance as well as administrative privileges on the underlying OS, you can also enable CLR integration for RDS Custom for SQL Server using sp_configure, similar to what we described earlier regarding SQL Server on Amazon EC2.

As of this writing, RDS Custom for SQL Server supports SQL Server 2019. For more information, refer to Requirements and limitations for Amazon RDS Custom for SQL Server.

All three methods to import CLR are available for RDS Custom:

• Back up and restore the database from the source instance to the SQL Server instance on Amazon EC2 that is already embedded with CLR assemblies
• Import CLR from DDL files created on the source to the target environment
• Create it directly on the target environment via T-SQL

Import a CLR assembly into Amazon RDS for SQL Server

Because Amazon RDS delivers a managed service experience, it doesn’t provide shell access to the underlying host. To create or import a CLR function into an existing database on an RDS for SQL Server instance, we use T-SQL.

Let’s take an example and review how to import a CLR function into an existing database. The CLR function comes from the Microsoft article Regular Expressions Make Pattern Matching And Data Extraction Easier.

Export a CLR assembly

To export a CLR assembly and the corresponding programmable object from the source SQL Server instance, complete the following steps:

1. Connect to the source instance using SQL Server Management Studio (SSMS).
2. Under Databases, expand your database.
3. Under Programmability, choose Assemblies.
4. Choose the required assembly (right-click) and choose Script Assembly as, CREATE To, and New Query Editor Window.
   
   The output is a T-SQL command that you can use to recreate the CLR assembly. Instead of choosing to open it in a new query editor window (as shown in the following screenshot), you can also save it in a file.
   
5. Similarly, generate the T-SQL script for the corresponding function.
   

You get the creation script for the CLR function that is based on the assembly we just exported. You can save the function in a file or open it in a new query editor window.

Repeat the steps if there are any other programmable constructs within your environment or if you need to migrate more than one CLR assembly.

Import the CLR assembly

Follow these steps to import a CLR assembly and the programmable object into an RDS for SQL Server instance:

1. Connect to the target RDS for SQL Server instance using SSMS.
2. In SSMS, open a new query window.
3. Create the CLR assembly and SQL function using the create object scripts from the previous steps.
   
4. Verify if the objects have been created successfully.
   

Test run the function

After the CLR assembly and other programmable objects are created, let’s test if the function works properly on the new environment.

1. Open a new query window for the RDS for SQL Server instance.
2. Test run the function by providing different input parameters.
   

The output shows that the CLR function works fine with Amazon RDS for SQL Server after manual import by T-SQL commands.

This way of exporting and importing CLR assemblies via T-SQL works fine for any of the SQL Server-based service offerings on AWS.

Conclusion

In this post, we showed how to enable CLR integration for different SQL Server database offerings on AWS and demonstrated how to import a CLR assembly into Amazon RDS for SQL Server. This solution helps you run managed code within SQL Server on AWS similar to that of your on-premises environment.

If you have questions or feedback, leave a comment.

About the Authors

Sikandra Chaudhary is a Database Specialist Solutions Architect at AWS. Sikandra helps AWS customers with their architecture design and provides efficient solutions to migrate and run their database workloads on AWS. He has been working with Microsoft SQL Server deployments since 2008.

Stas Bogachinsky is a Database Specialist Solutions Architect at AWS. He works with AWS customers to design, secure, and optimize their database workloads.