Server Name Issues

Question

Server Name Issues

TheRedneckDBA

SSChampion

Points: 14001
More actions
August 3, 2007 at 2:30 pm

#118070

We recently set up and deployed a new VMWare server with about 10 low-use SQL servers (2K5, SP2) on it.
Instead of building 10 servers and installing SQL Server on each, our network guy built one and cloaned it.
So we have db-srvr1, db-srvr2, db-srvr3, ...etc
The problem is he just renamed the servers, not the SQL instances.
So no matter what server you are on, @@servername returns db-srvr1 (the server everything was cloaned from).
2 Questions.
1) How big of an issue is this? The only thing I've noticed as a side effect (which is what lead me to discover this in the first place) is when you try to set up a linked server to db-srvr1 from any of the others it won't let (thank goodness) you because it thinks you are duplicating. Interestingly, I can do select * from db-srvr1.dbname.schema.table on any server and it queries itself 🙂
2) Will executing sp_addserver 'db-srvr2', @local = 'local' and then bouncing the server fix this, or are there other places I need to look to get the issue resolved?
Thanks,
Jason
The Redneck DBA

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

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

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 1

Jason,

There is some documentation on it around which seems to suggest its OK. The only time I have done it I ended up playing with fixing linked servers/remote logins, jobs, DTS etc for so long I just reinstalled the server and reapplied many of the things I had to have running.

http://www.sqlservercentral.com/columnists/bknight/renameserver.asp talks about doing this - make sure you check the opinion section (which is probably more useful) as well.

http://msdn2.microsoft.com/en-US/library/aa197071(sql.80).aspx suggests the sp_dropserver/sp_addserver should be fine.

As they are cloned machines I would probably just go for it the worst that can happen is you have to clone the machine again

James

--
James Moore
Red Gate Software Ltd

Bob Fazio SSChampion Points: 10794 More actions · Answer 2

I tried the sp_dropserver/sp_addserver and it worked ok, but I had issues later with patches and such. Not sure if that was the issue or not, but I personally would suggest detaching the DB's, uninstall->reinstall SQL Server. Then re-attach DB's on each of the servers. It will be cleaner.

Clifton Collins Default port Points: 1433 More actions · Answer 3

Renaming the sql servers may not be the only issue that you run into when 'cloning'. You may need to reinstall MSDTC and clean up any DCOM GUID/permission issues.

I normally do this with each new vmware guest:

A) Execute the rename sql server script

B) Perform the msdtc reinstall processes

A) rename sql server

exec sp_dropserver @server = '[%youroldservernamehere%]'

GO

exec sp_addserver @server = '[%[yournewservernamehere%]' @local = 'LOCAL'

GO

Print 'Restart the server now'

B) Resolve the duplicate MSDTC IDs:

1) From a command prompt, run: msdtc -uninstall

2) Reboot the system.

3) After rebooting, from a command prompt, run: msdtc -install

4) Run services.msc, and set the Distributed Transaction Coordinator service

to automatically start.

5) Reboot the system.

6) After rebooting, edit the MSDTC component security settings to

the required values for POC. Run dcomcnfg

[font="Arial"]Clifton G. Collins III[/font]

MIJ SSCommitted Points: 1915 More actions · Answer 4

I hope this isn't too far off topic, but does the MSDTC issue apply if you're not dealing with cloned servers? Or is this only a probelm becuase cloning results in duplicate GUIDs?

For example, we're getting ready to migrate a standalone default instance (SQL '05) to new hardware, but we intend to keep the old name. The plan is essentially:

install/configure SQL on server 'NewServer'.
backup (or detach) dbs etc. on server 'OldServer'
Remove OldServer from the domain.
Rename NewServer to OldServer; exec_spdropserver and sp_addserver.
Restore (or re-attach) dbs etc.

BOL makes it (the renaming) look easy; maybe too easy? From reading other links and comments the worst thing I've seen so far was trouble updating jobs and one caution not to reuse the old server name (why?), but those were both for SQL 2000.

So is it as easy as it looks to rename a server and the default instance, or am I underestimating this?

Thanks.

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 5

I sucessfully renamed all of mine by doing the dropserver / addserver. I only did it yesterday, but they are high-use servers so I assume I would know by now if anything too terrible is going to happen.

We've done basically the same steps you list for things like that before...should work. I'd backup the dbs and transfer the files to the new server rather than detaching them. That way if something hiccups in the process, you have the 'OldServer' as somewhat of a hot standby to fall back on.

The Redneck DBA

MIJ SSCommitted Points: 1915 More actions · Answer 6

Jason, thanks for the feedback. I thought it looked that easy, but I've never actually tried it, so it's always nice to hear that it worked for others.

Thanks again,

MIJ