By: Matteo Lorini | Updated: 2020-07-06 | Comments | Related: > 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.
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
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.
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
Start RStudio and execute the R code as shown below.
Next Steps
- 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
About the author
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-07-06
