为什么这个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