September 28, 2017 at 9:04 am
Hi Anyone know how to get alerted via email if the SQL server is not in the preferred owner in clustering?
If you have any T-SQL script that does check, that would be great?
September 28, 2017 at 9:50 am
You'll need to make use of Powershell on the Windows Cluster level, as that is where preferred nodes are set, and build you alerting around it.
https://technet.microsoft.com/en-us/library/ee460989.aspx - this will retrieve you OwnerNode for a resource or group.
Alternatively, you could query the SQL cluster instance every x min by using select SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
and wrap it around a script to alert, example:
IF
(
select SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
as CurrentNode
) > 'Mypreferrednodename'
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = 'emailprofile',
@recipients = 'DBA@mail.com',
@subject = 'Preferred Node has Changed',
@body_format='HTML',
@query_result_header=1,
@execute_query_database='master',
@query =
'select SERVERPROPERTY(''ComputerNamePhysicalNetBIOS''',
@query_result_separator=' ',
@attach_query_result_as_file = 0, --set to 1 for true, this script will keep results in email body
@query_attachment_filename = 'LogFileSizeResult.txt' -- will only attach when prior parameter set TRUE
end
You could schedule this to run as a SQL Agent every x minutes.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
September 28, 2017 at 1:02 pm
Got an error.
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 517
Query execution failed: ?Msg 102, Level 15, State 1, Server Servername\Instancename, Line 1
Incorrect syntax near 'ComputerNamePhysicalNetBIOS'.
September 28, 2017 at 1:10 pm
hello_san - Thursday, September 28, 2017 1:02 PMGot an error.Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 517
Query execution failed: Msg 102, Level 15, State 1, Server Servername\Instancename, Line 1
Incorrect syntax near 'ComputerNamePhysicalNetBIOS'.
This pair of lines needs the correction:@query =
'select SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')',
Note the additional right parenthesis.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 28, 2017 at 2:26 pm
Can you please help me if my instance is on right node then it should say instance is on preferred node, no action is necessary?
September 29, 2017 at 5:16 am
This property exists at the cluster role level and is called "Preferred Owners". It doesn't work the way you are expecting it to work though.
When the cluster starts, a list of possible nodes for a role is gathered and the preferred owners are set top of the list.
If a number of failovers occur your role may not be resident on a preferred owner, the only gotcha here is if you set allow failback.
The resource possible owners ultimately dictate where a resource and hence its role may be placed
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 29, 2017 at 7:29 am
hello_san - Thursday, September 28, 2017 2:26 PMCan you please help me if my instance is on right node then it should say instance is on preferred node, no action is necessary?
You would just be evaluating the same check for equal and not equal.
Please review Perry's comment as well https://www.sqlservercentral.com/Forums/Dialogs/PostDetails.aspx?PostID=1899885
IF
(
select SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
as CurrentNode
) <> 'YourPrefferedNode'
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = 'emailprofilename',
@recipients = 'dba@mail.com',
@subject = 'Preferred Node has Changed',
@body_format='HTML',
@query_result_header=1,
@execute_query_database='master',
@query =
'select SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')',
@query_result_separator=' ',
@attach_query_result_as_file = 0, --set to 1 for true, this script will keep results in email body
@query_attachment_filename = 'ClusterCurrentNode.txt' -- will only attach when prior parameter set TRUE
end
ELSE IF
(
select SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
as CurrentNode
) = 'YourPrefferedNode'
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = 'emailprofilename',
@recipients = 'dba@mail.com',
@subject = 'Preferred Node has not Changed - no action required',
@body_format='HTML',
@query_result_header=1,
@execute_query_database='master',
@query =
'select SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')',
@query_result_separator=' ',
@attach_query_result_as_file = 0, --set to 1 for true, this script will keep results in email body
@query_attachment_filename = 'ClusterCurrentNode.txt' -- will only attach when prior parameter set TRUE
end
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
September 29, 2017 at 7:47 am
Hi,
even I have my instance in preferred node, still i received an email stating "@subject = 'Preferred Node has Changed',"
Do you have any suggestion?
also email format will be like this...
-----------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
September 29, 2017 at 7:56 am
hello_san - Friday, September 29, 2017 7:47 AMHi,even I have my instance in preferred node, still i received an email stating "@subject = 'Preferred Node has Changed',"
Do you have any suggestion?
also email format will be like this...
-----------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
set @query_result_header=0,
Try doing some investigation into sp_send_dbmail for other options, I get the idea you haven't.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply