第八篇 SQL Server代理使用外部程序

时间:2023-03-08 16:56:37

本篇文章是SQL Server代理系列的第八篇,详细内容请参考原文


在这一系列的上一篇,学习了如何用SQL Server代理作业活动监视器监控作业活动和查看作业历史记录。在实时监控和管理SQL Server代理作业上,作业活动监视器是一个很好的工具。第八篇,你会看到SQL Server代理的另一个功能——the ability to shell out to the operating system and run programs outside of the SQL Server environment。有些程序是命令行(无论是传统的命令应用程序/脚本或PowerShell脚本),甚至ActiveX脚本,但是你可以运行几乎任何程序,只要程序不要求用户直接输入。你将学习如何运行CmdExec和PowerShell脚本,我们会简短讨论每个子系统的适用场景。你将学会如何从SQL Server代理调用其他程序。
内置作业子系统
在前面的文章中我们看到,存在几个内置的作业子系统。在这篇文章中,你将学习三个内置作业子系统,它们能够在Windows服务器范围运行脚本或程序,而不是在SQL Server环境。这三个子系统包括:
->操作系统(CmdExec)
->PowerShell
->ActiveX脚本
当一个程序或脚本从这三个子系统启动,一个单独的进程在Windows创建,当脚本或程序运行时,信息被传回到SQL Server代理作业。
操作系统(CmdExec)子系统
我们讨论的第一个子系统是操作系统(CmdExec)子系统。CmdExec子系统打开命令提示符,就如你已登录到运行有SQL Server的Windows计算机。从这一点你可以运行任何你可以自己在命令提示符窗口键入的命令。这包括任何批处理文件、脚本、甚至存在于服务器上的程序。
安全性如何
当然,这一切的大问题是权限——在什么样的安全上下文?默认情况下,当你创建CmdExec子系统作业(或其他的),这个作业将在SQL Server代理服务帐户的安全上下文运行(如图8.1所示)。你可能还注意到,运行身份下有一个下拉选项。你将在第十篇理解这些代理帐户。另一个重要提示:你必须是SQL Server服务器角色下sysadmin角色中的成员,才能以SQL Server代理服务帐户运行作业。
第八篇 SQL Server代理使用外部程序
图8.1 CmdExec作业步骤中的作业安全
创建一个CmdExec作业步骤
创建新作业(ShellOut),新建作业步骤,如图8.1所示。我们将步骤名设为s1,更改作业步骤类型为"操作系统(CmdExec)",运行身份默认(SQL Server代理服务帐户)。在命令窗口键入"dir C:\",点击确定,再确定保存作业。运行作业,执行完成后查看历史记录。点击作业步骤查看DIR输出结果(图8.2)
第八篇 SQL Server代理使用外部程序
图8.2 CmdExec作业运行结果
你可以想象,这是一个平常的命令,但是你可以运行复杂的批处理脚本,甚至启动程序(例如,"start notepad"将运行记事本)。值得注意的是,如果你运行一个程序,如记事本,它将在一个虚拟的隐藏桌面运行,等待用户输入。因为桌面是隐藏的,用户不可以提供任何输入,即使退出程序的命令。换句话说,你的作业步骤不会完成/结束(测试作业完成后,记事本还打开着)。你可以在任务管理器下找到记事本进程,然后kill掉进程。从CmdExec子系统运行程序有许多有趣的场景,只要程序完成时能自动返回控制到SQL Server代理。
PowerShell子系统
PowerShell的子系统随着SQL Server 2008发布被添加到数据库中。它有PowerShell 1.0或PowerShell 2.0版本,取决你服务器上安装的是哪个版本。当你创建一个作业步骤,选择你的步骤类型为PowerShell子系统,你会有类似于CmdExec子系统的选项。你可以输入一段PowerShell脚本,或调用一个现有的PowerShell脚本(.ps1)。当你使用SQL Server代理开始一个PowerShell会话,PowerShell provider和cmdlet会预加载。
有许多与PowerShell脚本签名和安全处理需要考虑的,对本篇文章来说太广泛。然而,你可以在Windows PowerShell Owner's Manual上读到这些技术开发文章。
尽管如此,PowerShell很可能会成为你最喜欢的脚本子系统。对于SQL Server中的普通任务,运行SQL脚本通常是简单的。然而,重复的任务,使用PowerShell的子系统就简单多了。
举例说明,重新打开你的ShellOut作业,并添加一个作业步骤s2。类型选择PowerShell,然后输入下面的脚本(图8.3)

$server = new-object('Microsoft.SqlServer.Management.Smo.Server') "localhost\SQL08R2"
foreach ($database in $server.databases)
{
$dbName = $database.Name
Write-Output "Database: $dbName"
}

第八篇 SQL Server代理使用外部程序
图8.3 PowerShell子系统作业
这个脚本将登录到你本地SQL Server(如果使用命名实例更改实例名称),然后通过循环得到各数据库的名称。你可以想象备份数据库,或检查其属性。要注意的另一件事是你必须登录并创建数据库连接。点击确定,再确定。调整步骤s1的工作流,让步骤s2正确运行。运行作业,并查看输出结果。你会看到服务器上的数据库列表已输出。
什么是PowerShell子系统真正有趣的,你可以从操作系统、或Active Directory查询信息,然后运行任何你的服务器支持的脚本。你要查询SQL Server监听端口?你可以使用WMI通过PowerShell。你想从注册表获取一些信息?在网上有很多PowerShell脚本(包括许多文章在SQLServerCentral)。
ActiveX脚本子系统
ActiveX脚本子系统允许你运行ActiveX脚本,可以使用VBScript或JScript在操作系统来完成任务。为了完整性,该子系统包含在,但是不建议在SQL Server代理使用ActiveX脚本。该子系统是过时的,这意味着它将在未来版本的数据库上删除。
选择哪个子系统?
如果你有一个现有的作业使用这些子系统,你应该继续保持除非你有一个令人信服的理由去改变它。然而,如果你创建一个新的作业或作业步骤,PowerShell子系统提供了最令人信服的能力。此外,微软显然已经转移到PowerShell方向作为所有微软服务器产品的标准脚本语言。值得你投资时间去学习PowerShell。
下一篇
SQL Server代理的CmdExec、PowerShell、ActiveX子系统允许你的数据库执行许多任务,包括运行批处理文件或外部程序。此外,使用PowerShell可以访问和控制几乎任何微软产品。新作业/作业步骤推荐使用PowerShell子系统。
在下一篇,我们将开始关注SQL Server代理安全。直到现在,这个系列已经假定你是sysadmin服务器角色的成员。下一步将在SQL Server代理中使用非系统管理员,同时学习更多的作业安全上下文。