SQLServer用powershell实现多台服务器批量执行SQL脚本
在运维工作中，会遇到很多重复性的操作。对于单台服务器重复性的工作，可以用job实现定期的处理；而对于多台服务器相同的需求，我们就可以利用powershell来进行批量的操作，对多台服务器进行批量相同的操作。本文重点分析这种方案的实现过程，而不纠结于对每台服务器具体执行的SQL脚本，所以在例子中以简单的SQL脚本为例：在中心服务器上执行一个powershell脚本，即可以收集所有服务器的信息。对类似于定期出report的任务，还可以将该powershell脚本设置成计划任务。
首先将需要执行批量操作的服务器放在文本文件Serverlist.txt中：
中心服务器：10.120.100.101
10.120.100.1
10.120.100.2
10.120.100.3
以下所有创建的文件应该和Serverlist.txt放在一个文件夹中！
一.编写SQL脚本实现需求，并保存.SQL文件：
脚本1-(脚本文件名1.Create_DB&Table.sql)：在每台服务器创建收集数据的数据库和表
--默认设置创建用于收集信息的专用数据库
CREATE DATABASE [Info_stats]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Info_stats', FILENAME = N'D:\SQLServer\Data\Info_stats.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Info_stats_log', FILENAME = N'D:\SQLServer\Data\Info_stats_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
--在该数据库中创建本例子中需要用到的表
Use [Info_stats]；
Create table DBName
(Servername nvarchar(100),
DBName nvarchar(100))
脚本2-(脚本文件名2.Select_DBName.sql)：查询所有用户数据库名字并存放在该服务器上的[Info_stats]-DBName表中
select name from sysdatabases where name not in(N'master', N'model', N'msdb', N'tempdb', N'distribution', N'DWDiagnostics', N'DWConfiguration',N'DWQueue', N'resource',N'ReportServer',N'ReportServerTempDB')
脚本3-(脚本文件名3.Copy Info to Centre.sql)：将查询出的该服务器的信息同步到中心服务器：
Set nocount on;
use [Info_stats];
truncate table [Info_stats].[dbo].[DBName];
insert into [Info_stats].[dbo].[DBName]
select * from
(
select top 1* from
OPENDATASOURCE(
'SQLOLEDB',
'Data Source=10.120.100.1;Integrated Security=SSPI'
).[Info_stats].[dbo].[DBName] order by Check_Date desc) c
union
select * from
(
select top 1* from
OPENDATASOURCE(
'SQLOLEDB',
'Data Source=10.120.100.2;Integrated Security=SSPI'
).[Info_stats].[dbo].[DBName] order by Check_Date desc) c
union
(
select * from (
select top 1* from
OPENDATASOURCE(
'SQLOLEDB',
'Data Source=10.120.100.3;Integrated Security=SSPI'
).[Info_stats].[dbo].[DBName] order by Check_Date desc)z)
Set nocount off;
注意：在中心服务器上需要打开‘Ad Hoc Distributed Queries’的选项，才可以使用OPENDATASOURCE来同步查询结果到中心服务器
二.开始powershell脚本的准备：
1.创建调用(1.Create_DB&Table.sql)的powershell脚本，用于第一次执行时使用：
脚本名：Create_DB&Table.ps1
$PSScriptRoot = Split-Path -Parent $MyInvocation.MyCommand.Definition
$Serverlist="$PSScriptRoot\Serverlist.txt"
$Create_table="$PSScriptRoot\1.Create_DB&Table.sql"
Foreach ($server in GC $Serverlist) {
Invoke-Sqlcmd -ServerInstance $server -InputFile $Create_table
}
2.创建调用(2.Select_DBName.sql)的powershell脚本，用于收集数据：
脚本名：QueryData.ps1
$PSScriptRoot = Split-Path -Parent $MyInvocation.MyCommand.Definition
$Serverlist="$PSScriptRoot\Serverlist.txt"
$ITSPCheck="$PSScriptRoot\2.Select_DBName.sql"
Foreach ($server in GC $Serverlist) {
Invoke-Sqlcmd -ServerInstance $server -InputFile $ITSPCheck
}
3.创建调用(3.Copy Info to Centre.sql)的powershell脚本，用于整合数据到中心服务器：
脚本名：IntegrateResult.ps1
$PSScriptRoot = Split-Path -Parent $MyInvocation.MyCommand.Definition
$IntegrateResult="$PSScriptRoot\3.Copy Info to Centre.sql"
$Selfcheck="$PSScriptRoot\2.Select_DBName.sql"
Invoke-Sqlcmd -ServerInstance $env:COMPUTERNAME -InputFile $IntegrateResult
Invoke-Sqlcmd -ServerInstance $env:COMPUTERNAME -InputFile $Selfcheck
4.创建统筹powershell脚本，来按步骤调用以上powershell脚本：
脚本名：ExecPs.ps1
$PSScriptRoot = Split-Path -Parent $MyInvocation.MyCommand.Definition
powershell.exe "$PSScriptRoot\QueryData.ps1"
powershell.exe "$PSScriptRoot\IntegrateResult.ps1"
第一次执行时，会多一个创建数据库和表的步骤：
脚本名：ExecPs_FirstTime.ps1
$PSScriptRoot = Split-Path -Parent $MyInvocation.MyCommand.Definition
powershell.exe "$PSScriptRoot\Create_DB&Table.ps1"
powershell.exe "$PSScriptRoot\QueryData.ps1"
powershell.exe "$PSScriptRoot\IntegrateResult.ps1"
第一次执行ExecPs_FirstTime.ps1，之后执行ExecPs.ps1便可收集信息到中心服务器，并且旧数据会保留在每个节点服务器上，中心服务器通过判断对最新的数据进行收集：
对于其他类似的需求，也可以根据本例进行扩展。在中心服务器上右键运行ExecPs.ps1就可以得到所有节点服务器的信息，非常方便！
