asked
How to find previously active cluster name
Hi Gurus, How can we find previous active node in sql server cluster?
I guess `sys.dm_os_cluster_nodes` DMV could be helpful
I guess if you want it find from sql server, then you should create a job on the clustered SQL Server instance that will send an email via Database Mail OR add an entry in a table OR SQL Serve Error Log whenever a restart of services occurs. This way you could always know which was the last active node. For e.g. On start of service an email would be generated to concerned persons, would log an entry in the SQL Error Log with something like declare @message varchar(200) SET @message = 'The Current Node is :- '+convert(varchar(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')); exec sys.xp_logevent 60000,@message; I could easily find out through emails that which node was/is active when, and can act accordingly. Moreover, If wanted to know the fail-overs happened by reading the error log (I guess this script can give me last 6 fail-overs instances on one node keeping in mind the default number of error files is 6 and there is no manual cycling of error files) DECLARE @DSQL NVARCHAR(MAX) SET NOCOUNT ON; CREATE TABLE #Logs ( Archive INT, Archivedate DATETIME, FileSize INT ) INSERT #Logs EXEC master.dbo.xp_enumerrorlogs CREATE TABLE #Log ( LogDate DATETIME, ProcessInfo VARCHAR(255), [Text] NVARCHAR(MAX) ) DECLARE @CurrentLog INT, @MaxLog INT SELECT @CurrentLog = MIN(Archive), @MaxLog = MAX(Archive) FROM #Logs WHILE @CurrentLog @CurrentLog END SELECT * FROM #Log DROP TABLE #Logs, #Log After running the above script on all my nodes, I could easily sort out which node was active at which time. This should be a pro-active exercise rather than reactive.
@Amardeep The OP needed an SQL based solution. The same was already answered by KenJ.
@Amardeep Would you enable xp_cmshell for reading the logs, which essentially would be running OS commands, when you have system procedures to do so? It is not allowed in most environments. I would rather prefer powershell as WilliamD has mentioned.
This will allow you to check the errorlog details in T-SQL: DECLARE @results AS TABLE (LogDate datetime, ProcessInfo varchar(255), LogText nvarchar(max)) DECLARE @TextToFind varchar(255), @SQL_Or_Agent tinyint, @ErrorLogNumber tinyint SELECT @TextToFind = 'The NETBIOS name of the local node that is running the server is', -- This is the error log entry for a SQL 2008 cluster node, may be different for other versions @SQL_Or_Agent = 1, -- 1 = SQL ErrorLog, 2 = SQLAgent Log @ErrorLogNumber = 1 -- 0 = Current Logfile, 1 = 1st Archive Log file, 2 = 2nd Archive Log file .... /* Grab the results of the readerrorlog procedure in a table variable to allow for further processing */ INSERT INTO @results EXEC sys.sp_readerrorlog @p1 = @ErrorLogNumber, @p2 = @SQL_Or_Agent, @p3 = @TextToFind SELECT SUBSTRING(LogText, 3, CHARINDEX('''', LogText, 3) - 3) AS DiscoveredNode,-- Awful hack to display the node name, because not everyone has a nice string split function CASE @SQL_Or_Agent WHEN 1 THEN 'SQL ErrorLog' WHEN 2 THEN 'SQLAgent Log' ELSE '#### ERROR - BAD INPUT ####' END AS LogType, @ErrorLogNumber AS LogFileNumber FROM (SELECT REPLACE(LogText, @TextToFind, '') LogText FROM @results r) r I tested this on a SQL 2008 Cluster, you may need to change the @TextToFind parameter for other SQL Server versions, I'm not too sure what text is written to the error logs for them, although I imagine it will be the same from 2005 onwards.
@WilliamD. First of all nice to hear from you after a bit of time. I have some reservations with reading the error log file. First, there would be much manual work, so the combination of sending an email, logging the event AND/OR insertion into a permanent table looks more feasible. Secondly, there could be more than one fail-overs, so hard-coding Archive#1 is not ideal in this case. Moreover, I find it much easier to log a custom message on the start of sql service in case of fail-over i.e. declare @message varchar(200) SET @message = 'The Current Node is :- '+convert(varchar(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')); exec sys.xp_logevent 60000,@message; Hence, can easily search through logs. Another thing to keep in mind is re-cycling of the error logs(default 6 files) Also, if there is manual cycling of the log files then should increase the number to optimal. Otherwise, the information may be lost. For reading all the error logs, I have added a snippet of mine. Please have a look and please share, how you react to / monitor your CLUSTER environment. Your feedback would be highly appreciated.
@Usmann Butt - I hadn't hard coded anything in my solution as far as I can see. What I was offering was an easy way to find out what the last node was before the instance was cycled (each parameter has an explanation and can be changed as needed). It would be trivial to automate this, similarly to what you have done. Your solution runs pretty much the same thing as mine (reading the error logs out), only you are injecting a custom message into the log file instead of looking for the standard message supplied when an instance starts up. If you are concerned about running through T-SQL and the system stored procedures, I suggest moving towards PowerShell. This is the advertised method from Microsoft (PowerShell being their newest attempt at offering a programmable management environment a la Linux/Unix) and it is what I have used in the past on clusters I have implemented. We run a very controlled cluster environment at my current employer and run failovers and monitoring using the PowerShell extensions for cluster environments. I would have no issues running the code I posted, as it uses well documented methods for accessing the data required.
@WilliamD Oh no please. You got the wrong meaning of my comment. I apologize for that. The point was you were addressing the OP's question but I was more interested in your implementation of monitoring the Cluster and at the same moment I wanted to express that I am not in favor of reading the error logs. Once again I apologize if you find it offensive.
No offence taken, just making my point clear. I see no issue with checking logs after the fact, especially for any unplanned failovers. The OP was stating a wish to see what node was used last, so the log does that nicely. The planned failovers we run using PowerShell fire off an email to tell our SysAdmin and DBA teams that a failover has taken place.
There you go by saying that an email is generated in your case as well. This is what I wanted to bring out so the OP and the users to follow would know what could be the best way as this should be a pro-active step. I started my comment with "I have some reservations with reading the error log file" and have no objection on your script as my script is almost the same as yours :)
Hi Please execute the below Query to find out active node Select ServerProperty('ComputerNamePhysicalNetBIOS') - Senthil
That shows the *current* active node. The question was about finding the *previous* active node...
use the below SQL query to find out the active node Select ServerProperty('ComputerNamePhysicalNetBIOS')
2 People are following this question.
