319
巧用利器Powershell，讓數據庫自動化運維事半功倍
作者介紹
許昌永，高級DBA，微軟SQL Server MVP，十年以上SQL Server使用經驗。曾就職於騰訊公司，從事了六年遊戲行業SQL Server數據庫開發和管理。目前就職於跨境電商DX.COM三年多，負責公司SQL Server和MongoDB的數據庫架構設計、高可用部署、運維管理和性能優化等工作。翻譯出版了書籍《PowerShell V3——SQL Server 2012數據庫自動化運維權威指南》。
目前市場上針對SQLServer的圖書，好的原創圖書屈指可數，翻譯的圖書較多，但隻限於專門針對SQL Server數據庫本身的開發、管理和商業智能。
而針對PowerShell的圖書，可以說大多為MSDN裏的學習筆記，主要針對Windows操作係統的管理。
微軟在大的布局和技術動向來看，力推雲平台。而平台化的基礎是自動化、高可用。那麼細化到最基礎的技術著眼點，微軟正在大力開發其所有服務器端產品對 PowerShell的支持。針對SQL Server來說，運用好PowerShell這項技能來將管理任務自動化，才能實現進一步的平台化，它是雲平台的基石。
國外大力推廣的DevOps、開發型運維，不僅僅讓數據庫管理員或係統管理員局限於手工來操作繁雜的日常工作，這樣風險極大。學習PowerShell來提升腳本開發能力，讓日常工作化繁為簡，是大勢所趨。
下麵我將通過我翻譯的一位微軟MVP著作《PowerShell V3——SQL Server 2012數據庫自動化運維權威指南》中的一個實例來講解下，使用PowerShell如何實現對SQL Server的自動化恢複數據庫。然後，通過Powershell調用MTools分析MongoDB性能，發送報表郵件來實現自動化運維。
實例一：
恢複SQL Server數據庫到一個時間點
在本方案中，我們將使用不同備份文件恢複到一個時間點。
準備：
在本方案中，我們將使用AdventureWorks20-08R2數據庫。你也可以選擇你的開發環境中的你更喜歡的數據庫。
AdventureWorks2008R2數據庫有一個包含一個單獨數據庫文件的文件組。我們將使用來自以下三種不同的備份類型的三個不同備份文件，來基於時間點恢複數據庫到另一個SQL Server實例：
-
完整備份
-
差異備份
-
事務日誌備份
我們可以使用PowerShell，像在之前的方案描述的，在AdventureWorks2008R2數據庫上創建這三種類型的備份。如果你對T-SQL相當熟悉，你也可以使用T-SQL備份命令。
為了幫助我們驗證是否基於時間點的恢複結果是我們期待的，在做任何類型的備份之前，創建一個時間戳標識的表。相應的，創建一個表，並在備份前插入一個時間戳標識的記錄到表中。
將這些備份放在C:\Backup\目錄。
你可以使用下麵的腳本來創建你的文件，6464 - Ch06 - 10 - Restore a database to a point in time - Prep.ps1，它包含在本書的可下載文件中。當腳本執行完整後，你應該在AdventureWorks2008R2數據庫中有時間戳標識的Student表，以一分鍾的間隔創建，類似於下麵的截屏：
（譯者注：可以從https://www.packtpub.com/books/content/support/10233下載該書代碼。）
對於我們的方案，我們將恢複AdventureWorks2008R2數據庫到另一個實例，KERRIGAN\SQL01，到2015-07-27 02:51:59。意味著，在基於時間點的恢複完成後，我們將隻有四個時間戳標識的Student表在KERRIGAN\SQL01在恢複的數據庫上：
-
StudentFull_201507270247
-
StudentDiff_201507270249
-
StudentTxn_201507270250
-
StudentTxn_201507270251
如何做...
為了使用完整、差異和一些事務日誌文件恢複到一個時間點，遵循如下步驟：
-
通過“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打開PowerShell控製台。
-
導入SQLPS模塊：#import SQL Server moduleImport-Module SQLPS -DisableNameChecking
-
添加如下腳本並運行：
如何實現...
在本方案中，我們使用Restore-SqlDatabase cmdlet，與Backup-SqlDatabase相對的cmdlet在SQL Server 2012中被介紹。
讓我們從高層概覽下如何實施時間點恢複，然後我們可以細化，並解釋包含在本方案中的片段：
1. 收集你的備份文件。
-
識別包含你想恢複的時間點的最後事務日誌備份文件。
2. 恢複最後的好的完整備份使用NORECOVERY。
3. 恢複最後的在完整備份後的差異備份使用NORECOVERY。
4. 恢複在差異備份後的事務日誌備份：
-
使用NORECOVERY恢複，直到包含你想恢複的時間點的日誌文件備份。你需要恢複最後的日誌文件備份到一個時間點，也就是說，你需要指定需要恢複的時間。最後，使用WITH RECOVERY恢複數據庫，使得數據庫可訪問並以備使用。
-
或者，你可以使用NORECOVERY恢複所有的事務日誌備份文件，在包含你想恢複到的時間點的日誌備份前。接下來，使用WITH RECOVERY恢複最後的日誌備份到一個時間點，那就是說，你需要指定恢複到何時。
步驟1 – 收集你的備份文件
你需要收集你的備份文件。它們不必位於相同的目錄或驅動設備，但這樣理想些，這樣可以簡化你的恢複腳本，你將有一個統一的目錄或驅動設備去參照。你也需要這些文件的讀許可權限。
在我們的方案，我們簡化了這個步驟。我們收集了完整，差異和事務日誌備份文件，存儲它們到C:\Backup\目錄，易於訪問。如果你的備份文件位於不同的位置，你隻需要適當的調整你腳本的參照目錄。
一旦有了這些備份文件，假設你遵循著文件命名規範，你可以過濾你目錄中的所有完整備份。在我們的示例中，我們使用命名規範databasename_type_timestamp.bak。對於這個方案，我們通過在文件名中指定關鍵字或匹配模式來抽取完整備份文件。我們使用Get-ChildItem過濾最新的完整備份文件：
#lookfor the last full backupfile
#youcan be more specific and specify filename
$fullBackupFile=
Get-ChildItem$backupfilefolder -Filter "*Full*" |
Sort-Property LastWriteTime -Descending |
Select-Last 1
一旦你有了完整備份的句柄，你可以讀取存儲在備份文件中的文件列表。你可以使用SMO Restore對象的ReadFileList方法。讀取文件列表可以幫助你通過抽取你需要恢複的數據和日誌文件的文件名來實現自動化。
#readthe filelist info within the backup file
#sothat we know which other files we need to restore
$smoRestore= New-Object Microsoft.SqlServer.Management.Smo.Restore
$smoRestore.Devices.AddDevice($fullBackupFile.FullName,[Microsoft.
SqlServer.Management.Smo.DeviceType]::File)
$filelist= $smoRestore.ReadFileList($server)
當讀取文件列表時，你可以抽取的一個屬性是存儲的文件的類型：
不同的類型為：
-
L代表日誌文件
-
D代表數據文件
-
F代表全文目錄
步驟2 – 使用NORECOVERY恢複最後的好的完整備份
在恢複操作的第一步是恢複最後的已知好的完整備份。這提供了你一個基線，基於此你可以恢複額外的文件。NORECOVERY選項非常重要，它保持（或不回滾）未提交的事務，並允許額外的文件被恢複。我們將會使用NORECOVERY選項在我們真個恢複過程中。
因為完整備份總是第一個需要恢複的文件，所有的準備工作需要就緒，此時移動文件也開始。
對於我們的方案，我們想去恢複數據庫，從源默認實例KERRIGAN到另一個實例KERRIGAN\SQL01。因此，我們需要移動我們的文件，從存儲我們備份文件的路徑，到我們想去使用的新路徑。在這個例子中，我們隻想從默認實例的默認數據目錄，移動到命名實例KERRIGAN\SQL01的數據目錄。我們從文件列表的原始數據和日誌文件獲取完整的路徑，使用我們想去恢複到的新位置來替代完整路徑。在下麵片段中的高亮代碼顯示了如何修改位置：
$relocateFileList= @()
$relocatePath= "C:\Program Files\Microsoft SQL Server\MSSQL11.SQL01\
MSSQL\DATA"
#weare putting this in an array in case we have
#multipledata and logfiles associated with the database
foreach($filein $fileList)
{
#restore to different instance
#replace default directory path for both
$relocateFile = Join-Path $relocatePath (Split-Path $file.
PhysicalName -Leaf)
$relocateFileList += New-ObjectMicrosoft.SqlServer.Management.
Smo.RelocateFile($file.LogicalName,$relocateFile)
}
注意，我們的數組包含了Microsoft.SqlServer.Management.Smo.RelocateFile對象，將包含我們數據庫文件的邏輯和（重定位的）物理文件名。
$relocateFileList += New-Object Microsoft.SqlServer.Management.Smo.
RelocateFile($file.LogicalName, $relocateFile)
為了恢複我們的數據庫，我們隻使用Restore-SqlDatabase cmdlet。這裏有一對很重要的選項，像RelocateFile和NoRecovery。
#restorethe full backup to the new instance name
#notewe have a NoRecovery option, because we have
#additionalfiles to restore
Restore-SqlDatabase`
-ReplaceDatabase`
-ServerInstance$instanceName `
-Database$restoredDBName `
-BackupFile$fullBackupFile.FullName `
-RelocateFile$relocateFileList `
-NoRecovery
步驟3 – 在完整備份恢複完後，使用NORECOVERY恢複最後的好的差異備份
一旦完整備份恢複，你可以添加最後的好的差異備份跟隨著完整備份。他並不是一個集成的過程，因為在這點上我們已經恢複了基礎數據庫並重定位了我們的文件。我們需要使用NORECOVERY恢複差異備份，阻止未提交的事務被回滾：
#usingPowerShell V2 Where syntax
$diffBackupFile=
Get-ChildItem$backupfilefolder -Filter "*Diff*" |
Where{$_.LastWriteTime -ge $fullBackupFile.LastWriteTime} |
Sort-Property LastWriteTime -Descending |
Select-Last 1
Restore-SqlDatabase`
-ReplaceDatabase`
-ServerInstance$instanceName `
-Database$restoreddbname `
-BackupFile$diffBackupFile.FullName `
-NoRecovery
注意，在你的環境中，你可能有，也可能沒有一個差異備份文件。如果沒有，不用擔心，它不會影響到你的可恢複性，隻要所有的事務日誌文件可用於恢複。
步驟4 – 在恢複差異備份後恢複事務日誌
在我們恢複了差異備份文件，我們開始恢複我們的事務日誌備份文件。這些事務日誌備份文件應該是跟隨著你的差異備份。你可能需要，或不需要跟隨著差異備份的日誌文件的完整集合。如果你需要恢複直到數據庫故障點，你將需要恢複所有的事務日誌備份包括尾日誌備份。如果不是，你隻需要知道你想恢複的事件點的備份文件。
對於我們的方案，我們識別出我們想去恢複的最後日誌備份文件。這很重要，因為我們需要知道如何使用PointInTime參數，當我們使用這個特定的事務日誌備份文件時。
#identifythe last txn log backup file we need to restore
#weneed this so we can specify point in time
$lastTxnFileName= "AdventureWorks2008R2_Txn_201507270252"
$lastTxnBackupFile=
Get-ChildItem$backupfilefolder -Filter "*$lastTxnFileName*"
對於所有其他的事務日誌備份文件，我們遍曆所有的備份目錄，恢複所有的在最後差異備份後的，在我們想去恢複的最後事務日誌備份文件之前的所有.txn文件。我們也需要通過WriteTime參數來排序這些文件，以便於我們依次恢複它們到數據庫。注意，我們需要使用NORECOVERY恢複所有的這些文件。
foreach($txnBackup in Get-ChildItem $backupfilefolder -Filter "*Txn*"
|
Where {$_.LastWriteTime -ge$diffBackupFile.LastWriteTime -and
$_.LastWriteTime-lt $lastTxnBackupFile.LastWriteTime} |
Sort-Property LastWriteTime)
{
Restore-SqlDatabase `
-ReplaceDatabase `
-ServerInstance $instanceName `
-Database $restoreddbname `
-BackupFile $txnBackup.FullName `
-NoRecovery
}
一旦所有的這些文件恢複後，然後我們準備恢複最後的事務日誌文件。一旦這個文件恢複，數據庫需要可訪問，所有的未提交事務需要被回滾。
有兩個方法去實現。第一個方法，我們在這個方案中使用的，是去使用ToPointInTime參數恢複最後的文件，並且不使用NoRecovery參數。
Restore-SqlDatabase `
-ReplaceDatabase`
-ServerInstance$instanceName `
-Database$restoreddbname `
-BackupFile$lastTxnBackupFile.FullName `
-ToPointInTime"2015-07-27 02:51:59"
另一個方法是恢複最後的事務日誌文件，也使用NoRecovery，但是在最後添加另一個命令，使用WITH RECOVERY恢複該數據庫。實際上，一直以來使用NORECOVERY恢複所有需要的事務日誌備份文件更為安全。它更安全，是因為當我們突然使用WITH RECOVERY恢複一個文件，糾正它的唯一方法是重做整個恢複過程。這可能對於小型數據庫沒多大關係，但是對於大型數據庫可能非常消耗時間。
一旦我們確認所有需要的文件已經被恢複，我們就可以使用WITH RECOVERY來恢複數據庫。在我們的方案中，一個方法是使用T-SQL語句，並傳遞該語句到Invoke-Sqlcmd：
#getthe database out of Restoring state
#makethe database accessible
$sql= "RESTORE DATABASE $restoreddbname WITH RECOVERY"
Invoke-Sqlcmd-ServerInstance $instanceName -Query $sql
RESTORE DATABASE命名使得我們的數據庫從一個正在恢複中的狀態，到可訪問和以備使用狀態。RESTORE命名回滾了所有未完成的事務，並讓數據庫以備使用。
實例二：
使用PowerShell調用MTools分析MongoDB性能並發送郵件
在MongoDB日常運維中，經常需要查看連接數的趨勢圖、慢查詢、Overflow語句、連接來源。任何數據庫的DBA都應該對數據庫情況進行定期的巡檢，以清楚了解數據庫的運行情況，健康狀況，隱患等等。MTools工具應運而生，它帶給DBA極大地幫助。
Mtools簡介
Mtools是由MongoDB Inc 官方工程師所寫，設計之初是為了方便自己的工作，但是隨著MongoDB用戶的增加，越來越多的朋友也開始使用Mtools，也越來越感受到Mtools帶來的便捷。
Github地址：https://github.com/rueckstiess/mtools
Mtools主要有以下組件：
-
mlogfilter
-
mloginfo
-
mplotqueries
-
mlogvis
-
mlaunch
-
mgenerate
首先，我們來簡單介紹下 mlogfilter,mloginfo和mplotqueries。mlogfileter我們可以簡單理解為日誌的過濾器，參數如下：
mlogfilter [-h] [--version] logfile[logfile ...]
[--verbose] [--shorten [LENGTH]]
[--human] [--exclude] [--json]
[--timestamp-format {ctime-pre2.4,ctime, iso8601-utc, iso8601-local}]
[--markers MARKERS [MARKERS ...]][--timezone N [N ...]]
[--namespace NS] [--operation OP][--thread THREAD]
[--slow [SLOW]] [--fast [FAST]] [--scan]
[--word WORD [WORD ...]]
[--from FROM [FROM ...]] [--to TO[TO ...]]
示例：
通過mlogfilter查詢日誌中某個表的slow log(超過100ms的)
mlogfilter --namespace xxx.xx --slow 100 mongod.log-20160611
mloginfo可以過濾總結出slow query的情況，以及為日誌中各類最常常出現情況進行統計，參數如下：
mloginfo [-h] [--version] logfile
[--verbose]
[--queries] [--restarts] [--distinct][--connections] [--rsstate]
示例：
通過mloginfo統計日誌中connections的來源情況
mloginfo mongod.log-20160611 --connections
mplotqueries相對複雜一些，功能是可以根據需求畫圖，以便更直觀的找出問題或者隱患所在，參數如下：
mplotqueries [-h] [--version]logfile [logfile ...]
[--group GROUP]
[--logscale]
[--type
{nscanned/n,rsstate,connchurn,durline,histogram,range,scatter,event}]
[--overlay [ {add,list,reset} ]]
[additional plot type parameters]
示例：
通過mplotqueries對連接情況進行分析，時間塊單位1800（30min）
mplotqueries mongod.log-20160611 --type connchurn --bucketsize 1800 --output-file 01-9.png
解決方案
筆者將在Windows上安裝MTools工具來分析mongod.log日誌，然後通過Powershell發送郵件。
（https://ultrasql.blog.51cto.com/9591438/1680156）
使用方法：
-
將DBA模塊放到相應的Modules\DBA目錄下。
-
在配置文件中加載模塊：Import-Module DBA -Force。
-
創建任務計劃，定時執行該MTools.ps1腳本。
最後更新：2017-05-13 08:43:52