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:
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:
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:
- In SQL Server Management Studio, in Object Explorer, expand Security, right-click Logins, and select New Login.
- In the Login – New dialog box, select Search.
- Select the Object Types and Groups checkboxes.
- Click Advanced, verify that the location to search is the current computer, and then click Find Now.
- Scroll through the list of group accounts on the server until you find one beginning with SQLRUserGroup.
- 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.
- If you are using a named instance, the instance name is appended to the default name, SQLRUserGroup.
- 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:
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:
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.
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:
Verify installed R version:
- Find the R.exe or Rgui.exe in the folder \R_SERVICES\bin\x64
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\bin\x64\Rgui.exe
- Double-click Rgui.exe to start the R Console application.
The console will show the current R version:
As an option, you can run aforementioned SQL script:
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:
- Remove the old version (optional, but preferable):
Open Control Panel > Programs > Programs and Features and uninstall Microsoft R Client
- Open https://docs.microsoft.com/en-us/machine-learning-server/r-client/install-on-windows
- Find “How to install” section and click on the Download Microsoft R Client from the following link: http://aka.ms/rclient/
- Download and run the RClientSetup.exe
- Accept default settings.
- Enjoy 🙂
- External scripts enabled Server Configuration Option: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/external-scripts-enabled-server-configuration-option
- Set up SQL Server Machine Learning Services (In-Database): https://docs.microsoft.com/en-us/sql/advanced-analytics/r/set-up-sql-server-r-services-in-database
- How To: Create a Resource Pool for R: https://docs.microsoft.com/en-us/sql/advanced-analytics/r/how-to-create-a-resource-pool-for-r
- Configuring R on SQL Server 2016: http://sqlmag.com/sql-server/configuring-r-sql-server-2016
- Monitor Machine Learning Services using custom reports in Management Studio: https://docs.microsoft.com/en-us/sql/advanced-analytics/r/monitor-r-services-using-custom-reports-in-management-studio
- Upgrade machine learning components in a SQL Server instance: https://docs.microsoft.com/en-us/sql/advanced-analytics/r/use-sqlbindr-exe-to-upgrade-an-instance-of-sql-server
- Enable or disable R package management for SQL Server: https://docs.microsoft.com/en-us/sql/advanced-analytics/r/r-package-how-to-enable-or-disable