|
?
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,'<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'?print?convert(varchar(30),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),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),109) + ': 非群集 - 退出...'
?goto OutputData
end
if UPPER(@xp_cmdshell_available) = 'N'
begin
?if @Debug = 'Y' print convert(varchar(30),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),109) + ':?运行系统的群集命令: ' + @Cmd
insert into?#CmdOutput with (tablockx) (OutputLine)
? exec xp_cmdshell?@Cmd
if @Debug?=?'Y'?print?convert(varchar(30),109) + ':?系统命令结束...'
if @Debug?=?'Y'?select OutputLine?from #CmdOutput?order?by id
if @Debug?=?'Y'?print?convert(varchar(30),109) + ':?解释数据...'
select @CurrentCmd = 'none'
select @id = 0
while?1?=?1
begin
?if @Debug = 'Y' print convert(varchar(30),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),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),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,Sequencer)
????select @ClusterID,'Groups',20
???insert into #w3 with (tablockx) (ParentID,'Servers',60
???select @DoneClusterName = 'Y'
??end
??if @CurrentCmd = 'nodelist'
??begin
???if @Debug = 'Y' print convert(varchar(30),109) + ':?? - ' + @CurrentCmd + ' text scan...'
???select @Node = substring(@Line,1,charindex(' ',@Line)),
????@Line = ltrim(substring(@Line,@Line),999))
???select @Num = substring(@Line,999))
???select @Status = substring(@Line,@Line))
???if @Debug = 'Y' print convert(varchar(30),109) + ':?? - ' + @CurrentCmd + ' lookup...'
???select @tmp = id from #w3
???where Type = 'Servers'
???if @Debug = 'Y' print convert(varchar(30),109) + ':?? - ' + @CurrentCmd + ' insert(1)...'
???insert into #w3 with (tablockx) (ParentID,Sequencer)
???values (@tmp,'Node',@Node,@Status,70)
???select @tmp = @@identity
???if @Debug = 'Y' print convert(varchar(30),109) + ':?? - ' + @CurrentCmd + ' insert(2&3)...'
???insert into #w3 with (tablockx) (ParentID,OtherData,'NodeGroups','Active Groups',80)
??end
??if @CurrentCmd = 'grouplist'
??begin
???select @Line = LTRIM(RTRIM(REVERSE(@Line)))
???select @Status = LTRIM(RTRIM(REVERSE(substring(@Line,@Line))))),999))
???if @Status = 'Online' and REVERSE(LTRIM(RTRIM(SUBSTRING(@Line,9)))) = 'Partially'
????select @Status = LTRIM(RTRIM(REVERSE(substring(@Line,@Line))))) + ' ' + @Status,999))
???select @Node = LTRIM(RTRIM(REVERSE(substring(@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,RunningOnServer,Sequencer)
???select @tmp,'Group',@Group,30
???select @tmp = @@identity
???insert into #w3 with (tablockx) (ParentID,'GroupResources','Resources',40
???insert into #w3 with (tablockx) (ParentID,'PreferredServers','Preferred Servers',45
???select @tmp = id from #w3
???where Type = 'NodeGroups'
????and OtherData = @Node
???if @@rowcount = 0 select @tmp = 0
???insert into #w3 with (tablockx) (ParentID,'NodeGroup',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,999))
???select @Node = substring(@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,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,'GroupResource',@Resource,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,'NodeGroupRes',substring(@Node + '' + @Group,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),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),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),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,Sequencer)
??select @tmp,'PreferredServer',replace(@Cmd,''),50 + @z
?end
?close PScsr
?deallocate PScsr
?if @z = 0
?begin
??if @Debug = 'Y' print convert(varchar(30),109) + '??????? : 没有首选的服务器组发现 "' + @Group + '" - 分配所有服务器...'
??insert into #w3 with (tablockx) (ParentID,Sequencer,PreferredServerExists)
??select @tmp,@ThisGroupsCurrentNode,51,'N'
??insert into #w3 with (tablockx) (ParentID,52,'N'
??from #w3
??where Type = 'Node'
??and Name <> @ThisGroupsCurrentNode
?end
?else
?begin
??If @Debug = 'Y' print convert(varchar(30),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),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),109) + ': 完成进程ID'
end
select @CurrentSQLServerGroup?=?null
if @OutputType = 'List'
begin
?if @Debug = 'Y' print convert(varchar(30),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),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,'') + '??? ' 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),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','NodeGroupRes') and Status in('Online','Partially','Pending') and ControlsTheCurrentSQLServer = 'N'
??????then 'Y'
??????else 'N'
?????end,
??BringOnlineCmd?= case when Type in ('Group','NodeGroupRes') and Status in ('Offline','Failed','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),109) + ':?输出数据...'
if @OutputType = 'List'
begin
?if @RunningOnACluster = 'N'
?begin
??insert into #w3 with (tablockx) (ParentID,Comment)
??select 0,'Msg','非群集','SQL Server 没有运行在一个群集服务器上!'
?end
?else if UPPER(@xp_cmdshell_available) = 'N'
?begin
??insert into #w3 with (tablockx) (ParentID,'不能使用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),109) + ':?开始清除...'
if @CreatedTempTables?=?'Y'
begin
?drop table #w3
?drop table #CmdOutput
?drop table? #w4
end
if @Debug?=?'Y'?print?convert(varchar(30),109) + ':?'?+?object_name(@@procid)?+?'?ended.'
end
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
