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





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


Article Last Updated: 2020-07-06

Comments For This Article





download














get free sql tips
agree to terms