March 10, 2017 at 9:08 am
Is anyone hosting a Confluence instance and using AG?
We are testing and consistently getting errors that the user cannot login after performing a failover. We can restart the application and get it up and running on either of the two replicas, whichever is primary at the time, but the application does not survive failover. It reports a login failure for the user - which is odd since it can connect to either server and perform work. Just chokes on failover with "login failed" in the error log.
Are there some applications which simply will not work with AG?
March 10, 2017 at 9:18 am
We have it running in an AG setup right now. I'm not trying to teach you to suck eggs, but is your application connection string definitely pointing to the listener? Also, have you mapped all of the logins from one instance to the other?
March 10, 2017 at 9:31 am
Glad to hear it. I'm not the person who is touching the Confluence set up. It's new here, and I don't know that the application connection string is definitely pointing to the listener. The person doing that work found a config file where the server name was listed, and changed it there, but without confidence that it would do the job.
Can you tell me how to change the app connection string for Confluence? All he found was a 20 page doc on how to migrate to a new server, which isn't what we're doing, obviously.
In most cases, I'm working on both ends - app and db, but in this end, I'm uninvolved at the app end.
Yes, the user (an sql user) exists on both servers, same pwd. Created independently, so not same SID. I know I could get the user copied over with the same SID, if that would make a difference.
March 13, 2017 at 10:09 am
I can't log on at the moment to check my setup, but there are a couple of things you can check if you haven't already.
Run this script on the primary replica for your Confluence db, and take a look at the TargetIP address, which will tell you whether the connection string is connecting to the listener or direct to an instance- or maybe even to an alias. (Actually, are you using a DNS alias for either the instances or the AG listener, because that might add another layer of obfuscation?)
Compare the IP address with the AG listener's IP address. If they are the same, the connection string is deliberately pointing to the listener.
SELECT distinct
es.login_name
,d.name as db
,es.host_name AS Originating_Hostname
,es.program_name AS Program
,local_net_address AS TargetIP
FROM sys.dm_exec_connections ec
JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
inner join sys.databases d on d.database_id = es.database_id
--left outer join sys.availability_group_listener_ip_addresses ag on local_net_address = ag.ip_address
WHERE local_net_address IS NOT NULL
SOURCE: I found a version of this script here, to give credit where it's due: http://www.patrickkeisler.com/2013/08/how-to-tell-if-your-users-are.html
We're using AD authentication for our setup.
March 13, 2017 at 11:23 am
When you created the SQL logins on the second server, did you specify the same SID and password as the logins have on primary? If not, your database users are orphaned (SID mismatch) and won't be usable.
There's lots of login migration scripts that script out logins from one server with SID and hashed password, so that you can create identical logins on the secondary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2017 at 5:46 pm
Yes - this is the first time we used SQL user on the db in the availability group. I do know how to migrate users to retain SID, just hadn't put it together that that was needed here and was the issue.
Thanks for the responses. We're in business!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply