為什么這個SQL Server DBA學(xué)習(xí)PowerShell

看見就非常 2014-07-14

展開全文

為什么這個SQL Server DBA學(xué)習(xí)PowerShell

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

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

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

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

學(xué)會了如何使用 Server Management Objects (SMO) 來實現(xiàn)數(shù)據(jù)庫相關(guān)任務(wù)的自動化執(zhí)行。

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

更加適應(yīng)OOP.

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

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

使用PowerShell管理多臺服務(wù)器

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

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

Server1

Server2

Server3

……

我將演示的例子中，我使用一個Foreach循環(huán)對這個清單里列出的每臺服務(wù)器執(zhí)行一個任務(wù)。這個簡單的服務(wù)器列表構(gòu)成了完成重復(fù)任務(wù)的基石。我主要的工作是在Microsoft的環(huán)境里，我發(fā)現(xiàn)使用PowerShell執(zhí)行重復(fù)任務(wù)要比之前用Python快。例如，Python需要多行語句讀取，打開和關(guān)閉一個文件，但是PowerShell中Get-Content cmdlet讀取一個文件只使用一行代碼。

# Read a file

get-content "C:\AllServers.txt"

如果你感覺要打過多的字，你可以通過它的別名來調(diào)用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í)行命令。當(dāng)我已經(jīng)確定了命令的正確順序時，我把它們保存為一個以.ps1擴展名的腳本文件，當(dāng)需要時再執(zhí)行。

自動化重復(fù)任務(wù)

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

SQL 任務(wù)

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

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

檢查多臺服務(wù)器上的SQLServer版本：

我運行如下腳本來確定，所有服務(wù)器是否處于公司規(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

}

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

核對實際的數(shù)據(jù)庫目錄和內(nèi)部數(shù)據(jù)庫目錄

每個月我都要核對實際的數(shù)據(jù)庫目錄和一個被其它應(yīng)用程序作為資源引用的內(nèi)部開發(fā)的數(shù)據(jù)庫目錄系統(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

}

這個查詢按服務(wù)器排序，返回非微軟(Non-Microsoft supplied,我猜想作者是指SqlServer 系統(tǒng)數(shù)據(jù)庫)提供的數(shù)據(jù)庫名稱。之后，我會將之與一份數(shù)據(jù)庫目錄系統(tǒng)產(chǎn)生的報表進行對比。

從SysAdmin角色中移除BULTIN\Administrator組

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

rmba ServerName

此函數(shù)接受一個參數(shù)，與服務(wù)器建立連接，然后執(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();

}