How configure correctly AlwaysOn readable secondary replica for applicationIntent

  • Article

 

Hi all,

 

I would like to share with you an issue that I got recently because  I didn't set  up correctly AlwaysOn to enable readable database on the secondary replica.

 

Problem description: When I am trying to connect to my application with .Net or PowerShell, I got the following issue only when I add ApplicationIntent=Readonly on my connection string:

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : TCP Provider: No connection could be made because the target machine actively refused it..
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server
. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see
SQL Server Books Online..

Reproduction: I am able to reproduce with SQL CMD: sqlcmd -S myListener -E -K ReadOnly -Q "SELECT @@SERVERNAME" However, when I remove -K ReadOnly, it works fine.

Other interesting stuff, I can see a login/logout on my primary server inside the profiler trace, but nothing on the secondary

 

Solution: In my case, the issue was that the read_only_routing port mismatch with the port of my instance. I enclose below what you have to look.

 

How configure correctly AlwaysOn readable secondary replica:

1.      Add Application Intent in the connection string by adding: ;ApplicationIntent=ReadOnly

2.      ALLOW_CONNECTIONS property of the secondary replica is READ_ONLY.

3.      The READ_ONLY_ROUTING_URL for each replica is set. The TCP port is a port number that is used by the Database Engine of the SQL Server instance

alteravailabilitygroupmyGroupName
modifyreplicaon'myMachine1instanceName1'with
(secondary_role(read_only_routing_url='tcp://myMachine1:1433'))

GO

alteravailabilitygroupmyGroupName
modifyreplicaon'myMachine2instanceName2'with
(secondary_role(read_only_routing_url='TCP://myMachine2:1433'))

 

4.       The READ_ONLY_ROUTING_LIST option must be set for each replica in the CREATE AVAILABILITY GROUP or ALTER AVAILABILITY GROUP Transact-SQL command, as part of the PRIMARY_ROLE replica options. The READ_ONLY_ROUTING_LIST can contain one or more routing targets. Multiple routing targets can be configured. Routing occurs in the order that targets are specified in routing list.

 alteravailabilitygroupmyGroupName
modifyreplicaon'myMachine1instanceName1'with
(primary_role(read_only_routing_list=('myMachine2instanceName2')))

GO

alteravailabilitygroupmyGroupName
modifyreplicaon'myMachine2instanceName2'with
(primary_role(read_only_routing_list=('myMachine1instanceName1')))

GO

Select Replica_Server_name,Endpoint_url,Secondary_Role_allow_Connections_Desc,Backup_Priority,  Read_only_Routing_URLfrom Sys.AVailability_replicas

selectdns_name,port,is_conformant,ip_configuration_string_from_cluster FROM sys.availability_group_listeners

 

5.       Then I installed the both KB to fix this issue System.ArgumentException: Keyword not supported: 'applicationintent'.

 

6.       To enable Kerberos, I configured in Active Directory by a domain administrator for each availability group listener name

Configure SPN in AD for each AG Listener name - A Server Principal Name (SPN) must be configured in Active Directory by a domain administrator for each availability group listener name in order to enable Kerberos for the client connection to the availability group listener. When registering the SPN, you must use the service account of the server instance that hosts the availability replica . For the SPN to work across all replicas, the same service account must be used for all instances in the WSFC cluster that hosts the availability group. (https://msdn.microsoft.com/en-us/library/hh213417.aspx#SPNs)

             setspn -A MSSQLSvc/myListenerName.FQDN:1433  myDomainmyserviceAccount    

  

7. Configuring the Windows Firewall to Allow SQL Server Access

 

 

If you got any connectivity issues, I invite you to read: How to resolve SQL Server connectivity issues

 

 

Regards,

Michel Degremont | Xbox Live Music - DBA Team - SQL Server & PDW |