Post reply

Renaming the SQL Server

  • August 17, 2010 at 6:52 am

    #236675

    Hi,

    Am in process of renaming the SQL server 2008 instance but am stuck while dealing with jobs.

    Renaming the SQL Server in SQL 2000

    step1 :

    exec sp_dropserver 'oldserver'

    exec sp_addserver 'newserver','local'

    step2 :

    restart the sql server and check whether below query output is same or not .

    select @@servername,serverproperty('servername')

    step3 : for updating jobs

    use msdb

    go

    update sysjobs

    set originating_server = serverproperty('servername')

    go

    step4: Restart sql server and start using it.

    SQL Server 2008

    =================

    I wanted to do the same SQL 2008. This is what i have done but for jobs n all how to do i because in SQL 2008,

    we cannot make any direct updates and sysjobs is different in SQL 2008, we have the originating server id and not

    directly originating_server column where i can directly update the servername.

    step1 :

    exec sp_dropserver 'oldserver'

    exec sp_addserver 'newserver','local'

    step2 :

    restart the sql server and check whether below query output is same or not .

    select @@servername,serverproperty('servername')

    step3 : for updating jobs

    How to do it?????

    step4: If some linked servers,

    update master..sysservers

    set datasource = 'oldservername'

    where datasource = 'newservername'

    /*

    Msg 259, Level 16, State 1, Line 2

    Ad hoc updates to system catalogs are not allowed

    */

    For this , i have dropped all the Linked Servers which are pointing to same machine(doesnt exist in real time but for testing)

    and recreated linked servers. For other Linked Servers which are pointing to other SQL instances i didnt disturb those.

    How can i rename or what steps to be followed in SQL 2008 to ensure, sql server renaming is complete.

    Note : Am not dealing with any Replication or Full text or anyother High Availability features.

    Just want to know what are the steps involved for sql server instance renaming whenever machine name is changed by network administrators

    or going for sql server machine migration?

    Thanks in Advance.

  • August 17, 2010 at 11:44 pm

    #1208306

    U changed the physical machine or server name??

  • August 18, 2010 at 10:34 am

    #1208618

    Machine name is changed.

  • August 18, 2010 at 11:31 am

    #1208665

    Been there, done that.

    If you install SQL Server and then change the machine name, you will need to uninstall and reinstall SQL Server. There are some issues with SQL Server settings still looking for the original machine name and causing some things to not work properly, or not work at all.

  • August 18, 2010 at 10:23 pm

    #1208885

    i agree with Jerry, i have also experienced similar problems. The Ideal way is to uninstall and re-install the SQL Server

  • August 19, 2010 at 12:20 am

    #1208904

    Hey Jerry/Verma,

    We cannot reinstall sql server on prod. There is work around for this to be done. I am almost done , but just want to know is there any modifications needed to be done for SQL jobs.

    http://msdn.microsoft.com/en-us/library/ms143799.aspx

    Hope this helps everyone.:-)

  • August 19, 2010 at 2:59 am

    #1208943

    Normally changing the machine name running the procedure to drop and create the server will work ..

  • August 19, 2010 at 6:52 am

    #1209070

    Yes i have done the same thing.

    For Maintenance plans , we need to re-create them.

  • August 19, 2010 at 8:01 am

    #1209128

    I have renamed plenty of boxes running SQL 2008. The biggest thing is make sure select @@servername returns the correct name. For linked servers, just script them out and recreate them if they give you an issue. Same with jobs. Maintenance plans you will probably need to recreate however, since it's usually faster to do that than mess around with exporting/changing/importing ssis packages.

  • August 19, 2010 at 8:08 am

    #1209134

    I would agree with scripting things. I'd script out all jobs and then search/replace.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 19, 2010 at 8:23 am

    #1209148

    Thanks You ! 🙂

  • August 19, 2010 at 12:13 pm

    #1209316

    It is possible that the issues I ran into were corrected in the RTM release of R2. However, I can't vouch for that.

    I ran into the issue while authoring courseware for SQL 2008 R2. We tried to use the steps in the article and still had to resort to re-install.

    Be sure and let us know how it all ends up. This will be very valuable information.

  • August 19, 2010 at 12:14 pm

    #1209317

    Sorry, totally brain dead.

    Accidentally posted twice.

  • August 19, 2010 at 9:14 pm

    #1209445

    Jerry, can you let me know what are those issues you come across for SQL 2008 renaming??

  • August 20, 2010 at 9:55 am

    #1209769

    I will see if I still have the notes on it. That was around two years ago so don't know if I can find them.

Viewing 15 posts - 1 through 14 (of 14 total)

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