2016 SQL Standard - Always on Availability Group questions

[The_Boz]

Hello,

Thank you in advance for any assistance. I setup two 2019 servers with SQL 2016 Standard, with the hopes of setting them up with Always on Availability. I am at the point were the databases are synchronized between each server, but each time I perform a failover test, the Secondary data (which becomes primary) is unreadable. Event viewer logs display the following message:

One recommendation is to ensure that the Replicas have "Readable Secondary" set to yes, but that option is not available in SQL 2016 Standard.

I have also read forums, which indicates that I just need to "Resume Data Movement" after failover, but each time I check, the option is already enabled (Suspend is presented as an option).

I followed multiple guides when setting this environment up.

They are two 2019 servers, which are part of Windows Failover Cluster

The SQL databases, do you an Admin service account between each other.

Any other recommendations or suggestions would be greatly appreciated. 

Thank you,

[#2]

With further investigations, it looked like the SQL Accounts were getting orphaned after failover and was failing at login. After using:

exec sp_change_users_login 'auto_fix'

I was able to get access to the database. As a test, I failover again and the accounts got orphaned again. Trying to determine why the accounts get orphaned after a failover now.

[#3]

Hi @The_Boz  -- This is most likely occurring because the SQL logins were created separately on each Availability Group node.  When a SQL login is created, a unique SID is also created.

So while the SQL login names may be identical on each Availability Group node, the SIDs likely are not which is what is causing the orphaning.

You can use the process outlined at this link to transfer logins between the Availability Group nodes while maintaining the same SID.  Take care.

[#4]

@bake13 Thank you, this has been helpful.