為什麼這個SQL Server DBA學習PowerShell–SMO任務及雜項

為什麼這個SQL Server DBA學習PowerShell

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

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

SMO是一個對象集合，它允許你自動化任何Microsoft SQL  Server相關的管理工作。同樣的，對於不熟悉物件導向編程的DBA來說，最大的障礙就是使用更令人生畏的物件模型。同樣的，像WMI一樣，您需要知道如何檢查一個對象以確定它可用的屬性和方法。

在SMO的例子中，您將會再次看被用於執行SMO代碼的Foreach迴圈。所有的例子通過設定一個SMO程式集引用開始。一旦你建立了這個引用，那麼指令碼便能執行個體化從這個程式集類中派生的新對象。

瀏覽SMO類

雖然SMO類在聯機叢書中有記錄，但是如果你學會擷取對象的屬性和方法的列表也會很有用。為了瀏覽SMO類，你需要設定一個引用然後使用。Get-Member(gm) cmdlet會顯示該對象的屬性和方法。

# To examine the SMO Server object in PowerShell:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$svr = new-object ("Microsoft.SqlServer.Management.Smo.Server") "ServerName"

$svr | get-member

為了研究不同的對象，相應地改變上述指令碼中的第二行和第三行。

 

列出伺服器上SysAdmin角色的成員

依據你前面的經驗，搞明白SMO物件模型是如何運作的可能很棘手。我明白基礎的物件導向的編程知識，但不是完全理解，直到我執行一個列出伺服器上SysAdmin角色成員的指令碼時。起初，我嘗試使用以下指令碼並收到錯誤資訊。(我在測試時，沒有遇到作者所說的錯誤資訊，只是把$svrole當作一個字串變數輸出，沒有得預期角色成員的結果。所以這裡就直接引用了作者的圖。)

# Before I understood the concept of objects completely, I tried…

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$svr="ServerName"

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"

$svrole = 'sysadmin'

$svrole 

在這一點上我有了小小的頓悟。最終使我理解了物件導向編程的概念和PowerShell中“任何東西都是一個對象”。我成功的建立了一個伺服器對象的執行個體，並從那裡，我想要用自己的方式為SysAdmin角色處理伺服器角色對象。所以，我設定一個變數$svrole，並賦值‘sysadmin’。

然後我嘗試調用這個字串對象的方法，並認為我是在調用伺服器角色對象的方法。在這種情況下，變數$svrole只包含字串對象而不是對伺服器角色對象的引用。因此，才會發生上面的錯誤。

下面的指令碼把列出伺服器上SysAdmin成員所需的PowerShell代碼封裝成了一個函數。

# create sa function to list sysadmin members

# usage: sa ServerName

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

function sa ($s)

{

$svr="$s"

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"

$svrole = $srv.Roles | where {$_.Name -eq 'sysadmin'}

$svr

$svrole.EnumServerRoleMembers()

}

列出伺服器上的本地管理員

我使用下面的指令碼(及前一個)保持在伺服器和SQL Server上擁有Admin許可權的人數最少。這個例子由Microsoft MVP Ying Li所寫並貼在他的部落格上。它示範了如何列出伺服器上的本地管理員。這個函數接收一個伺服器名稱，然後串連指定的伺服器並列出其上的本機系統管理員群組的成員。

# create ListAdmins function to list local Administrators on a server.

# usage: ListAdmins ServerName

function ListAdmins ($svr)

{

$domain = [ADSI]""

$strComputer = $svr

$computer = [ADSI]("WinNT://" + $strComputer + ",computer")

$computer.name;

$Group = $computer.psbase.children.find("administrators")

$Group.name

$members= $Group.psbase.invoke("Members") | %{$_.GetType().InvokeMember("Name", 'GetProperty', $null, $_, $null)}

$members

} 

查詢多台伺服器上的登入或AD組

我最初的SMO例子之一，靈感來自於我的主管，她讓我找出資料建模組能訪問那些資料庫伺服器。她希望只是程式開發伺服器能被這個組訪問。

下面的例子以5到7行代碼就結束了(這也取決於你的格式編排),但是不管你的伺服器資訊清單上有多少伺服器，這將會找出登入/組。

# Find a login or AD group on multiple servers

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

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

{

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"

trap {"Oops! $_"; continue } $srv.Logins | where {$_.Name -eq 'DOMAIN\ITS_DATA_ADMIN'} | select Parent, Name

}

指令碼中Trap語句處理串連伺服器時發生的錯誤。在這個例子中，如果串連伺服器時有錯誤，將會返回伺服器名稱和錯誤資訊。偶爾，在輸出中我會看到：“Oops!Failed to connect to the server ServerName”.

 

檢查多台伺服器上失敗的SQL 代理作業

每天早上我執行如下指令碼來檢查在我的伺服器上任何失敗的SQL代理作業：

# Check for failed SQL jobs on multiple servers

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

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

{

write-host $svr

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"

$srv.jobserver.jobs | where-object {$_.lastrunoutcome -eq "Failed" -and $_.isenabled -eq $TRUE} | format-table name,lastrunoutcome,lastrundate -autosize

} 

雜項任務

下面這些純PowerShell的例子，回答了一些DBA也許會有的疑問。

 檢查已安裝的修複程式

  # List all installed hotfixes on a server

get-wmiobject Win32_QuickFixEngineering

# Check if a specific hotfix is installed on a server

get-wmiobject Win32_QuickFixEngineering | findstr KB928388

尋找連接埠號碼

我經常被開發人員問到具名執行個體的連接埠號碼。通過一個短短的具名管道結合兩個cmdlet：Get-Content和Select-String。你就可以通過一行程式在錯誤記錄檔中找到連接埠號碼。這比手動尋找錯誤記錄檔或者執行一段SQL代碼快多了。

我曾嘗試只用Select-String去搜尋錯誤記錄檔，但是由於某些原因，Get-String不能讀取活動的錯誤記錄檔，除非與Get-Content結合使用。下在的例子中我在錯誤記錄檔中尋找“Listening”一詞。

# Find a port number

gc \\ServerName\ShareName\MSSQL2005\MSSQL.2\MSSQL\LOG\ERRORLOG | select-string "listening"

 

請記住，如果在伺服器上您有迴圈的錯誤記錄檔，您需要尋找的行可能不在當前的錯誤記錄檔裡。您將需要調整以下的命令，通過在ERRROLOG後追加1，2，3等等，來尋找錯誤記錄檔存檔。

如果你在SQL Server 2000的具名執行個體上搜尋錯誤記錄檔，你需要用反引號將檔案路徑中的$轉義。如下所示：

get-content \\ServerName\ShareName\MSSQL2000\MSSQL`$SQL100\LOG\ERRORLOG | select-string "listening"

 

產生隨機密碼

如果您需要為SQL登入產生隨機密碼，您可以使用如下所示的.NET類：

# generate a random password

[Reflection.Assembly]::LoadWithPartialName(”System.Web” ) | out-null

[System.Web.Security.Membership]::GeneratePassword(10,2) # 10 bytes long

[System.Web.Security.Membership]::GeneratePassword(8,2) # 8 bytes long

 

查檢多台伺服器上的當前備份

在我的環境中，我有兩個資料庫配置並且備份並不總放置在一個標準位置。因此，我用“Brute Force”方案來檢查備份。

# Checking backups are current

write-host ''

write-host 'ServerName'

get-childitem \\ServerName\ShareName\dump_data\*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime

如果伺服器上有多個磁碟機需要檢查，我就為額外的磁碟機重複執行Get-ChildItem cmdlet。這裡是我ChkBkups.ps1指令碼的一個片段：

# checking three dump locations on a default instance.

write-host ''

write-host 'Server1'

get-childitem \\Server1\e$\dump_data\ServerName\*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime

get-childitem \\Server1\g$\dump_data\ServerName\*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime

get-childitem \\Server1\i$\dump_data\ ServerName \*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime

# checking one dump location on a named instance.

write-host ''

write-host ' Server2'

get-childitem \\Server2\ShareName\dump_data\ServerName\Instance\db_dump\*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-1) } | Select name, LastWriteTime

我每天早上運行這個指令碼。我們有一套每晚啟動並執行自動化的常式，它們處理標準的DBA任務，像備份，完整性檢查，索引維護等等。每個伺服器維護進程會發郵件來報告它們的狀態。這個指令碼節省了我查看多封郵件的時間。

 

總結：

我認為使用PowerShell會讓我成為一名更好的DBA，因為我有意識去自動化平常的任務，更快地收集有關伺服器的資訊，以及更好地管理我的伺服器工作負載。我還發現，使用PowerShell會延伸我的知識到時那些我通常不會涉及的領域(這隻會是一件好事情)。

短短几行PowerhShell代碼，怎麼能做這麼多的事情，這實在很驚人。

在我看來，花時間學習PowerShell是用得其所。

 

引用：

ADO.Net

• Books24x7.com – subscription required"> Microsoft ADO.NET 2.0 Step by Step by Rebecca M. Riordan

SMO

• SQL Server Books Online:
  http://msdn.microsoft.com/en-us/library/ms162169.aspx
• Blogs:
  http://blogs.msdn.com/mwories/archive/tags/SMO+Samples/default.aspx

WMI

• http://msdn.microsoft.com/en-us/library/aa394582.aspx
• http://msdn.microsoft.com/en-us/library/aa394572(VS.85).aspx

PowerShell

• “Getting Started Guide”
  http://msdn.microsoft.com/en-us/library/aa973757(VS.85).aspx
• Books.
• “Windows PowerShell In Action” by Bruce Payette
• Windows PowerShell: TFM” by Don Jones and Jeffery Hicks
• Newsgroups
• microsoft.public.windows.powershell
• Blogs and articles:
• http://www.mssqlengineering.com/
• http://www.simple-talk.com/sql/database-administration/managing-sql-server-using-powersmo/
• http://myitforum.com/cs2/blogs/yli628/archive/2007/08/30/powershell-script-to-add-remove-a-domain-user-to-the-local-administrators-group-on-a-remote-machine.aspx
• http://sqlblog.com/blogs/allen_white/archive/2008/01/25/using-powershell-and-sql-server-together.aspx
• http://msmvps.com/blogs/robfarley/archive/2007/01/05/powershell-and-sql.aspx
• http://myitforum.com/cs2/blogs/dhite/archive/2007/06/03/powershell-mini-sql-query-analyzer.aspx
• http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx