SQLTeam.com | Weblogs | Forums

AlwaysON listener connectivity issue

Dears,
I have a 2 node AlwaysON Ag setup,both in same subnet.
I have two application servers that connect to the database using the listener name.
The problem is, when the node1 is primary both the application servers are connecting to the database without issues,but when I peform the failover only one of the application server is connecting and the other application server says,cannot update the database as it is readonly.

All the users and logins are exactly same with SID on both the nodes.
Databases are in sync on both the nodes.
Listener name and IP lookup is also fine.

Any help is appreciated.

Thanks
javeed

Are you using a sql.login or an AD account for the application? Does the application user exist on secondary and does it have the proper permissions?

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e26beae4-ebec-43d4-94c8-eb4f5b9775a7/sql-2012-alwayson-does-not-allow-database-update?forum=sqldisasterrecovery

It is using sql login. Yes, it is present on both the nodes with same permissions.

Verify the connection string(s) on the application server that is failing - and check for any aliases that may have been created on that server.

It appears that the connection string wasn't updated correctly and that server is connecting directly to the primary node. When the databases fail over - that application server is now connecting and attempting to read/write from the now secondary instance.

If someone setup an alias (client alias on the application server) - they may have set it up using the IP address of the primary node instead of setting it to the listener name.

There were no aliases created on the app servers, but I tried creating alias on the SQL server and looks its working fine now, application is able to connect without issues, also tested the connectivity after failover.