April 16, 2021 at 7:04 pm
Folk, I hope this message finds you well
I have two issues with Availability Group that I am hoping to get some feedback on
Issue One: Application not working when failed over to different server
The issue here is that when SQLServerA is the primary node the application works fine. When we failover to SQLServerB and it becomes the primary the application no longer connects. The application uses an ODBC connection and will not connect. More specifically, the ODBC will connect to only 2 of the 5 database. Also each database uses a different SQL Login.
I checked the SID ID of all five SQL Logins used to connect the application to SQL server and they are exactly the same. One thing I did notice is that when failing over to SQLServerB and I check the properties of the SQL Login I do not see that login mapped to that database. When I tired to map it - I got a message saying ... the user already exists in the database.
With the SID the same why do you think I am having the trouble and what do you recommend I check
Issue Two: Databases Not Synchronizing
So we get a network blip and the secondary goes off line for a minute and then comes back on line. All database in the secondary are going from Yellow to Green just fine. We often get one or maybe two database what will not change over to Green. Its like there is no data movement between the Primary and the secondary for those databases. The connection is fine. I look at the LSN and look at time to recovery and it just sits there.
I tried ,from the secondary only, to suspend data movement and and then resume data movement and no luck. Would I have to do this from the primary?
I do not want to take the DB out of AG and then add back, so what can I do to make it start synchronizing again.
Is removing and adding the DB back to AG the only way?
Please any feedback is appreciated.
Jeff
April 17, 2021 at 7:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
April 17, 2021 at 8:30 pm
Hi,
1. Double/triple check the sids because looks like they differ if there are no matching, or if you can just recreate them using sp_help_revlogin proc
2. Check the error log during failover or issue it is messy but there should be info why db are not syncing, or try
-- Good overview of AG health and status (Query 17) (AlwaysOn AG Status)
SELECT ag.name AS [AG Name], ar.replica_server_name, ar.availability_mode_desc, adc.[database_name],
drs.is_local, drs.is_primary_replica, drs.synchronization_state_desc, drs.is_commit_participant,
drs.synchronization_health_desc, drs.recovery_lsn, drs.truncation_lsn, drs.last_sent_lsn,
drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn,
drs.last_hardened_time, drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size,
drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate,
drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.database_state_desc
FROM sys.dm_hadr_database_replica_states AS drs WITH (NOLOCK)
INNER JOIN sys.availability_databases_cluster AS adc WITH (NOLOCK)
ON drs.group_id = adc.group_id
AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag WITH (NOLOCK)
ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar WITH (NOLOCK)
ON drs.group_id = ar.group_id
AND drs.replica_id = ar.replica_id
ORDER BY ag.name, ar.replica_server_name, adc.[database_name] OPTION (RECOMPILE);
April 18, 2021 at 2:46 pm
Thank you very much for the help
I will use the SP_help_Revlogin to recreate the logins on the secondary
I will also try the query see what the results are.
Jeff
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply