Microsoft SQL Server and R Services

Steph Locke (@SteffLocke)

2017-04-20

Steph Locke

github.com/StephLocke | itsalocke.com | T: SteffLocke

SQL Server

Overview

  • RDBMS
  • Use on physical or virtual machines

Key things to manage

  • Data
  • Code
  • Permissions
  • Licences

Next steps

R

Overview

R is an integrated suite of software facilities for data manipulation, calculation and graphical display

  • Open source
  • In-memory & single-core (by default)
  • Multi-platform
  • Extensible environment
  • Delivered by the R Foundation, supported by the R Consortium, grown by R developers
  • r-project.org

What’s it look like?

ggplot2::qplot(mpg, wt, data = mtcars, colour = cyl)

Versions

Key things to manage

  • Packages
  • RAM
  • Code

Next steps

SQL Server + R Services

Overview

  • Call R through SQL Server

What’s it look like?

EXECUTE   sp_execute_external_script
                @language = N'R'
              , @script = N'OutputDataSet <- df<-as.data.frame(installed.packages()[,1])'
              , @input_data_1 = N'SELECT 1 as Col'
 WITH RESULT SETS ((col varchar(50) not null))        

2016 Editions

  • Express - R Open or base R
  • Standard - R Open or base R
  • Enterprise - R Server

SQL Server feature comparison

Key things to manage

  • RAM & CPU
  • Licenses vs Server resources
  • Permissions
  • Code

Next steps

Deploying SQL Server & R Services

Azure is your friend

  • Automatable
  • Disposable
  • Scalable

ARM templates

  • JSON specification of resources
  • Paramaterised for reuse
  • Downloadable
  • Deploy in many different languages

PowerShell

  • Azure modules
  • Use ARM templates
  • Used in Azure Automation

Demo

Github

Next steps

Other Microsoft & R things

Azure ML

  • GUI data science
  • R & Python
  • Notebooks
  • Code deployment potential
  • Easy webservices

R in PowerBI

  • Interactive reports
  • R data source
  • R graphics

Wrapup

Wrapup