Step by Step Installation of SQL Server 2019 ML Services in Windows Local Machine

9. August 2020 Mehedi Machine Learning , SQL Server Comments (0)

Introduction

Hello, this article is going to cover step by step installation of SQL Server 2019 ML Services and running a Python script on windows local machine.

What is ML Services in SQL Server

ML services is a feature of SQL Server which enables to run Python or R scripts on relational data for predictive analytics and machine learning.

History

Machine learning or ML services were first introduced in SQL Server 2016 version and it was called R Services. From SQL Server 2017, it was renamed to Machine Learning Services and Python was included. In SQL Server 2019, some additional features are added.

Why ML Services in SQL Server

Actually, it is now business need to analyze and gain knowledge from data more inherently. There are lots of powerful ML services which use databases as their major data sources. Still Microsoft included ML services in SQL Server as the R and Python scripts are executed in database without moving data outside SQL Server or over the network. Thus it gives edge computing of ML services on relational data.

Pre-installation Checklist

As per SQL Server 2019 doc, below are the major checklist for pre-installation:

  • A database engine instance is required

  • In case of, Always On Availability Groups, each node needs installation, configuration of ML services, and configure packages

  • Don't install ML services on a domain controller. The Machine Learning Services portion of setup will fail.

  • Don't install Shared Features > Machine Learning Server (Standalone) on the same computer running a database instance. A stand-alone server will compete for the same resources, diminishes the performance of both installations.

  • Side-by-side installation with other versions of Python and R is supported but isn't recommended. It's supported because the SQL Server instance uses its own copies of the open-source R and Anaconda distributions. It isn't recommended because running code that uses Python and R on a SQL Server computer outside SQL Server can lead to various problems:

    • Using a different library and executable files will create inconsistent results, than what you are running in SQL Server.
    • R and Python scripts running in external libraries can't be managed by SQL Server, leading to resource contention.

Installation

First get your preferred media of SQL Server Enterprise/Standard/Express/Developer edition.

  1. Run the setup as administration on local machine
  2. Start the setup wizard
  3. From Installation tab, select New SQL Server stand-alone installation or add features to an existing installation
  4. From Feature Selection tab, choose Database Engine Services, R and Python as per below image. DON'T shared features

5. For the remaining part, give your consent for R and Python.

6. Enable external script by running below query in SSMS:

EXEC sp_configure 'external scripts enabled', 1 

RECONFIGURE WITH OVERRIDE


Pic:- Before enabling the external script permission


Pic:- After enabling the external script permission

7. Now restart the database engine

Running Python Script

Run below script in SSMS which actually a python script and output is "Hello Mehedi"

EXEC sp_execute_external_script  @language =N'Python',
@script=N'
OutputDataSet = InputDataSet;',
@input_data_1 =N'SELECT ''Hello Mehedi'' AS Test'
WITH RESULT SETS (([Print] varchar(20) not null));
GO


If you see the above output then you have successfully complete the installation process. That is all about the installation.

Tags:

Add comment