SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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

sqlcmd-enumerate-sql-server-instances-list-sql-servers

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.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems