Getting Started with Machine Learning Services in SQL Server

  • Article

I would like to introduce you to some simple videos that will help you get started with Machine Learning Services in SQL Server. The videos below cover how to install and enable Machine Learning Services. After completing the instructions found here, you will be ready to execute R and Python in SQL Server. I summarized the key steps from the videos in this blog post if you want to follow along. Also check out the SQL Machine Learning Services documentation to learn more.

How to Install Machine Learning Services in SQL Server

Key steps covered in the video:

  1. Get the SQL Server 2017 installation media. In this walk through we will use the free developer edition that you can download here: https://aka.ms/SQLDownload If you already have SQL Server 2017 installed, but don't have ML Services, you can simply add that feature to your existing installation during the installation wizard.
  2. Start the installation. If you chose the free developer edition, make sure to s the custom installation so we can add Machine Learning Services to the install.
       
  3. Start the installation wizard:
  4. Complete each step in the wizard and be sure to select R and/or Python in the feature list:

You now have Machine Learning Services installed on SQL Server.

How to Enable Machine Learning Services in SQL Server

Machine Learning Services is not enabled after installation in order to provide full control to DB administrators. We need to enable it before executing any R or Python code.

  1. Open SQL Server Management Studio and connect to your instance. (If you don't have SSMS, you can get the free download here: https://aka.ms/DownloadSSMS
  2. Open a new query, paste this statement and execute:
  3. EXEC sp_configure 'external scripts enabled', 1 RECONFIGURE WITH OVERRIDE
  4. Now let's restart the SQL Server service and verify if the configuration was successful.
  5. Open SQL Server Configuration manager. (You can also use SSMS or Windows Services Manager)
  6. Right click on the instance you just configured and click restart.
  7. Make sure both the "SQL Server" and the "SQL Server Launchpad" services are both running by clicking "Refresh" under the "Action" menu.
  8. Open a new query in SSMS and add either of these hello world samples for Python or R:
  9. EXEC sp_execute_external_script  @language =N'Python', @script=N'print("Hello World")' EXEC sp_execute_external_script  @language =N'R', @script=N'print("Hello World")'
  10. If you see the Hello World printed in the output, you know configuration was successful. If you do not see Hello World print, visit the documentation for more details (https://aka.ms/sqlmldocs).
  11. Here is a simple example of passing data from SQL to Python.
  12. EXEC sp_execute_external_script  @language =N'Python', @script = N'OutputDataSet = InputDataSet + 4;', @input_data_1 =N'SELECT 1 AS Col1';
  13. Visit our documentation, or stay tuned for our next blog post where we will discuss more details about input parameters and other basics in executing R/Python in SQL Server.

Congratulations, you are now ready to use ML Services and execute R and Python code! Check out this next post that covers the basics of the syntax, parameters, and data types. We want to hear your feedback, please comment below to ask questions and tell us what you want to learn next!