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
The 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)”
Apart 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.
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.
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
select * from TCHCEK Except Select * from TSOURCE
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