Advanced Analytics is becoming a norm now. Every organization wants to use the power of advanced analytics to predict the future outcome and take proactive corrective measures to remain competitive and win over its competitors. Some of the scenarios where predictive analysis can help to find future outcomes are:
Some of the examples in different industries where predictive analysis is playing or can play crucial roles are:
It’s now becoming necessary for every organization to exploit the data that they have (or in some cases external data) and predict future outcomes, to remain competitive by offering their customers what they want, when they want and where they want. Looking at this potential, SQL Server 2016 brings native support doing advance analytics in the database itself using R Services.
In this article we will learn what R is, what on-premise advance analytics options from Microsoft are and how to get started using R Services with SQL Server 2016.
R programming language is an open source, popular and powerful statistical programming language optimized for Statistical Analysis, Machine Learning and Data Science. It has an ever growing, vibrant community of developers and data scientists across businesses, academics, research organization, etc. This community has contributed more than 8000 free pre-built solutions or packages (algorithms, test data and evaluations) to CRAN (Comprehensive R Archive Network), which can be reused and leveraged across your projects easily and quickly. It also includes packages for creating varieties of data visualization for better data exploration.
R Programming Language
R programming language is one of the fastest growing languages and now ranks at 6 in the IEEE Spectrum survey result published in July 2015.
No matter what platform you are targeting, Microsoft has Advance Analytics offerings for both on-premise as well as cloud platforms. In this article I will be talking about advance analytics offerings available on-premise (though there is some overlap; for example, you can use Microsoft R Server on your on-premise machine as well as in the cloud). To learn about Cortana Intelligence Suite, advance analytics offerings in the cloud from Microsoft, you can refer here.
Figure 1 – Microsoft Advance Analytics Offerings
Microsoft has broadly three advance analytics offerings for on-premises:
Microsoft R Server and SQL Server R Services are both enterprise ready and have been designed to scale and perform, although SQL Server R Services has these additional benefits:
SQL Server 2016 installation wizard has been enhanced to let you install R Services on your SQL Server instance. On the Feature Selection page of the wizard, you need to select R Services (In-Database), as shown in the figure below, which includes and installs Advance Analytics extension (that supports executing external scripts and processes) for integration of standard T-SQL statement with R language.
SQL Server 2016 Setup
In case you want to automate the process of installing R Services on a SQL Server instance, you can use execute this command from the SQL Server installation media folder:
You need to install the database engine on each instance where you will use R Services (In-Database) in SQL Server 2016 and that’s the reason I have used both, SQL and AdvanceAnalytics features to be installed in the above command.
If you are doing offline installation, you need to follow the instructions mentioned here.
So far, we have installed the components required for running R Services; now to enable the R Services feature itself you need to explicitly enable it with the following command before you can invoke R scripts in SQL Server.
Verify R Services
Finally, you need to restart SQL Server service, which will automatically restart the related SQL Server Trusted Launchpad service (it runs advanced analytics extensions to enable integration with Microsoft R Open using standard T-SQL statements; disabling this service will make the Advanced Analytics features of SQL Server unavailable) as well for executing external R scripts.
SQL Server Configuration Manager
At this time, if you execute the following command again, you will notice 1 for both config_value and run_value, which means you are not good to execute R script on this SQL Server instance:
Verify R Services
SQL Server 2016 includes a new system stored procedure (sp_execute_external_script) to enable you to execute external scripts inside the database engine. The stored procedure loads and executes a script written in a supported language from an external location. In my subsequent article, I will discuss this stored procedure in detail, though here is an example of calling this stored procedure to execute R script, which fetches a sample Irish dataset and returns to SQL Server.
Results
SQL Server 2016 brings native support to doing advanced analytics in the database itself using R Services. In this article I talked about what R is, what the on-premise advance analytics options from Microsoft are, and how to get started using R Services with SQL Server 2016. In next article we will look into executing R scripts from SQL Server Management Studio and other R client tools.
Set up SQL Server R Services
Data Science End-to-End Walkthrough
Getting Started with SQL Server R Services
See all articles by Arshad Ali
Subscribe to Cloud Insider for top news, trends & analysis
DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases–foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.
Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.
Property of TechnologyAdvice.
© 2022 TechnologyAdvice. All Rights Reserved
Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.