How has the SQL Server Always On Availability Group (AG) synchronized the Security Objects Across Replicas (2012 - 2025)? Part I

In SQL Server Always On Availability Groups, security objects (logins, linked servers, credentials, SQL Server Agents jobs etc.) are stored at the instance level, not the database level. This means they are not automatically replicated across primary and secondary replicas. To maintain consistent security settings across all replicas, administrators must manually synchronize security objects.

Each SQL Server version (2012-2025) has introduced improvements in security synchronization. Below is a detailed breakdown of synchronization methods for each SQL Server version.

1. SQL Server 2012 - Manual Synchronization Using T-SQL
Key Methods:
Manually create logins on secondary replicas using T-SQL.

Manually synchronize database users with logins.
Transfer logins using sp_help_revlogin.

SQL Server 2012 lacked built-in tools for automated login synchronization.
Use Microsoft’s sp_help_revlogin script to export logins and recreate them on secondary replicas.

EXEC sp_help_revlogin;

Limitations:
Requires manual intervention every time a new login is created.
No automatic synchronization of security objects.