Impact of SQL Server name change

  • U J

    SSCrazy

    Points: 2420

    What would be the impact of SQL server name change

    i.e

    if i use sp_dropserver and sp_addserver may i need to recreate all my jobs,logins,users etc. etc.

     

    Thnks for any help !

  • Lowell

    SSC Guru

    Points: 323450

    the impact is very minimal; only if you replace a server does it require recreating logins/jobs/etc.

    typically, you'd right click on my computer>>Properties>>Network Identification and give the computer a new name. then, after it reboots, you'd do exactly as you described:

    sp_dropserver 'old server name'

    sp_addserver 'new server name', 'local'

    you won't have to recreate any of the items you mentioned. those items are stored in the master database or msdb database, and are not referenced in any way by servername

    the only real change you would see is if you did select @@servername, it would have tne new value

     

    Instances would be handled automatically, ie if you had installed any instances of SQL2K or SQL 2005, then people accessing them would simply change from serveroldname\instancename to servernewname\instancename, still witht he same username/passwords that they were using previously.

     

     

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Eddy!-427737

    SSC Enthusiast

    Points: 190

    I did this recently and had problems when returning @@servername as the old server name kept srvid 0 in master..sysservers and the new servername was srvid 1.   I'm opting for reinstalling SQL on our production server next time i do a server move!

  • U J

    SSCrazy

    Points: 2420

    Thanks a Ton............ Lowell !

Viewing 4 posts - 1 through 4 (of 4 total)

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