How to Auto Monitor the Changes of Port Numbers for Multiple SQL Servers

If there is a change in the SQL Server’s port number, connections will fail. In order to avoid this, it is a good practice to proactively monitor SQL Server’s port number and act on non-compliance. Monitoring SQL Server’s port number is important. So in this article, we would be learning to automate it.

TCP/IP – A universal method

TCP/IPThe TCP/IP is a universal network protocol that is used to connect to a SQL server instance. When the port number of SQL Server is changed, application connected to it would not function. These applications connecting to the SQL Server instance might receive errors such as:

“A network related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. “

“(provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)” or “(provider: TCP Provider, error: 0 – No such host is known.) (Microsoft SQL Server, Error: 11001)”

SQL Port NumberApart from the change in port number, SQL Server damage can also be a reason for connectivity issues. Let’s create a database and track SQL Server’s port number. We can then compare our master table and the log table using EXCEPT clause and identify non-compliant SQL Server instances. If we identify that there is no change in the SQL port number, then we can focus completely to identify if SQL Server is corrupted.

STEP1 – prepare tables

Run the script1 to create 3 tables. In table “TSOURCE” insert Server names and their actual Port number. Table “TSERVERS” will hold all server names for which we would be creating Linked Servers. Through Table “TCHECK” will act as a log table.

SCRIPT1:

Create table TSOURCE
(cservername varchar(200),cport int)
CREATE table TSERVERS
(cservername varchar(200))
CREATE table TCHECK
(cservername varchar(200),cport int)

STEP2 – Ad hoc or scheduled:

Run script2 as ad hoc query from a new query window or schedule it as a SQL Server agent job. Using a cursor, script2 will read each server name from the table “TSERVERS” and prepares a Linked Server. Port number of each server is collected and stored in the table “TCHECK”. Now using script3 you can easily identify the non-compliant servers.

SCRIPT2:

DECLARE @cmd NVARCHAR(2000)
DECLARE @cmd2 NVARCHAR(4000)
DECLARE @vsvrname VARCHAR(200)
DECLARE @vuser VARCHAR(100)
DECLARE @vpwd VARCHAR(100)

DECLARE cur1 CURSOR
FOR
SELECT cservername
FROM TSERVERS

OPEN cur1

FETCH NEXT
FROM cur1
INTO @vsvrname

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @vsvrname

    SET @vuser = 'linkedserveruser'
    SET @vpwd = 'linkedserveruser'
    SET @cmd = 'EXEC sp_addlinkedserver @server=''' + @vsvrname + ''''

    PRINT @cmd

    EXEC (@cmd)

    SET @cmd = 'EXEC sp_addlinkedsrvlogin ''' + @vsvrname + ''', ''false'', NULL, ''' + @vuser + ''', ''' + @vpwd + ''''

    PRINT @cmd

    EXEC (@cmd)

    SET @cmd = 'INSERT INTO TCHECK(cport) SELECT distinct local_tcp_port FROM [' + @vsvrname + '].master.sys.dm_exec_connections where local_tcp_port IS NOT NULL '

    PRINT @cmd

    EXEC (@cmd)

    UPDATE TCHECK
    SET cservername = @vsvrname
    WHERE cservername IS NULL

    FETCH NEXT
    FROM cur1
    INTO @vsvrname
END

CLOSE cur1

DEALLOCATE cur1

SCRIPT3:

select * from TCHCEK
Except
Select * from TSOURCE

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook fix and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.