In SQL Server Always On Availability Groups, logins are not automatically synchronized between replicas. While the availability group databases are synchronized, logins, agent jobs, SSIS packages, linked servers, and server configurations are not. This means that if a failover occurs and the primary replica becomes unavailable, the secondary replica may not have the necessary logins to support the applications.

To ensure a seamless failover and avoid login issues, it is important to manually synchronize the logins between the primary and secondary replicas. In this article, we will explore different methods to synchronize logins between SQL Server Always On Availability Group replicas.

Method 1: Manual Login Creation

The first method involves manually creating the login on the secondary replica. This can be done by executing a CREATE LOGIN script on the secondary replica, using the same password hash and SID as the login on the primary replica. By matching the SIDs between the primary and secondary replicas, the application will not face login issues during an availability group failover.

Example:

CREATE LOGIN [SQLAG2User] WITH PASSWORD=0x0200B9AD82CA73B5065CDCA01F3C5B225CA9D0B9D5DE7394F4701575E25CC343D04545FB8086EC8B356E57385CA9A505C45A2EF903B316544B90459FB7FA24392E5E04023226 HASHED, SID=0x04162837B648EC4AA174954E9C0960AF;

Method 2: Using DBATools PowerShell Module

Another method to synchronize logins between replicas is by using the DBATools PowerShell module. DBATools provides a collection of useful functions and cmdlets for performing database administration tasks for SQL Server. With DBATools, you can automate the synchronization of logins between replicas.

The following PowerShell script demonstrates how to use DBATools to synchronize logins:

$primaryReplica = Get-DbaAgReplica -SqlInstance "PrimaryReplica"
$secondaryReplicas = Get-DbaAgReplica -SqlInstance "SecondaryReplica"

$LoginsOnPrimary = Get-DbaLogin -SqlInstance $primaryReplica.Name

$secondaryReplicas | ForEach-Object {
    $LoginsOnSecondary = Get-DbaLogin -SqlInstance $_.Name
    $diff = $LoginsOnPrimary | Where-Object Name -notin $LoginsOnSecondary.Name
    if ($diff) {
        Copy-DbaLogin -Source $primaryReplica.Name -Destination $_.Name -Login $diff.Name
    }
}

Conclusion

Synchronizing logins between SQL Server Always On Availability Group replicas is crucial to ensure a smooth failover process and avoid login issues. By manually creating logins on the secondary replica or using tools like DBATools, you can automate the synchronization process and maintain consistency across replicas.

Remember to keep the logins synchronized and regularly update them if any changes occur on the primary replica. This will ensure that your applications continue to function seamlessly even during availability group failovers.