Quick Start Guide for Data Science with SQL Server and R Services
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?
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.
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.
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.
Click on Install R for the first time.
Click on Download.
Double click on R-4.0.0-win.exe and start the installation of R.
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.
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.
Step 4 – Download AdventureWorks database
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
Start RStudio and execute the R code as shown below.
- In the future parts of this tutorial, we will focus on R language and we will see how to execute code from both RStudio and SSMS.
- There is a version of RStudio available for free in the cloud. Users that do not wish to install the client on their computer can use the free cloud version at: https://rstudio.cloud/
- The reader will need to install RStudio in order to test this tip.
- Check out these related tips
Last Updated: 2020-07-06
About the author
View all my tips