Slackbot
10/19/2020, 8:48 PMTim Riegler
10/19/2020, 8:54 PMRay Davis
10/19/2020, 8:55 PMRay Davis
10/19/2020, 8:55 PMTim Riegler
10/19/2020, 8:57 PMRay Davis
10/19/2020, 8:58 PMDennis Parker
10/19/2020, 10:40 PMRay Davis
10/20/2020, 10:55 AMDennis Parker
10/20/2020, 1:44 PM<#
Script : SyncLoginsToDistributedAG.ps1
Author : Andreas Schubert (http://www.linkedin.com/in/schubertandreas)
Purpose: Sync logins between all replicas in an Availability Group automatically.
--------------------------------------------------------------------------------------------
The script will connect to the listener name of the Availability Group
and read all replica instances to determine the current primary replica and all secondaries.
It will then connect directly to the current primary, query all Logins and create them on each
secondary.
Attention:
The script is provided so that no action is actually executed against the secondaries (switch -WhatIf).
Change that line according to your logic, you might want to exclude other logins or decide to not drop
any existing ones.
--------------------------------------------------------------------------------------------
Usage: Save the script in your file system, change the name of the AG Listener (AGListenerName in this template)
and schedule it to run at your prefered schedule. I usually sync logins once per hour, although
on more volatile environments it may run as often as every minute
#>
param (
# define the AG name
[Parameter(Mandatory=$true)][string]$AvailabilityGroupName,
[string]$ClientName = 'AG Login Sync helper'
)
# internal variables
#$ClientName = 'AG Login Sync helper'
$primaryInstance = $null
$secondaryInstances = @{}
[array]$PrimaryAGSecondaryInstances = @{}
[array]$SecondaryAGsecondaryInstances = @{}
$AvailabilityGroupListener = $AvailabilityGroupName + "-L"
try {
# connect to the Distributed AG listener, get the name of the primary and secondary AGs
$DistAG = Get-DbaAgReplica -SqlInstance $AvailabilityGroupListener -AvailabilityGroup $AvailabilityGroupName
$primaryInstance = $DistAG | Where Role -eq Primary | select -ExpandProperty name
$primaryInstanceListener = $primaryInstance + "-L"
$PrimaryAG = Get-DbaAgReplica -SqlInstance $primaryInstanceListener -AvailabilityGroup $primaryInstance
$PrimaryAGPrimaryInstance = $PrimaryAG | Where Role -eq Primary | select -ExpandProperty name
$PrimaryAGSecondaryInstances = $PrimaryAG | Where Role -ne Primary | select -ExpandProperty name
$SecondaryAGname = $DistAG | Where Role -ne Primary | select -ExpandProperty name
$SecondaryAGlistener = $SecondaryAGname + "-L"
#All Secondary AG servers are, by definition, secondary instances
$SecondaryAGsecondaryInstances = Get-DbaAgReplica -SqlInstance $SecondaryAGlistener -AvailabilityGroup $SecondaryAGname | select -ExpandProperty name
$secondaryInstances = $PrimaryAGSecondaryInstances + $SecondaryAGsecondaryInstances
# create a connection object to the primary
$primaryInstanceConnection = Connect-DbaInstance $PrimaryAGPrimaryInstance -ClientName $ClientName
# loop through each secondary replica and sync the logins
$secondaryInstances | ForEach-Object {
$secondaryInstanceConnection = Connect-DbaInstance $_ -ClientName $ClientName
Get-DbaLogin -SqlInstance $primaryInstanceConnection -ExcludeSystemLogin -Type SQL -ExcludeFilter '##*' | where { $_.DateLastModified -gt ((Get-Date).AddDays(-1)) } | % { Copy-DbaLogin -Source $primaryInstanceConnection -Destination $secondaryInstanceConnection -Login $_.Name -Force }
Copy-DbaLogin -Source $primaryInstanceConnection -Destination $secondaryInstanceConnection -ExcludeSystemLogins
}
}
catch {
$msg = $_.Exception.Message
Write-Error "Error while syncing logins for Availability Group '$($AvailabilityGroupName): $msg'"
}c4rm0
10/20/2020, 1:59 PMc4rm0
10/20/2020, 2:01 PMRay Davis
10/20/2020, 2:28 PMRay Davis
10/20/2020, 2:28 PM