SQL Server Alias not updating

  • Hi SSC,

    In Dev I have a linked server called "MyLinkedServer" that references an alias under the "SQL Native Client 11.0 Configuration" section of configuration manager and when I change the pointer from ServerA to ServerB, my test query still returns ServerA as a host.

    It feels like SQL Server caches this data, is this correct?

    What triggers this refresh?

    Is there anyway I can get SQL Server to recognize the change without causing an outage to the instance itself by disabling and enabling the protocol the alias uses?

    I'm using a 64 bit host, running SQL Server 2012 (RTM)

    Example Query

    EXEC ('SELECT @@SERVERNAME') AT MyLinkedServer

    Things I've tried:

    A) Deleting the alias, testing the linked server and to my surprise with the alias gone the linked server still works... yikes!

    B) Dropping the linked server and re-creating it, this works but I'm hoping I don't have to do this each time as it requires permissions being injected on the create, etc.

    C) Restarting the SQL Server Browser, this didn't work

    I could move this down to DNS itself but then I have to clear the cache of the entire host itself ... not that I see it being a big problem but still, it would be neat to understand the internals of SQL Server in this scenario.

    Thanks for the help!

  • Have you looked at sp_setnetname?

    https://msdn.microsoft.com/en-us/library/ms190318.aspx

  • Interesting, thanks for the lead, I appreciate it!

    Yesterday I found I could "trigger" a refresh of the linked server by running sp_serveroption, setting an existing option to itself for the linked server(s) that use the alias I changed.

  • arnipetursson, great find. I confirmed sp_setnetname worked and I consider it much cleaner as behind the scene this line does the magic:

    EXEC %%LinkedServer ( Name = @server ) . SetDatasource ( Datasource = @netname )

    I hope this helps the next person who runs into this situation!

    Cheers!

Viewing 4 posts - 1 through 4 (of 4 total)

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