Adventures in setting up machine learning services in MS SQL Server 2019

This article describes how to set up machine learning services for R and Python. Despite my extensive experience in IT, almost nothing works for me right away. Therefore, there will be links to what to do if it doesn’t start right away. As a result, I installed a new instance (instance) of SQL Server with machine learning services, which, finally! earned.

Initially, SQL Server 2016 introduced the ability to execute R scripts on data in SQL Server, the new component was called “SQL Server R Services”. In 2017, they added support for Python, and since 2019 – Java, and the component was renamed to Machine Learning Services.

First I will tell you what to do to make it work, and then I will briefly outline my two days of suffering. In an ideal world, after installing an instance of MS SQL Server, the distribution kit remained and it Same versions as the instance itself. In this case, run Setup. Select “New SQL Server stand-alone installation or add features to an existing instance”

And then on the list of components select “Instance Features -> Machine Learning Services and Languages”

Do NOT select Standalone Shared Machine Learning Services (Shared Features Machine Leaning Services Standalone).

When the installation is complete, launch SQL Server Configuration Manager. On Windows 10 Configuration Manager hides here C:\Windows\SysWOW64\SQLServerManager15.msc for

AT SQL Server Configuration Manager restart the MS SQL Server instance to which the machine learning services were added.

Further create system environment variable “MKL_CBWR” with value “AUTO”

We return to SQL Configuration Manager again and start the SQL Server Launchpad service. After the service has successfully started, we launch SQL Server Management Studio and activate machine learning services.

EXEC sp_configure  'external scripts enabled', 1


EXEC sp_configure 'external scripts enabled'

And we see the result

We carry out Hello world in R and Python

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));

EXECUTE sp_execute_external_script @language = N'Python'
    , @script = N'OutputDataSet = InputDataSet'
    , @input_data_1 = N'SELECT 1 AS hello'

And we get the result

Happiness – machine learning services are set up!

So, my path, apparently, passed with almost all the rakes that could be stepped on. I installed SQL Server a long time ago and deleted the original distribution, so I downloaded it again SQL Server 2019 Developer Editionto use machine learning components as a source.

I came across an article that suggested installing Standalone Shared Services, and I installed them. Didn’t work. We look at the Microsoft documentation and see that it is not recommended to install shared features if there is a SQL Server on the machine (“if all else fails, finally read the documentation”)

Then I installed Machine Learning Services from the documentation (now the ones you need), tried to start the service – an error. In the event log (in Windows 10 Event Viewer -> Windows Event Log -> System)

Despite the fact that the documentation does not say this directly, I thought that the installed Shared Services could be affected, since there is not much memory on the computer and they compete for the resource. Removed Shared Services via Uninstall Programs. Did not help.

There were two versions of articles on the topic “What to do if the service does not start?”:

The first one says that the service won’t start because the version of installed machine learning services is different from the instance. (somewhere here).

The second calls the reason that lacks right, but when checking I did not have a SQLRUserGroup user, and I decided to return to the idea with the wrong version.

I decided to return to the idea with different versions and put a cumulative update, hoping that both SQL Server and Machine Learning Services would be updated. Did not help. Moreover, after the upgrade, the distribution no longer allows you to add a component to an existing instance. Having suffered a little, I installed a new instance of SQL Server and, as you understand, now you can see the difference in versions.

SQL server cumulative update considers service updated along with SQL Server instance

The version of the service in SQL Configuration Manager is still the same as before the upgrade.

stackoverflow the thread about the service not starting and trying to fix it by allowing a TCP connection didn’t help either.

Moral – do not despair if something does not work for you for a long time or does not work. At least there are still people for whom everything that could not work did not work either.

That is why I try not to talk about installing and configuring software in open lessons, but show the use of the installed product. Come to the open classwhich will analyze examples of data analytics and machine learning libraries built into SQL Server.

Similar Posts

Leave a Reply