Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

change datasource of server in [sys].[servers] Expand / Collapse
Author
Message
Posted Tuesday, April 22, 2014 9:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:02 PM
Points: 46, Visits: 519

We will shutting down an older sql server and standing up a newer 2008 version using the same server name as the original. Does the sp_addserver populate the datasource field with the name of ther server being added? If not, should it be populated and how?

Thanks in Advance
Post #1563892
Posted Tuesday, April 22, 2014 9:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:38 PM
Points: 31,018, Visits: 15,453
Which datasource field?

Do you mean you are uninstalling the old server on a Windows host and then installing SQL 2K8 on the same host? It's not clear what you are doing or what you are trying to check.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1563908
Posted Tuesday, April 22, 2014 9:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:02 PM
Points: 46, Visits: 519

The current production machine named app01 will be taken off the network and shutdown. The new machine currently named app02 with an instance of app02 on it will be renamed to app01. Once renamed to app01 and we do sp_dropserver app02 and sp_addserver app01. We simply see the field datasource populated with the server name in sys.servers. We just wonder if that also flipped\updated after sp_addserver app01. We don't see a reference to that field in the sp_addserver proc.
Post #1563921
Posted Tuesday, April 22, 2014 10:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:02 PM
Points: 46, Visits: 519

What originally updated sys.server entry with the provider and data_source info? Was it an ODBC entry? We just want to make it's put in after sp_addserver process.

Thanks in Advance
Post #1563941
Posted Tuesday, April 22, 2014 1:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:38 PM
Points: 31,018, Visits: 15,453
sp_addserver should populate the fields correctly for applications to work with the instance. I'm not surprised it isn't documented, but you can certainly submit a doc bug to have that added.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1564015
Posted Tuesday, April 22, 2014 3:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:02 PM
Points: 46, Visits: 519
Thank you
Post #1564045
Posted Tuesday, April 22, 2014 3:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:30 PM
Points: 12,895, Visits: 32,089
i believe sp_addserver is used internally when you add a linked server.

having just replaced a server recently, this tidbit might be of some help.
we create a machine named APP-TEMP to replace APP-PRODUCTION; the usual newer Operating system and newer version of SQL was used.

we migrated everything we needed to...logins, jobs, linked servers, credentials,database mail settings ..everything via script.
we toggled all the databases to read only on the APP-PRODUCTION; then we backed up and restored the databases on the new server.
we turned off the original APP-PRODUCTION box, and then renamed APP-TEMP to APP-PRODUCTION.

at this point, other machines could connect to the replace ment server.

sys.servers serverid= 0 is supposed to be the name of the server itself, and of course at this point it still says APP-TEMP, as well as when you select @@SERVERNAME.
we ran this command to reset the name:
EXEC sp_dropserver 'APP-TEMP' 
EXEC sp_addserver 'APP-PRODUCTION', 'local'

looking at that same @@SERVERNAME / name from sys.servers where server_id = 0 was still the old value.
we restarted the SQL service, and only then did the name change for us...i was under the impression the renaming was supposed to be instant, but that's what we saw here.

we then had to stop and start the service, and at that point sys.servers and @@servername was correct.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1564048
Posted Wednesday, April 23, 2014 2:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:02 PM
Points: 46, Visits: 519

Thanks Lowell. What did the provider and data_source say after the renaming?
Post #1564149
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse