為什么這個SQL Server DBA學習PowerShell

看見就非常 2014-07-14

展開全文

為什么這個SQL Server DBA學習PowerShell

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

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

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

提高了我關于.NET的認識，這樣我就能夠更好地與我所支持的應用程序開發(fā)人員交流。

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

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

更加適應OOP.

在這篇文章里，我描述了一些使用PowerShell的例子，同時希望這些對DBA有用。我的腳本將會演示在一臺或多服務器如何執(zhí)行SQL查詢，WMI查詢和SMO代碼，以及幫助您更好的管理多臺數據庫服務器。所有腳本均在SQL Server 2005中測試通過。

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

使用PowerShell管理多臺服務器

使用PowerShell管理多臺服務器的核心就是一份簡單的服務器清單，它包括您希望在其上執(zhí)行常規(guī)任務和健康檢查的服務器名字。

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

Server1

Server2

Server3

……

我將演示的例子中，我使用一個Foreach循環(huán)對這個清單里列出的每臺服務器執(zhí)行一個任務。這個簡單的服務器列表構成了完成重復任務的基石。我主要的工作是在Microsoft的環(huán)境里，我發(fā)現使用PowerShell執(zhí)行重復任務要比之前用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即是交互式命令行也是腳本環(huán)境。我剛開始著手解決一個問題是通過在命令行中執(zhí)行命令。當我已經確定了命令的正確順序時，我把它們保存為一個以.ps1擴展名的腳本文件，當需要時再執(zhí)行。

自動化重復任務

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

SQL 任務

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

為每臺服務器讀取數據庫服務器清單
創(chuàng)建一個表用于存儲結果
建立與服務器的連接
執(zhí)行查詢并格式化查詢結果

檢查多臺服務器上的SQLServer版本：

我運行如下腳本來確定，所有服務器是否處于公司規(guī)定的補丁級別：

# 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

}

下面的這個腳本遵循我用于對多臺服務器執(zhí)行SQL腳本的標準模板。它用foreach循環(huán)讀取服務器清單，連接到服務器和執(zhí)行一個返回用戶數據庫名稱的SQL查詢。為了這篇文章，我已經編輯過例子的格式，注釋用綠色，PowerShell代碼用藍色，SQL用紅色。

核對實際的數據庫目錄和內部數據庫目錄

每個月我都要核對實際的數據庫目錄和一個被其它應用程序作為資源引用的內部開發(fā)的數據庫目錄系統(tǒng)。

# 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 系統(tǒng)數據庫)提供的數據庫名稱。之后，我會將之與一份數據庫目錄系統(tǒng)產生的報表進行對比。

從SysAdmin角色中移除BULTIN\Administrator組

這個腳本定義了一個函數而不是foreach循環(huán)，允許我在任何服務器上從SysAdmin角色中移除BULTIN\Administrator組。僅鍵入：

rmba ServerName

此函數接受一個參數，與服務器建立連接，然后執(zhí)行sp_dropsrvrolememeber系統(tǒng)存儲過程。

# 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();

}