Does renaming a server cause problems with SQL 2000 or 2005?

Question

Does renaming a server cause problems with SQL 2000 or 2005?

Ian Harris-306729

Mr or Mrs. 500

Points: 505
More actions
January 15, 2008 at 6:29 am

#185438

We are due to migrate from SQL 2000 to SQL 2005. As part of this plan we will be renaming two servers. One of these will be hosting SQL 2005 and the other will be hosting SQL 2000. Once the servers have been renamed we will run sp_dropserver / sp_addserver and restart the SQL server service and SQL Agent service.
My question is : Is this a safe operation? Can we expect to encounter any issues after renaming the servers?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

We just went through this on 2K5. The only problem area is Reporting Services. If you've got that running there's a few manual edits you'll have to do. Can't seem to find the article right now but it's on MSDN. I'll look for it later and post the link.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

It is best to delete the local proxy account before renaming the server, and add the new one after you rename it. If you don't, you may have to make direct changes to the registry to fix it.

You should run this code after stopping the SQL Agent to update your jobs after you rename the server.

update msdb.dbo.sysjobs

set

originating_server = 'MyNewServerName'

Pam Brisjar SSChampion Points: 12094 More actions · Answer 3

Here's the article I was talking about:

http://msdn2.microsoft.com/en-us/library/ms345235.aspx

Here's another ref to look at:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=83229&SiteID=1

Ian Harris-306729 Mr or Mrs. 500 Points: 505 More actions · Answer 4

Thanks Pam and Michael.

Looks like there should be no real show stoppers, which is what I was hoping.

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 5

Ian,

There are other possible show stoppers. Linked servers and queries that use OPENDATASOURCE / OPENROWSET. Make sure you account for all possible objects that have references to these items, otherwise, you might get caught short once you're done.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Bob Fazio SSChampion Points: 10794 More actions · Answer 6

I tried to rename the server, and there was just one issue after the other.

My suggestion is to detatch the DB's, re-install after you rename the server, and re-attach the db's.

Security is a little bit of a pain, but the sp_help_revlogin script helps there.

Jobs are easy to copy.

It's always good to know how to rebuild from scratch.

AshikAhmed SSC Journeyman Points: 92 More actions · Answer 7

➡ We renamed a server and had problems creating linked server. We had to update the data_source (db column) in sys.servers (meta) sys table.

Please keep this in mind, if you need to add linked servers.

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 8

I've renamed a lot of servers on SQL 2000 and haven't had any major problems. None of them had any BI stuff (analysis services, reporting services ) installed. Following are my notes I use when going through this process:

-- Run on SQL 2000 after server name change.

-- To correct the sysservers system table. Note the following excerpt from online help about remote users and logins:

-- If the computer has any remote logins, for example, if it is a

-- replication Publisher or Distributor, sp_dropserver may generate

-- an error similar to this:

-- Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44

-- There are still remote logins for the server 'SERVER1'.

-- To resolve the error, you may need to drop remote logins for this server.

-- If replication is installed, disable replication on the server before

-- running the sp_dropserver stored procedure.

--Side note:

--

--SQL Books Online is slightly incorrect with regard to sp_addserver when renaming a server. The correct syntax is

-- exec sp_addserver ' ', 'local'

--

-- If you do it the way it is specified in books online, the server is added with a new server id which is an identity field.

-- If you try to replicate from (and possibly to) this server, replication setup will fail because it is looking for a server

-- with an ID of 0 which won't be there. For some reason it uses this to determine something about the local computer

-- and bombs.

--sp_helpserver will show you the name SQL currenlty thinks the server is named.

exec sp_dropserver

go

exec sp_addserver ' ', 'local'

go

-- To correct orginating server on JOBS: If this isn't done, the jobs on

-- the server can't be edited, SQL thinks they came from another server

update msdb.dbo.sysjobs

set originating_server = ' '

where originating_server = ' '

go

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 9

The site edited out part of my syntax on the previous post. Here are the commands that got edited:

--sp_helpserver will show you the name SQL currenlty thinks the server is named.

exec sp_dropserver 'old servername'

go

exec sp_addserver 'new servername', 'local'

go

-- To correct orginating server on JOBS: If this isn't done, the jobs on

-- the server can't be edited, SQL thinks they came from another server

update msdb.dbo.sysjobs

set originating_server = 'new servername'

where originating_server = 'old servername'

go

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 10

The solution of runnind sp_dropserver and sp_addserver is a lot better than the solution I found at MS which was to rename the server back, drop the maintenance plan jobs, and then rename the server again, and add the jobs back.

I will have to keep this approach in mind.

-- Mark D Powell --