Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to setup Machine Learning Services in SQL Server 2017 CTP2


By:   |   Read Comments   |   Related Tips: More > SQL Server 2017

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

Establishing machine learning capabilities for predictive analysis is on the rise in the industry. SQL Server 2017 CTP2 ships with built-in support using languages like R and Python. To enable this services, different packages and distributions of R and Python are installed. Also extensions related to these are added to the database engine to enable the use of Python and R within T-SQL scripts. In this tip, we will look at how to install SQL Server 2017 CTP2 with Machine Learning Services and how to run sample R and Python scripts to test a successful installation.

Solution

Machine Learning Server and Machine Learning Services (In-Database) enables the use of Python and R for developing machine learning, predictive analytics and such other capabilities.

Installing In-Database Machine Learning Services

The first step towards setting up machine learning services is to download and setup SQL Server 2017 CTP2 from here. After you have downloaded, unzip the setup and execute the setup file. The rest of the installation of SQL Server is the same as other versions. Let’s take a look at steps specific to Machine Learning Services.

Once you are on the feature selection page, you should be able to find options as shown below. In the shared features, select Machine Learning Server. SQL Server 2017 CTP2 introduces support for Python as well. Select R as well as Python to setup R server as well as Python Server. Select these under the Database Engine services as shown below, so that extensions are installed to support execution of R and Python scripts from T-SQL and Database Engine.

Feature Selection - Description: Feature Selection

If you have opted to setup R server, you will be asked to provide your consent to download and install Microsoft R Open which is Microsoft’s distribution to R server as shown below.

Microsoft R Open - Description: Consent to install R

If you have opted to setup Python server, you will be asked to provide your consent to download and install the Anaconda distribution of Python as shown below.

Anaconda Distribution - Description: Consent to install Python

After you finish the setup and installation is complete, open SQL Server Management Studio. By default external scripts are not enabled in the database engine. You need to enable the external scripts option using a simple command as shown in this article. You may have to restart the server for the changes to take effect. After the external scripts execution is enabled, we can use sp_execute_external_script to execute R and Python scripts.

In order to test whether the Python server has been successfully setup and is responding to script execution requests from T-SQL, you can execute a small sample script as shown below.

execute sp_execute_external_script 
@language = N'Python',
@script = N'
import sys
import os
import pip
import numpy
 
# System Version, Working Dir and Hello World
print("System Version : ", sys.version)
print("Working Dir : ", os.getcwd())
print("Hello World")
'
   
Test Python Server - Description: Sample script to test Python Server

In order to test whether the R server has been successfully setup and is responding to script execution requests from T-SQL, you can execute a small sample script as shown below.

--R Version
execute sp_execute_external_script 
@language = N'R',
@script = N'
print(R.version)
'
   
R Server - Description: Sample Script to test R Server

In this way we can install, configure and test Machine Learning Server and In-Database Machine Learning Services in SQL Server 2017 CTP2.

Next Steps
  • Consider testing different packages and scripts in Python and R to check the features available in Machine Learning Server.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools