SQL Named Instances in R with Microsoft SQL Server

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 Server titled “Data Science End-to-End Walkthrough” available R tutorial: Develop model in SQL – SQL Server Machine Learning Services | Microsoft Docs;

SQL Server R Services Tutorials

The tutorials walk you through various data science scenarios that show possible 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 Developer version; SQL Server Downloads | Microsoft and give it a try.


Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.