AlwaysON database not synchronizing mode SQL service account change
Suddenly, one of our alwaysON server databases are went not synchronizing mode.
Errors from the log
The availability group database “DB” is changing roles from “RESOLVING” to “PRIMARY” because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.
It is due to changes in service account from an existing account to the new account. It was a GUI setup, the GUI creates all behind the screen. The endpoint uses a dedicated SQL server service account for creating an endpoint.
Endpoints in AlwaysOn Availability Groups or database mirroring. Read the under “Determining the Authentication Type for a Database Mirroring Endpoint”
Here is the DMV from dba stackexchange http://dba.stackexchange.com/questions/29468/how-can-i-inspect-the-security-settings-of-a-sql-server-endpoint
DMV query to find the endpoint name of the SQL server service account is used.
SELECT ep.name, sp2.name AS Grantee, sp.name AS Grantor, p.permission_name, ep.state_desc FROM sys.server_permissions p INNER JOIN sys.endpoints ep ON p.major_id = ep.endpoint_id INNER JOIN sys.server_principals sp ON p.grantor_principal_id = sp.principal_id INNER JOIN sys.server_principals sp2 ON p.grantee_principal_id = sp2.principal_id WHERE p.class_desc = 'ENDPOINT' AND ep.type_desc = 'DATABASE_MIRRORING'