SQL Server: Synchronize logins on AlwaysOn replicas with dbatools

The SQL Server environment  I worked with today has dozens of SQL Server instances using AlwaysOn Availability Groups for High Availability.
When a login is created on the Primary replica of an Availability Group it is not synchronized automatically on secondary replicas. This might cause some issues after a failover (Failed logins).

Since this is not done automatically by SQL Server out of the box the DBA has to perform this task.
To avoid doing this with T-SQL (sp_help_revlogin) or SSMS I use the magical dbatools and perform the following tasks once a week.

1. Get the number of logins on each instance.

$primary = Get-DbaLogin -SqlInstance 'SQL01\APPX'
$primary.Count

$secondary = Get-DbaLogin -SqlInstance 'SQL02\APPX'
$secondary.Count

PS C:\> $primary.Count
41
PS C:\> $secondary.Count
40

2. If numbers don’t match, I use the Copy-Login function to synchronize the missing login.

Copy-DbaLogin -Source 'SQL01\APPX' -Destination 'SQL02\APPX' `
	-Login (($primary | Where-Object Name -notin $secondary.Name).Name)

PS C:\>
Type             Name         Status     Notes
----             ----         ------     -----
Login - SqlLogin loginName    Successful

Obviously, there are many drawbacks to this process;

• Having the same number of logins doesn’t mean they are actually the same.
  Some logins can be missing on both sides compared to the other one and both instances have the same number of logins.
• I need to know which instance is the current primary replica (-Source in Copy-DbaLogin)
• This is a manual process I do on every pair of instances using AlwaysOn.
• I want a script that can manage any number of secondary replica

So I decided to write a new script that would automatically synchronize login from primary replicas to all secondary replicas. The only parameter I want to use as input for this script is the name of the listener.

Here is the simplest version of this script I could write;

$lsn = 'APP01-LSTN'

$primaryReplica =    Get-DbaAgReplica -SqlInstance $lsn | Where Role -eq Primary
$secondaryReplicas = Get-DbaAgReplica -SqlInstance $lsn | Where Role -eq Secondary

# primary replica logins
$primaryLogins = (Get-DbaLogin -SqlInstance $primaryReplica.Name)

$secondaryReplicas | ForEach-Object {
    # secondary replica logins
    $secondaryLogins = (Get-DbaLogin -SqlInstance $_.Name)

    $diff = $primaryLogins | Where-Object Name -notin ($secondaryLogins.Name)
    if($diff) {
        Copy-DbaLogin -Source $primaryReplica.Name -Destination $_.Name -Login $diff.Nane
    }   
}

Using just the listener name with Get-DbaAgReplica I can get all the replicas by Role, either Primary or Secondary.
Then I just need to loop through the secondary replicas and call Copy-DbaLogin.

I use a Central Management Server as an inventory for my SQL servers. I have groups containing only listeners.

The list of listeners can be easily retrieved from the CMS with Get-DbaRegisteredServer.

$Listeners= Get-DbaRegisteredServer -SqlInstance 'MyCmsInstance' | Where-Object {$_.Group -Like '*Prod*Listener'};

Now, looping through each listener I can sync dozens of secondary replicas in my SQL Server Prod environment with a single script run.
I had some issues with instances having multiple availability groups so I added: “Sort-Object -Unique”.
Notice I also filtered out some logins I don’t want to synchronize.

$Listeners = Get-DbaRegisteredServer -SqlInstance 'MyCmsInstance' | Where-Object {$_.Group -Like '*Prod*Listener*'};

foreach ($lsn in $Listeners) {

    $primaryReplica =    Get-DbaAgReplica -SqlInstance $lsn.ServerName | Where Role -eq Primary | Sort-Object Name -Unique
    $secondaryReplicas = Get-DbaAgReplica -SqlInstance $lsn.ServerName | Where Role -eq Secondary | Sort-Object Name -Unique
    <#
    Some instances have more than 1 AvailabilityGroup
        => Added Sort-Object -Unique
    #>

    # primary replica logins
    $primaryLogins = (Get-DbaLogin -SqlInstance $primaryReplica.Name -ExcludeFilter '##*','NT *','BUILTIN*', '*

Do not test this script in Production. Try it in a safe environment first, then remove the "-WhatIf" switch.
The next step for me might be to run this script on a schedule. Or even better, trigger the execution after an Availability Group failover?

Copy-DbaLogin is one of many dbatools commands that can be very useful to synchronize objects between instances. You can find a few examples below.



 	
Copy-DbaAgentJob


 	
Copy-DbaCredentials


 	
Copy-DbaLinkedServer


 	
Copy-DbaXESession



)
    
    $secondaryReplicas | ForEach-Object {
        # secondary replica logins
        $secondaryLogins = (Get-DbaLogin -SqlInstance $_.Name -ExcludeFilter '##*','NT *','BUILTIN*', '*

Do not test this script in Production. Try it in a safe environment first, then remove the "-WhatIf" switch.
The next step for me might be to run this script on a schedule. Or even better, trigger the execution after an Availability Group failover?

Copy-DbaLogin is one of many dbatools commands that can be very useful to synchronize objects between instances. You can find a few examples below.



 	
Copy-DbaAgentJob


 	
Copy-DbaCredentials


 	
Copy-DbaLinkedServer


 	
Copy-DbaXESession



)
        
        $diff = $primaryLogins | Where-Object Name -notin ($secondaryLogins.Name)
        if($diff) {
            Copy-DbaLogin -Source $primaryReplica.Name -Destination $_.Name -Login ($diff.Nane) -Whatif
        } 
    }  
}

Do not test this script in Production. Try it in a safe environment first, then remove the “-WhatIf” switch.
The next step for me might be to run this script on a schedule. Or even better, trigger the execution after an Availability Group failover?

Copy-DbaLogin is one of many dbatools commands that can be very useful to synchronize objects between instances. You can find a few examples below.

• Copy-DbaAgentJob
• Copy-DbaCredentials
• Copy-DbaLinkedServer
• Copy-DbaXESession