转至:http://www.cnblogs.com/SameZhao/p/4743692.html
有时候,当我们并没有合适的第三方工具(大部分需要付费)去管理多台数据库服务器,那么如何做最省力、省心呢?!Powershell一个强大的工具,可以很方便帮到我们处理日常的数据库维护工作 。简单的几步搞定,下面介绍一个简单例子便于大家理解:
1,创建一个文件夹,如 D:\ExecScriptOnMultiServer
2,创建一个文本文件ServerList.txt,将所有需要管理的数据库实例名写到里面,格式如下:
SQLInstanceName1
SQLInstanceName2
SQLInstanceName3
...
然后保存到可以访问到的目录,如D:\ExecScriptOnMultiServer\ServerList.txt
3,将下面的Powershell脚本保存到目录 D:\ExecScriptOnMultiServer\ExecuteQueryOnMultiServers.ps1
##Save the below powershell script in folder "D:\ExecScriptOnMultiServer\ExecuteQueryOnMultiServers.ps1" $QueryPath= "D:\ExecScriptOnMultiServer\SQLQuery.sql" $OutputFile = "D:\ExecScriptOnMultiServer\QueryOutput.txt" $ExecuteQuery= Get-Content -path $QueryPath | out-string "Results -- > `r`n`r`n" > $OutputFile FOREACH($server in GC "D:\ExecScriptOnMultiServer\ServerList.txt") { $server "---------------------------------------------------------------------------------------------------------" >> $OutputFile $server >> $OutputFile "---------------------------------------------------------------------------------------------------------" >> $OutputFile invoke-sqlcmd -ServerInstance $server -query $ExecuteQuery -querytimeout 65534 | ft -autosize | out-string -width 4096 >> $OutputFile }
4,将你要在上述ServerList文件中的服务器实例执行的SQL语句,可先写到目录文件D:\ExecScriptOnMultiServer\SQLQuery.sql
例如:我要对所有实例下所有数据的的版本、SP和群集节点等信息收集,如下代码:
declare @Nodes Varchar(100) set @Nodes='' if (SERVERPROPERTY('IsClustered') = 1) begin select @Nodes=@Nodes+ NodeName +',' from sys.dm_os_cluster_nodes order by NodeName set @Nodes=substring(@Nodes,0,LEN(@Nodes)) select @Nodes as HostName, SQLInstanceName = @@SERVERNAME, 'Yes' as IsClustered, CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(50)) As ActiveNode, SERVERPROPERTY('edition') As SQLEdition, Case when cast(serverproperty('productversion') as varchar) like '8.%' then 'SQL2000' when cast(serverproperty('productversion') as varchar) like '9.%' then 'SQL2005' when cast(serverproperty('productversion') as varchar) like '10.0%' then 'SQL2008' when cast(serverproperty('productversion') as varchar) like '10.50.%' then 'SQL2008R2' when cast(serverproperty('productversion') as varchar) like '11.%' then 'SQL2012' when cast(serverproperty('productversion') as varchar) like '12.%' then 'SQL2014' ELSE 'SQL7.0' END +' '+ cast(SERVERPROPERTY('productlevel') as varchar(50))+' ('+ cast(SERVERPROPERTY('productversion') as varchar(50)) + ')' as SQLVersion end else begin select @Nodes=CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(50)) select @Nodes as HostName, SQLInstanceName = @@SERVERNAME, 'No' as IsClustered, SERVERPROPERTY('edition') As SQLEdition, Case when cast(serverproperty('productversion') as varchar) like '8.%' then 'SQL2000' when cast(serverproperty('productversion') as varchar) like '9.%' then 'SQL2005' when cast(serverproperty('productversion') as varchar) like '10.0%' then 'SQL2008' when cast(serverproperty('productversion') as varchar) like '10.50.%' then 'SQL2008R2' when cast(serverproperty('productversion') as varchar) like '11.%' then 'SQL2012' when cast(serverproperty('productversion') as varchar) like '12.%' then 'SQL2014' ELSE 'SQL7.0' END +' '+ cast(SERVERPROPERTY('productlevel') as varchar(50))+' ('+ cast(SERVERPROPERTY('productversion') as varchar(50)) + ')' as SQLVersion end
5,开启Window PowerShellISE程序,注意要用管理员身份启动,打开Powershell脚本ExecuteQueryOnMultiServers.ps1,点击执行。
执行后结果保存如代码中标注,位置在D:\ExecScriptOnMultiServer\QueryOutput.txt
6,或者直接在PowerShell命令行执行,
PS C:\>D:\ExecScriptOnMultiServer\ExecuteQueryOnMultiServers.ps1