Skip to main content

Requirements for AI Analyses

AI Analyses require a SQL environment that can execute R scripts with Machine Learning Services.

Important

AI Analyses only work if R can run and the processed dataset fits into the memory available to the R runtime.

Supported SQL environments​

EnvironmentSupportedNotes
SQL Server 2019 / 2022 on WindowsYesMachine Learning Services with R required
SQL Server on Azure Virtual Machine (Windows)YesMachine Learning Services with R required
Azure SQL Managed InstanceYesMachine Learning Services with R is supported by the platform
Azure SQL DatabaseNoDoes not support Machine Learning Services with R
SQL Server ExpressNoNot supported
SQL Server on LinuxNoNot supported
Hint

For RAM sizing, CPU guidance, and Azure SQL Managed Instance hardware recommendations, see Resource requirements for R based AI analytics.

1. Choose your platform​

1.1 SQL Server on-premises or Azure Virtual Machine​

Use this option if you want the most control over memory allocation, SQL Server configuration, and the R runtime.

  • Supported versions: SQL Server 2019 and SQL Server 2022 on Windows
  • Machine Learning Services with R must be installed
  • The dab prepackaged R runtime is recommended
  • max server memory and Resource Governor can be used to leave enough memory for R

Microsoft documentation

1.2 Azure SQL Managed Instance​

Use this option if you want a managed Azure SQL platform and accept the platform limits of Machine Learning Services.

  • Machine Learning Services with R is supported by Azure SQL Managed Instance
  • The base R runtime is provided by Azure SQL Managed Instance
  • Do not use RegisterRext.exe or Windows service steps on Azure SQL Managed Instance
  • Resource Governor external resource pools are not supported on Azure SQL Managed Instance
  • By default, R can use only up to 20% of Managed Instance resources
  • Required packages must be installed with sqlmlutils

Microsoft documentation

2. Enable Machine Learning Services​

2.1 SQL Server on-premises or Azure Virtual Machine​

Run the following command on the SQL instance:

sp_configure 'external scripts enabled', 1;
RECONFIGURE;

Restart the SQL Server Launchpad service afterwards.

2.2 Azure SQL Managed Instance​

Run the same command on the Managed Instance:

sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;

Azure SQL Managed Instance restarts the instance automatically during this change and is unavailable for a short time. There is no Launchpad service for you to restart manually.

3. Verify R execution​

Execute the following on the SQL environment:

EXEC sp_execute_external_script @language = N'R', @script = N'print("OK")';

Expected output:

OK

To check the installed R version:

EXEC sp_execute_external_script @language = N'R', @script = N'print(R.version)';

To list installed packages:

EXEC sp_execute_external_script @language = N'R', @script = N'print(installed.packages()[, c("Package", "Version")])';

4. Install required R packages​

4.1 SQL Server on-premises or Azure Virtual Machine​

The recommended option is to use the dab prepackaged R runtime.

  1. Download the runtime from dab Portal.

  2. Copy it to the SQL Server.

  3. Extract it to a local folder accessible by the SQL Server service account.

  4. Change to the folder where you extracted it, go to the library\RevoScaleR\rxLibs\x64 subfolder in the R installation

  5. Register it with SQL Server:

    RegisterRext.exe /configure /rhome:"C:\Program Files\R\R-4.x.x" /instance:"MSSQLSERVER"

  6. Restart the SQL Server Launchpad service.

  7. Verify execution with a simple R script.

Important

This runtime registration process applies only to self-managed SQL Server on Windows and SQL Server on Azure Virtual Machine. It does not apply to Azure SQL Managed Instance.

4.2 Azure SQL Managed Instance​

Azure SQL Managed Instance already includes the base R runtime. Only the required packages must be installed.

  • Install required packages with sqlmlutils from a client machine
  • Do not attempt to copy a local R runtime to the service
  • If you use failover groups, install packages into a user database rather than master

Use the Microsoft package management guide:

Troubleshooting​

IssueLikely causeSolution
R scripts do not executeexternal scripts enabled is disabledEnable the setting and retry
Out-of-memory error during analysisThe dataset does not fit into the memory available to RReduce dataset size or increase available RAM. See Resource requirements for R based AI analytics
Launchpad does not startWindows service misconfigurationCheck the SQL Server Launchpad service. Applies only to self-managed SQL Server
RegisterRext.exe does not workWrong platformUse it only on self-managed SQL Server on Windows or Azure VM
Azure SQL Managed Instance package installation failsWrong package installation methodUse sqlmlutils
Nexus cannot connectSQL Auth disabled or firewall restrictionsEnable SQL Auth and open the required network path