Raincode Database Runner

Version 4.2.424.0

1. Introduction

RadaR (Raincode Database Runner) allows the execution of the code compiled by the Raincode legacy compilers locally on the server that hosts the database. This minimizes latency and maximizes communication throughput between the program and the database.

To make this possible, RadaR uses Microsoft SQL Server Language Extensions.

More specifically, we are using the .Net C# language extension to orchestrate an execution of rclrun.exe on the server, passing it the arguments that were given to the call of RadaR.

RadaR consists of two parts: The first part runs on the SQL Server machine, executing code locally on the server. The second part triggers this execution, which can be on any machine that can contact the SQL Server.

For more information on the security aspects of RadaR, we refer to the SQL server documentation about the architecture that is used to integrate the SQL Server Language Extensions. More specifically, this is treated in the security architecture for the extensibility framework in SQL Server Machine Learning Service and the extensibility architecture in SQL Server Machine Learning Services.

This document guides us through Installation, Execution and Troubleshooting.

2. Installation

The first part of RadaR must be installed on the SQL server that hosts the database since it will execute code on this machine.

2.1. Prerequisites

On the SQL server that hosts the database the following prerequisites must be met:

  • Windows OS and Powershell.

  • SQL Server 2019 CU3+ Database Engine instance with the extensibility framework (Machine learning Services and Language Extensions).

  • In the SQL Server network configuration, have named pipes, shared memory and TCP/IP enabled. Note that this is not the default configuration.

  • The SQL Server Launchpad service is up and running. Note that this is not the default configuration.

  • An installation of the Raincode runtime with a working rclrun.exe.

If the above prerequisites are not met, RadaR cannot be installed or run. Note that with some of these prerequisites not met, installation may appear to be successful. But this will not be the case. See Troubleshooting for more information.

2.2. Steps to install

Place the following files in a directory on the SQL server machine:

  • dotnet-core-CSharp-lang-extension-windows-release.zip

  • MSSQLRunner.dll

  • install.ps1

  • perms.ps1

As an administrator, run the install.ps1 Powershell script with the following arguments:

  • MSSqlDir: (a mandatory argument) the full path to SQL Servers' MSSQL directory (e.g. "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL")

  • ConnectionString: (a mandatory argument) the connection string to the database.

  • RadaRPath : (an optional argument) the full path to the directory that contains install.ps1. If this argument is not given, the current working directory ($pwd) is used.

For each directory where .dll files produced by the Raincode legacy compilers will be placed, as an adminstrator run the perms.ps1 Powershell script with the following argument:

  • AssemblyDir: (a mandatory argument) the full path to the directory.

The radar.ps1 script is not required on the server machine; it is only used for Execution. For more details, refer to Execution.
Microsoft SQL Server Language Extensions and RadaR are run inside a sandbox that is started by the SQL server. The most important consequence is that file system access permissions must be given to directories where the code is located and where the code will read or write data. This is the role of the perms.ps1 script.

3. Execution

The second part of RadaR is triggering the execution of the code on the SQL Server machine. This can be performed from any other location that can connect to the database.

3.1. Prerequisites

Execution of code with RadaR has the following prerequisites:

  • The compiled code is on the SQL Server machine (in a directory that has been given the right permissions with the perms.ps1 script).

  • When not called from Submit, a temporary directory to hold intermediate files as well as console output. For example, the Windows temporary directory is a suitable candidate.

  • A working sqlcmd.exe with environment variables set to connect to the database (e.g. SQLCMDDBNAME, SQLCMDSERVER, SQLCMDUSER, SQLCMDPASSWORD) without requiring additional arguments.

  • The radar.ps1 script.

These prerequisites are different than those for installation. If they are not met, RadaR will not be able to run.

We need a working sqlcmd.exe to be able to recuperate both the return code of the execution as well as its output on stdout and stderr. Considering the environment variables needed, the database name set in SQLCMDDBNAME needs to be the same database name as the one given in the connection string passed to install.ps1 (in the installation step above).

3.2. Using RadaR

To execute code, run the radar.ps1 script with the following arguments:

  • SqlFile: (an optional argument, see below) A full path to a temporary file name of your choosing (e.g. in the temporary directory). Note that if the file already exists, it will be overwritten.

  • RunArgs: (a mandatory argument) The arguments that will be passed to rclrun.exe.

The RunArgs argument specifies the command line arguments that will be passed to rclrun.exe on the server. It should contain any optional arguments to be passed to rclrun.exe, e.g. "-AddAssemblySearchDir=C:\cobol\"(if the compiled code on the SQL server machine is in C:\cobol\), as well as the program name.

When called from Submit, RadaR has full support for the PARM parameter of the JCL. PARM is treated identically to a local execution of the program: the contents of the PARM parameter will simply be added to the RunArgs.

The SqlFile argument is ignored when radar.ps1 is being run from Submit, i.e. when it is called as the PGM of a JCL. In those cases, the value of SqlFile is given by the environment variables set by Submit: it will be RC_JOB_SYSOUT_DIR\RC_STEP_IDU.rdr.

If RC_JOB_SYSOUT_DIR and RC_STEP_IDU are not set, i.e. radar.ps1 is not being run from Submit, then SqlFile is a mandatory argument.

The script will generate SQL code into the temporary file SqlFile. Fundamentally the file contains a call to a stored procedure that runs rclrun.exe. The script then performs this call by using sqlcmd.exe, and places the console output of the call in a .out file. The return code of the script is the return code of rclrun.exe.

Any paths passed to rclrun.exe in the RunArgs argument, e.g. for -AddAssemblySearchDir, need to be full paths.
The execution of the SQL causes rclrun.exe to run on the SQL server machine, with the RunArgs as arguments. The behaviour of rclrun.exe is as if it were run at the command line on the SQL server machine that hosts the database.

4. Troubleshooting

The error messages the SQL server provides tend to focus on saying that something failed and are somewhat low on actionable information on addressing the issue. In this section, we provide a guide on establishing where the actual error may lie, and pointers on resolving the issue.

4.1. Ensure rclrun works

If something fails during the execution of rclrun.exe, then the error messages produced by rclrun.exe will be recuperated by RadaR and placed in its output file. In the absence of error messages from rclrun.exe, the first item to verify is whether rclrun.exe can execute a (test) program.

To do this, connect to the SQL Server machine, open a console and enter "%RCBIN%\rclrun.exe" <RunArgs> (replacing <RunArgs> with the contents of the RunArgs argument to radar.ps1). If rclrun.exe does not work, take the necessary steps to address this issue.

Needless to say, if rclrun.exe does work but produces some error message, use the information in that message to solve the problem.

Since RadaR runs rclrun.exe from a working directory that is chosen by the SQL server, avoid using relative paths in RunArgs . Instead, use full paths whenever a path argument is needed, e.g. in -AddAssemblySearchDir.

4.2. Verify permissions

Security provided by the Microsoft SQL Server Language Extensions relies (among others) on permissions set on various directories. If these are not set correctly, installation of the language extension will fail (possibly silently) and execution will fail. The role of the install.ps1 and perms.ps1 scripts are to set these permissions correctly.

There are two special permissions holders that need to be granted read and execute access: ALL APPLICATION PACKAGES and SQLRUserGroup (the latter being the group that is associated with the Launchpad service).

Read and execute permissions need to be present on the following directories:

  • On the directory that holds rclrun.exe permissions should have been granted to both.

  • On the MSSQL directory, and its subdirectories, permissions should have been granted to ALL APPLICATION PACKAGES.

  • On each directory where rclrun.exe needs access, i.e. to load dlls for programs and to read files, permissions should have been granted to both. Additionally, if user code needs to write to files, write access should be granted as well.

If permissions are not set correctly, refer to the install.ps1 and perms.ps1 script for correcting this.

If the permissions are not set correctly on the MSSQL directory, the installation has probably failed silently. Refer to the next section to address this.

4.3. Verify installation dlls

It is possible to execute the install.ps1 script but the dlls shipped with RadaR are not correctly installed by SQL Server. Indeed, there are multiple scenarios of missing prerequisites where SQL Server reports that the language extension was installed correctly, but this is not the case. One example is when in SQL Server network configuration named pipes is not enabled. In this scenario, install.ps1 will not produce any errors, yet radar.ps1 will fail.

It is not straightforward to verify correct installation of the required dlls as part of install.ps1. This is because the installation is only performed by the SQL server on the first call of the language extension (i.e. the first execution of radar.ps1), and the paths of where these dlls end up vary.

Hence a last troubleshooting step is manual verification of the correct placement of these dlls. There are two dlls that should be present inside the MSSQL directory:

  • nativecsharpextension.dll is the dll for .Net language support. It should be present in a subfolder of MSSQL\ExternalLanguages, e.g. MSSQL\ExternalLanguages\5\65541\

  • rcrun.dll is a dll that is part of RadaR. It should be present in a subfolder of MSSQL\ExternalLibraries, e.g.MSSQL\ExternalLibraries\5\65541\1\

If neither of these two dlls are present (after an execution of radar.ps1) then installation has failed.

To address a failed installation, first, double-check if all prerequisites for installation have been satisfied. If not, make it so.

Second, the SQL server may erroneously consider that the language and library have been installed. Verify this with the following SQL statements: SELECT * FROM sys.external_libraries should not contain rcrun.dll and SELECT * FROM sys.external_languages should not contain Dotnet. If this is the case, execute the following SQL: DROP EXTERNAL LIBRARY [rcrun.dll] and DROP EXTERNAL LANGUAGE Dotnet respectively.

Third, perform the installation using the install.ps1 script as detailed previously.