October 8, 2007 at 8:32 am
On starting a new job I have discovered that a number of databases seem to have been installed before the server was renamed, as both select * from sysservers and select @@servername show a different name to the actual server, I assume that the name being displayed is the original name. I know how to fix this, but what I'm not sure of is the impact of changing this, at the moment the application seems to be connecting OK and as far as I can see the connection to the server is via ODBC and that is using the correct servername. Any help appreciated...
October 8, 2007 at 9:13 am
The SQL Server name needs to match the Windows server name.
Sp_dropserver
go
sp_addserver (new name), local
will fix the naming. For clients, they'll need to repoint to the new name unless they are using IP (bad idea) or DNS/FQDN name. In that case, the DNS can be changed to point to the new server.
October 8, 2007 at 5:31 pm
Hi,
If you are working on MS SQL SERVER 2000, You can update the master..sysservers table by enableing the sp_configure 'allow updates',1.
update master..sysservers set srvname='New name' ,Datasource='New name' where srvid=0
Regards,
Reddyprasad.A
MCITP
October 8, 2007 at 5:51 pm
addankireddyprasad (10/8/2007)
Hi,If you are working on MS SQL SERVER 2000, You can update the master..sysservers table by enableing the sp_configure 'allow updates',1.
update master..sysservers set srvname='New name' ,Datasource='New name' where srvid=0
Regards,
Reddyprasad.A
MCITP
Bad Advice. bad, bad,bad.
never update sys tables directly. never. use the provided stored procedures.
just updating the table directly does not take all possibilitites into consideration.. . just do sp_helptext sp_addserver and look at the logic the code goes thru, as well as the multiple updates it does.
look at the validation, and the updating of status that goes on behind the scenes when you do it the approved way. do you know what changing the status does or does not do? wonder if there is a reason the make the name lower case and not whatever you type in?
create procedure sp_addserver
@server sysname, --server name
@local varchar(10) = NULL, -- NULL or 'local'
@duplicate_ok varchar(13) = NULL -- NULL or 'duplicate_ok'
as
-- VARS
declare @retcodeint
-- CHECK IF SERVER ALREADY EXISTS
if exists (select * from master.dbo.sysservers where srvname = @server)
begin
if @duplicate_ok = 'duplicate_ok'
return (0)
raiserror(15028,-1,-1,@server)
return (1)
end
-- VALIDATE @local PARAMETER
if @local is not null
begin
select @local = lower(@local)
if @local <> 'local'
begin
raiserror(15379,-1,-1,@local)
return (1)
end
-- ERROR IF ALREADY HAVE A LOCAL SERVER NAME
if exists (select * from master.dbo.sysservers where srvid = 0)
begin
raiserror(15090,-1,-1)
return (1)
end
end
-- ADD THE SERVER (CHECKS PERMISSIONS, ETC)
execute @retcode = sp_addlinkedserver @server
if @retcode <> 0
return @retcode
-- SET THE SERVER ID IF LOCAL OPTION SPECIFIED
if @local = 'local'
begin
declare @srvid smallint
-- UPDATE DEFAULT MAPPING CREATED BY sp_addlinkedserver
select @srvid = srvid from master.dbo.sysservers where srvname = @server
update master.dbo.sysxlogins set srvid = 0 where srvid = @srvid
update master.dbo.sysservers
set srvid = 0,
schemadate = getdate()
where srvname = @server
end
-- FOR COMPATIBILITY, TURN OFF THE data access SERVER OPTION
execute @retcode = sp_serveroption @server, 'data access', 'off'
if @retcode <> 0
return @retcode
--SET 'local login mapping', 'off' (make rpc-s behave as in 6.5)
update master.dbo.sysservers
set srvstatus = srvstatus & ~32, schemadate = getdate()
where srvname = @server
-- SUCCESS
return (0) -- sp_addserver
Lowell
October 8, 2007 at 10:07 pm
I agree with Lowell. Don't update the system tables. Especially in this case. These are two simple procs to run to fix this.
October 9, 2007 at 8:57 am
I would second Steve on both of his advise.
sopheap
October 9, 2007 at 12:55 pm
I'm fine with fixing this with sp_dropserver sp_addserver what I'm not a hundred percent sure of is whether the 100's of users connected will have any problems if I fix this. I think it should be transparent but would like some confirmation.
October 9, 2007 at 1:50 pm
Are clients connecting now? They shouldn't be if they're using server name. If they're using IP, it should work.
October 9, 2007 at 2:15 pm
Actually if they're using IP, and DNS hasn't been updated (or rather - DNS still has both names pointing to the IP address) - BOTH aliases should work.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 9, 2007 at 2:19 pm
Also, keep in mined that these changes will not take effect until you stop/start SQL Server.
October 9, 2007 at 2:20 pm
One piece of advice script out local Jobs before changing the name!!!
* Noel
October 9, 2007 at 2:26 pm
October 10, 2007 at 4:39 am
If I alter the server name I will use sp_dropserver sp_addserver, however the clients are all connecting via an application which uses an .ini file that contains both connection details to the original server DNS name and the new server DNS name, What I was not sure of is that if I correct the name what might happen to users connection, could I be cutting them off. I wondered if there was a way to tell what they are connecting via - the old or new name. I am 90% sure that they won't notice a thing, but just wondered.
October 10, 2007 at 7:19 am
It's hard to know. The application could support multiple names, there could be caching, etc.
I'd probably schedule this for a slow time or off hours. Change the name and try a workstation. If you have problems, you can roll back. The same sp_dropserver/addserver can be done in reverse.
October 10, 2007 at 9:07 am
This should be an easy change to rollback if needed as Steve has suggested. This could also be easily tested on a test system if you have that available. 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply