Monitor SQL Server List using SQLCMD -L Utility and Enumerate SQL Servers
The following t-sql SQLCMD command will enumerate SQL Servers on a network and give SQL Server administrators the SQL Server list in their network.
EXEC XP_CMDSHELL 'SQLCMD -L c'
Please note that using SQLCMD utility with -L switch will enumerate locally configured SQL Server computers and SQL Servers that are broadcasting on the network. Using SQLCMD -L command a maximum number of 3000 SQL Server can be enumerated.
The additional c parameter that is used after SQLCMD -L command returns a table structured SQL Server list.
This method to enumerate SQL Servers is more suitable to use the returned SQL Server list in t-sql.
Before using sql xp_cmdshell procedure, SQL Server administrators should enable xp_cmdshell.
For more detail refer to how to enable xp_cmdshell stored procedure sql tutorial.
SQL developers can modify the above sqlcmd command as follows to store the SQL Server list in a sql table.
CREATE TABLE SQLServerInstances (
SQLServer varchar(100)
)
INSERT INTO SQLServerInstances
EXEC XP_CMDSHELL 'SQLCMD -L c'
SELECT * FROM SQLServerInstances
SQL Server administrators can use the below sql stored procedure in order to monitor the active SQL Server list.
T-SQL developers can modify the below sql stored procedure sp_MonitorSQLServerInstances in order to send database mail from SQL Server database in order to inform about SQL Server status changes.
The following stored procedure can be called within a SQL Serve Agent sql job and will populate regularly a table which is used to list SQL Servers on the network.
If new SQL Server instances are found, these new SQL Server instances will merge with the existing SQL Servers list using the t-sql Merge command.
If there is a status change of the SQL Server instance, these status changes will be listed using the last SELECT statement in the sql stored procedure.
CREATE PROCEDURE sp_MonitorSQLServerInstances
AS
/*
CREATE TABLE SQLServerInstances (
SQLServer nvarchar(200),
LastActive datetime,
PreviousStatus tinyint,
LastStatus tinyint
)
*/
CREATE TABLE ##SQLServerInstances (
SQLServer nvarchar(200)
)
INSERT INTO ##SQLServerInstances
EXEC XP_CMDSHELL 'SQLCMD -L c'
--SELECT * FROM ##SQLServerInstances
MERGE SQLServerInstances
USING
(
SELECT
SQLServer
FROM ##SQLServerInstances
WHERE SQLServer IS NOT NULL
) MergeData ON SQLServerInstances.SQLServer = MergeData.SQLServer
WHEN MATCHED THEN
UPDATE SET
SQLServerInstances.LastActive = GETDATE(),
PreviousStatus = LastStatus,
LastStatus = 1
WHEN NOT MATCHED BY TARGET THEN INSERT VALUES(SQLServer, GETDATE(), 1, NULL)
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET
PreviousStatus = LastStatus,
LastStatus = 0;
DROP TABLE ##SQLServerInstances
--SELECT * FROM SQLServerInstances
SELECT * FROM SQLServerInstances
WHERE ISNULL(LastStatus,0) <> ISNULL(PreviousStatus,0)
GO
I hope this sql stored procedure will be useful for SQL Server administrators to monitor SQL Servers on the network.