The R language is one of the most popular languages for data science, machine learning services and computational statistics. There are several IDEs that allow seamless R development. Owing to the growing popularity of the R language, R services have been included by Microsoft in SQL Server 2016 onwards. In this article, we will briefly review how we can integrate R with SQL Server 2017. We will see the installation process and will also execute the basic R commands in SQL Server 2017.

Environment Setup

To run R scripts in SQL Server, you have to install Machine Learning Services in SQL Server, which can be done in two different ways. You can install machine learning services to an existing installation of SQL Server or you can configure to install these services with a fresh installation of SQL Server. In this article we will see the second approach where we will download a new version of SQL Server 2017 with machine learning services enabled. To do so, follow these steps:

1. Go to the SQL Server 2017 download link, and select the Developer version of SQL Server for downloading as shown below:

   

2. Once the download is complete, open the “downloaded” executable file. You should see the following options:

   

   Machine Learning Services is an optional feature which is not installed by default in SQL Server Management Studio. To install these services manually, click the Custom installation from the three options you see in the above screenshot.

3. A new window will appear where you have to specify the installation path.

   

   Specify the installation path and click “Install” button. The download will take some time before the installation window appears.

4. From the window that appears select “Installation” option from the left. You will see several options on the right. Select the first one which reads “New SQL Server stand-alone installation or add features to an existing installation”. This is shown in the following figure:

   

5. Select free “Developer” edition from the window that appears and click “Next” button. Accept the License Agreement and click “Next” button again. Walk through each step until you reach the “Feature Selection” window.

6. From the feature selection window select “Database Engine Services.” Under the “Database Engine Service” option, you should see “Machine Learning Services (In Database)” option, which further contains R and Python options. Select both R and Python options as shown below:

   

   Click “Next” button.

7. Give name to your SQL Server instance in the window that appears. You can also use default name and then click “Next” button.

   

8. Walk through each step until you reach the “Database Engine Configuration” option as shown below:

   

   Here you can click “Add Current User” button to add yourself as the database administrator. Click “Next” button.

9. A window will appear prompting you to give consent to install “Microsoft R Open” as shown below:

   

   Click “Accept” button and then click “Next” button.

10. Repeat Step 9 to give consent for installing Python Services.
11. “Feature Configuration Rules” window will appear. Click “Next” button.

12. Finally in the “Ready to Install” window, click “Install” button as shown below:

    

13. Depending upon your processor speed and the internet, the installation process can take some time. Once the installation is complete, you should see the following window:

    

    If you see the above window, the installation is successful.

Enabling Machine Learning Services

In the previous section, we installed the machine learning services required to run R scripts in SQL Server. However, the services are not enabled by default.

To enable the machine learning services, go to SQL Server Management Studio. If you have not already installed SQL Server Management Studio, you can download it from this link.

In the SQL Server Management Studio, open a new query window and type the following script:

The script above enables execution of any external scripts in SQL Server. If the above script executes successfully, you should see the following message.

Configuration option ‘external scripts enabled’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Before the R scripts can be executed, we need to restart the SQL Server. To do so, open the SQL Server Configuration Manager from the windows start menu. From the options on the left, select “SQL Server Services”. You will see list of all the SQL Server Instances, running on your system as shown below:

Right Click the SQL Server Instance that you installed along with machine learning services and click “Restart”.

Executing R Scripts

We have installed and enabled the services that are required to run R scripts in SQL Server. Now is the time to run our R script in SQL Server. Execute the following script:

In the first line, we call the “sp_execute_external_script” store procedure; as a parameter we pass it the “language” that the script belongs to and the actual “script”. Notice we passed N‘R’ as language. In the script we simply print a message on the screen. In the console window, you should see the following output when the above script is executed:

STDOUT message(s) from external script:
[1] “Welcome to R in SQL Server”

If the corresponding services are installed, the process for running any external script remains the same.

Executing Python Scripts

During the installation of machine learning services, we also selected Python. Let’s modify our script to see how Python can be executed inside SQL Server.

Execute the following script:

You can see, the only thing we changed here is the language and the text inside the string (which is optional). The output looks like this:

STDOUT message(s) from external script:
Welcome to Python in SQL Server

Conclusion

In this article, we saw how we can configure SQL Server in order to run R scripts along with the changes we need to make during installation for enabling machine learning services that are required to run R in SQL Server. Finally we ran a simple R script to print the text on screen. By running a Python script, we also proved that the process of running external scripts in SQL server is more or less the same for both of these languages.

Other great articles from Ben

• Author
• Recent Posts

Ben Richardson

Ben Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blog

View all posts by Ben Richardson

Latest posts by Ben Richardson (see all)

• Working with the SQL MIN function in SQL Server - May 12, 2022
• SQL percentage calculation examples in SQL Server - January 19, 2022
• Working with Power BI report themes - February 25, 2021