Extend RDS for SQL Server using CLR integration

When to use CLR

CLR handles tasks that T-SQL cannot solve efficiently or at all:

• Complex string processing — Use the .NET regular expression library for medical record text parsing, data cleansing, and similar tasks.

• High-performance computing — Run complex mathematical operations such as option pricing in financial applications.

• File and network operations — Exchange data across systems, such as automated document processing in logistics.

• Custom encryption and decryption — Protect sensitive data with end-to-end encryption solutions.

• Image processing — Analyze product images and validate quality in retail pipelines.

• Machine learning (ML) integration — Build real-time recommendation features directly inside the database.

• Complex business logic — Support multi-dimensional decisions such as credit limit evaluation.

Use CLR as a complement to T-SQL and the application layer, not a replacement. T-SQL is the right tool for data queries and foundational business logic. The application layer handles complex interactions and workflows. Reach for CLR when T-SQL cannot implement the logic, or when moving the logic to the application layer creates performance, deployment, or security constraints.

Running logic in the database through CLR provides three concrete advantages:

Limitations

CLR integration is not supported on shared or Serverless RDS SQL Server instances.

Prerequisites

Before you begin, ensure that you have:

• An ApsaraDB RDS for SQL Server instance (not shared or Serverless)

• A self-managed SQL Server instance (version 2016 or later) to compile and export the assembly

• SQL Server Management Studio (SSMS) installed on your local machine

• Visual Studio 2022 installed for writing and compiling C# code

• dbo permissions on the target database of your RDS instance

Step 1: Enable CLR on your RDS instance

1. Go to the Parameters page in the ApsaraDB RDS console.

2. Set the instance parameter clr enabled to 1 (the default is 0).

Step 2: Write and compile the CLR program

Create a class library project in Visual Studio 2022, paste the C# code below, set the target framework to .NET Framework 4.7.2 or later, and build the project to produce a DLL file (for example, CLRFuncDemo.dll).

The demo function, AnalyzeSentiment, scores a comment by matching words against a built-in sentiment dictionary. Words with positive connotations return scores between 0 and 1; negative words return scores between -1 and 0.

After building, confirm the output DLL path (for example, E:\Backup\CLRFuncDemo.dll).

Step 3: Export the assembly as a binary string

Because RDS SQL Server does not support direct DLL uploads, register the DLL as an assembly on a self-managed SQL Server instance (version 2016 or later) first, then export it as a binary string to import into RDS.

1. On the self-managed SQL Server instance, run:

   CREATE ASSEMBLY [CLRFuncDemo]
   FROM 'E:\Backup\CLRFuncDemo.dll'
   WITH PERMISSION_SET = SAFE

   The PERMISSION_SET option controls what resources the assembly can access. SAFE is the most restrictive option and grants access only to local database resources with no external system access. Use EXTERNAL_ACCESS if the assembly needs to read files or call web services, and UNSAFE only if it requires unmanaged code — both carry higher security risk and require stronger justification.

2. Use SSMS to export the assembly as a binary string and copy the result for use in the next step.

Step 4: Import the CLR assembly to your RDS instance

Connect to the RDS SQL Server instance using SSMS with an account that has dbo permissions on the target database, then run:

-- Create the assembly from the binary string
CREATE ASSEMBLY [CLRFuncDemo]
FROM 0x4D5A9000... -- Replace with the actual binary string
WITH PERMISSION_SET = SAFE;

-- Create the CLR function backed by the assembly
CREATE FUNCTION dbo.AnalyzeSentiment(@text NVARCHAR(MAX))
RETURNS FLOAT
AS EXTERNAL NAME CLRFuncDemo.[SentimentAnalysis].AnalyzeSentiment;

The assembly and function appear in the database after the statements succeed.

Step 5: Test the CLR function

Create a test table, insert sample reviews, and query the sentiment scores:

The query classifies each review as Positive (score > 0.3), Negative (score < -0.3), or Neutral, ordered from most positive to most negative.

Troubleshooting