none
Issue with Changing Host IP (SQL Server 2016)

    Question

  • Hi,

    because of the changed network configuration, I have to change host IP of the SQL Server 2016.

    Unfortunately SQL server did not registered this changing and has still the old IP address in the SQL configuration manager.

    I started the server after IP change, but this did not solve the issue,

    Why SQL Server 2016 cannot catch on this change?

    I don't want that I must this change manually. SQL MUST catch on it!

    Best Regards

    Birdal

    Tuesday, March 28, 2017 7:56 AM

All replies

  • Hello,

    Are you able to ping the server from another computer by computer name and by IP address?

    Did you updated the DNS server host record or verified that DNS has the new IP on the host record of the server?


    Did you clear the DNS cache on that computer?

    Please do all these steps.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    Tuesday, March 28, 2017 11:02 AM
    Moderator
  • Hi Alberto,

    I did these steps before I created this ticket.

    Yes, I can ping other computers using name or IP on each computer.

    Yes, I deleted DNS cache.

    I started servers many times.

    The issue exists.

    Best regards.

    Birdal


    • Edited by _Birdal Tuesday, March 28, 2017 11:14 AM
    Tuesday, March 28, 2017 11:13 AM
  • Did you check to see what your SQL Server is listening on?

    EXEC sys.xp_readerrorlog 0, 1, N'listening'
    

    This really should have work. Is it possible there are multiple network cards on this machine?

    Tuesday, March 28, 2017 12:26 PM
  • Hello,

    It may not work and it may seem not related, but could you try below procedure?

    https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Tuesday, March 28, 2017 12:39 PM
    Moderator
  • Hi Hilary,

    as SQL Configuration Manager shows, it listens on "old" IP address.I executed the command and got

    2017-03-28 09:43:50.110	spid11s	Server is listening on [ 'any' <ipv6> 1433].
    2017-03-28 09:43:50.110	spid11s	Server is listening on [ 'any' <ipv4> 1433].
    2017-03-28 09:43:50.120	Server	Server is listening on [ ::1 <ipv6> 49524].
    2017-03-28 09:43:50.120	Server	Server is listening on [ 127.0.0.1 <ipv4> 49524].
    2017-03-28 09:43:50.120	Server	Dedicated admin connection support was established for listening locally on port 49524.

    I am sure that is a bug in SQL.

    We changed only and only IP address. There is no second NIC or second IP.

    Best Regards

    Birdal

    Tuesday, March 28, 2017 12:44 PM
  • I just noticed that one of mine is incorrect also.

    I bounced my SQL Services and they remained. I do have a hosts file entry which corresponds to one of the entries I have in SQL Server Configuration Manager.

    What your error log tells me is that SQL Server is listening on all IP addresses.

    Are you having a connectivity problem?

    Tuesday, March 28, 2017 1:02 PM
  • Hi Hilary,

    firstly, in a big environment we cannot handle with hosts files.

    The servers can only communicate on the host level (ping, name resolution is ok).

    Remote connections to SQL Server and the connections between SQL servers are not possible.

    Best Regards

    Birdal

    Tuesday, March 28, 2017 1:20 PM
  • Hi Microsoft SQL Team,

    it seems a BUG on SQL 2016!!!!

    Any solution?

    Best Regards

    Birdal

    Monday, May 8, 2017 8:05 AM
  • Are there any firewall rules that need to be updated? 

    Randy in Marin

    Wednesday, May 17, 2017 10:50 PM