By: Siddharth Mehta
Overview
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.
Explanation
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.
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.
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.
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.
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.
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.
Configure the services account settings. If you do not wish to make any changes, you can proceed with the default options too.
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.
Now you are required to provide your consent to download and install Python. Click on the Accept button and proceed to the next step.
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.
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.
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.
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 print(sys.version) '
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.
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.
