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'
