PowerShell 2.0 实践（十二）管理 SQL Server 2008 R2(1)

yxxs123

　　DBA可以使用的工具很多，对于SQL Server来说，有查询分析器、事件探查器、命令行工具等，其中SQL语句是重中之重，但是PowerShell的出现使得DBA又多了一种选择。
　　测试脚本下载
　　本系列所有测试脚本均在Windows Server 2008 R2 DataCenter (PowerShell 2.0) + PowerGUI Script Editor Free Edition x64中测试通过。
　　SQL Server系列使用了SQL Server 2008 R2 DataCenter x64
　　转载请注明出处：http://www.iyunv.com/brooks-dotnet/archive/2010/10/11/1848282.html
　　
　　微软对SQL Server提供了如下几种PowerShell扩展，可以使用Get-PSSnapin-Registered查看：

　　SQL Server 2008 R2针对主数据服务还提供了一种扩展：MasterDataServicesSnapin，后续篇章测试主数据服务时再做讨论。
　　微软提供的PowerShell扩展包含的命令并不多，只能完成一些基础操作，更高级的操作还是要借助SQL Server Management Object (SMO，SQL Server对象模型)。
　　CodePlex上有人发布了SQL Server的PowerShell扩展，提供了更多、更常用的命令，后续篇章我们来测试一下。
　　
　　由于管理的对象是SQL Server 2008 R2，故首先回顾一下其新特性。

　　Microsoft SQL Server 2008 R2是SQL Server数据库的最新版本， R2标志表示这是SQL Server的一个中间版本，而不是一个主版本，但R2版本一样为DBA和开发人员提供了大量有趣的新功能。除了新功能外，也发布了两个新版本：SQL Server 2008 R2 Datacenter(数据中心版)和SQL Server 2008 R2 Parallel Data Warehouse(并行数据仓库版)。

• SQL Server 2008 R2 新特性
  

　　1、Report Builder 3.0
　　Report Builder是一个工具集，通过它可以开发出发布到Web上的报表，通过Report Builder可以创建包含图像，图表，表格和打印控件的报表，此外，Report Builder也支持下钻和排序，如果你熟悉第三方报表工具，图Crystal Reports(水晶报表)，那么你一定会使用Report Builder。
　　SQL Server 2008 R2/Report Builder 3.0中的新特性包括：
　　1）地图图层，它可以容纳空间和分析数据，可以和微软的虚拟地球(Microsoft Virtual Earth)无缝集成;
　　2）指示器，用于显示一个值的状态;
　　3）报表部件，这个对象可以重复使用或在多个报表之间共享;
　　4）聚合计算(Aggregate Calculating)，允许你计算其它聚合计算结果的汇总值。
　　2、SQL Server 2008 R2 Datacenter
　　SQL Server 2008 R2的数据中心版的目标是企业版用户，他们要求更好的性能，新版本支持256颗逻辑处理器，更多的实例数和更多的内存。
　　3、SQL Server 2008 R2 Parallel Data Warehouse
　　SQL Server 2008 R2的另一个新版本是并行数据仓库版，正式代号是"Madison"，它主要目标是处理非常大的数据量，它使用大规模并行处理功能将大表分散到多个SQL节点，这些节点通过微软的专利技术Ultra Shared Nothing进行控制，它可以将查询任务分配到各个计算节点上，然后从各个节点收集计算结果。
　　4、StreamInsight
　　SQL Server 2008 R2中出现了一个新组件，叫做StreamInsight，这个有趣的组件允许在运行中分析流数据，也就是直接从源数据流进行处理，然后再保存到数据表中。如果你的系统是一个实时系统，这个功能就非常非常的有用，因为实时系统需要分析数据，但又不能引起数据写入时的延迟，一些常见的例子如股票交易数据流，Web点击分析流和工业处理控制，可以同时监控多个输入数据流。
　　5、主数据服务
　　主数据服务(Master Data Services，MDS)既是一个概念又是一个产品，主数据服务的概念是对核心业务数据有一个集中的数据入口看守人，数据项如客户账单地址，雇员/客户姓名，以及产品名称应该集中管理，以便让所有消费应用系统都具有相同的信息。微软提供了一个示例，在customer表中记录了一个顾客地址，但在mailing表中记录了一个不同的地址。主数据服务应用程序可以保证所有表只有一个正确的地址，而一个MDS可以是一个本地应用程序，SQL Server 2008 R2包括一个应用程序和一个接口管理核心数据。
　　6、PowerPivot for SharePoint
　　PowerPivot是一个终端用户工具，它与SharePoint，SQL Server 2008 R2和Excel 2010联合使用，可以在几秒内处理大量数据，PowerPivot的作用有点像Excel中的数据透视表，提供了分析功能。
　　7、数据层应用
　　数据层应用(Data-Tier Application，缩写为DAC，不知道C代表什么含义，不要与Windows数据访问组件混淆了，因为它的缩写也是DAC)是一个对象，它可以为一个工程存储所有需要的数据库信息，如登录，表和Visual Studio可以使用的存储过程。通过创建一个数据层应用，SQL Server包版本和每个Visual Studio编译版本一起保存，也就是可以将应用程序和数据库构建成一个统一的版本，方便后期维护和管理。
　　8、Unicode压缩
　　SQL Server 2008 R2使用一个新的算法，为Unicode存储提供了一个简单的压缩方案，通过Unicode压缩，可以减少Unicode字符对空间的占用，它由SQL Server引擎自动管理，因此不需要修改现有应用程序，DBA也无须做任何干涉。
　　9、SQL Server Utility
　　新的SQL Server Utility是一个集中控制多个SQL Server实例的仓库对象，性能数据和配置策略可以存储在一个单一的Utility中，Utility也包括一个资源管理器工具，可以创建多个服务器仪表板。
　　10、多服务器仪表板
　　虽然SQL Server Management Studio也可以连接到多个服务器，但不能在一个集中的视图上查看所有的数据库，每个数据库服务器需要独立管理，在SQL Server 2008 R2中，可以创建同时显示多个服务器的仪表板。
　　

• SQL Server驱动器
  

　　当添加了微软的PSSnapin后，就会添加SQL Server驱动器：
　　Add-PSSnapin-NameSqlServerProviderSnapin100
　　Add-PSSnapin-NameSqlServerCmdletSnapin100
　　
　　Get-PSDrive
　　运行结果：

　　
　　有了SQL Server驱动器后就可以像访问文件系统那样访问SQL Server对象，如实例名、数据库、表、关系等，非常方便。
　　如上图所示，SQLSERVER:\是SQL Server驱动器的"根目录"，其"目录结构"如下：（摘自MSDN）

Folder	SQL Server object model namespace	Objects
SQLSERVER:\SQL	Microsoft.SqlServer.Management.Smo 　　Microsoft.SqlServer.Management.Smo.Agent 　　Microsoft.SqlServer.Management.Smo.Broker 　　Microsoft.SqlServer.Management.Smo.Mail	Database objects, such as tables, views, and stored procedures.
SQLSERVER:\SQLPolicy	Microsoft.SqlServer.Management.Dmf 　　Microsoft.SqlServer.Management.Facets	Policy-based management objects, such as policies and facets.
SQLSERVER:\SQLRegistration	Microsoft.SqlServer.Management.RegisteredServers 　　Microsoft.SqlServer.Management.Smo.RegSvrEnum	Registered server objects, such as server groups and registered servers.
SQLSERVER:\Utility	Microsoft.SqlServer.Management.Utility	Utility objects, such as managed instances of the Database Engine.
SQLSERVER:\DAC	Microsoft.SqlServer.Management.DAC	Data-tier application objects such as DAC packages, and operations such as deploying a DAC.
SQLSERVER:\DataCollection	Microsoft.SqlServer.Management.Collector	Data collector objects, such as collection sets and configuration stores.

　　
　　那么我们查看一下其子集：
　　Clear-Host
　　Set-Location SQLSERVER:\
　　Get-ChildItem
　　运行结果：

　　可以得到子集目录的简要描述。
　　
　　获取本机安装的所有SQL Server实例的信息：
　　#将BrooksPC替换为你自己的机器名
　　Set-Location SQLSERVER:\SQL\BrooksPC
　　Get-ChildItem
　　运行结果：

　　可以得到很多数据库的元数据信息，如默认实例名、字符集、.NET版本等，非常有用。
　　
　　查询本机默认实例上的所有数据库和表：
　　Set-Location SQLSERVER:\SQL\BrooksPC\DEFAULT\Databases
　　Get-ChildItem | Select Name, Tables
　　运行结果：

　　可以看到和GUI中的一致，系统数据库、系统表除外：

　　
　　查询本机安装的数据库的详细版本信息：
　　注意PowerShell中的switch语句没有case。
　　Set-Location SQLSERVER:\SQL\BrooksPC
　　$svr=Get-ChildItem | Select Edition, VersionString
　　switch ($svr.VersionString)
　　{
　　"8.194.0"   {Write-Host$svr.VersionString"SQL Server 2000 RTM"$svr.Edition; break}
　　"8.384.0"   {Write-Host$svr.VersionString"SQL Server 2000 SP1"$svr.Edition; break}
　　"8.534.0"   {Write-Host$svr.VersionString"SQL Server 2000 SP2"$svr.Edition; break}
　　"8.760.0"   {Write-Host$svr.VersionString"SQL Server 2000 SP3"$svr.Edition; break}
　　"8.00.2039" {Write-Host$svr.VersionString"SQL Server 2000 SP4"$svr.Edition; break}
　　"9.00.1399" {Write-Host$svr.VersionString"SQL Server 2005 RTM"$svr.Edition; break}
　　"9.00.2047" {Write-Host$svr.VersionString"SQL Server 2005 SP1"$svr.Edition; break}
　　"9.00.3042" {Write-Host$svr.VersionString"SQL Server 2005 SP2"$svr.Edition; break}
　　"9.00.4035" {Write-Host$svr.VersionString"SQL Server 2005 SP3"$svr.Edition; break}
　　"10.0.1600" {Write-Host$svr.VersionString"SQL Server 2008 RTM"$svr.Edition; break}
　　"10.0.2531" {Write-Host$svr.VersionString"SQL Server 2008 SP1"$svr.Edition; break}
　　"10.50.1600.1" {Write-Host$svr.VersionString"SQL Server 2008 R2 RTM"$svr.Edition; break}
　　default    {Write-Host$svr.VersionString"version cannot be determined"$svr.Edition; break}
　　}
　　运行结果：


　　
　　小结：
　　本次对SQL Server 2008 R2有了一个大体的了解，练习了SQL Server驱动器下查询信息的方法。后续篇章将开始深入涉及SMO(SQL Server Management Object)，并测试其他PowerShell扩展，包含微软及其第三方的。
　　相信PowerShell会是对SQL语句的一个有益补充而逐渐被SQL Server DBA所接受。