Set Up SQL Server for CLR Procedures [Deprecated]

NCache provides CLR stored procedures that synchronize the cache with the database. Setting up SQL Server for NCache CLR Stored Procedures involves enabling CLR integration, configuring database trust levels, and deploying NCache-specific assemblies. It does not involve database monitoring or a notification mechanism; instead, you can execute any complex logic using CLR procedures, minimizing the risk of excessive notifications.

Follow the steps below on the database to enable and implement CLR procedures.

Step 1: Enable CLR Integration via SQL Commands

In order to use CLR stored procedures, make sure that CLR integration is enabled on the database. If you haven't enabled CLR integration already, execute the following command:

-- Check and enable Common Language Runtime (CLR) for the instance
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Step 2: Install the NuGet Package for Assemblies and Deploy Them

To use NCache in the CLR stored procedure, NCache assemblies must be registered with the database. This enables the database to use the NCache API within stored procedures. NCache provides CLRStoredProcedure.NCache for the required dependent assemblies. Therefore, you first need to download the CLRStoredProcedure.NCache NuGet package or install it in your application. The required assemblies should be referenced from this NuGet package.

Step 3: Register and Deploy NCache Assemblies to SQL Server

Once you have obtained the NuGet package, deploy the assemblies using the following commands:

Note

To deploy Alachisoft.NCache.Client.dll in the following query, you need to provide the path according to the location of the NuGet package.

USE Northwind
ALTER DATABASE Northwind
SET TRUSTWORTHY ON;
GO
-- REGISTER SYSTEM ASSEMBLIES ...
DROP ASSEMBLY [System.Web]
DROP ASSEMBLY [System.Management]
DROP ASSEMBLY [Microsoft.CSharp]


CREATE ASSEMBLY [System.Web] FROM N'C:\Windows\Microsoft.Net\Framework64\v4.0.30319\System.Web.dll' WITH PERMISSION_SET=UNSAFE

CREATE ASSEMBLY [System.Management] FROM N'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Management.dll' WITH PERMISSION_SET=UNSAFE

CREATE ASSEMBLY [Microsoft.CSharp] FROM N'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Microsoft.CSharp.dll' WITH PERMISSION_SET=UNSAFE

CREATE ASSEMBLY [Alachisoft.NCache.Client] FROM N'C:\Users\john_doe\Downloads\CLRStoredProcedure.NCache\lib\net40\Alachisoft.NCache.Client.dll' WITH PERMISSION_SET=UNSAFE

After completing these steps, the environment for CLR procedures will be set up. Please refer to Programmer's Guide for help with the implementation of stored procedures.

See Also

Setup SQL Server Environment
Setup Oracle Database Environment
Setup Ole​Db Environment
Monitor Caches