To configure Database Audit in AlwaysOn Availability Group (AAG), we cannot just simply create the SERVER AUDIT and the DATABASE AUDIT SPECIFICATION by using SSMS GUI, otherwise the database audit specification will lost its linkage to the server audit after the AAG failover back and forth. This is because how the database audit specification being mapped to a server audit is by an AUDIT_GUID uniqueidentifier, the GUID will be different among AAG replicas even the server audit name is the same when you create them using SSMS GUI (referring to sys.server_audits DMV and sys.database_audit_specifications DMV). To overcome this situation, you should create the server audit by specifying the same AUDIT_GUID in all AAG replicas, and then create the database audit specification by specifying the server audit name as usual. Below are the procedures:
1. In AAG Primary node, create the Server Audit using SSMS GUI.
2. Generate the creation script for the above server audit, so you got the AUDIT_GUID.
3. Execute the above server audit creation script on other AAG replicas.
4. In primary node, create the database audit specification as usual.
5. Run the following queries to see the database audit specification is correctly mapped to the server audit guid. Failover the AAG to other nodes to see the audit mapping still there.
SELECT * FROM sys.database_audit_specifications;
SELECT * FROM sys.server_audits;
According to the Microsoft documentation:
"To support scenarios such as database mirroring, an audit needs a specific GUID that matches the GUID found in the mirrored database. The GUID can't be modified after the audit is created."
You can see Microsoft doesn't clearly mention that you should pay attention on it. But actually it's critical if you rely on sql server audit to meet security compliance in AAG.
