July 20, 2018 at 8:09 am
We have a typical AG set up, there are 2 nodes in one data on same subnet and 1 node in another datacenter same domain but different subnet. I would ideally like to create linked servers directly pointing to the listener, however they randomly stop working because the listener is working on IP on other data center, i guess this is nature how listener works. I understand a work around would be to just create linked server to an IP and during a DR situation recreate them , IMO that is not really a true HA. Wondering how others have dealt with this. Sounds like a very common problem.
This is what MSFT recommended, i am taking that with grain of salt because it is ridiculous to have that implemented.
July 20, 2018 at 9:59 am
I have implemented Microsoft recommendation in my environment, and that has resolved my timeout problems, I am however not using linked servers, but if you create a linked server using listener instead of IP address and having random timeouts I would assume that your apps are connecting directly to the instance otherwise you will be dealing with app folks every time timeout occurs.
you could create job to recreate listener every time fail over occurs trigger by SQL Agent alert, however I personally wont recommend this and stick with Microsoft recommendation.
July 20, 2018 at 10:11 am
goher2000 - Friday, July 20, 2018 9:59 AMI have implemented Microsoft recommendation in my environment, and that has resolved my timeout problems, I am however not using linked servers, but if you create a linked server using listener instead of IP address and having random timeouts I would assume that your apps are connecting directly to the instance otherwise you will be dealing with app folks every time timeout occurs.you could create job to recreate listener every time fail over occurs trigger by SQL Agent alert, however I personally wont recommend this and stick with Microsoft recommendation.
Basically you let the CNO update DNS record on fly? Linked server has no issues when created on top of IP address, issue is when created on top of listener.
July 23, 2018 at 7:19 am
yes, by adjusting HostRecordTTL and RegisterAllProvidersIP values, as I said if you are having issues with linked server I fail to understand why you are not having connectivity issues when connecting to listener
July 23, 2018 at 12:48 pm
goher2000 - Monday, July 23, 2018 7:19 AMyes, by adjusting HostRecordTTL and RegisterAllProvidersIP values, as I said if you are having issues with linked server I fail to understand why you are not having connectivity issues when connecting to listener
I stand corrected, issue exisit everywhere even through SMS
If just change RegisterAllProvidersIP to 0 , listener would always have only 1 IP. What I am not sure is if I make this change would AG continue synching the data with other node in different subnet.
July 23, 2018 at 3:31 pm
curious_sqldba - Monday, July 23, 2018 12:48 PMgoher2000 - Monday, July 23, 2018 7:19 AMyes, by adjusting HostRecordTTL and RegisterAllProvidersIP values, as I said if you are having issues with linked server I fail to understand why you are not having connectivity issues when connecting to listenerI stand corrected, issue exisit everywhere even through SMS
If just change RegisterAllProvidersIP to 0 , listener would always have only 1 IP. What I am not sure is if I make this change would AG continue synching the data with other node in different subnet.
I see your point. I followed steps listed here and now it is resolving to primary IP. I still have to test how this is going to work in-case of failover to another subnet. Thank you
July 23, 2018 at 3:39 pm
curious_sqldba - Monday, July 23, 2018 3:31 PMcurious_sqldba - Monday, July 23, 2018 12:48 PMgoher2000 - Monday, July 23, 2018 7:19 AMyes, by adjusting HostRecordTTL and RegisterAllProvidersIP values, as I said if you are having issues with linked server I fail to understand why you are not having connectivity issues when connecting to listenerI stand corrected, issue exisit everywhere even through SMS
If just change RegisterAllProvidersIP to 0 , listener would always have only 1 IP. What I am not sure is if I make this change would AG continue synching the data with other node in different subnet.I see your point. I followed steps listed here and now it is resolving to primary IP. I still have to test how this is going to work in-case of failover to another subnet. Thank you
Hmm...just tried it is returning the ip from subnet again,basically linked servers stopped working again.
July 25, 2018 at 1:47 pm
goher2000 - Monday, July 23, 2018 7:19 AMyes, by adjusting HostRecordTTL and RegisterAllProvidersIP values, as I said if you are having issues with linked server I fail to understand why you are not having connectivity issues when connecting to listener
Is your AD and DNS same, i mean are they both on Windows Server? In our case DNS is on InfoBlox and AD is on Windows.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply