SQL Named Instances in R with SQL 2016
The amount of information and training for SQL 2016 is deep and rich. I am in the process of creating training materials and was reviewing the R tutorials available on MSDN. There is a detailed user-friendly tutorial on using R in SQL 2016 titled “Data Science End-to-End Walkthrough” available here;
SQL Server R Services Tutorials
The tutorials walk you through various data science scenarios that show what is possible in SQL Server R Services. You can build custom R solutions, deploy a model to SQL Server, and then score the model using transactional or reporting data available on the server or provided through other internal or external data sources.
Named Instances and R Services
One thing that I discovered in the tutorials was the assumption that your databases are being hosted in the default SQL instance. I usually set up my instances as named instances. In lesson 2, “View and Summarize Data using R” the script “RSQL_RWalkthrough.R” has the following connection string:
connStr <- “Driver=SQL Server;Server=RSQLSVR01;Database=NYCTaxi_Sample;Uid=;Pwd=”
This example is not a named instance which would be in the form, “server\instanceName”.
The connection string for a named instance requires a double slash in between the server and instance name. The following change to the script fixed the error I was having when using the instance name “SEYSQL”:
connStr <- “Driver=SQL Server; Server=RSQLSVR01\\SEYSQL; Database=NYCTaxi_Sample;Uid=”xxxx”;Pwd=”xxxx”
The interesting error was that the slash in the server name was being taken as a control code. Using double slashes solved this issue. The same technique is used when using file locations in R such as:
sqlShareDir <- paste(“C:\\AllShare\\”, Sys.getenv(“USERNAME”), sep=””)
Key Benefit of using R in SQL 2016
The benefit of having R integrated with your TSQL code is that you can code in R and place it in stored procedures allowing the data and results to be consumed in the other products in the BI Stack. I have taken the same code and run them through both RStudio and R in SQL 2016. It will be interesting to see how this can be leveraged in real-world analysis scenarios.
Give the latest CTP preview a try, available here;