SQL Server 2017 Machine Learning

1.啟動 SQL Server Launchpad service

2.檢查並啟用 external scripts enabled
    預設 SQL Server的external scripts enabled是停用的





執行以下語法啟用 external scripts enabled
EXEC sp_configure  'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE 

 
    如果檢查run_value不是1，則需要重新啟動SQL Server讓設定生效


若正常啟用，run_value是1

3.請確認有 Python
   執行Python Script檢查Python版本

    若出現錯誤Msg 39011，可能是SQL Server Launchpad service服務沒有啟動，

 
   或是需要重新啟動一次

     若出現錯誤，可能是找不到Python
    Msg 39021, Level 16, State 1, Line 0
    Unable to launch runtime for 'Python' script. Please check the configuration of the 'Python' runtime.
    Msg 39019, Level 16, State 2, Line 0
    An external script error occurred:
    Unable to launch the runtime. ErrorCode 0x80070490: 1168(元素找不到。).

    或是SQL Server沒有安裝Machine Learning Services(In-Database)>Python

4.Run a Hello World script
    執行用Python Script Hello World

    若用R語言Hello World

5.確認revoscalepy可供使用
下列指令碼會示範如何擷取包含在 revoscalepy 內建的範例.xdf 資料檔案。

6.查看預先安裝的Python 套件清單
Microsoft 預先安裝在SQL Server執行個體的機器學習服務 Python 套件

Revoscalepy
維基百科
revoscalepy是由Microsoft創建的Python中的機器學習包。它作為Microsoft SQL Server 2017和Machine Learning Server 9.2.0及更高版本中的機器學習服務的一部分提供。[1]
該軟件包包含用於創建線性模型，邏輯回歸，隨機森林，決策樹和提升決策樹的功能，以及用於檢查數據的一些摘要功能。[2] 其他機器學習算法，如神經網絡，是在microsoftm中提供的，這是一個單獨的軟件包，它是MicrosoftML的Python版本。[3]
revoscalepy還包含用於在不同計算環境中運行機器學習算法的函數，包括SQL Server，Apache Spark和Hadoop。[2]

Next Step:
Quickstart: Create, train, and use a Python model with stored procedures in SQL Server
https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/quickstart-python-train-score-in-tsql?view=sql-server-2017

Use Python with revoscalepy to create a model that runs remotely on SQL Server
https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/use-python-revoscalepy-to-create-model?view=sql-server-2017


Reference:
SQL Server Machine Learning tutorials in R and Python
https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/machine-learning-services-tutorials?view=sql-server-2017

Quickstart: Verify Python exists in SQL Server
https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/quickstart-python-verify?view=sql-server-2017

revoscalepy (Python module in SQL Server)
https://docs.microsoft.com/zh-tw/sql/advanced-analytics/python/ref-py-revoscalepy?view=sql-server-2017

Set up python development environment 設定Python開發環境

Set up python development environment 設定Python開發環境

1.安裝Visual Studio或Visual Studio Core

https://code.visualstudio.com.

or

https://visualstudio.microsoft.com/

2.安裝Python

install Python from the Microsoft Store

3.確認Python安裝成功

4.啟動Visual Studio Core，安裝Python extension

5.選取 [Python 解譯器]
opening the Command Palette (Ctrl+Shift+P), start typing the command Python: Select Interpreter to search, then select the command

6.第一次啟動Python: Select Interpreter需要一些時間偵測此台主機已安裝的Python環境

7.選擇Python環境

https://code.visualstudio.com/docs/python/environments

8.在VS Code 終端機執行Python程式
   啟動終端機方法1，Terminal > New Terminal

    啟動終端機方法2，View > Terminal

9.若出現無法辨識conda命令

   輸入python若出現無法辨識python命令

    解決方法: 設定環境變數，在Path變數加入以下兩個路徑
    C:\Program Files (x86)\Microsoft Visual Studio\Shared\Anaconda3_64\Scripts
    C:\Program Files (x86)\Microsoft Visual Studio\Shared\Anaconda3_64

 
    正常啟動終端機的畫面如下

10.執行python，進入Python環境
     輸入印出Hello world的Python程式碼 print("Hello World")

11.測試執行python script檔
   新增一個py檔，並輸入以下Python script
   msg = "Hello World"
   print(msg)
   儲存後，按右鍵選擇[ Run Python File in Terminal]

 
    執行結果如下

SQL Server Machine Learning - Python client:

1.下載PowerShell script安裝Python for SQL Server Machine Learning Service/Server
https://aka.ms/mls93-py
or
https://aka.ms/mls-py

The script installs Anaconda 4.2.0, which includes Python 3.5.2, along with all packages listed previously.
Microsoft Python packages common to both client and server systems include the following:

• revoscalepy
• microsoftml
• azureml-model-management-sdk
• pre-trained models

This article describes how to install a Python interpreter (Anaconda) and Microsoft's Python packages locally on a client machine. Once installed, you can use all of the Python modules in Anaconda, Microsoft's packages, and any third-party packages that are Python 3.5 compliant. For remote compute context, you can only call the Python functions from packages in the above list.

2.若沒有指定安裝路徑，預設安裝在 C:\ProgramFiles\Microsoft\PyForMLS

3.設定環境Path變數
C:\Program Files\Microsoft\PyForMLS

4.IDE開發工具才能偵測到安裝號的Python環境
    Visual Studio Code

    Visual Studio須自訂這個新的Python環境

Reference:
 Get started using Python on Windows for beginners
 https://docs.microsoft.com/en-us/windows/python/get-started/python-for-education
 Getting Started with Python in VS Code

 https://code.visualstudio.com/docs/python/python-tutorial
 How to install Python client libraries for remote access to a Machine Learning Server
 https://docs.microsoft.com/en-us/machine-learning-server/install/python-libraries-interpreter

Learn Python from Microsoft

Get started using Python on Windows for beginners
https://docs.microsoft.com/en-us/windows/python/get-started/python-for-education

Introduction to Python
https://docs.microsoft.com/en-us/learn/modules/intro-to-python/

Visual Studio | Python
https://docs.microsoft.com/en-us/visualstudio/python/?view=vs-2019



基本上，一個Module就是一個py檔
將多個py檔包在一個目錄下，且包含一個__init__.py檔，就是一個Package

MyPackage1/
├── __init__.py
├── module1.py
└── module1_import.py

Extending Windows basic volume 延伸磁碟空間

Extending Windows basic volume 延伸磁碟空間

1.我們可以直接extend non-boot or non-system volume在連續的空間
2.若要extend到非連續的空間，就必須先轉換成動態磁碟
3.若要extend boot or system volumes ，也必須先轉換成動態磁碟

實作上Windows 2012 R2預設安裝有獨立一個隱藏系統保留磁碟區(System volume)，而C磁碟是啟動磁碟區(boot volume)，此時C磁碟機是可以延伸(Extend)

假設是VMware Virtual Machine，透過VMware vSphere Client調整磁碟空間

修改磁碟空間，將Provisioned Size從40GB改成80GB

等待下方工作狀態顯示完成，表示虛擬磁碟機擴充成功

遠端桌面到Windows 2012 R2，啟動磁碟管理(Disk Management)，若虛擬磁碟機擴充的磁碟空間沒有出現，請執行重新掃描磁碟(Rescan Disks)

虛擬磁碟機擴充的磁碟空間顯示為未配置

在C磁碟機按右鍵>延伸磁碟區(Extend Volume)
Right-click on Volume C and click Extend Volume

下一步

延伸磁碟精靈會自動選取磁碟機C

完成

完成磁碟空間延展

Reference:
Extend a basic volume
https://docs.microsoft.com/en-us/windows-server/storage/disk-management/extend-a-basic-volume
Increasing the size of a virtual disk (1004047)
https://kb.vmware.com/s/article/1004047
Increasing the size of a disk partition (1004071)
https://kb.vmware.com/s/article/1004071

SQL Server Integration Services學習資源

SQL Server Integration Services學習資源

SSIS How to Create an ETL Package
https://learn.microsoft.com/en-us/sql/integration-services/ssis-how-to-create-an-etl-package?view=sql-server-2017

SQL Server Integration Services Tutorial Videos
https://channel9.msdn.com/Shows/SQL-Server-Integration-Services-Tutorial-Videos?page=2
https://channel9.msdn.com/Shows/SQL-Server-Integration-Services-Tutorial-Videos
Last episode: Aug 11, 2015 at 10:04AM
Tutorial Videos of SQL Server Integration Services (SSIS).
Videos Created based on SSIS 2012, but it will apply on SSIS 2014.

Data Source Driver

MySQL

MySQL Connector/ODBC
https://dev.mysql.com/downloads/connector/odbc/
MySQL Connector/NET
https://dev.mysql.com/downloads/connector/net/

Oracle

Oracle Database Software Downloads
https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
Oracle Database 19c Client (19.3) for Microsoft Windows x64 (64-bit)
Oracle Database 19c Client (19.3) for Microsoft Windows (32-bit)

Visual Studio Support for .NET Development

Visual Studio Support for .NET Development

Visual Studio 2019 Support for .NET Development

• .NET Framework versions 4.7.2, 4.7.1, 4.7, 4.6.2, 4.6.1, 4.6, 4.5.2, and 3.5
• .NET Core 3.0 Preview, 2.2, 2.1, and 1.1.
• .NET Native
• Mono

Visual Studio 2017 Support for .NET Development

• .NET Framework versions 4.7, 4.6.2, 4.6.1, 4.6, 4.5.2, and 3.5
• .NET Core 2.0, 1.1, and 1.0.
• .NET Native
• Mono

Visual Studio 2015 Compatibility with Previous Releases

NET 4.6 is a highly compatible in-place update of .NET 4, .NET 4.5, .NET 4.5.1, and .NET 4.5.2. See Application Compatibility in the .NET Framework for more information on .NET compatibility.


Visual Studio 2019 Platform Targeting and Compatibility
https://docs.microsoft.com/en-us/visualstudio/releases/2019/compatibility
Visual Studio 2017 Platform Targeting and Compatibility
https://docs.microsoft.com/en-us/visualstudio/productinfo/vs2017-compatibility-vs
Visual Studio 2015 Platform Targeting and Compatibility
https://docs.microsoft.com/en-us/visualstudio/productinfo/vs2015-compatibility-vs


Visual Studio 2017安裝.NET Framework 4.7 Developer Pack，新增專案時架構才能選到 .Net Framework 4.7

Microsoft .NET Framework 4.7 Developer Pack and Language Packs for Windows 7 SP1, Windows 8.1, Windows 10 Anniversary Update, Windows Server 2008 R2 SP1, Windows Server 2012, Windows Server 2012 R2 and Windows Server 2016
https://www.microsoft.com/en-US/download/details.aspx?id=55168

Microsoft .NET Framework 4.7.1 Developer Pack and Language Packs for Windows 7 SP1, Windows 8.1, Windows 10 Anniversary Update, Windows 10 Creators Update, Windows Server 2008 R2 SP1, Windows Server 2012, Windows Server 2012 R2 and Windows Server 2016
https://www.microsoft.com/en-us/download/details.aspx?id=56119

Download .NET Framework 4.7.2
https://dotnet.microsoft.com/download/dotnet-framework/net472