Post reply

Renamed computer and connected to sql server without problem!

  • September 5, 2005 at 4:22 am

    #91015

    I have read that if I change "computer name" and then try to startup sql server it will recognize the changes automatically BUT you need to execute sp_dropserver and sp_addserver procedures too in order to set all thing right.But yesterday I tried it on a Personal edition on xp and after reboot sql server came up without any problem and I could connec it without executing sp_dropserver and sp_addserver! Was it a specific situation or no need to those SPs at all?

    -PM: Is there any difference between named or default instances in this context? I have read you need those SPs fot both of them,but I doubt now!Just curious!

    -Thanks

  • September 6, 2005 at 5:59 am

    #587730

    It doesn't cause any problems other than a possible invalid or null value for the global variable @@servername.

    What do you get when you execute

    SELECT @@ServerName

    Of course, you must stop and then restart SQL Server to see the effect of sp_dropserver and sp_addserver.

  • September 6, 2005 at 6:04 am

    #587731

    When you connecting to a SQL Server install (simply), say "SQLServerBox" it looks for the computer name in DNS, resolves this to IP address, then connects to that.

    SQL Server (2000) does not care what the computer is called and will start OK.

    But, SELECT @@SERVERNAME will return the old name (from sysservers) thus the sp_dropserver etc is needed.

    SELECT SERVERPROPERTY('MachineName') returns the host (computer ) name

    And I forget what SERVERPROPERTY('ServerName') does, I would have to check.

    Now if you renamed your XP box and the connected to "NewName" rather than "OldName", then "NewName" would be resolved to an IP address etc (see above)

    Yes, the same applies for instances... because the hostname is part of the instance name, where as a default instance just uses the hostname

    Er, clear?

    Cheers, Shawn

  • September 6, 2005 at 7:00 am

    #587768

    I think it was SQL 7 that got very upset about changes to computer names.  SQL 2000 corrected this as described previously

  • September 6, 2005 at 8:22 am

    #587794

    Thank all for your replies,

    I have read it in a SQL Server 2000 book.I considered it to be uptodate but it seems some DBAs(authors) are stick to older versions and habits without any malicious purpose!

    -Regards

  • September 6, 2005 at 3:27 pm

    #587942

    SQL v7 had an option on the installation CD that 'you had to trust' ... it worked 98% of the time. As for SQL2000, the sp_dropserver/sp_addserver/reboot is still the 'correct' method to make sure all the midofications to the SQL Server 'engine' have been completed after a physical server rename. Trust the book. There are hundreds of things (differenct functionalities of the DBMS) scattered all over that you may not use now, but may be broken later since you did not perfrom sp_dropserver/sp_addserver/reboot sequence.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • April 12, 2006 at 4:20 am

    #632046

    I have the requirement to have to do this with a SQL 7 Server Soon.  The Instructions for this would seem to be

     

    • Rename the physical server by going

    • Reboot the server.

    • Run the SQL Server setup program.

    • Reboot the server.

    • Run this stored procedure: "sp_dropserver <old server name>"

    • Run this stored procedure: "sp_addserver <new server name>, local"

     

    What should be run on the SQL 7 Setup disk - is it a case of going through the motions as if to install SQL 7 again ?  My concern is that I will overwrite my existing installation

    Cheers

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply