Configuring Machine Learning Services in SQL Server 2017

rconfig-title

In order to operationalize freshly installed SQL Server 2017 Machine Learning Services, we can spend quite a few hours (or days). I hope this article will help you to save your time.

I could refer the reader to the official documentation, but I am sure you’ve already been there.

Enable external scripts

The first step will be to enable execution of external scripts in the SQL Server, running Machine Learning Services. Connect to the SQL Server with the SQL Server Management Studio, and execute the following script:

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

Restart your SQL Server to make this change effective. After restarting SQL services, verify that SQL Server Launchpad service is running:

rconfig-services

Now you can check that script execution is working. Open a new Query window, and then run the following command:

EXEC sp_execute_external_script 
       @language =N'R',
       @script=N'OutputDataSet <- InputDataSet;',
       @input_data_1 =N'SELECT 1 AS hello'
WITH RESULT SETS (([hello] int not null));
GO

If the command executes without errors, you will see the result:

rconfig-hello

Enable implied authentication for the Launchpad

To enable authentication for the Launchpad, you should create a new SQL Login for SQLRUserGroup user. This process is well described in the https://docs.microsoft.com/en-us/sql/advanced-analytics/r/set-up-sql-server-r-services-in-database, but I will repeat these steps here:

  1. In SQL Server Management Studio, in Object Explorer, expand Security, right-click Logins, and select New Login.
  2. In the Login – New dialog box, select Search.
  3. Select the Object Types and Groups checkboxes.
  4. Click Advanced, verify that the location to search is the current computer, and then click Find Now.
  5. Scroll through the list of group accounts on the server until you find one beginning with SQLRUserGroup.
  6. The name of the group that’s associated with the Launchpad service for the default instance is always just SQLRUserGroup. Select this account only for the default instance.
  7. If you are using a named instance, the instance name is appended to the default name, SQLRUserGroup.
  8. Click OK and close dialog boxes.

Give users permission to run external scripts

If a user, running R scripts, does not have elevated permissions, you must grant the user permissions to run scripts in each database where external scripts will be used:

USE 
GO
GRANT EXECUTE ANY EXTERNAL SCRIPT TO [UserName]

Modify server resource pools

Under SQL server default settings, the R runtime will probably have insufficient memory. First, check the resources allocated to the default pool for the server:

SELECT * FROM sys.resource_governor_resource_pools WHERE name = 'default'

By default, the max_cpu, max_memory and cap_cpu are set to 100 percent:

rconfig-sql1

Now check the resources allocated to the default external resource pool:

SELECT * FROM sys.resource_governor_external_resource_pools WHERE name = 'default'

By default, the maximum memory that R can use is 20% of the remaining memory:

rconfig-sql2

The following settings will reduce the maximum computer memory that can be used by SQL Server and increase the memory allocated to external processes:

ALTER RESOURCE POOL "default" WITH (max_memory_percent = 60); 
ALTER EXTERNAL RESOURCE POOL "default" WITH (max_memory_percent = 40); 
ALTER RESOURCE GOVERNOR reconfigure; 
GO

The values are just suggested settings to start with.

Please remember that SQL Server Standard edition does not support resource governance, i.e. memory distribution between SQL server and R engine:

  • Standard Edition

    Has all the capabilities of in-database analytics included with Enterprise Edition, except for resource governance. Performance and scale is also limited: the data that can be processed must fit in server memory, and processing is limited to a single compute thread, even when using the RevoScaleR functions.

Source: https://docs.microsoft.com/en-us/sql/advanced-analytics/r/differences-in-r-features-between-editions-of-sql-server.

Upgrade R Services to the latest version

First verify server’s version of the R Services, running the SQL script:

EXEC sp_execute_external_script @language =N'R',
@script=N'myvar <- version$version.string
OutputDataSet <- as.data.frame(myvar);'

SQL Server 2017 goes with the R Services of version 3.3.3, which is quite an old one. But you can upgrade the version, if you install Microsoft Machine Learning Server 9.2.1 (or upgrade it to this version).

Read the article: Upgrade machine learning components in a SQL Server instance. The article Install Machine Learning Server for Windows has links to the Machine Learning Server installer. For instance, Machine Learning Server (Developer Edition) for Windows setup can be found here.

Start the installer ServerSetup.exe for Machine Learning Server. Be sure to run the installer on the computer that has the instance you want to upgrade. To upgrade an instance, select the checkbox next to the instance name. If you do not select an instance, a separate installation of Machine Learning Server is created, and the SQL Server libraries are unchanged:

rconfig-mlserver

Verify installed R version:

  1. Find the R.exe or Rgui.exe in the folder \R_SERVICES\bin\x64
    For example:
    C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\bin\x64\Rgui.exe
  2. Double-click Rgui.exe to start the R Console application.

The console will show the current R version:

rconfig-console

As an option, you can run aforementioned SQL script:

rconfig-version

Update local Microsoft R Client to the latest version

If you need to upgrade your local Microsoft R Client to the latest version to synchronize it with the server’s version, you can:

Useful Links

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s