Installing and Setting up SQL Server to use Python


SQL Server 2017 is in RC2 as of the draft of this lesson. We can use CTP 2.1 or higher, to install and configure Machine Learning Server as well as Machine Learning Services (In-Database). We would need an installable setup of SQL Server 2017 which you can download from here. After you have downloaded SQL Server 2017, unzip the setup and execute the setup file.


Installing SQL Server 2017 RC2 with Machine Learning Services

The first step in the installation wizard asks for the product key. This is a little different from CTP 2.1 and other versions which start with global updates. You can select the free / evaluation edition as a starting point.

SQL Server 2017 RC2 Setup - Product Key - Description: SQL Server 2017 RC2 Setup

The next step will required you to accept the terms of the license. Click on the check-box to the accept the terms and click Next.

SQL Server 2017 RC2 Setup - License Terms - Description: SQL Server 2017 RC2 Setup

The next step will install setup files and check installation rules. You may get a warning related to your firewall if your Windows Firewall is turned on.

SQL Server 2017 RC2 Setup - Description: SQL Server 2017 RC2 Setup

Assuming that SQL Server 2017 has never been installed, select new installation as shown below. In case you do not have any other SQL Server instances installed, you will find the bottom area of the wizard blank. In my case, I already have the SQL Server 2017 CTP 2.1 installed, hence it shows the available instances.

SQL Server 2017 RC2 Setup - Installation Type - Description: SQL Server 2017 RC2 Setup

Select In-Database Machine Learning Services under the Database Engine Services and Machines Learning Server with Python under Shared Features as shown below. We need a Python server as well as extensions to communicate with this server from the database engine using T-SQL.

SQL Server 2017 RC2 Setup - Feature Selection - Description: SQL Server 2017 RC2 Setup

Provide the instance name for the SQL Server being installed. You can make it the default installation if it’s the first time you are installing SQL Server on your machine. Alternatively you can also install it as a named instance.

SQL Server 2017 RC2 Setup - Instance Configuration - Description: SQL Server 2017 RC2 Setup

Configure the services account settings. If you do not wish to make any changes, you can proceed with the default options too.

SQL Server 2017 RC2 Setup - Server Configuration - Description: SQL Server 2017 RC2 Setup

Select Windows Authentication as the authentication mode for the Database Engine. You also need to assign an administrator of the server. If you are the administrator of the server, you can just click on Add Current User, assuming that you are installing SQL Server.

SQL Server 2017 RC2 Setup - Database Engine Configuration - Description: SQL Server 2017 RC2 Setup

Now you are required to provide your consent to download and install Python. Click on the Accept button and proceed to the next step.

SQL Server 2017 RC2 Setup - Consent to install Python - Description: SQL Server 2017 RC2 Setup

Finally you are ready to install SQL Server with Machine Learning Services as well as Python. Click on Install button and this should complete the installation process.

SQL Server 2017 RC2 Setup - Ready to Install - Description: SQL Server 2017 RC2 Setup

After you complete SQL Server 2017 RC2 installation, install SQL Server Management Studio (SSMS) from the installation wizard because we are using this tool to execute T-SQL queries as well as Python scripts.

Installing Visual Studio 2017 with Data science and analytical applications features

Microsoft has been using the Visual Studio shell for development tools and environments like SSMS and SQL Server Data Tools (SSDT). For developing data science solutions, Microsoft has made provisions in Visual Studio 2017 (VS2017) by adding a category of solutions named “Data science and analytical applications”. For developing R solutions and working directly with R server, these feature can be extremely useful.

You can download Visual Studio 2017 Community Edition from here, provided you are just using it for learning purposes. After downloading the web installer when you are at the feature selection page, you can select the Python related features as shown below. This would install components in VS2017 that would provide a development environment for Python solutions.

Visual Studio 2017 Data Science and Analytical Applications - Description: Visual Studio 2017

After you have successfully installed Visual Studio 2017 with data science features, you can open Visual Studio 2017 and create a new Python command-line project from the File > New > Project menu. Once the blank Python project opens, you will see the Python development environment as shown below.

The Solution Explorer window shows the different files in the solution. You can see the Anaconda distribution of Python in the Solution Explorer. The bottom left section shows different debugging windows. You would get intellisense as you type code in the .py file. You can type just 2 lines as shown below, and you will be able to find that the code detected the Anaconda version which we installed as part of the SQL Server setup.

Python Project in Visual Studio - Description: Python Project

Our focus is on working with Python scripts from T-SQL, so we will limit our discussion of developing Python solutions using Visual Studio 2017 for now.

Explore and test execution of Python Scripts

We have completed the installation of all the tools related to Python development in SQL Server. Now it’s time to test whether we are able to execute Python scripts from T-SQL using SSMS. By default external scripts are not enabled in the database engine. You need to enable the external scripts option using a simple command as shown in this article. You may have to restart the server for the changes to take effect. After the external scripts execution is enabled, we can use sp_execute_external_script to execute R scripts as shown below. Execute the below script using SSMS.

--Python Version
execute sp_execute_external_script 
@language = N'Python',
@script = N'
import sys

It the same command that we have used in Visual Studio and the same output is being rendered here as well. We would look at the usage of this stored procedure to execute external scripts in the upcoming chapters. For now, we have successfully verified the integration of Python with the SQL Server Database Engine as well as Visual Studio 2017.

SSMS Python Testing - Description: SSMS Python Testing

Now that we have completed a successfully installation, configuration and acceptance testing of components for executing Python scripts from SQL Server, let’s learn some basic concepts of Python programming in the next lesson.

Additional Information
  • Consider exploring the Python related options and features in Visual Studio environment after you open a Python project.

Comments For This Article

Tuesday, April 24, 2018 - 4:58:56 PM - Jeff Winchell Back To Top (75774)

 Maybe the Visual Studio install does this for you (though I consider VS to be absurdly heavy for doing python or R dev), but if not, you need to add this to your tutorial: you need to enable using external scripts on the SQL Server engine you are running your R or python scripts. I wish Microsoft just did this automatically on install since it already knows you want the python/R features, but until they get wiser, you need to manually do that last step.


Friday, April 6, 2018 - 12:46:19 PM - Tushar Back To Top (75631)

 Does MS SQL Server 2017 (free) Express Edition support python?



get free sql tips
agree to terms