为什么这个SQL Server DBA学习PowerShell--SMO任务及杂项

lmwtzw6u5l0

　　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是用得其所。