CHANGING THE IP ADDRESS

Question

CHANGING THE IP ADDRESS

MillerTime

SSC Veteran

Points: 234
More actions
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

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

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

sswords SSCrazy Eights Points: 8207 More actions · Answer 1

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.

MillerTime SSC Veteran Points: 234 More actions · Answer 2

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"

sswords SSCrazy Eights Points: 8207 More actions · Answer 3

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.

Mike Prascak SSC Eights! Points: 930 More actions · Answer 4

Mike Prascak

SSC Eights!

Points: 930

June 30, 2005 at 6:25 am

#569731

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

Richard-212791 SSC Rookie Points: 43 More actions · Answer 5

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