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.
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.
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.
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")
'
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)
'
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.
