January 20, 2015 at 10:11 am
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!
January 21, 2015 at 5:54 pm
Have you looked at sp_setnetname?
January 21, 2015 at 9:17 pm
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.
January 22, 2015 at 8:00 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply