為什麼這個SQL Server DBA學習PowerShell–SQL任務

為什麼這個SQL Server DBA學習PowerShell

原文出自：http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/

Joe.TJ翻譯整理，僅用於傳播資訊之目的。

     我開始學習PowerShell，是因為我在尋找一種快速和高效的方式收集有關我的SQL Servers的資訊和更好地管理我的伺服器工作負載的方式。我以為，我正在學習是另一門新的指令碼語言，它能幫我做到前面提到的設想。實際上，我發現學習PowerShell，不僅提供了一種強大的手段去實現許多常規和重複性的伺服器任務和健全狀態檢查;也是一個提高我其它方面技能的有用的跳板。例如，在我學習PowerShell時，我發現：

  提高了我關於.NET的認識，這樣我就能夠更好地與我所支援的應用程式開發人員交流。

  學會了如何使用 Server Management Objects (SMO) 來實現資料庫相關任務的自動化執行。

  學會了 Windows Management Instrumentation (WMI)相關的知識，它能允許我只使用一條資訊就查詢多一台或者多台伺服器。

  更加適應OOP.

 

      在這篇文章裡，我描述了一些使用PowerShell的例子，同時希望這些對DBA有用。我的指令碼將會示範在一台或多伺服器如何執行SQL查詢，WMI查詢和SMO代碼，以及協助您更好的管理多台資料庫伺服器。所有指令碼均在SQL Server 2005中測試通過。

      本文的目的不是寫成一份PowerShell教程。我假設您已經熟知以下內容：基本的PowerShell文法，如何使用cmdlets擷取協助，命令列是如何工作的，如何運行指令碼，具名管道是什麼，別名是什麼等等。如果您不知道這些內容，你可以在各種線上文章，新聞群組和部落格中找到大量的協助（文章結尾引用章節中列出了部分資源）。這篇文章中的部分指令碼來源於我閱讀這些資源時遇到的。

使用PowerShell管理多台伺服器

         使用PowerShell管理多台伺服器的核心就是一份簡單的伺服器資訊清單，它包括您希望在其上執行常規任務和健全狀態檢查的伺服器名字。

         在我的例子中，我使用一個簡單的包含我的伺服器的清單AllServers.txt。格式如下：

         Server1

         Server2

         Server3

         ……

      我將示範的例子中，我使用一個Foreach迴圈對這個清單裡列出的每台伺服器執行一個任務。這個簡單的伺服器列表構成了完成重複任務的基石。我主要的工作是在Microsoft的環境裡，我發現使用PowerShell執行重複任務要比之前用Python快。例如，Python需要多行語句讀取，開啟和關閉一個檔案，但是PowerShell中Get-Content cmdlet讀取一個檔案只使用一行代碼。

# Read a file

get-content "C:\AllServers.txt"

   如果你感覺要打過多的字，你可以通過它的別名來調用Get-Content cmdlet。

gc "C:\AllServers.txt"

為了便於閱讀而定義的最佳實務是在命令列中使用別名和在指令碼中完成cmdlet。您可以使用Get-Alias cmdlet列出所有PowerShell中的別名：

# List aliases, sort by name or definition

get-alias | sort name

get-alias | sort definition

PowerShell即是互動式命令列也是指令碼環境。我剛開始著手解決一個問題是通過在命令列中執行命令。當我已經確定了命令的正確順序時，我把它們儲存為一個以.ps1副檔名的指令檔，當需要時再執行。

 

自動化重複任務

      PowerShell 使得為我所有的伺服器實現自動化常規的和重複性任務變得更容易，使得用一些關於伺服器的位資訊(bit of information)便能快速和高效處理看似層出不窮的即席請求。接下來的章節只是描述一些我已經寫好的，用來實現自動化重複性任務的指令碼。這些例子的進步來自：我發現那些曾經花了很多精力解決才解決的問題，轉換成Powershell來處理則變得非常簡單。

 SQL 任務

      把Python轉換成PowerShell最簡單的任務就是對多台伺服器執行一條語句。在這些例子中基本的步驟如下：

1. 為每台伺服器讀取資料庫伺服器清單
2. 建立一個表用於儲存結果
3. 建立與伺服器的串連
4. 執行查詢並格式化查詢結果

 

     檢查多台伺服器上的SQLServer版本：

          我運行如下指令碼來確定，所有伺服器是否處於公司規定的補丁層級：

# SQLVer.ps1

# usage: ./SQLVer.ps1  

# Check SQL version

foreach ($svr in get-content "C:\data\AllServers.txt")

{

$con = "server=$svr;database=master;Integrated Security=sspi"

$cmd = "SELECT SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('ProductLevel') as SP"

$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

$dt = new-object System.Data.DataTable

$da.fill($dt) | out-null

$svr

$dt | Format-Table -autosize

}        

 

     下面的這個指令碼遵循我用於對多台伺服器執行SQL指令碼的標準模板。它用foreach迴圈讀取伺服器資訊清單，串連到伺服器和執行一個返回使用者資料庫名稱的SQL查詢。為了這篇文章，我已經編輯過例子的格式，注釋用綠色，PowerShell代碼用藍色，SQL用紅色。

核對實際的資料庫目錄和內部資料庫目錄

每個月我都要核對實際的資料庫目錄和一個被其它應用程式作為資源引用的內部開發的資料庫目錄系統。

# inv.ps1

# usage: ./inv.ps1

# Database inventory

foreach ($svr in get-content "C:\data\AllServers.txt")

{

$con = "server=$svr;database=master;Integrated Security=sspi"

$cmd = "SELECT name FROM master..sysdatabases WHERE dbid > 4 AND name NOT IN ('tracedb','UMRdb','Northwind','pubs','PerfAnalysis') ORDER BY name"

$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

$dt = new-object System.Data.DataTable

$da.fill($dt) | out-null

$svr

$dt | Format-Table -autosize

} 

這個查詢按伺服器排序，返回非微軟(Non-Microsoft supplied,我猜想作者是指SqlServer 系統資料庫)提供的資料庫名稱。之後，我會將之與一份資料庫目錄系統產生的報表進行對比。

 

從SysAdmin角色中移除BULTIN\Administrator組

這個指令碼定義了一個函數而不是foreach迴圈，允許我在任何伺服器上從SysAdmin角色中移除BULTIN\Administrator組。僅鍵入：

rmba ServerName

此函數接受一個參數，與伺服器建立串連，然後執行sp_dropsrvrolememeber系統預存程序。

# Remove BUILTIN\Administrators from sysadmin role

function rmba ($s)

{

$svr="$s"

$cn = new-object System.Data.SqlClient.SqlConnection"server=$svr;database=master;Integrated Security=sspi"

$cn.Open()

$sql = $cn.CreateCommand()

$svr

$sql.CommandText = "EXEC master..sp_dropsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin';"

$rdr = $sql.ExecuteNonQuery();

}

這個指令碼節省我的時間，因為我不必要跳轉到SSMS去完成這個任務。在SMO章節中您會發現兩個其它我建立的函數，它們用來列出BULTIN\Administrator和伺服器本地管理員的成員。

--------------------------------------------

如蒙轉載或引用，請保留以下內容：
Joe's Blog:http://www.cnblogs.com/Joe-T/