Setup SQL Server for CLR Procedures
NCache provides you with CLR stored Procedures which synchronizes the cache with the database. It does not involve database monitoring or notification mechanism, instead the user can execute any complex logic using CLR procedures and saves the user with too many notifications being thrown.
Follow the following steps on the database in order to enable CLR procedures and then implementing them.
Step 1: Enable CLR on Database
In order to use CLR Stored Procedures make sure that CLR integration is enabled on the database. If you haven't enabled CLR integrations already execute the following command to enable CLR integrations:
-- Enable CLR Integration on Database sp_configure'clr enabled', 1 GO RECONFIGURE GO
Step 2: Place the Assemblies
To use NCache in CLR stored procedure, NCache assemblies need to be registered with database. This will enable database to use NCache API in stored procedure.
SQL server does not resolve referred assemblies from GAC, therefore assemblies required by NCache need to be copied in NCache’s assembly directory before deploying them on database.
Place the following assemblies in the %NCHOME%\bin\assembly\4.0:
- Renci.SshNet.dll: Located at %NCHOME\bin\tools\gui\libs
- SharpSnmpLib.dll: Located at %NCHOME\bin\tools\gui\libs
- Mono.Cecil.dll: Located at %NCHOME\bin\tools\gui\libs
- protobuf-net.dll: Located at C:\Windows\assembly\GAC_MSIL\protobuf-net\22.214.171.1242__257b51d87d2e4d67\protobuf-net.dll
- log4net.dll: Located at C:\Windows\assembly\GAC_MSIL\log4net\126.96.36.199__1b44e1d426115821\log4net.dll
Step 3: Deploy the Assemblies
Once all the assemblies mentioned above are placed in the %NCHOME%\bin\assembly\4.0, deploy these assemblies using the following commands:
If NCache install directory is different from used in this query, change path accordingly.
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.Web] FROM N'C:\Program Files\NCache\bin\assembly\4.0\Alachisoft.NCache.Web.dll' WITH permission_set=unsafe
After following these steps, the environment for CLR procedures will be set. Please refer to Programmer's Guide to get help in the implementation of stored procedures.
Step 4: Execute Stored Procedure
Execute the stored procedure after successfully publishing it through Microsoft SQL Server.
Right click on the stored procedure and then select Execute Stored Procedure.