在Microsoft Azure Virtual Machines中使用SSD來存放TempDB和啟用緩衝集區擴充

2014-09-29

1812
0
SQL Server

說明

日前Microsoft Azure推出D系列的Virtual Machines（以下簡稱Azure VM）讓較要求CPU、磁碟效能以及記憶體的應用程式，有更適合的Azure VM層次可以使用，D系列尤其強調磁碟效能，全系列採SSD來存放虛擬硬碟（VHD），但雲端服務的計費模式採用多少算多少，若您用到D系列的Azure VM可以享受更高的效能，當然也必須支付更高的費用，詳細價目資訊請見下表以及參考資料一節。

除了應用程式外，SQL Server的TempDB也非常適合放在SSD中運行來提升效能，若您的SQL Server需要使用到大量的暫存資料表、資料表變數及維護線上索引作業，當您把SQL Server部署在Azure VM中，就非常適合將TempDB做適當的設定，進而善用SSD儲存體的優點。再者，若您使用的是SQL Server 2014，那麼不能錯過使用緩衝集區擴充（Buffer Pool Extensions）這個新功能，透過這個功能將SSD儲存體做為記憶體的延伸，進而提升SQL Server的I/O輸送量（throughput），有關緩衝集區擴充的介紹，請見參考資料一節。

眾所皆知，Azure VM的D磁碟是用來存放暫存資料，不保證存放在D磁碟的資料的可用性，官方也強調重要資料以及需要保存的資料，應避免放在D磁碟，而SQL Server TempDB存放的是暫存資料及物件，會在每次SQL Server重新啟動時自動重建，因此不需擔心資料遺失的問題；另緩衝集區擴充只存放乾淨的頁面（Clean Pages），有被異動過的資料不會存放在緩衝集區擴充之中，因此也不用擔心資料遺失的問題。

SSD 緩衝集區延伸模組架構

因此，當您使用D系列Azure VM，可以利用下列步驟來將TempDB和緩衝集區擴充放在VM的D磁碟，透過SSD儲存體所帶來的高I/O輸送量（throughput）來提升SQL Server的效能，詳細步驟請見下一節。

建立TempDB和緩衝集區擴充資料夾

請在D磁碟中建立存放TempDB資料檔和交易記錄檔以及緩衝集區擴充檔案所需的資料夾，或是您也可以建立一個資料夾就好，同時存放TempDB和緩衝集區擴充檔案。

本文示範將TempDB放在D:\TempDB資料夾，緩衝集區擴充的檔案放在D:\BPE資料夾（如下圖）。

修改TempDB的存放位置

為了讓TempDB的資料庫檔案和交易記錄檔由預設安裝路徑改放到D:\TempDB，必須執行下列的T-SQL指令碼來搬移TempDB。


GO

ALTER DATABASE tempdb MODIFY FILE (NAME= tempdev, FILENAME= 'D:\TempDB\tempdb.mdf') 
GO

ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'D:\TempDB\templog.ldf') 
GO

啟用緩衝集區擴充

一般建議實體記憶體大小（max_server_memory）與緩衝集區擴充大小比率為 1：16 以下，建議 1：4 到 1：8 的範圍內的比率可能會比較適當，若您知道可以指派給SQL Server的最大記憶體大小是多少，可以使用下列T-SQL指令碼來查詢：


GO

RECONFIGURE
GO

EXEC sp_configure 'max server memory'
GO

接著執行下列的T-SQL來啟用緩衝集區擴充，並將緩衝集區擴充設定為10GB並且存放在D:\BPE資料夾。


SET BUFFER POOL EXTENSION ON
(FILENAME = 'D:\BPE\ExtensionFile.BPE' , SIZE = 10GB)

修改SQL Server資料庫引擎和SQL Server Agent的服務啟動模式

為了避免存放在D磁碟的TempDB以及緩衝集區擴充資料夾被刪掉，需要做點手腳，先將SQL Server資料庫引擎和SQL Server Agent的服務啟動模式設為手動，請從SQL Server Configuration Manager將上述兩個服務的Start Mode設為Manual。

建立啟動SQL Server資料庫引擎和SQL Server Agent所需的PowerShell

因為我們已經將TempDB搬移到D:\TempDB資料夾，並且將緩衝集區擴充的檔案放在D:\BPE資料夾，因此在啟動服務前必須先檢查該資料夾是否存在，若不存在則需在啟動服務前先行建立，請輸入下列PowerShell指令碼後，將檔案儲存為C:\StartSQLServices.ps1。

$SQLService="SQL Server (MSSQLSERVER)"
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"

$TempDBFolder="D:\SQLTEMP"
if (!(test-path -path $TempDBFolder)) {
    New-Item -ItemType directory -Path $TempDBFolder
}

$BPEFolder="D:\BPE"
if (!(test-path -path $BPEFolder)) {
    New-Item -ItemType directory -Path $BPEFolder
}

Start-Service $SQLService
Start-Service $SQLAgentService

再來必須修改執行PowerShell的安全性設定，將執行政策設定為RemoteSigned，以使得從網路下載的PowerShell檔案要求要簽署以確保發行者是安全的發行者，而本機的PowerShell檔案可以忽略簽署。

Set-ExecutionPolicy RemoteSigned

建立工作排程執行PowerShell

開啟Administrative Tools，點選Task Scheduler來建立工作排程。

點選右側的Create Basic Task來建立基本工作，輸入工作名稱Start SQL Services。

在Trigger步驟中選擇When the computer starts，使得排定工作會在電腦啟動時被觸發。

由於我們要執行的是一段事先準備好的PowerShell指令碼，因此在Action步驟需選擇Start a program。

於Program/script欄位輸入【PowerShell.exe】，以及add arguments欄位輸入【-file "C:\StartSQLServices.ps1"】（在此檔案名稱必須要用雙引號包起來，若您使用單引號可能會發生執行工作時回傳0xfffd0000的錯誤）。

最後您應該會看到如下圖的結果。

工作排程建立後，請將Security options調整為Run whether user is logged on or not，並指派適當的使用者帳戶。

測試工作排程

由於我們已經事先將SQL Server資料庫引擎和SQL Server Agent設為手動啟動，接著在電腦啟動時透過工作排程來檢查TempDB以及緩衝擴充集區所需的資料夾，並執行PowerShell來啟動SQL Server資料庫引擎和SQL Server Agent服務。若前面的步驟都設定正確，在工作排程執行歷程中可以看到執行成功的紀錄（如下圖）。