Post reply

CHANGING THE IP ADDRESS

  • June 29, 2005 at 9:15 am

    #167314

    Hello

    The network folks want to change the IP address's of a few of my SQL Server 2K machines.

    What effect does changing the IP address have on the SQL server ?

    Thanks for any info/advice/warnings

  • June 29, 2005 at 10:01 am

    #569395

    I don't know the structure of your network, but if DNS and everything else is cool with it, you should be okay.  We've changed server names and IP addresses on a few servers and once the DNS servers were updated all was well.

    One thing to watch for is that I have seen client installs that used the IP address rather than the server name to resolve and you'll need to make sure you're not using these on your clients, or find and correct them if you do.  Hope this helps. 

    My hovercraft is full of eels.

  • June 29, 2005 at 12:52 pm

    #569518

    Thanks for the information.  What about a clustered SQL server ?  I have heard about problems with virtual servers and IP.

     

    "It is only a fleshwound"

  • June 29, 2005 at 1:16 pm

    #569533

    To be honest, I haven't had a lot of experience with changind a clustered install so I'll leave that to someone who's already gone through it if they care to reply. 

    However, there is a MS KB item here on this issue that seems pretty detailed:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;244980

    Note that you'll need the installation CD's for SQL Enterprise Edition.  I'd definitely want to test it first if at all possible. 

     

    "He who would cross the bridge of death must answer me

    these questions three before the other side he see...." 

    My hovercraft is full of eels.

  • June 30, 2005 at 6:25 am

    #569731

    "oh, it's just a little bunny, isn't it"

  • June 30, 2005 at 6:49 am

    #569742

    changing the ip should be no problem

     

    these are the bits that need doing in sql to reflex a machine name change

     
    --- will provide you with details about what sql thinks it is called
    sp_helpserver
    go
     
    -- drop old name
    sp_dropserver 'old_name'

    go

     
    -- add new sql server name

    sp_addserver 'new_name', 'local'

    go

    If the server had maintance plans/jobs configured with the old name the sqlserveragent requires some amendments using the following sp...
     
    use msdb

    go

     
    if exists (select * from sysobjects where name = N'sp_sqlagent_rename' and type ='P')

        drop proc dbo.sp_sqlagent_rename

    go

     
    create proc dbo.sp_sqlagent_rename

    @old_server nvarchar(30)

    as

     
    set nocount on
     
    if (charindex(N'8.00', @@version, 0) = 0)

    begin

        raiserror('sp_sqlagent_rename is only required on SQL Server 2000, procedure will abort', 11, 1)

    end

     
    if (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 0)

    begin

        raiserror('sp_sqlagent_rename can only be used by sysadmin role members, procedure will abort', 11, 1)

    end

     
    declare @new_server nvarchar(30)

    select @new_server = convert(nvarchar(30), serverproperty(N'servername'))

     
    if exists(select * from msdb.dbo.sysjobs where upper(originating_server) = upper(@old_server))

    begin

        update msdb.dbo.sysjobs

        set originating_server = @new_server

        where originating_server = @old_server

     
        raiserror('sp_sqlagent_rename, %d entries updated from %s to %s', 10, 1,

            @@rowcount,  @old_server, @new_server )

    end

    else

    begin

        raiserror('sp_sqlagent_rename, no entries found for @old_server = %s, 0 rows are updates', 10, 1, @old_server)

    end

    go

     
    -- sample usage

    -- exec msdb.dbo.sp_sqlagent_rename @old_server = 'MYSQLSERVER'

     

    from memory clusters need the cd to perform maintance on the virtual server to change the name...but i also remember on a cluster when the ip address changed I had to also amend the registry on the nodes to reflex the new ip address, sql would start but no listen via tcpip on 1433..only named pipes

     

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

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