Sql-server – Remove an IP address from server config

configurationNetworksql-server-2008-r2

What is the safe way to remove an old IP address from SQL Server configuration?

The scenario is, I have a server with 2 NIC's that should have been configured for teaming but weren't at the time SQL was installed. Each had a different IP so SQL picked up on that and added each IP to its configuration.

The NICs are now teamed and the box is down to a single IP. I want to remove from SQL configuration the IP that is no longer assigned to the server. I don't want to just deactivate it, but rather I want to completely remove it.

Is there a safe way to do this? Or does one just delete the keys under HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.[Instance Name]\MSSQLServer\SuperSocketNetLib\Tcp\ related to the IP in question and restart?

Best Answer

Having searched quite a bit and come up empty I decided to just go ahead and remove the registry keys for the IP address I wanted to delete from the configuration.

Steps:

Backup the registry key HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\
Stop SQL services
Go to HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.[Instance Name]\MSSQLServer\SuperSocketNetLib\Tcp\ (must do this for each SQL instances on the server)
Find the IP# key that contains the IpAddress value of the address you want to remove where # is a number from 1 to the number of IP addresses SQL found on your machine during install
Delete the IP# key found in step 4
If the number in the keyname is not the last one, rename the remaining IP# keys to be sequential starting with 1.
Restart SQL Services

After doing this the old IP address was no longer available in SQL Server Configuration Manager and SQL Server no longer tried to listen on that IP address. There were no longer any "Listening on [old address]" entries in the SQL Log and netstat -an showed no listeners as well.

Related Solutions

Connecting from SSMS to an EC2 SQL Instance

If the server is installed as the default instance, you should be able to connect with DNS name or IP Address. The "\SQL2008" you've added to the address would be used to connect to a named instance called "SQL2008".

Try specify the port in your connection string e.g. "123.123.123.123,1433". Without the port specified, the client will query port 1434 initially to determine which port to use. Alternatively you could open port 1434.

SQL Server Browser Service

When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.

Edit: If you can connect using MACHINENAME\SQL2008 when you are RDP'd to the server, you must be using a named instance. If this is the case, your connection string with the port specified will be "123.123.123.123\SQL2008,1433".

SQL Server – I/O Requests Taking Longer than 15 Seconds

Note that all servers use the same NICs - Broadcom 5709Cs with up-to-date drivers. The servers themselves are Dell R610's.

Kyle Brandt has an opinion on Broadcom network cards which echoes my own (repeated) experience.

Broadcom, Die Mutha

My problems have always been associated with TCP Offload features and in 99% of cases disabling or switching to a-n-other network card has resolved the symptoms. One client that (as in your case) uses Dell servers, always orders separate Intel NICs and disables the on-board Broadcom cards on build.

As described in this MSDN blog post, I would start with disabling in the OS with:

netsh int ip set chimney DISABLED

IIRC it may be necessary to disable the features at the card driver level in some circumstances, it certainly won't hurt to do so.

Best Answer

Related Solutions

Connecting from SSMS to an EC2 SQL Instance

SQL Server – I/O Requests Taking Longer than 15 Seconds

Related Question