2

I edited the question hoping to check the basic stuff first.

Does anyone have linked servers that successfully work with Availability Group listener? Have anyone heard of problems with linked servers?

(My setup is SQL 2014 on windows 2012R2, sync mode, auto-failover, two replicas, listener with statis IP, same subnet, standard port)

===========

I am setting up a SQL 2014 AAG (2 replicas - HASQL1, HASQL2) on Windows Server 2012R2.

Below is my AlwaysOn Setup:

Two Replicas: HASQL2,HASQL1

Availabilty GroupName: HAG 

Listener Name:HAGL

HASQL1's databases: hadb (in HAG), nonhadb 

HASQL2's databases: hadb (in HAG)

I have one more server (APPSQL) which queries HAGL through Linked server

The issue i am facing goes like this..

When both replicas are initially rebooted (with HASQL1 as primary), APPSQL HAGL linked server shows hadb only. which is correct.

When HAG is failed over to HASQL2, APPSQL's HAGL linked server shows hadb only. which is correct.

So far, so good.

But when HAG is failed over to HASQL1 again, APPSQL's HAGL linked server shows nonhadb only . which is not correct.

If I query from APPSQL to HAGL.hadb.*.* table, I get an error.

The OLE DB provider "SQLNCLI10" for linked server "HAGL" does not 
contain the table ""hadb"."dbo"."table1"". 

This problem is limited to the linked server. I can directly connect to HAGL using SSMS without any problem.

The dashboard does not show any arror. The cluster log shows this:

Cluster network name resource 'HAG_HAG' failed registration of one or 
more associated DNS name(s) for the following reason:
DNS operation refused. Ensure that the network adapters associated with 
dependent IP address resources are configured with at least one 
accessible DNS server.

I am wondering about the "HAG_HAG" thing (it repeats the HAG).

I searched for linked server and AAG, but didn't find anyone reporting this problem, so suspect that perhaps I missed something.

What are the things I can check? I would appreciate any help.

== update ==

Here is the creation script from the powershell scripter.

CREATE AVAILABILITY GROUP [HAG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
    FAILURE_CONDITION_LEVEL = 3,
    HEALTH_CHECK_TIMEOUT = 30000)
    FOR DATABASE [hadb]
REPLICA ON N'HASQL1' WITH (ENDPOINT_URL = N'TCP://HASQL1.company.com:5022', 
  FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
  SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50,                      
  PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'HASQL2' WITH (ENDPOINT_URL = N'TCP://HASQL2.company.com:5022', 
  FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
  SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50,                              
  PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))
LISTENER N'HAGL' ( WITH IP ((N'10.10.123.123', N'255.255.0.0')) , PORT=1433);

== update 2 ==

Looks like it is a problem with one replica only. The failover cluster is installed on the same host as the second replica, which works correctly. But the primary replica fails consistently except right after first reboot. It seems now to be a network setup problem.

== Update 3 ==

We are not using Kerberos, but using NTLM. Would this make a difference with linked server? (Is there any requirement like, "to make the linked server work with AAG listener, Kerberos is required"?)

== linked server creation script ==

EXEC master.dbo.sp_addlinkedserver @server = N'HAGL', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HAGL',@useself=N'False',@locallogin=N'A_WindowsAccount',@rmtuser=N'A_SQLAccount',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'HAGL', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'HAGL', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'HAGL', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'HAGL', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'HAGL', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'HAGL', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'HAGL', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'HAGL', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'HAGL', @optname=N'collation name', @optvalue=NULL
EXEC master.dbo.sp_serveroption @server=N'HAGL', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'HAGL', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'HAGL', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'HAGL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
  • The "failed registration" error message is not a problem assuming you are using static IPs, and have manually created entries in DNS for them. – Max Vernon May 30 '16 at 17:41
  • Thanks. i am using a static IP. I don't have access to DNS but I can nslookup both ways (HAGL to IP, IP to HAGL) so assume that it is set up OK. – benik9 May 30 '16 at 17:45
  • Share your linked server creation script – TheGameiswar May 31 '16 at 13:52
  • Look at the DNS under the cluster manager, do they all show status of "ok"? Static IPs can still have DNS issues of the cluster object on the domain does not have proper access. – Shawn Melton May 31 '16 at 14:08
  • @Shawn I don't see DNS under cluster manager. But I am told that the cluster account was given CNO priv in domain. – benik9 May 31 '16 at 14:42
1

After I reset all the settings and re-created the environment, the problem went away. So the problem was perhaps very specific to local situation and not general to a linked server to AAG. I appreciate all those who commented.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.