Post reply

change datasource of server in [sys].[servers]

  • April 22, 2014 at 9:02 am

    #288152

    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

  • April 22, 2014 at 9:30 am

    #1707805

    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.

  • April 22, 2014 at 9:44 am

    #1707815

    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.

  • April 22, 2014 at 10:04 am

    #1707832

    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

  • April 22, 2014 at 1:52 pm

    #1707893

    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.

  • April 22, 2014 at 3:00 pm

    #1707920

    Thank you

  • April 22, 2014 at 3:15 pm

    #1707922

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 23, 2014 at 2:42 am

    #1707993

    Thanks Lowell. What did the provider and data_source say after the renaming?

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

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