Install SQL Server 2022 Machine Learning Services (Python and R) on Windows

Articol
03/11/2024

Applies to: SQL Server 2022 (16.x)

This article shows you how to install SQL Server 2022 Machine Learning Services on Windows. You can use Machine Learning Services to run Python and R scripts in-database.

Note

These instructions are specific to SQL Server 2022 (16.x) on Windows. To install SQL Server Machine Learning Services on Windows for SQL Server 2016 (13.x), SQL Server 2017 (14.x), or SQL Server 2019 (15.x), see Install SQL Server Machine Learning Services (Python and R) on Windows.

For Linux, see Install SQL Server Machine Learning Services (Python and R) on Linux.

Pre-installation checklist

A database engine instance is required. You can't install just Python or R features, although you can add them incrementally to an existing instance.
For business continuity, Always On availability groups are supported for Machine Learning Services. Install Machine Learning Services, and configure packages, on each node.
Installing Machine Learning Services is also supported on an Always On failover cluster instance in SQL Server 2019 and later.
Don't install Machine Learning Services on a domain controller. The Machine Learning Services portion of setup will fail.
Side-by-side installation with other versions of Python and R is supported, but we don't recommend it. It's supported because the SQL Server instance uses its own copies of the open-source R and Anaconda distributions. We don't recommend it because running code that uses Python and R on a computer outside SQL Server can lead to problems:
- Using a different library and different executable files will create results that are inconsistent with what you're running in SQL Server.
- SQL Server can't manage R and Python scripts that run in external libraries, leading to resource contention.

Important

After you finish setup, be sure to complete the post-configuration steps described in this article. These steps might include enabling SQL Server to use external scripts. Configuration changes generally require a restart of the instance or a restart of the Launchpad service.

Get the installation media

The download location for SQL Server depends on the edition:

SQL Server Enterprise, Standard, and Express editions. These editions are licensed for production use. For the Enterprise and Standard editions, contact your software vendor for the installation media. You can find purchasing information and a directory of Microsoft partners on the Microsoft purchasing website.
The latest free edition.

Run setup

For local installations, you must run the setup as an administrator. If you install SQL Server from a remote share, you must use a domain account that has read and execute permissions on the remote share.

If you encounter any installation errors during setup, check the summary log in the Setup Bootstrap log folder (for example, %ProgramFiles%\Microsoft SQL Server\160\Setup Bootstrap\Log\Summary.txt).

Start the SQL Server 2022 Setup wizard.
On the Installation tab, select New SQL Server stand-alone installation or add features to an existing installation.
On the Feature Selection page, select these options:
- Database Engine Services
  
  To use R or Python with SQL Server, you must install an instance of the database engine. You can use either a default or a named instance.
- Machine Learning Services and Language
  
  This option installs the database services that support R and Python script execution.
This screenshot shows the minimum instance features to check when you're installing SQL Server 2022 (16.x) Machine Learning Services.

Install runtimes and packages

Beginning with SQL Server 2022 (16.x), runtimes for R, Python, and Java are no longer shipped or installed with SQL Server setup. Instead, use the following sections to install your custom runtimes and packages.

Setup R support

Install R runtime

Download and install the most recent version of R 4.2 for Windows.
Install dependencies for CompatibilityAPI and RevoScaleR. From the R terminal of the version that you installed, run the following commands:
```
# R Terminal
install.packages("iterators")
install.packages("foreach")
install.packages("R6")
install.packages("jsonlite")
```

Download and install the latest version of CompatibilityAPI and RevoScaleR packages:

install.packages("https://aka.ms/sqlml/r4.2/windows/CompatibilityAPI_1.1.0.zip", repos=NULL)

install.packages("https://aka.ms/sqlml/r4.2/windows/RevoScaleR_10.0.1.zip", repos=NULL)

Configure R runtime with SQL Server

Configure the installed R runtime with SQL Server. You can change the default version by using the RegisterRext.exe command-line utility. The utility is in an R application folder that depends on the installation. Usually, it's in %ProgramFiles%\R\R-4.2.3\library\RevoScaleR\rxLibs\x64.

You can use the following script to configure the installed R runtime from the installation folder location of RegisterRext.exe. The instance name is MSSQLSERVER for a default instance of SQL Server, or the instance name for a named instance of SQL Server.
```
.\RegisterRext.exe /configure /rhome:"%ProgramFiles%\R\R-4.2.3" /instance:"MSSQLSERVER"
```
If you are configuring a named instance of SQL Server that you would normally refer to as ".\SQLEXPRESS", or "MACHINENAME\SQLEXPRESS", include only the instance name. For example:
```
.\RegisterRext.exe /configure /rhome:"%ProgramFiles%\R\R-4.2.3" /instance:"SQLEXPRESS"
```
By using SQL Server Management Studio (SSMS) or Azure Data Studio, connect to the instance where you installed SQL Server Machine Learning Services. Select New Query to open a query window, and then run the following command to enable the external scripting feature:
```
EXEC sp_configure  'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE
```
If you've already enabled the feature for another language, you don't need to run RECONFIGURE a second time for R. The underlying extensibility platform supports both languages. To verify, confirm that the following command returns 1 for config_value and run_value:
```
EXEC sp_configure  'external scripts enabled';
```
Restart the SQL Server service. Restarting the service also automatically restarts the related SQL Server Launchpad service.

You can restart the service by using the right-click Restart command for the instance in SSMS Object Explorer, or by using the Services item in Control Panel, or by using SQL Server Configuration Manager.
Verify the installation by running a simple T-SQL command to return the version of R:
```
EXEC sp_execute_external_script @script=N'print(R.version)',@language=N'R';
GO
```

Setup Python support

Install Python runtime

Download the most recent version of Python 3.10 for Windows. Install it by using the following options:
1. Open the Python Setup application and select Customize installation.
2. Verify that the Install launcher for all users (recommended) checkbox is selected.
3. For Optional Features, select the features that you want (or select them all).
4. On the Advanced Options page, select Install for all users, accept other default options, and then select Install.
  
  We recommend using a Python installation path that all users can access (such as C:\Program Files\Python310), and not one that's specific to a single user.

Download and install the latest version of the revoscalepy package and its dependencies from a new elevated command prompt:

cd "C:\Program Files\Python310\"
python -m pip install -t "C:\Program Files\Python310\Lib\site-packages" dill numpy==1.22.0 pandas patsy python-dateutil
python -m pip install -t "C:\Program Files\Python310\Lib\site-packages" https://aka.ms/sqlml/python3.10/windows/revoscalepy-10.0.1-py3-none-any.whl

Run the following icacls commands to grant READ and EXECUTE access to the installed libraries to SQL Server Launchpad Service and SID S-1-15-2-1 (ALL_APPLICATION_PACKAGES). You need to grant permissions to the service account associated with the Launchpad service, check in SQL Server Configuration Manager.

icacls "C:\Program Files\Python310\Lib\site-packages" /grant "NT Service\MSSQLLAUNCHPAD":(OI)(CI)RX /T
icacls "C:\Program Files\Python310\Lib\site-packages" /grant *S-1-15-2-1:(OI)(CI)RX /T

If you installed SQL Server as a named instance, the service account may have a $ in the middle, for example:

icacls "C:\Program Files\Python310\Lib\site-packages" /grant "NT Service\MSSQLLAUNCHPAD$SQLEXPRESS":(OI)(CI)RX /T
icacls "C:\Program Files\Python310\Lib\site-packages" /grant *S-1-15-2-1:(OI)(CI)RX /T

Configure Python runtime with SQL Server

Configure the installed Python runtime with SQL Server. You can change the default version by using the RegisterRext.exe command-line utility. The utility is in the custom installation location (for example, C:\Program Files\Python310\Lib\site-packages\revoscalepy\rxLibs).

From an elevated command prompt, you can use the following script to configure the installed Python runtime from the installation folder location of RegisterRext.exe. The instance name is MSSQLSERVER for a default instance of SQL Server, or the instance name for a named instance of SQL Server.
```
cd "C:\Program Files\Python310\Lib\site-packages\revoscalepy\rxLibs"
.\RegisterRext.exe /configure /pythonhome:"C:\Program Files\Python310" /instance:"MSSQLSERVER"
```
If you are configuring a named instance of SQL Server that you would normally refer to as ".\SQLEXPRESS", or "MACHINENAME\SQLEXPRESS", include only the instance name. For example:
```
cd "C:\Program Files\Python310\Lib\site-packages\revoscalepy\rxLibs"
.\RegisterRext.exe /configure /pythonhome:"C:\Program Files\Python310" /instance:"SQLEXPRESS"
```
Use SQL Server Management Studio or Azure Data Studio to connect to the instance where you installed SQL Server Machine Learning Services. Select New Query to open a query window, and then run the following command to enable the external scripting feature:
```
EXEC sp_configure  'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE
```
If you've already enabled the feature for another language, you don't need to run RECONFIGURE a second time for R. The underlying extensibility platform supports both languages. To verify, confirm that the following command returns 1 for config_value and run_value:
```
EXEC sp_configure  'external scripts enabled';
```
Restart the SQL Server service. Restarting the service also automatically restarts the related SQL Server Launchpad service.

You can restart the service by using the right-click Restart command for the instance in SSMS Object Explorer, or by using the Services item in Control Panel, or by using SQL Server Configuration Manager.

Verify the installation by running a simple command to return the version of Python:

EXEC sp_execute_external_script @script=N'import sys;print(sys.version)',@language=N'Python'
GO

Install Java

For information on installing and using Java, see Install SQL Server Java Language Extension on Windows.

Additional configuration

If the external script verification step was successful, you can run R or Python commands from SQL Server Management Studio, Visual Studio Code, or any other client that can send T-SQL statements to the server.

Whether the additional configuration is required depends on your security schema, where you installed SQL Server, and how you expect users to connect to the database and run external scripts.

If you got an error when you ran the command, you might need to make additional configurations to the service or database. At the instance level, additional configurations might include:

Configure a firewall for SQL Server Machine Learning Services
Enable additional network protocols
Enable remote connections
Create a login for SQLRUserGroup
Manage disk quotas to prevent external scripts from running tasks that exhaust disk space

Starting with SQL Server 2019 on Windows, the isolation mechanism has changed. This mechanism affects SQLRUserGroup, firewall rules, file permission, and implied authentication. For more information, see Isolation changes for Machine Learning Services.

On the database, you might need configuration updates. For more information, see Give users permission to SQL Server Machine Learning Services.

Suggested optimizations

Now that you have everything working, you might also want to optimize the server to support machine learning or install a pre-trained machine learning model.

Optimize the server for script execution

The default settings for SQL Server setup are intended to optimize the balance of the server for a variety of other services and applications.

Under the default settings, resources for machine learning are sometimes restricted or throttled, particularly in memory-intensive operations.

To ensure that machine learning jobs are prioritized and resourced appropriately, we recommend that you use SQL Server Resource Governor to configure an external resource pool. You might also want to change the amount of memory that's allocated to the SQL Server database engine, or increase the number of accounts that run under the SQL Server Launchpad service.

To configure a resource pool for managing external resources, see Create an external resource pool.
To change the amount of memory reserved for the database, see Server memory configuration options.
To change the number of R accounts that SQL Server Launchpad can start, see Scale concurrent execution of external scripts in SQL Server Machine Learning Services.

If you're using Standard Edition and don't have Resource Governor, you can use dynamic management views, SQL Server Extended Events, and Windows event monitoring to help manage the server resources.

Install additional Python and R packages

The Python and R solutions that you create for SQL Server can call:

Basic functions.
Functions from the proprietary packages installed with SQL Server.
Third-party packages that are compatible with the version of open-source Python and R that SQL Server installs.

Packages that you want to use from SQL Server must be installed in the default library that the instance uses. If you have a separate installation of Python or R on the computer, or if you installed packages to user libraries, you can't use those packages from T-SQL.

To install and manage additional packages, you can set up user groups to share packages on a per-database level. Or you can configure database roles to enable users to install their own packages. For more information, see Install Python packages and Install new R packages.

Standalone RevoScale packages for Python and R runtime

RevoScale packages are also supported as a standalone package with Python and R runtimes. In order to setup Python or R runtime for the standalone scenario, follow the instructions in the Install Python runtime and Install R runtime sections respectively.

Python developers can learn how to use Python with SQL Server by following these tutorials:

R developers can get started with some simple examples and learn the basics of how R works with SQL Server. For your next step, see the following links:

Partajați prin

Install SQL Server 2022 Machine Learning Services (Python and R) on Windows

Pre-installation checklist

Get the installation media

Run setup

Install runtimes and packages

Setup R support

Install R runtime

Configure R runtime with SQL Server

Setup Python support

Install Python runtime

Configure Python runtime with SQL Server

Install Java

Additional configuration

Suggested optimizations

Optimize the server for script execution

Install additional Python and R packages

Standalone RevoScale packages for Python and R runtime

Feedback

Resurse suplimentare

Partajați prin

Install SQL Server 2022 Machine Learning Services (Python and R) on Windows

Pre-installation checklist

Get the installation media

Run setup

Install runtimes and packages

Setup R support

Install R runtime

Configure R runtime with SQL Server

Setup Python support

Install Python runtime

Configure Python runtime with SQL Server

Install Java

Additional configuration

Suggested optimizations

Optimize the server for script execution

Install additional Python and R packages

Standalone RevoScale packages for Python and R runtime

Related content

Feedback

Resurse suplimentare