为什么这个SQL Server DBA学习PowerShell--SQL任务

petrel919

　　我开始学习PowerShell，是因为我在寻找一种快速和高效的方式收集有关我的SQL
Servers的信息和更好地管理我的服务器工作负载的方式。我以为，我正在学习是另一门新的脚本语言，它能帮我做到前面提到的设想。实际上，我发现学习
PowerShell，不仅提供了一种强大的手段去实现许多常规和重复性的服务器任务和健康检查;也是一个提高我其它方面技能的有用的跳板。例如，在我学
习PowerShell时，我发现：
　　提高了我关于.NET的认识，这样我就能够更好地与我所支持的应用程序开发人员交流。
　　学会了如何使用 Server Management Objects (SMO) 来实现数据库相关任务的自动化执行。
　　学会了 Windows Management Instrumentation (WMI)相关的知识，它能允许我只使用一条信息就查询多一台或者多台服务器。
　　更加适应OOP.
　　
在这篇文章里，我描述了一些使用PowerShell的例子，同时希望这些对DBA有用。我的脚本将会演示在一台或多服务器如何执行SQL查询，WMI查
询和SMO代码，以及帮助您更好的管理多台数据库服务器。所有脚本均在SQL Server 2005中测试通过。
　　
本文的目的不是写成一份PowerShell教程。我假设您已经熟知以下内容：基本的PowerShell语法，如何使用cmdlets获取帮助，命令行
是如何工作的，如何运行脚本，命名管道是什么，别名是什么等等。如果您不知道这些内容，你可以在各种线上文章，新闻组和博客中找到大量的帮助（文章结尾引
用章节中列出了部分资源）。这篇文章中的部分脚本来源于我阅读这些资源时遇到的。
　　使用PowerShell管理多台服务器
　　使用PowerShell管理多台服务器的核心就是一份简单的服务器清单，它包括您希望在其上执行常规任务和健康检查的服务器名字。
　　在我的例子中，我使用一个简单的包含我的服务器的清单AllServers.txt。格式如下：
　　Server1
　　Server2
　　Server3
　　……
　　
我将演示的例子中，我使用一个Foreach循环对这个清单里列出的每台服务器执行一个任务。这个简单的服务器列表构成了完成重复任务的基石。我主要的工
作是在Microsoft的环境里，我发现使用PowerShell执行重复任务要比之前用Python快。例如，Python需要多行语句读取，打开和
关闭一个文件，但是PowerShell中Get-Content cmdlet读取一个文件只使用一行代码。
get-content　　如果你感觉要打过多的字，你可以通过它的别名来调用Get-Content cmdlet。
gc "C:\AllServers.txt"

　　为了便于阅读而定义的最佳实践是在命令行中使用别名和在脚本中完成cmdlet。您可以使用Get-Alias cmdlet列出所有PowerShell中的别名：
get-alias | sort name　　

　　
get-alias | sort definition
　　PowerShell即是交互式命令行也是脚本环境。我刚开始着手解决一个问题是通过在命令行中执行命令。当我已经确定了命令的正确顺序时，我把它们保存为一个以.ps1扩展名的脚本文件，当需要时再执行。
　　自动化重复任务
　　PowerShell 使得为我所有的服务器实现自动化常规的和重复性任务变得更容易，使得用一些关于服务器的位信息(bit of
information)便能快速和高效处理看似层出不穷的即席请求。接下来的章节只是描述一些我已经写好的，用来实现自动化重复性任务的脚本。这些例子
的进步来自：我发现那些曾经花了很多精力解决才解决的问题，转换成Powershell来处理则变得非常简单。
　　SQL 任务
　　把Python转换成PowerShell最简单的任务就是对多台服务器执行一条语句。在这些例子中基本的步骤如下：

• 　　为每台服务器读取数据库服务器清单
  
• 　　创建一个表用于存储结果
  
• 　　建立与服务器的连接
  
• 　　执行查询并格式化查询结果
  

　　检查多台服务器上的SQLServer版本：
　　我运行如下脚本来确定，所有服务器是否处于公司规定的补丁级别：
# 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
　　

　　
}
　　下面的这个脚本遵循我用于对多台服务器执行SQL脚本的标准模板。它用foreach循环读取服务器清单，连接到服务器和执行一个返回用户数据库名称的SQL查询。为了这篇文章，我已经编辑过例子的格式，注释用绿色，PowerShell代码用蓝色，SQL用红色。
　　核对实际的数据库目录和内部数据库目录
　　每个月我都要核对实际的数据库目录和一个被其它应用程序作为资源引用的内部开发的数据库目录系统。
# 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 系统数据库)提供的数据库名称。之后，我会将之与一份数据库目录系统产生的报表进行对比。
　　从SysAdmin角色中移除BULTIN\Administrator组
　　这个脚本定义了一个函数而不是foreach循环，允许我在任何服务器上从SysAdmin角色中移除BULTIN\Administrator组。仅键入：
　　rmba ServerName
　　此函数接受一个参数，与服务器建立连接，然后执行sp_dropsrvrolememeber系统存储过程。
# 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();
　　

　　
}
　　这个脚本节省我的时间，因为我不必要跳转到SSMS去完成这个任务。在SMO章节中您会发现两个其它我创建的函数，它们用来列出BULTIN\Administrator和服务器本地管理员的成员。