使用PowerShell执行SQL查询

前言

你是否需要一些自动化的PowerShell脚本,定期去数据库里查询数据。你会选择用什么工具来实现呢?计划任务?还是SQL Agent + T-SQL?选择有很多,本文主要关注如何使用PowerShell来实现自动化执行SQL语句。

本文对象

  • 面向主要对象是运维人员 (Operation)
  • 难度级别: 200 (Level)

知识前提

  • 熟悉基本的PowerShell脚本语言
  • 知道简单的T-SQL语言(SELECT,UPDATE等)
  • 了解基本的SQL Server管理知识

背景知识

SQL PowerShell

SQL Server 2008/2008 R2 自带SQLPS mini-shell,也被称为SQLPS工具。你可以通过在SSMS(SQL Server Management Studio)中从对象浏览器中右键点击一个对象,选择开始PowerShell。当时设计的这个mini-shell仅仅是一个预先加载了SQL拓展模块的封闭shell,因此用途比较局限。因为DBAs(Database admin)和开发人员通常需要加载额外的模块和插件以便通过PowerShell与其他系统进行集成。

另外一种方法是启动一个完整的PowerShell session,根据PowerShell的版本来加载SQLPS模块或者插件(snap-ins)。

在SQL Server 2012到来以后原先的这种mini-shell设计被微软放弃。当你在SSMS(SQL Server Management Studio)中从对象浏览器中右键点击一个对象,选择开始PowerShell。一个完整的PowerShell session会启动,并默认自动加载SQLPS模块。 SQL Server里面的对象会以PSDrive的形式暴露出来以便访问、管理。有点类似文件系统的管理,你可以使用Get-ChildItem之类的命令来访问数据库里的内容。

SQL Server Management Object(SMO)

SQL Server Management Objects (SMO)第一次出现在SQL Server 2005,它允许通过程序化的方法来访问和管理SQL Server。SMO可以在任何.NET语言,包括C# ,VB.NET,和PowerShell。SMO同时支持向前兼容,支持最老的版本是SQL Server 2000。

SMO有两种类型组成:

  • Instance Classes (实例类): 用来访问如数据库,数据库服务器,表等对象。

  • Utility Classes (工具类):用来进行SQL Server管理。

安装SQL SMO

如果你是安装SQL server 2012或者已经安装了SQL server 2012那么可以通过安装Client Tools SDK来安装SMO。 如果你只是想要安装SMO,那么可以下载SQL Server 2012功能包(feature pack)。

alt

安装好了之后,你可以在"\SQL Server 安装路径\110\ SDK\Assemblies"看到相关的DLL文件。

alt

创建SQL Server实例对象(Instance Object)

这一步可以理解为建立与数据库的连接。

#导入SQLPS模块
Import-Module sqlps -DisableNameChecking  
#创建变量instancename来保存服务器名字。如果instance不是默认的MSSQLSERVER,那么格式应该有‘<machine name>\<instance name>’
$instancename = ‘ZanjoyDBServer’
#创建Server Instance变量
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instancename

如果将使用Windows身份认证方式访问SQL Server,那么只要你当前登录的用户拥有SQL的访问权限那么Server Instance就可以使用了。 如果你使用SQL Server内置的身份认证来访问,那么你还需要做下面的修改。

#取消,Windows身份认证模式
$server.ConnectionContext.set_LoginSecure($false)
#输入SQL Server 用户名
$server.ConnectionContext.set_Login('sa')
#输入SQL Server 用户密码
$server.ConnectionContext.set_securePassword((ConvertTo-SecureString 'YourPassword' -AsPlainText -Force))
#通过下面命令可以查看当前的ConnectionString是否正确
$server.ConnectionContext.ConnectionString

这步完成之后,基于SQL内置用户的Instance Object就成功建立了。

非常棒,接下来开始第一个SQL查询吧!

这里我查询AdventureWorks2012数据库Person.Person表里的所有数据。 如果是在SQL Server Management Studio里我们需要的语句如下:

SELECT * FROM Person.Person  

在PowerShell中我们要做事情几乎一样。这里,我们要用到非常重要的一个命令:Invoke-Sqlcmd, 是它让一切变成了可能!

$dbName = "AdventureWorks2012"
       $db = $server.Databases[$dbName]
       #execute a passthrough query, and export to a CSV file
       Invoke-Sqlcmd `
       -Query "SELECT * FROM Person.Person" `
       -ServerInstance "$instanceName" `
       -Database $dbName |
       Export-Csv -LiteralPath "C:\Temp\ResultsFromPassThrough.csv" `
       -NoTypeInformation

通过上面的一段代码,我们查询了数据库,并且导出了Excel文件,这些都是一气呵成,是不是非常方便?

结论

好了,通过上面的简单介绍,不难看出通过PowerShell执行SQL查询语句,非常灵活易用。有些数据格式,如Json,很难直接用T-SQL进行解析、处理。PowerShell则支持输入输出多种格式的数据,而且代码简单,执行速度快。