Quick Start Guide for Data Science with SQL Server and R Services


By:   |   Updated: 2020-07-06   |   Comments   |   Related: More > Install and Uninstall


Problem

The Data Scientist and Data Analyst roles are critical for organizations looking to extract insight from information assets from any kind of structured or un-structured data. The question is, if we have been working with Microsoft SQL Server for a while, what is the best place to start learning Data Science or Data Analytics?

Solution

Beside third-party solutions, the main two open-source programming languages that are widely used for Data Science and Analytics are: Python and R. I am planning to write this tutorial for beginners, and I will mainly focus on R and SQL Server R Service 2016.

Getting Started with R Services in SQL Server

In this tip we look at how to get R services up and running and run a simple script to test the installation.

Step 1: The first thing we need to do is to install SQL Server R Service. The installation process is pretty easy and, we need to start with launching the setup wizard for SQL Server 2016. I chose the 2016 version, however other versions after 2016 are just fine.

When prompted, clicked on the Installation tab, select New SQL Server stand-alone installation or add features to an existing installation.

Install R Services (In-Database)

On the Feature Selection page, select the following options:

  • Select Database Engine Services - The database engine is required in each instance that uses machine learning.
  • Select R Services (In-Database) - Installs support for in-database use of R.
install sql server r services

From this point on, accept consent to Install Microsoft R Open page, click Accept and continue with the installation wizard.

Step 2: The second step we need to do is to enable sp_execute_external_script procedure in order to execute R scripts inside SSMS (SQL Server Management Studio).

EXEC sp_configure  'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE

Step 3: The third step is to install open source R from https://www.r-project.org/. For this tutorial we are using the Windows version of R.

install sql server r services

Click on Install R for the first time.

install sql server r services

Click on Download.

install sql server r services

Double click on R-4.0.0-win.exe and start the installation of R.

install sql server r services
install sql server r services
install sql server r services
install sql server r services
install sql server r services
install sql server r services
install sql server r services
install sql server r services
install sql server r services

Step 4: The fourth step is to install RStudio as a development GUI tool. RStudio is a free tool and it can be easily downloaded from www.rstudio.com. RStudio is available for multiple operating systems, in this tutorial I will use the most recent Windows version.

install sql server r services
install sql server r services

RStudio is not the only GUI available for R development. Microsoft Visual Studio offers also an add-on for R however, RStudio is an open-source application and it is widely used. Below are the installation steps needed to install RStudio on Windows.

install sql server r services
install sql server r services
install sql server r services
install sql server r services
install sql server r services
install sql server r services

Step 4 – Download AdventureWorks database

Well, a tutorial needs some sample data to play around. I will use the AdventureWorks2014 database that can be easily downloaded from Microsoft and restore the database to SQL Server.

install sql server r services

Verification

Let’s us now run a quick verification that our environments is ready to start developing with R.

Start SSMS and execute the following command:

EXEC sp_execute_external_script
@language = N'R',
@script = N'OutputDataSet <- data.frame(seq(1,4,0.5));';  
GO
install sql server r services

Start RStudio and execute the R code as shown below.

install sql server r services
Next Steps


Last Updated: 2020-07-06


get scripts

next tip button



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Cleanly Uninstalling Stubborn SQL Server Components

SQL Server install error Wait on the Database Engine Recovery Handle Failed

Fix SQL Server Installation issue Wait on the Database Engine recovery handle failed with Single User Mode Login Option

SQL Server Installation Best Practices

Install SQL Server 2019 Standard Edition














get free sql tips
agree to terms