sqlsever Java监控_监控sqlserver 2008 R2群集状态

declare @OutputType varchar(20)

declare @debug char(1)

declare @ForceRefresh char(1)

declare  @xp_cmdshell_available char(1)

set @OutputType='LIST'

set  @Debug= 'Y'

set  @ForceRefresh= 'Y'

set  @xp_cmdshell_available = 'Y'

begin

set nocount on

declare @id int

declare @id2 int

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' print convert(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 if lower(@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,'') + '''.'

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' print convert(varchar(30),getdate(),109) + ': Calling Spot_CheckCluster...'

select @FailCnt = 0, @PreferredServers = 0

--检查xp_cmdshell是否开启

--declare @LocalTable table(result int, ErrorCode int)

--insert into @LocalTable

-- execute QS_SoSS58_CheckCmdShell

--if 1 <> (select count(*) from @LocalTable where result = 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' print convert(varchar(30),getdate(),109) + ': 创建临时表...'

create table #w3

(id int identity,

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,

TakeOfflineCmd char(1) null,

BringOnlineCmd char(1) null,

MoveGroupCmd char(1) null,

StopMSCSCmd char(1) null,

StartMSCSCmd char(1) null,

RunningOnServer sysname null,

Sequencer smallint null

)

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

create table #CmdOutput

(

id int identity,

OutputLine varchar(255) null

)

create unique clustered index #PK_CmdOutput on #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' print convert(varchar(30),getdate(),109) + ': 运行系统的群集命令: ' + @Cmd

insert into #CmdOutput with (tablockx) (OutputLine)

exec xp_cmdshell @Cmd

if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': 系统命令结束...'

if @Debug = 'Y' select OutputLine from #CmdOutput order by id

if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': 解释数据...'

select @CurrentCmd = 'none'

select @id = 0

while 1 = 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' print convert(varchar(30),getdate(),109) + ': 收集首选服务器...'

select @id = 0

while 1 = 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,'') + ').'

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,'') +

',运行在节点: ' + isnull(@CurrentSQLServer,'')

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' print convert(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 id   as 'ID',

ParentID   as 'ParentID',

Type   as 'Type',

Name  as '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'

end     as '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',

ControlsTheCurrentSQLServer  as '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'

end       as '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

set nocount on

if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': 开始清除...'

if @CreatedTempTables = 'Y'

begin

drop table #w3

drop table #CmdOutput

drop table  #w4

end

if @Debug = 'Y' print convert(varchar(30),getdate(),109) + ': ' + object_name(@@procid) + ' ended.'

end