August 20, 2013 at 11:52 am
Hello Everyone
I am working with a company that needs, or wants to rename one of the SQL boxes to a name of an existing SQL box. The plan is to remove the original box from the network. Rename the other box to the name of the original server. My question is this: What impact will this have one the current SQL Instance that is on that new box? Will SQL Server Service even start?
Has anyone tried this? Or had to perform this task? I do not have a couple servers on hand to test this, or I would.
Thanks in advance for all your assistance, suggestions and comments.
Andrew SQLDBA
August 20, 2013 at 12:07 pm
One of the issues Brent Ozar's sp_Blitz looks for is when the @@SERVERNAME Property does not match the actual server name. This can happen when the server name is changed after SQL Server was installed. On our site SQL continued to function. I didn't know about the mismatch or the server repurpose & rename until running sp_Blitz.
Ozar's recommendation is to use sp_dropserver and sp_addserver when the original box name has changed:
http://www.brentozar.com/blitz/servername-not-set/
Try your scenario out on a nonProd or local EXPRESS installation you can afford to break and let us know how it goes.
August 23, 2013 at 5:16 am
We get the same issue when VMs are created from a template, I just use the below (which just uses sp_addserver and sp_dropserver as recommended above) to check and reset if needed.
Rod
declare @srvname sysname
declare @sql varchar(200)
declare @instname sysname
--Get old servername/ instancename and remove
set @srvname=@@servername
SET @instname= (select CONVERT(nvarchar,SERVERPROPERTY ('InstanceName')));
IF @instname IS NOT NULL SET @Srvname=@srvname+'\'+@Instname
set @sql='sp_dropserver ''' +@srvname+ ''';'
EXECUTE (@SQL)
--Get current servername and add
EXEC xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\', 'ComputerName', @srvname OUTPUT
IF @instname IS NOT NULL SET @srvname=@srvname+'\'+@Instname
set @sql='sp_addserver ''' +@srvname+ ''', local;'
EXEC (@SQL)
Print 'Servername reset within SQL'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply