Getting Started with SQL Server Machine Learning Services – Part 1

Introduction

We know humans learn from their past experiences. Meanwhile, Machines follow Instructions given by humans. But what if humans can train Machines to learn from past data? In simple terms, this is what Machine learning is !!!!. SQL Server has capabilities of Machine Learning. In this article, we will discuss the capabilities of Machine Learning in SQL Server.

Machine Learning Services in SQL Server

In 2016, Microsoft introduced Machine Learning Services in SQL Server with R. Based on the market demand, Microsoft incorporated the language called Python in 2017. Let’s have a look at how machine learning is done outside the database.

We have to extract the required data from the applications and store those data in some databases. Then those data are moved to the Analytics server in which we do Data Transformations, Model Training, and prepare the Model. Once the Model is prepared, we send that to a Separate Service in which Scoring is done. And finally, we send the New Data to the scoring service and then send our predictions back to the Applications.

Fig. 1 Machine Learning Outside the Database

SQL Server Machine Learning Services makes this process much simpler. We no need to transfer the data all over. All the Machine Learning process is done within the Database.

Fig.2 Machine Learning within SQL Server Database

How it Works

We are not running Python or R languages in SQL Server Engine. Then how the Data science languages are executed ???. To execute Python or R Language SQL Server uses Launchpad. The launchpad is able to launch satellite programs, that might be Python or R language and it is capable to get the data SQL Server without moving them outside.

Fig.3 LaunchPad in SQL Server to Execute Data Science Languages

What is Python?

First, let's understand what Python is,

• Python is an Open Source Programming Language
• Due to its flexibility, it is popular among Data Scientists.
• To gain additional functionalities additional libraries and packages can be added easily.

Using Python with SQL Server

• Data no longer needs to be exported or moved outside.
• It's more simply the ability to run scripts on our relational data.
• Python supports processing a larger amount of data.
• Data remains inside of SQL Server’s security wrapper, which maintains monitoring and auditing compliance.
• Representative samples no longer need to be taken and analyses can better process a full data set.
• Script execution benefits from performance technologies, such as in-memory table and column store indexes.

Running Python Code

We can use the same SSMS or Azure Data Studio Query editor to write and execute our Python code in SQL Server. We can also save the scripts as stored procedures for other users to run

Install Machine Learning Services for Python

The support for Python is not automatically installed with the basic SQL Server installation. But we can easily add it as part of custom configuration. My SQL Server version is 2019 and later from 2017 SQL Server, we can add the required components to our instance.

Add Machine Learning Functionality

To add Machine learning Functionality, go to Start Menu and find SQL Server Program group. Choose SQL Server Installation Center.

Fig. 4 SQL Server Installation Center

Switch over to the Installation step from the left

Fig.5 Installation

Choose the first option, “New SQL Server stand-alone installation or add features to an existing installation”.

Fig.6 Step Up Process

Once the media files are installed successfully, press the Next Button to perform some system checks.

Fig.7 System Checks

Click the Next button and it takes us to Installation Type. Two options are shown as,

• Perform a new installation of SQL Server 2019
• Add features to an existing instance of SQL Server 2019

In my case, I am choosing the second one.

Fig.8 Choose Installation Name from the dropdown

Choose the Installation Name from the Dropdown Menu. Choose that and press the Next Button. Now the installation takes us to the Feature Selection.

Fig. 9 Feature Selection

In our scenario, choose Python. Event if you wish, you can install R from the Machine Learning Services and Language Extensions option. If we scroll little bit down in the screen, we could see another option as Machine Learning Server (Standalone), make sure we leave this option unchecked. Now press the Next Button.

Accept the license terms under the Consent to install Python. In this Anaconda is installed.

Fig. 10 Consent to install Python

Click the Next button and it will lead to the Ready to Install screen. and press the install button.

Fig. 11 Ready to Install Screen

The installation will take a few minutes,

Fig. 12 Installation Progress

Everything should wrap up without any issues. And when installation is completed we should be able to see the screen as shown below,

Fig. 13 Installed Successfully

It's always good to restart the system after installing.

Conclusion

In this article, we have discussed What is Machine Learning and How it works in SQL Server. I hope this article will be base for many of us to kick start to explore Machine Learning in SQL Server. We will discuss more concepts in the upcoming article series. Please share your feedback in the comment section.