SQL Server 2016 CTP3 整合R語言安裝配置手冊

標籤：sql 2016   r語言   r script   

1. 適用範圍

SQL Server 2016 CTP3基於 Windows Server 2016 TP3進行單機R整合部署指引手冊

非域環境，R非群集

2. 安裝準備2.1 硬體準備

MSDN建議最低1GB記憶體，但我們考慮到R需要大量記憶體進行計算故建議至少4GB

2.2 軟體準備

Revolution R Open 3.2.2 for Revolution R Enterprise 7.5.0

http://go.microsoft.com/fwlink/?LinkId=626650

Revolution R Enterprise 7.5

http://go.microsoft.com/fwlink/?LinkId=626652

SQL Server 2016 CTP3

http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/

3. 開始安裝配置3.1 Revolution R Open安裝

650) this.width=650;" height="408" title="clip_image002" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image002" src="http://s3.51cto.com/wyfs02/M02/75/53/wKiom1Y2xNahV_1TAAGj7VxH1pE658.jpg" border="0" />

安裝完成後可直接連接RRO進行分析工作，但僅為傳統R分析設計器

650) this.width=650;" height="505" title="clip_image004" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image004" src="http://s3.51cto.com/wyfs02/M00/75/53/wKiom1Y2xNihTHF6AALLd9xLGWo165.jpg" border="0" />

3.2 Revolution R Enterprise Node SQL安裝（2選1）

只是計算節點，通過SQL 2016調用，無VS2008設計介面

通過嚮導安裝必要的組件

650) this.width=650;" height="477" title="clip_image005" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image005" src="http://s3.51cto.com/wyfs02/M00/75/51/wKioL1Y2xRXyXVG6AAIwEH_CFVc660.jpg" border="0" />

安裝完成所有必要組件後開始RRE Node安裝

650) this.width=650;" height="409" title="clip_image007" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image007" src="http://s3.51cto.com/wyfs02/M02/75/53/wKiom1Y2xNywCRIfAAF1kySNKn4978.jpg" border="0" />

650) this.width=650;" height="488" title="clip_image008" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image008" src="http://s3.51cto.com/wyfs02/M02/75/53/wKiom1Y2xN6RfLqRAAJFMY9MGaw639.jpg" border="0" />

650) this.width=650;" height="483" title="clip_image009" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image009" src="http://s3.51cto.com/wyfs02/M00/75/53/wKiom1Y2xOHSUvUoAAFdvp_GuKY907.jpg" border="0" />

650) this.width=650;" height="564" title="clip_image010" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image010" src="http://s3.51cto.com/wyfs02/M01/75/53/wKiom1Y2xOSjYVEhAAIDGiIyJLs103.jpg" border="0" />

3.3 Revolution R Enterprise安裝（2選1）

帶VS2008設計介面，在伺服器上能夠同時使用SQL 2016以及VS2008編輯R指令碼

650) this.width=650;" height="524" title="clip_image012" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image012" src="http://s3.51cto.com/wyfs02/M01/75/51/wKioL1Y2xSGjjbyvAAICJuZPocI230.jpg" border="0" />

650) this.width=650;" height="591" title="clip_image013" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image013" src="http://s3.51cto.com/wyfs02/M02/75/51/wKioL1Y2xSTzm7T0AANY_M-msoE241.jpg" border="0" />

650) this.width=650;" height="601" title="clip_image014" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image014" src="http://s3.51cto.com/wyfs02/M01/75/53/wKiom1Y2xOuiC2dxAALZm8PtgQ0529.jpg" border="0" />

報錯資訊說沒有找到MSVCR120.dll，這個dll檔案位於Visual C++ Redistributable Packages for Visual Studio 2013，我們可以通過兩種方式解決：

l 下載並安裝Visual C++ Redistributable Packages for Visual Studio 2013 X86與X64安裝包先行安裝

l 直接安裝基於VS2013的SSDT(可選SQL 2014的SSDT安裝)

650) this.width=650;" height="515" title="clip_image016" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image016" src="http://s3.51cto.com/wyfs02/M02/75/53/wKiom1Y2xO6gGzajAAKkVXWi7pg623.jpg" border="0" />

650) this.width=650;" height="537" title="clip_image018" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image018" src="http://s3.51cto.com/wyfs02/M00/75/51/wKioL1Y2xSyx3g68AAJaolOsQV8766.jpg" border="0" />

650) this.width=650;" height="558" title="clip_image020" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image020" src="http://s3.51cto.com/wyfs02/M02/75/51/wKioL1Y2xS2DJvZbAAFxfqf0QHQ128.jpg" border="0" />

安裝完成SSDT後再運行安裝RRE 7.5.0即可通過組件安裝驗證，並自動安裝VS2008 shell，這是整合的傳統RRE開發介面

650) this.width=650;" height="538" title="clip_image021" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image021" src="http://s3.51cto.com/wyfs02/M01/75/53/wKiom1Y2xPTAf6UlAAKEj2kAI4I952.jpg" border="0" />

650) this.width=650;" height="533" title="clip_image022" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image022" src="http://s3.51cto.com/wyfs02/M00/75/51/wKioL1Y2xTHDEBMAAAGMGHe-B9k353.jpg" border="0" />

650) this.width=650;" height="537" title="clip_image023" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image023" src="http://s3.51cto.com/wyfs02/M00/75/53/wKiom1Y2xPzjSx3JAAH23lgDgmc920.jpg" border="0" />

650) this.width=650;" height="540" title="clip_image024" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image024" src="http://s3.51cto.com/wyfs02/M01/75/53/wKiom1Y2xQGjjmJeAAGM9F1xD20356.jpg" border="0" />

650) this.width=650;" height="553" title="clip_image025" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image025" src="http://s3.51cto.com/wyfs02/M01/75/51/wKioL1Y2xT-RKjaYAAJkp5bNqA4903.jpg" border="0" />

3.4 開始安裝SQL Server 2016

650) this.width=650;" height="571" title="clip_image027" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image027" src="http://s3.51cto.com/wyfs02/M00/75/53/wKiom1Y2xQXSwaq9AAL0n4zUxs8194.jpg" border="0" />

650) this.width=650;" height="563" title="clip_image029" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image029" src="http://s3.51cto.com/wyfs02/M02/75/53/wKiom1Y2xQeRdY2sAAIQML9JMMk561.jpg" border="0" />

題外話：注意到之前的PowerPivot for Sharepoint不見了，這是因為挪了地兒，在安裝Analysis Service的時候你會發現現在有3個選項了

650) this.width=650;" height="597" title="clip_image031" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image031" src="http://s3.51cto.com/wyfs02/M00/75/53/wKiom1Y2xQrDmROYAANCG90qzG8626.jpg" border="0" />

650) this.width=650;" height="622" title="clip_image033" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image033" src="http://s3.51cto.com/wyfs02/M00/75/53/wKiom1Y2xQyC2Te5AANaw5YfDXI903.jpg" border="0" />

看到這個警告其實你並沒有做錯什麼，而是必須在安裝後進行配置啟用AAE才能調度外部R指令碼，這個跟以前通過配置才能使用sp_sqlcmd其實是一個道理

650) this.width=650;" height="563" title="clip_image035" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image035" src="http://s3.51cto.com/wyfs02/M01/75/51/wKioL1Y2xUrwSWPxAAJXAZXMIIg167.jpg" border="0" />

命名或預設執行個體均可，後續配置外部連結參數會有細微不同，不用在意這個細節

650) this.width=650;" height="613" title="clip_image037" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image037" src="http://s3.51cto.com/wyfs02/M02/75/51/wKioL1Y2xVDDRBTKAAOzjnEGOno059.jpg" border="0" />

注意兩個地方：

l SQL Server Launchpad這個服務其實就是Advanced Analytics Service跟MSDN上不一致，這個應該是個小Bug，不用在意

l 下邊這個Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service建議勾上，主要用於在建立大資料檔案時不用格式檔案卷從而提升初始化速度。例如：在資料庫中建立16GB大的資料檔案以前需要後台格式化大約10秒鐘，現在幾乎0秒完成，但注意這個參數對ldf記錄檔無效，所以記錄檔還得初始化一個大點的值並盡量防止其自動擴充

P.S. 應該建議再將鎖定記憶體頁也一併加入進去，免得安裝完成後再改

650) this.width=650;" height="563" title="clip_image039" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image039" src="http://s3.51cto.com/wyfs02/M00/75/51/wKioL1Y2xVLw2G_FAAKv7yy95RY400.jpg" border="0" />

650) this.width=650;" height="572" title="clip_image041" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image041" src="http://s3.51cto.com/wyfs02/M01/75/51/wKioL1Y2xVrzF3MzAAKPjLkwbq8885.jpg" border="0" />

多了一個TempDB初始配置卡，其實做過調優的DBA以前都是自己用T-SQL配置的，這裡給出來只是一個輔助手段

650) this.width=650;" height="547" title="clip_image043" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image043" src="http://s3.51cto.com/wyfs02/M00/75/51/wKioL1Y2xVzxCIALAAItNiw2Q0c306.jpg" border="0" />

傳說報表格服務有重大改進，稍後體驗一下，跟本文主題沒啥關係

650) this.width=650;" height="557" title="clip_image045" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image045" src="http://s3.51cto.com/wyfs02/M02/75/51/wKioL1Y2xV6BstcMAAIEmOhNdPA004.jpg" border="0" />

稍後測試的時候玩一下的，跟本文沒啥關係

650) this.width=650;" height="546" title="clip_image047" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image047" src="http://s3.51cto.com/wyfs02/M01/75/51/wKioL1Y2xWDAACogAAJDUGZmCik570.jpg" border="0" />

Polybase是我們下一個主題，稍後討論，先安裝吧

650) this.width=650;" height="522" title="clip_image049" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image049" src="http://s3.51cto.com/wyfs02/M00/75/51/wKioL1Y2xWKzf6W5AAJDfv-Z2nc525.jpg" border="0" />

開始安裝吧

650) this.width=650;" height="527" title="clip_image051" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image051" src="http://s3.51cto.com/wyfs02/M00/75/54/wKiom1Y2xTiSt5aKAAGFVu7cueo802.jpg" border="0" />

650) this.width=650;" height="524" title="clip_image053" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image053" src="http://s3.51cto.com/wyfs02/M01/75/54/wKiom1Y2xTrijEY6AAIrWNQT8sw787.jpg" border="0" />

3.5 配置R功能

650) this.width=650;" height="176" title="clip_image054" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image054" src="http://s3.51cto.com/wyfs02/M00/75/54/wKiom1Y2xTuS2sl4AACL5oQqfK8789.jpg" border="0" />

650) this.width=650;" height="609" title="clip_image056" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image056" src="http://s3.51cto.com/wyfs02/M01/75/54/wKiom1Y2xT6yOgNPAAQ8Xl-_SUM418.jpg" border="0" />

4. 驗證安裝

RRE 運行成功

650) this.width=650;" height="438" title="clip_image058" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image058" src="http://s3.51cto.com/wyfs02/M02/75/54/wKiom1Y2xT_Ry50EAAIdtNDLGH0586.jpg" border="0" />

650) this.width=650;" height="505" title="clip_image060" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image060" src="http://s3.51cto.com/wyfs02/M00/75/54/wKiom1Y2xUPj5HOPAAK-i5jhSl0527.jpg" border="0" />

對於這麼醜的設計器也無力吐槽了，希望RTM的時候能變得好看點吧，如果單純編寫R指令碼個人還是推薦Rstudio，漂亮，適用，查函數使用方法都很方便

650) this.width=650;" height="489" title="clip_image062" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image062" src="http://s3.51cto.com/wyfs02/M01/75/54/wKiom1Y2xUjDvWlDAAIc9CBh6Sw087.jpg" border="0" />

T-SQL Call R

650) this.width=650;" height="611" title="clip_image063" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image063" src="http://s3.51cto.com/wyfs02/M02/75/54/wKiom1Y2xUrRH1QyAAJCHHWgkls464.jpg" border="0" />

650) this.width=650;" height="600" title="clip_image064" style="border:0px;padding-top:0px;padding-right:0px;padding-left:0px;background-image:none;" alt="clip_image064" src="http://s3.51cto.com/wyfs02/M00/75/51/wKioL1Y2xYngfHV8AAK_0F2S4eU595.jpg" border="0" />

5. 參考資料

Installing SQL Server R Services

https://msdn.microsoft.com/library/mt604883(SQL.130).aspx

Microsoft MPI

https://msdn.microsoft.com/en-us/library/bb524831(v=vs.85).aspx

Hardware and Software Requirements for Installing SQL Server 2016

https://msdn.microsoft.com/en-us/library/ms143506.aspx#pmosr

Install R Packages and Providers for SQL Server R Services

https://msdn.microsoft.com/en-us/library/mt590809.aspx

Install Advanced Analytics Extensions

https://msdn.microsoft.com/en-us/library/mt590808.aspx

Post-Installation Server Configuration (SQL Server R Services)

https://msdn.microsoft.com/en-us/library/mt590536.aspx

Known Issues for SQL Server R Services

https://msdn.microsoft.com/en-US/library/mt590540.aspx

Visual C++ Redistributable Packages for Visual Studio 2013

http://www.microsoft.com/en-US/download/details.aspx?id=40784

Download Latest SQL Server Data Tools

https://msdn.microsoft.com/en-us/library/mt204009.aspx

Revolution R Enterprise 7.5.0 (RRE-7.5.0)

https://www.microsoft.com/en-us/download/details.aspx?id=49505

Revolution-R-Enterprise-Node-SQL-7.5.0-Windows.exe. On a server running SQL Server 2016, install this to create an R server with SQL Server support. You can run R packages on the server, but the server can also accept requests from remote clients.

Revolution-R-Enterprise-SQL-7.5.0-Windows.exe. Same as above, but adds an R IDE. Install this on a server if you want your development tools and server on the same computer. Install this on a client workstation to create a complete R development environment integrated with SQL Server 2016 .

Revolution R Open 3.2.2 for Revolution R Enterprise 7.5.0

https://www.microsoft.com/en-us/download/details.aspx?id=49525

Configure and Manage Advanced Analytics Extensions

https://msdn.microsoft.com/en-us/library/mt590869.aspx?f=255&MSPPError=-2147217396

Security Considerations for the R Runtime in SQL Server

https://msdn.microsoft.com/en-US/library/mt590870.aspx

SQL Server R Services Tutorials

https://msdn.microsoft.com/en-US/library/mt591993.aspx

本文出自 “Fast” 部落格，請務必保留此出處http://greece760.blog.51cto.com/660798/1708710

SQL Server 2016 CTP3 整合R語言安裝配置手冊