Hi Friends,
The article what I am going to share is by far the most useful one as far as myself is concerned.
Well how many of us would get an alert when the clustered instance gets failed over to another node or clustered instance gets restarted on same node.
This post would help to configure those. If you search on the internet most of us would get how to trigger an alert from the same instance but not from your CMS the problem with this approach is you need to enable Database mail on each and every server. If you have enabled CMS then we can capture all the information in to one place and from there we can deliver an alert mechanism.
Again use this as reference as I have not passed port number as parameter .
Below is the powershell code where you need to replace the server name given below with your CMS server
param
(
[int]$Start,
[int]$End,
[switch]$verbose,
[switch]$debug
)
function main()
{
# Load Microsoft.SQLServer.SMO Library
[reflection.assembly]::
[reflection.assembly]::
[reflection.assembly]::
if ($verbose) {$VerbosePreference = "Continue"}
if ($debug) {$DebugPreference = "Continue"}
$SqlConnection = New-Object System.Data.SqlClient.
$SqlConnection.
$SqlCmd = New-Object System.Data.SqlClient.
$SqlCmd.CommandText = "select vir_name from tbl_cluster_failover_check"
$SqlConnection.open()
$DelayInMinutes =30
$SqlCmd.Connection = $SqlConnection
$dr = $sqlcmd.executereader()
while ($dr.Read()) {
$Instance_Name = $dr.getvalue(0)
write-host $Instance_Name
#$Instance_Name | OUT-FILE E:\Errorlog.txt -append
SQLServiceRestartDate $Instance_Name
}
$dr.close()
$SqlConnection.close()
}
function SQLServiceRestartDate ($Instance_Name)
{
Trap { "$Instance_Name $($_.Exception.Message)" |format-table | OUT-FILE D:\RestartLog.txt -append ; Continue}
$serverInstance = $Instance_Name
$SqlConnection = New-Object System.Data.SqlClient.
$SqlConnection.
$SqlConnection.open()
$RestartDatecmd ="select convert(varchar(max),
trap [Exception]
{
"$Instance_Name $($_.Exception.Message)" |format-table | OUT-FILE D:\RestartLog.txt -append ; Continue
}
if($SqlConnection.state -eq “Open”) {
$RestartDateStatus = New-Object System.Data.SqlClient.
$RestartDateStatus.CommandText = $RestartDatecmd
$RestartDateStatus.Connection = $SqlConnection
$dr= $RestartDateStatus.
while ($dr.Read()) {
$currenthost= $dr.getvalue(0)
$RestartDate= $dr.getvalue(1)
$CurrentDate= $dr.getvalue(2)
InsertDataToCetrtalServer $Instance_Name $currenthost $RestartDate $CurrentDate
}
$SqlConnection.close()
}
}
function InsertDataToCetrtalServer ($Instance_Name, $currenthost, $RestartDate, $CurrentDate ) {
Trap { "$Instance_Name $($_.Exception.Message)" |format-table | OUT-FILE D:\\RestartLog.txt -append ; Continue}
$SqlConnection = New-Object System.Data.SqlClient.
$SqlConnection.
$SqlCmdInsert = New-Object System.Data.SqlClient.
$SqlConnection.Open()
$Query = " INSERT INTO master.dbo.tbl_cluster_
('$Instance_Name','$
$SqlCmdInsert = New-Object System.Data.SqlClient.
$SqlCmdInsert.CommandText = $Query
$SqlCmdInsert.Connection = $SqlConnection
$SqlCmdInsert.executenonquery(
$SqlConnection.close()
}
main
The article what I am going to share is by far the most useful one as far as myself is concerned.
Well how many of us would get an alert when the clustered instance gets failed over to another node or clustered instance gets restarted on same node.
This post would help to configure those. If you search on the internet most of us would get how to trigger an alert from the same instance but not from your CMS the problem with this approach is you need to enable Database mail on each and every server. If you have enabled CMS then we can capture all the information in to one place and from there we can deliver an alert mechanism.
Again use this as reference as I have not passed port number as parameter .
Below is the powershell code where you need to replace the server name given below with your CMS server
param
(
[int]$Start,
[int]$End,
[switch]$verbose,
[switch]$debug
)
function main()
{
# Load Microsoft.SQLServer.SMO Library
[reflection.assembly]::
[reflection.assembly]::
[reflection.assembly]::
if ($verbose) {$VerbosePreference = "Continue"}
if ($debug) {$DebugPreference = "Continue"}
$SqlConnection = New-Object System.Data.SqlClient.
$SqlConnection.
$SqlCmd = New-Object System.Data.SqlClient.
$SqlCmd.CommandText = "select vir_name from tbl_cluster_failover_check"
$SqlConnection.open()
$DelayInMinutes =30
$SqlCmd.Connection = $SqlConnection
$dr = $sqlcmd.executereader()
while ($dr.Read()) {
$Instance_Name = $dr.getvalue(0)
write-host $Instance_Name
#$Instance_Name | OUT-FILE E:\Errorlog.txt -append
SQLServiceRestartDate $Instance_Name
}
$dr.close()
$SqlConnection.close()
}
function SQLServiceRestartDate ($Instance_Name)
{
Trap { "$Instance_Name $($_.Exception.Message)" |format-table | OUT-FILE D:\RestartLog.txt -append ; Continue}
$serverInstance = $Instance_Name
$SqlConnection = New-Object System.Data.SqlClient.
$SqlConnection.
$SqlConnection.open()
$RestartDatecmd ="select convert(varchar(max),
trap [Exception]
{
"$Instance_Name $($_.Exception.Message)" |format-table | OUT-FILE D:\RestartLog.txt -append ; Continue
}
if($SqlConnection.state -eq “Open”) {
$RestartDateStatus = New-Object System.Data.SqlClient.
$RestartDateStatus.CommandText = $RestartDatecmd
$RestartDateStatus.Connection = $SqlConnection
$dr= $RestartDateStatus.
while ($dr.Read()) {
$currenthost= $dr.getvalue(0)
$RestartDate= $dr.getvalue(1)
$CurrentDate= $dr.getvalue(2)
InsertDataToCetrtalServer $Instance_Name $currenthost $RestartDate $CurrentDate
}
$SqlConnection.close()
}
}
function InsertDataToCetrtalServer ($Instance_Name, $currenthost, $RestartDate, $CurrentDate ) {
Trap { "$Instance_Name $($_.Exception.Message)" |format-table | OUT-FILE D:\\RestartLog.txt -append ; Continue}
$SqlConnection = New-Object System.Data.SqlClient.
$SqlConnection.
$SqlCmdInsert = New-Object System.Data.SqlClient.
$SqlConnection.Open()
$Query = " INSERT INTO master.dbo.tbl_cluster_
('$Instance_Name','$
$SqlCmdInsert = New-Object System.Data.SqlClient.
$SqlCmdInsert.CommandText = $Query
$SqlCmdInsert.Connection = $SqlConnection
$SqlCmdInsert.executenonquery(
$SqlConnection.close()
}
main
Below is the stored procedure which holds the logic for the alerting mechanism. So what you need to do is Fill up in that tables are virtual server name, current host name and restart date.
CREATE PROC [dbo].[usp_cluster_fail_check]
AS
BEGIN
SET NOCOUNT ON
DECLARE @VNAME VARCHAR(200)
DECLARE @PHOST VARCHAR(200)
DECLARE @HOST VARCHAR(200)
DECLARE @body1 NVARCHAR(MAX)
declare @CurRestartDate datetime,@PreRestartDate datetime
DECLARE clu_cursor CURSOR FOR
select a.vir_name,a.hostname as PREV_HOSTNAME,b.hostname,b.
join tbl_cluster_failover_check2 b on a.vir_name=b.vir_name and a.RestartDate<>b.RestartDate
OPEN clu_cursor
FETCH NEXT FROM clu_cursor INTO @VNAME,@PHOST,@HOST,@
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PHOST<>@HOST
BEGIN
PRINT @PHOST
PRINT @HOST
--PRINT @HOST
DECLARE @MSG varchar(7000)
SET @MSG= 'Cluster Instance '+ @VNAME + ' Failed Over from '+@PHOST+ ' to '+@HOST + ' at '+CONVERT(VARCHAR(20),getdate(
EXEC msdb..sp_send_dbmail
@profile_name='xyxx',
@recipients = 'xxxxxxx',
@subject = '**** - Critical - Cluster Failover - Alert****',
@body = @MSG
--PRINT @MSG
update tbl_cluster_failover_check set hostname=@host where vir_name=@VNAME
update tbl_cluster_failover_check set RestartDate=@CurRestartDate where vir_name=@VNAME
END
else if @CurRestartDate<>@
begin
SET @MSG= 'Cluster Instance '+ @VNAME + ' Restarted on '+@HOST + ' at '+CONVERT(VARCHAR(20),@
EXEC msdb..sp_send_dbmail
@profile_name='xyxyx',
@recipients = 'xxxxxxx',
@subject = '**** - Critical - Cluster SQL Restart - Alert****',
@body = @MSG
update tbl_cluster_failover_check set RestartDate=@CurRestartDate
where vir_name=@VNAME
end
FETCH NEXT FROM clu_cursor INTO @VNAME,@PHOST,@HOST ,@CurRestartDate,@
END
CLOSE clu_cursor
DEALLOCATE clu_cursor
END
AS
BEGIN
SET NOCOUNT ON
DECLARE @VNAME VARCHAR(200)
DECLARE @PHOST VARCHAR(200)
DECLARE @HOST VARCHAR(200)
DECLARE @body1 NVARCHAR(MAX)
declare @CurRestartDate datetime,@PreRestartDate datetime
DECLARE clu_cursor CURSOR FOR
select a.vir_name,a.hostname as PREV_HOSTNAME,b.hostname,b.
join tbl_cluster_failover_check2 b on a.vir_name=b.vir_name and a.RestartDate<>b.RestartDate
OPEN clu_cursor
FETCH NEXT FROM clu_cursor INTO @VNAME,@PHOST,@HOST,@
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PHOST<>@HOST
BEGIN
PRINT @PHOST
PRINT @HOST
--PRINT @HOST
DECLARE @MSG varchar(7000)
SET @MSG= 'Cluster Instance '+ @VNAME + ' Failed Over from '+@PHOST+ ' to '+@HOST + ' at '+CONVERT(VARCHAR(20),getdate(
EXEC msdb..sp_send_dbmail
@profile_name='xyxx',
@recipients = 'xxxxxxx',
@subject = '**** - Critical - Cluster Failover - Alert****',
@body = @MSG
--PRINT @MSG
update tbl_cluster_failover_check set hostname=@host where vir_name=@VNAME
update tbl_cluster_failover_check set RestartDate=@CurRestartDate where vir_name=@VNAME
END
else if @CurRestartDate<>@
begin
SET @MSG= 'Cluster Instance '+ @VNAME + ' Restarted on '+@HOST + ' at '+CONVERT(VARCHAR(20),@
EXEC msdb..sp_send_dbmail
@profile_name='xyxyx',
@recipients = 'xxxxxxx',
@subject = '**** - Critical - Cluster SQL Restart - Alert****',
@body = @MSG
update tbl_cluster_failover_check set RestartDate=@CurRestartDate
where vir_name=@VNAME
end
FETCH NEXT FROM clu_cursor INTO @VNAME,@PHOST,@HOST ,@CurRestartDate,@
END
CLOSE clu_cursor
DEALLOCATE clu_cursor
END
using this TSQL and powershell Logic we will achieve the desired results and the output will be like this. So schedule the job based on your needs as I am following the polling technique.
Comments