Enable Machine Learning Services on SQL Server

Eduardo Pivaral, 2018-11-08

R Services (SQL Server 2016) or Machine Learning Services (2017 and 2019 CTP) provide you with the ability to perform data analysis from the database itself using T-SQL.

You can learn a little more about what you can do in the SQL Server blog.

On this post, I will show you how to setup and configure it so you can start using it right away in your applications and reports.

To install it

You have to choose it from instance features on your SQL server setup window, you then choose the languages you want to work (R, Python)

Note: if your computer does not have access to the internet, you will have to download two packages separately and then locate them in the setup window.

Continue the setup as usual and finish it.

After you run the setup, please make sure that SQL Server LauchPad service is running for the instance you have installed the Machine Learning Services.

I recommend you to patch the instance with the latest update available in case you encounter any issue with the service startup.

To configure it

Just connect to the instance where the Machine Learning Services was added, using your favorite tool, and run the following T-SQL to enable the code execution:

EXEC sp_configure  'external scripts enabled', 1

You must see the following output:

After that, you have to restart the SQL Server service for it to work.

our last step is just to test it…

Testing the script execution

To execute scripts you must use the sp_execute_external_script procedure, you can find the official documentation here.

This is the most basic command I can think you can execute to see if everything is ok, displaying the license information of the R language:

EXEC sp_execute_external_script  
@language =N'R',
@script= N'license()'

Note that the first execution after a service restart will take some time to complete, but after waiting you should see the license information in the Messages window:

And now… a simple example with real data

For this example, we will take Application.Countries table from WideWorldImporters test database and display it sorted by population in descendent order with an R script, running this T-SQL:

USE WideWorldImporters

EXEC sp_execute_external_script @language =N'R',
dsTest<- InputDataSet
OutputDataSet <- dsTest[order(-dsTest$LatestRecordedPopulation),]
@input_data_1 =N'
FROM [Application].[Countries]
WHERE Region = ''Americas'''
[CountryName] [nvarchar](60) NOT NULL,
[FormalName] [nvarchar](60) NOT NULL,
[LatestRecordedPopulation] [bigint] NULL,
[Continent] [nvarchar](30) NOT NULL

If you have the database installed and run it, you can see the output with the data sorted from the R script:

You have learned how to execute simple R scripts against SQL server tables from the database engine, so now you can make more complex examples and do your own data analysis scripts.

For more in-depth information visit this link.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads