SQL Server High Availability – How to capture Server(s) status whether it is online or offline using T-SQL programming

 Below is an example to capture
Server(s) status whether it is online or offline. We are using XP_CMDSHELL
extended stored procedure to ping and get response from the server. We are
using a table in which we are maintaining list of server(s) to which we need to
monitor the status. Whenever we run this code, table will be refreshed with
latest status. This is very useful and helpful when we need to develop
affordable high availability solution with in SQL Server.

--Create a table which will contain list of ServerName that needs to be monitored with their status & timestamp
CREATE TABLE [dbo].[HA_Server_Health_Monitoring] (
	ID INT
	,IDENTITY
	,[Fully_Qualified_Server_Name] NVARCHAR(500)
	,[IPAddress] NVARCHAR(500)
	,[Health_Flag] NVARCHAR(500)
	,[Last_Refresh_Date] DATETIME
	)

--Cleaning of table
TRUNCATE TABLE [dbo].[HA_Server_Health_Monitoring]

-- Populating Server details
INSERT INTO [dbo].[HA_Server_Health_Monitoring] (
	[Fully_Qualified_Server_Name]
	,[IPAddress]
	,[Health_Flag]
	,[Last_Refresh_Date]
	)
VALUES (
	'IND-SQL-01'
	,'127.0.0.1'
	,NULL
	,NULL
	)

INSERT INTO [dbo].[HA_Server_Health_Monitoring] (
	[Fully_Qualified_Server_Name]
	,[IPAddress]
	,[Health_Flag]
	,[Last_Refresh_Date]
	)
VALUES (
	'IND-SQL-02'
	,'127.0.0.2'
	,NULL
	,NULL
	)

INSERT INTO [dbo].[HA_Server_Health_Monitoring] (
	[Fully_Qualified_Server_Name]
	,[IPAddress]
	,[Health_Flag]
	,[Last_Refresh_Date]
	)
VALUES (
	'IND-SQL-03'
	,'127.0.0.3'
	,NULL
	,NULL
	)

--Declaring Variables
DECLARE @Cnt INT
DECLARE @Min INT
DECLARE @CMDString NVARCHAR(2000)
DECLARE @FLAG BIT
DECLARE @IPAddress NVARCHAR(100)
DECLARE @ErrorMessage NVARCHAR(100)

--Setting Minimun value as 1
SET @Min = 1

-- Count of total server(s)
SELECT @Cnt = COUNT(*)
FROM [dbo].[HA_Server_Health_Monitoring]

--- Starting WHILE loop
WHILE @Cnt >= @Min
BEGIN
	SELECT @IPAddress = IPAddress
	FROM [dbo].[HA_Server_Health_Monitoring]
	WHERE ID = @Min

	--Making command string
	SET @CMDString = 'PING' + @IPAddress

	--Executing extended stored proccedure and capturing the result into variable
	EXEC @FLAG = xp_cmdshell @CMDString
		,no_output

	-- 0 -- Success
	-- 1-- Failure
	-- Updaing Status with timestamp
	UPDATE [dbo].[HA_Server_Health_Monitoring]
	SET Health_Flag = CASE 
			WHEN @FLAG = 0
				THEN 'Online'
			WHEN @FLAG = 1
				THEN 'Offline'
			END
		,Last_Refresh_Date = GETDATE()

	IF @FLAG = 1
	BEGIN
		SET @ErrorMessage = 'Server is offline. Need immediate attention & action'
	END

	SET @Min = @Min + 1
END