Edit

Key differences between Machine Learning Services in Azure SQL Managed Instance and SQL Server

  • Article

This article describes the few, key differences in functionality between Machine Learning Services in Azure SQL Managed Instance and SQL Server Machine Learning Services.

Language support

Machine Learning Services in both SQL Managed Instance and SQL Server support the Python and R extensibility framework. A key difference in SQL Managed Instance is that only Python and R are supported, and external languages such as Java cannot be added.

The initial versions of Python and R are different in SQL Managed Instance and SQL Server:

Platform Python runtime version R runtime versions
Azure SQL Managed Instance 3.7.2 3.5.2
SQL Server 2022 * - -
SQL Server 2019 3.7.1 3.5.2
SQL Server 2017 3.5.2 and 3.7.2 (CU22 and later) 3.3.3 and 3.5.2 (CU22 and later)
SQL Server 2016 Not available 3.2.2 and 3.5.2 (SP2 CU14 and later)

* Beginning with SQL Server 2022, runtimes for R, Python, and Java, are no longer shipped or installed within SQL Setup. Instead, install your desired R and/or Python custom runtime(s) and packages. For more information, see Install SQL Server 2022 Machine Learning Services (Python and R) on Windows.

Python and R Packages

There is no support in SQL Managed Instance for packages that depend on external runtimes (like Java) or need access to OS APIs for installation or usage.

For more information about managing Python and R packages, see:

Manage packages via sqlmlutils

You can install binary packages with sqlmlutils, if you can build locally to produce a binary package as output. For examples, see Install R packages with sqlmlutils or Install Python packages with sqlmlutils.

Resource governance

In SQL Managed Instance, it's not possible to limit R resources through Resource Governor, and external resource pools are not supported.

By default, R resources are set to a maximum of 20% of the available SQL Managed Instance resources when extensibility is enabled. To change this default percentage, create an Azure support ticket at https://azure.microsoft.com/support/create-ticket/.

Extensibility is enabled with the following SQL commands (SQL Managed Instance will restart and be unavailable for a few seconds):

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

To disable extensibility and restore 100% of memory and CPU resources to SQL Server, use the following commands:

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

The total resources available to SQL Managed Instance depend on which service tier you choose. For more information, see Azure SQL Database purchasing models.

Insufficient memory error

Memory usage depends on how much is used in your R scripts and the number of parallel queries being executed. If there is insufficient memory available for R, you'll get an error message. Common error messages are:

  • Unable to communicate with the runtime for 'R' script for request id: *******. Please check the requirements of 'R' runtime
  • 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. ...an external script error occurred: "..could not allocate memory (0 Mb) in C function 'R_AllocStringBuffer'"
  • An external script error occurred: Error: cannot allocate vector of size.

If you receive one of these errors, you can resolve it by scaling your database to a higher service tier.

If you encounter out of memory errors in Azure SQL Managed Instance, review sys.dm_os_out_of_memory_events.

SQL Managed Instance pools

Machine Learning Services is currently not supported on Azure SQL Managed Instance pools (preview).

Outbound network access

Network access is disallowed or blocked and cannot be enabled. The outbound network connection for Azure SQL Managed Instance is not avaialble for Machine Learning Services.

Next steps