监控sqlserver 2008 R2群集状态

declare @OutputTypevarchar(20)
declare @debug char(1)
declare @ForceRefreshchar(1)
declare @xp_cmdshell_available char(1)

set@OutputType='LIST'
set @Debug= 'Y'
set @ForceRefresh= 'Y'
set @xp_cmdshell_available = 'Y'

begin
set nocounton
declare@idint
declare @id2int
declare @Line varchar(300)
declare @CurrentCmd varchar(30)
declare@Cmd varchar(300)
declare@Node varchar(100)
declare @Resource varchar(100)
declare @Status varchar(20)
declare @i int
declare@Group varchar(100)
declare @Num varchar(10)
declare @ClusterID int
declare @tmp int
declare@ThisGroupsCurrentNode varchar(100)
declare @DoneClusterName char(1)
declare@RunningOnACluster char(1)
declare @Line2 varchar(300)
declare@SQLName sysname
declare @WindowsMachineName sysname
declare @PreferredServer sysname
declare@SQL varchar(4100)
declare @FailCnt int
declare @PreferredServers int
declare @z int
declare@CurrentSQLServerGroup sysname
declare @CurrentSQLServer sysname
declare@QuorumResource varchar(300)
declare @CreatedTempTables char(1)
declare @LoopGroup varchar(300)
declare@PreferredServerExists char(1)
if @Debug='Y'printconvert(varchar(30),getdate(),109) + ':'+object_name(@@procid)+'started.'
select @DoneClusterName='N'

--验证@OutputType类型
if lower(@OutputType)in ('q', 'quick','s', 'summary','sum') select@OutputType='Quick'
else iflower(@OutputType) in('l','list')select @OutputType = 'List'
else
begin
select @Cmd = 'Error in ' + object_name(@@procid) +
': @OutputType parameter must be ''Quick'' or ''List'', not ''' +
isnull(@OutputType,'<null>') + '''.'
raiserror 50001 @Cmd
return
end

if UPPER(@xp_cmdshell_available) in('Y','')
SET @xp_cmdshell_available = 'Y'
else
SET @xp_cmdshell_available = 'N'
if @Debug='Y'printconvert(varchar(30),getdate(),109) + ':CallingSpot_CheckCluster...'
select @FailCnt=0, @PreferredServers = 0

--检查xp_cmdshell是否开启

--declare@LocalTabletable(result int, ErrorCode int)
--insert into@LocalTable
--execute QS_SoSS58_CheckCmdShell
--if 1 <>(selectcount(*) from@LocalTablewhereresult = 1)

if (0 = (select value_in_use from sys.configurations where name = 'xp_cmdshell'))
set @xp_cmdshell_available = 'N'

select @RunningOnACluster = case when convert(int,serverproperty('IsClustered')) = 1 then 'Y' else 'N' end
set @SQLName = convert(sysname,serverproperty('ServerName'))

set @WindowsMachineName = convert(sysname,serverproperty('MachineName'))

if @Debug='Y'printconvert(varchar(30),getdate(),109) + ':创建临时表...'
create table #w3
(idintidentity,
ParentID int null,
Type varchar(20) null,
Name sysname null,
OtherData sysname null,
Status varchar(20) null,
Comment varchar(255) null,
PreferredServerExists char(1),
RunningOnPreferredServer char(1) null,
ControlsTheCurrentSQLServer char(1) null,
TakeOfflineCmdchar(1) null,
BringOnlineCmd char(1) null,
MoveGroupCmd char(1) null,
StopMSCSCmd char(1) null,
StartMSCSCmd char(1) null,
RunningOnServer sysname null,
Sequencersmallint null
)

create table #w4 (id int identity(1,1),group_name varchar(255)NULL)

create table #CmdOutput
(
idintidentity,
OutputLine varchar(255) null
)
create uniqueclusteredindex#PK_CmdOutputon #CmdOutput(id)
select @CreatedTempTables='Y'

if @RunningOnACluster='N'
begin
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': 非群集 - 退出...'
goto OutputData
end

if UPPER(@xp_cmdshell_available) = 'N'
begin
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': xp_cmdshell不可用 - 退出...'
goto OutputData
end

if @OutputType = 'List'
Select @Cmd = 'echo Cluster Name & cluster /ver | findstr "Name" & cluster node & cluster group & cluster resource & cluster /quorum'
else
select @Cmd = 'cluster node & cluster group & cluster resource'

if @Debug='Y'printconvert(varchar(30),getdate(),109) + ':运行系统的群集命令: ' + @Cmd
insert into#CmdOutput with (tablockx) (OutputLine)
exec xp_cmdshell@Cmd
if @Debug='Y'printconvert(varchar(30),getdate(),109) + ':系统命令结束...'
if @Debug='Y'select OutputLinefrom #CmdOutputorderby id
if @Debug='Y'printconvert(varchar(30),getdate(),109) + ':解释数据...'
select @CurrentCmd = 'none'

select @id = 0
while1=1
begin
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': - 开始获取下一条记录...'
select @id = min(id)
from #CmdOutput
where id > @id
and OutputLine not like '-------%'
and isnull(OutputLine,'') <> ''
and OutputLine <> char(13)
if @@rowcount = 0 or @id is null break
select @Line = replace(replace(OutputLine,char(13),' '), char(12), ' ')
from #CmdOutput
where id = @id
if @@rowcount = 0 break
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': - 获得下一条记录...'
select @Node = '', @Group = '', @Resource = '', @Status = '', @tmp = 0,
@Line2 = @Line
select @Line = replace(@Line, 'Online Pending', 'OnlinePending')
select @Line = replace(@Line, 'Offline Pending', 'OfflinePending')
if @Line like 'Listing status for%'
or @Line like 'Quorum Resource Name%'
begin
select @CurrentCmd = 'none'

end
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': @CurrentCmd = ' + @CurrentCmd
if @CurrentCmd <> 'none'
begin

if @CurrentCmd = 'clusterlist'
and @OutputType = 'List'
begin
ClusterList:
select @Line = LTRIM(RTRIM(SUBSTRING(@Line,14,999)))
insert into #w3 with (tablockx) (ParentID, Type, Name, Status, Sequencer)
select 0, @CurrentCmd, @Line, '', 10
select @CurrentCmd = 'none', @ClusterID = @@identity

insert into #w3 with (tablockx) (ParentID, Type, Name, Status, Sequencer)
select @ClusterID, 'Groups', 'Groups', '', 20
insert into #w3 with (tablockx) (ParentID, Type, Name, Status, Sequencer)
select @ClusterID, 'Servers', 'Servers', '', 60
select @DoneClusterName = 'Y'
end
if @CurrentCmd = 'nodelist'
begin
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': - ' + @CurrentCmd + ' text scan...'
select @Node = substring(@Line,1,charindex(' ', @Line)),
@Line = ltrim(substring(@Line, charindex(' ', @Line), 999))
select @Num = substring(@Line,1,charindex(' ', @Line)),
@Line = ltrim(substring(@Line, charindex(' ', @Line), 999))
select @Status = substring(@Line,1,charindex(' ', @Line))
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': - ' + @CurrentCmd + ' lookup...'
select @tmp = id from #w3
where Type = 'Servers'
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': - ' + @CurrentCmd + ' insert(1)...'
insert into #w3 with (tablockx) (ParentID, Type, Name, Status, Sequencer)
values (@tmp, 'Node', @Node, @Status, 70)
select @tmp = @@identity
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': - ' + @CurrentCmd + ' insert(2&3)...'
insert into #w3 with (tablockx) (ParentID, Type, Name, OtherData, Status, Sequencer)
values (@tmp, 'NodeGroups', 'Active Groups', @Node, '', 80)
end
if @CurrentCmd = 'grouplist'
begin
select @Line = LTRIM(RTRIM(REVERSE(@Line)))
select @Status = LTRIM(RTRIM(REVERSE(substring(@Line,1,charindex(' ', @Line))))),
@Line = ltrim(substring(@Line, charindex(' ', @Line), 999))
if @Status = 'Online' and REVERSE(LTRIM(RTRIM(SUBSTRING(@Line,1,9)))) = 'Partially'
select @Status = LTRIM(RTRIM(REVERSE(substring(@Line,1,charindex(' ', @Line))))) + ' ' + @Status,
@Line = ltrim(substring(@Line, charindex(' ', @Line), 999))
select @Node = LTRIM(RTRIM(REVERSE(substring(@Line,1,charindex(' ', @Line))))),
@Line = ltrim(substring(@Line, charindex(' ', @Line), 999))
select @Group = REVERSE(LTRIM(RTRIM(@Line)))
select @tmp = id from #w3
where Type = 'Groups'
insert into #w4 with (tablockx) (group_name) SELECT @Group
insert into #w3 with (tablockx) (ParentID, Type, Name, OtherData, Status, RunningOnServer, Sequencer)
select @tmp, 'Group', @Group, @Node, @Status, @Node, 30
select @tmp = @@identity
insert into #w3 with (tablockx) (ParentID, Type, Name, OtherData, Status, Sequencer)
select @tmp, 'GroupResources', 'Resources', @Group, '', 40
insert into #w3 with (tablockx) (ParentID, Type, Name, OtherData, Status, Sequencer)
select @tmp, 'PreferredServers', 'Preferred Servers', @Group, '', 45
select @tmp = id from #w3
where Type = 'NodeGroups'
and OtherData = @Node
if @@rowcount = 0 select @tmp = 0
insert into #w3 with (tablockx) (ParentID, Type, Name, OtherData, Status, Sequencer)
select @tmp, 'NodeGroup', @Group, @Node, @Status, 90
end

if @CurrentCmd = 'resourcelist'
and @OutputType = 'List'
begin
-- We need to parse this in reverse order
select @Line = ltrim(rtrim(reverse(@Line)))
select @Status = substring(@Line,1,charindex(' ', @Line)),
@Line = ltrim(substring(@Line, charindex(' ', @Line), 999))
select @Node = substring(@Line,1,charindex(' ', @Line)),
@Line = ltrim(substring(@Line, charindex(' ', @Line), 999))
select @id2 = 0
while 1 = 1
begin
select @id2 = id, @LoopGroup = RTRIM(LTRIM(group_name)) from #w4 where id = (select min(id) from #w4 where id > @id2)
if @@ROWCOUNT = 0
begin
SELECT @LoopGroup = ''
break
end
if @LoopGroup = RTRIM(LTRIM(REVERSE(SUBSTRING(@Line,1,DataLength(@LoopGroup)))))
begin
select @Group = @LoopGroup
break
end
end
SELECT @Line = ltrim(substring(@Line, DATALENGTH(@LoopGroup) + 1, 999))
select @Resource = @Line
select @Status = ltrim(rtrim(reverse(@Status))),
@Node = ltrim(rtrim(reverse(@Node))),
@Resource = ltrim(rtrim(reverse(@Resource)))
select @tmp = id from #w3
where Type = 'GroupResources'
and OtherData = @Group

if @@rowcount = 0 select @tmp = 0
insert into #w3 with (tablockx) (ParentID, Type, Name, Status, OtherData, RunningOnServer, Sequencer)
select @tmp, 'GroupResource', @Resource, @Status, @Group, @Node, 50
select @tmp = id from #w3
where Type = 'NodeGroup'
and Name = @Group
and OtherData = @Node
if @@rowcount = 0 select @tmp = 0
insert into #w3 with (tablockx) (ParentID, Type, Name, Status, OtherData, Sequencer)
select @tmp, 'NodeGroupRes', @Resource, @Status, substring(@Node + '\' + @Group,1,100), 100
end

if @CurrentCmd = 'quorum'
and @OutputType = 'List'
begin
select @QuorumResource = @Line
end
end
select @Line = @Line2
if @Line = 'Cluster Name'
begin
select @CurrentCmd = 'clusterlist'
end
if @Line like 'Node%Node%ID%Status%'
begin
if @DoneClusterName = 'N'
begin
Select @Line = 'CLUSTER'
select @DoneClusterName = 'Y'
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': 没有群集名用 - 使用默认值 ''' + @Line + '''...'
goto ClusterList
end
select @CurrentCmd = 'nodelist'
end
if @Line like 'Group%Node%Status%'
begin
select @CurrentCmd = 'grouplist'
end
if @Line like 'Resource%Group%Node%Status%'
begin
select @CurrentCmd = 'resourcelist'
end
if @Line like 'Quorum%Resource%Name%'
begin
select @CurrentCmd = 'quorum'
end

end

if @Debug='Y'printconvert(varchar(30),getdate(),109) + ':收集首选服务器...'
select @id = 0
while1=1
begin
delete from #CmdOutput with (tablockx)
select @Group = rtrim(Name), @id = id, @ThisGroupsCurrentNode = OtherData
from #w3
where Type = 'Group'
and id > @id
order by id desc
if @@rowcount = 0 break
select @tmp = id from #w3
where Type = 'PreferredServers'
and OtherData = @Group
if @@rowcount = 0 select @tmp = 0
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': 开始进程ID ' + convert(varchar(5), @tmp) + ' ...'
select @Cmd = 'cluster group "' + @Group + '" /listowners'
insert into #CmdOutput (OutputLine)
exec xp_cmdshell @Cmd

declare PScsr cursor for
select OutputLine
from #CmdOutput
where isnull(OutputLine,'') <> ''
and OutputLine <> char(13)
and OutputLine not like 'Preferred Owner Nodes%'
and OutputLine not like '--------%'
and OutputLine not like 'Listing preferred%'
order by id
open PScsr
select @z = 0
while 1 = 1
begin
fetch PScsr into @Cmd
if @@fetch_Status <> 0 break
select @z = @z + 1
insert into #w3 with (tablockx) (ParentID, Type, Name, OtherData, Status, Sequencer)
select @tmp, 'PreferredServer', replace(@Cmd,char(13),''), @Group, '', 50 + @z
end
close PScsr
deallocate PScsr
if @z = 0
begin
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ' : 没有首选的服务器组发现 "' + @Group + '" - 分配所有服务器...'
insert into #w3 with (tablockx) (ParentID, Type, Name, OtherData, Status, Sequencer, PreferredServerExists)
select @tmp, 'PreferredServer', @ThisGroupsCurrentNode, @Group, '', 51, 'N'
insert into #w3 with (tablockx) (ParentID, Type, Name, OtherData, Status, Sequencer, PreferredServerExists)
select @tmp, 'PreferredServer', Name, @Group, '', 52, 'N'
from #w3
where Type = 'Node'
and Name <> @ThisGroupsCurrentNode
end
else
begin
If @Debug = 'Y' print convert(varchar(30),getdate(),109) + ' ... 检查组"' + @Group + '"是否在首选服务器上运行'
select @tmp = 0
select @tmp = node.id
from #w3 curr,
#w3 node
where curr.Type = 'Group'
and curr.Name = @Group
and curr.Name = node.OtherData
and node.Type = 'PreferredServer'
and curr.OtherData = node.Name
select @PreferredServer = node.Name, @PreferredServerExists = node.PreferredServerExists
from #w3 node
where node.Type = 'PreferredServer'
and node.OtherData = @Group
order by id desc

if exists (select 1
from #w3
where Type = 'PreferredServer'
and OtherData = @Group
and id < @tmp)
begin
select @PreferredServers = @PreferredServers + 1
if @OutputType = 'List'
begin
If @Debug = 'Y' print convert(varchar(30),getdate(),109) + ' --- 未在首选的服务器上运行'
update #w3 with (tablockx)
set RunningOnPreferredServer = 'N',
Comment =
Case @PreferredServerExists
when 'N' then 'No preferred server has been set.'
else 'Warning: Not running on preferred server (' + isnull(@PreferredServer,'<null>') + ').'
end
where id = @id
and Status = 'Online'
update b
set RunningOnPreferredServer = a.RunningOnPreferredServer,
Comment = a.Comment
from #w3 a
join #w3 b
on a.id = @id
and b.Type = 'NodeGroup'
and b.Name = @Group
where a.id = @id
and b.Type = 'NodeGroup'
and b.Name = @Group
end
end
end
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': 完成进程ID'
end
select @CurrentSQLServerGroup=null
if @OutputType = 'List'
begin
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': 设置优选服务器状态...'
update ps
set Status = node.Status
from #w3 ps join #w3 node
on node.Type = 'Node'
and ps.Type = 'PreferredServer'
and node.Name = ps.Name
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': 收集当前的SQL Server依赖...'
select @id = 0
while 1 = 1
begin
delete from #CmdOutput with (tablockx)
select @Resource = rtrim(Name), @Group = rtrim(OtherData), @id = id
from #w3
where Type = 'GroupResource'
and id > @id
order by id desc
if @@rowcount = 0 break

select @Cmd = 'cluster resource "' + @Resource + '" /prop:type' +
' & ' +
'cluster resource "' + @Resource + '" /priv:name'
insert into #CmdOutput with (tablockx) (OutputLine)
exec xp_cmdshell @Cmd
if exists (select 1 from #CmdOutput
where OutputLine like '%Type%Network Name%')
begin
if exists (select 1 from #CmdOutput
where replace(OutputLine,char(13),'') + ' ' like '% ' + @WindowsMachineName + ' %')
begin
select @CurrentSQLServerGroup = @Group
break
end
end

end
select @CurrentSQLServer = null
select @CurrentSQLServer = OtherData
from #w3
where Type = 'NodeGroup'
and Name = @CurrentSQLServerGroup
if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': SQL Server 组是: ' + isnull(@CurrentSQLServerGroup,'<null>') +
',运行在节点: ' + isnull(@CurrentSQLServer,'<null>')

update #w3 with (tablockx)
set ControlsTheCurrentSQLServer = 'Y'
where Type = 'clusterlist'
or (Type in ('Group', 'NodeGroup') and Name in (@CurrentSQLServerGroup, @CurrentSQLServer))
or (Type in ('Node') and Name = @CurrentSQLServer)
or ((OtherData = @CurrentSQLServerGroup or OtherData like '%\' + @CurrentSQLServerGroup)
and Name not like '%SQL Server Agent%'
and Name not like '%SQL Server Fulltext%'
and Name not like '%MSDTC%')
update #w3 with (tablockx)
set ControlsTheCurrentSQLServer = 'N'
where ControlsTheCurrentSQLServer is null
update #w3 with (tablockx)
set TakeOfflineCmd= case when Type in ('Group', 'NodeGroup', 'GroupResource', 'NodeGroupRes') and Status in('Online', 'Partially', 'Pending') and ControlsTheCurrentSQLServer = 'N'
then 'Y'
else 'N'
end,
BringOnlineCmd= case when Type in ('Group', 'NodeGroup', 'GroupResource', 'NodeGroupRes') and Status in ('Offline', 'Failed', 'Partially', 'Partially Online') and ControlsTheCurrentSQLServer = 'N'
then 'Y'
else 'N'
end,
MoveGroupCmd= case when Type in ('Group', 'NodeGroup') and ControlsTheCurrentSQLServer = 'N'
then 'Y'
else 'N'
end,
StopMSCSCmd= 'N',
StartMSCSCmd= 'N'

update #w3 with (tablockx)
set Comment = '没有可用的群集节点.'
where Type in ('Node', 'PreferredServer')
and isnull(Status, '') <> 'Up'
and isnull(Comment,'') = ''

update #w3 with (tablockx)
set Comment = '警告: 群集 ' +
case when Type like '%Res%' then '资源'
when Type like '%Group%' then '组'
else Type
end + ' offline.'
where Status = 'Offline'
and isnull(Comment,'') = ''
update #w3 with (tablockx)
set Comment = '警告: 群集组包括一些脱机资源.'
where Status like '%Partial%'
and isnull(Comment,'') = ''
update #w3 with (tablockx)
set Comment = '群集资源失败.'
where Status like '%Failed%'
and isnull(Comment,'') = ''
update #w3 with (tablockx)
set Comment = '<== 仲裁资源',
TakeOfflineCmd = 'N'
where @QuorumResource like Name + '%'
and isnull(Comment,'') = ''
update #w3 with (tablockx)
set Comment = '<== 当前的SQL Server组'
where Name = @CurrentSQLServerGroup
and isnull(Comment,'') = ''
end

OutputData:
if @Debug='Y'printconvert(varchar(30),getdate(),109) + ':输出数据...'

if @OutputType = 'List'
begin

if @RunningOnACluster = 'N'
begin
insert into #w3 with (tablockx) (ParentID, Type, Name, Status, Comment)
select 0, 'Msg', '非群集', '', 'SQL Server 没有运行在一个群集服务器上!'
end
else if UPPER(@xp_cmdshell_available) = 'N'
begin
insert into #w3 with (tablockx) (ParentID, Type, Name, Status, Comment)
select 0, 'Msg', '非群集', '', '不能使用xp_cmdshell. 没有数据可用!'
end

select idas 'ID',
ParentIDas 'ParentID',
Typeas 'Type',
Nameas 'Name',
replace(replace(
isnull(case Status when 'Partially' then 'Partially Online' else Status end,''),
'OnlinePending', 'Online Pending'), 'OfflinePending', 'Offline Pending')as 'Status',
isnull(Comment,'')as 'Comment',
isnull(OtherData,'')as 'OtherData',
case when Type in ('Group', 'NodeGroup') and RunningOnPreferredServer = 'N' then '警告'
when Status in ('Up', 'Online', '') then 'OK'
when Status = 'Offline' then '警告'
when Status like 'Partially%' then '警告'
when Status like '%Pending%' then '警告'
when Status in('Down', 'Failed') then '错误'
else 'Unknown'

endas 'IconFlag',

isnull(TakeOfflineCmd,'N') as 'TakeOfflineCmd',

isnull(BringOnlineCmd,'N') as 'BringOnlineCmd',

isnull(MoveGroupCmd,'N') as 'MoveGroupCmd',

isnull(StopMSCSCmd,'N') as 'StopMSCSCmd',

isnull(StartMSCSCmd,'N') as 'StartMSCSCmd',

isnull(RunningOnPreferredServer,'N')as 'RunningOnPreferredServer',

ControlsTheCurrentSQLServeras 'ControlsTheCurrentSQLServer',

isnull(RunningOnServer,'') as 'RunningOnServer',

case when Type in ('PreferredServer', 'Node') then

case when Status in ('Up', 'Online') then 'Y'
else 'N'
end
else 'N'
endas 'CanAcceptMove'
from #w3
order by Sequencer, Name
end
else
begin
if @RunningOnACluster = 'N' goto Output2
select @FailCnt = count(*)
from #w3
where Type = 'Node'
and Status <> 'Up'
Output2:

select 1 as 'ID',
case @RunningOnACluster when 'Y' then 1 else 0 end as 'IsClusteredServer',
isnull(@FailCnt,0) as 'DownServerCnt',
isnull(@PreferredServers,0) as 'PreferredServers'
end
setnocounton
if @Debug='Y'printconvert(varchar(30),getdate(),109) + ':开始清除...'
if @CreatedTempTables='Y'
begin
drop table #w3
drop table #CmdOutput
drop table #w4
end
if @Debug='Y'printconvert(varchar(30),getdate(),109) + ':'+object_name(@@procid)+'ended.'

end