Microsoft now offers SQL Server R Services. By integrating the R language with SQL server, users can run advanced data analytics with data stored in SQL. In many ways, the relational database is a natural fit for R, which has long been used by data scientists for advanced statistical analyses. This three-part series will explain why R SQL is advantageous and how to get started implementing it. In this part, we’ll discuss the advantages, basic operations, and potential problems of R SQL.

Why use R SQL?

The short answer is that R is an open source language that can be utilized for advanced statistical analysis, machine learning and data science. Unlike other languages you might use with SQL databases (such as Python), R is specifically designed for statistical analyses.

Businesses are increasingly turning to R for statistical analyses. There is a vibrant R community that provides more than 8000 pre-built solutions that can be deployed and adapted for a number of different purposes. R is already familiar to data analysis professionals, while DBAs are familiar with SQL. R SQL provides a system that can meet both stakeholders’ needs.

How does R SQL work?

When you implement SQL Server R Services, you are installing both the Open Source R Package and Microsoft R Package. The latter offers R capacity specific to the Microsoft SQL Server. The SQL Server R Services supports the installation of R packages not available from OpenSource R and Microsoft R. With the SQL Extensibility Architecture, you can even run non-SQL code within the same environment.

SQL Server R Services installs a program called the Launchpad. This is essential for using R with your SQL databases. Launchpad functions as a separate Service SID and has lower permissions than the main SQL Server R Services, offering additional security for your data as you run operations.

Here’s how it works: Users perform a query in SQL Server. The object then travels to Launchpad Service, and then to one or more Local User Accounts. These satellite operations can communicate back to SQL and retrieve data. The analysis is therefore performed outside of SQL Server itself. It is also possible in some instances to use T-SQL to perform R queries.

To successfully perform queries, you must configure your server properly in Launchpad and use a machine that is either connected to the Internet or capable of offline downloading.

What are the potential problems?

Although R SQL can offer the capacity to perform advanced data analysis, there are potential challenges. Most SQL administrators are used to running operations within seconds, but R analyses can take hours or days to perform. R consumes a lot of data in many cases. The R language can be complex to learn, and users must take steps to ensure that data is clean while performing analysis.

Adopting best practices with R SQL helps manage these challenges. In Part 2 of this series, we’ll talk about how R SQL offers superior security and scalability. Contact Ntirety for more information.