Login synchornisation on Distributed always on AG #7845

EbenRaja · 2021-10-06T18:50:12Z

EbenRaja
Oct 6, 2021

Hello there,
I have used one of the feature put forth in article "keeping availability group logins in sync automatically" and it worked out well on AG servers, however on an DAG setup it fails when i trigger my PS script
I am perplexed why the listener is searching for the DAG name here & wished to know if something has to be altered or the parent script wouldn't work out for an DAG requirement to synch logins
Please advise

D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\AG\SyncLoginsToReplica.ps1 : Error while syncing logins for Availability
Group 'XXXXXX: Error connecting to [XXXXXXQAG]: The network path was not found'
At line:1 char:1

.\SyncLoginsToReplica.ps1

  + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
  + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,SyncLoginsToReplica.ps1

----Script used

define the AG name

$AvailabilityGroupName ='listenername'

internal variables

$ClientName = 'AG Login Sync helper'
$primaryInstance = $null
$secondaryInstances = @{}

try {
# connect to the AG listener, get the name of the primary and all secondaries
$replicas = Get-DbaAgReplica -SqlInstance $AvailabilityGroupName
$primaryInstance = $replicas | Where Role -eq Primary | select -ExpandProperty name
$secondaryInstances = $replicas | Where Role -ne Primary | select -ExpandProperty name
# create a connection object to the primary
$primaryInstanceConnection = Connect-DbaInstance $primaryInstance -ClientName $ClientName
# loop through each secondary replica and sync the logins
$secondaryInstances | ForEach-Object {
$secondaryInstanceConnection = Connect-DbaInstance $_ -ClientName $ClientName
Copy-DbaLogin -Source $primaryInstanceConnection -Destination $secondaryInstanceConnection -ExcludeSystemLogins
}
}
catch {
$msg = $_.Exception.Message
Write-Error "Error while syncing logins for Availability Group '$($AvailabilityGroupName): $msg'"
}

andreasjordan · 2021-10-28T16:35:52Z

andreasjordan
Oct 28, 2021
Collaborator

Can you run the commands in the try block one by one and tell me which command failed?

0 replies

EbenRaja · 2021-11-02T15:32:44Z

EbenRaja
Nov 2, 2021
Author

Hi Andreas, Thanking you for your kindness to review the concern, find below PS C:\Windows\system32> $replicas = Get-DbaAgReplica -SqlInstance $AvailabilityGroupName PS C:\Windows\system32> $primaryInstance = $replicas | Where Role -eq Primary | select -ExpandProperty name PS C:\Windows\system32> $secondaryInstances = $replicas | Where Role -ne Primary | select -ExpandProperty name PS C:\Windows\system32> $primaryInstanceConnection = Connect-DbaInstance $primaryInstance -ClientName $ClientName Connect-DbaInstance : Cannot bind argument to parameter 'SqlInstance' because it is null. At line:1 char:50 + ... maryInstanceConnection = Connect-DbaInstance $primaryInstance -Client ... + ~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidData: (:) [Connect-DbaInstance], ParameterBindingValidationException + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Connect-DbaInstance PS C:\Windows\system32> In define AG name i am issuing the Listener name as that connects both replicas on primary DC and the DR DC is an different cluster as its an DAG setup. Using 1.0.49 DBA tools version and got this error With latest version of DBA tools i was getting an direct error that the DAG name is not reachable and that was just an conventional name that we gave as DAG name during DAG setup and hence felt helpless. I am not an expert in SQL so ay guidance and gaps from my end then let me know Thanking you Eben # define the AG name $AvailabilityGroupName ='XXXXXXAG01'

…

On Thu, Oct 28, 2021 at 9:36 AM Andreas Jordan ***@***.***> wrote: Can you run the commands in the try block one by one and tell me which command failed? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#7845 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ANJUWA3TC7FWKAOLQF2DLQTUJGCYJANCNFSM5FPM66JA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

0 replies

EbenRaja · 2021-11-10T15:19:26Z

EbenRaja
Nov 10, 2021
Author

Hi Andreas, Where you are able to review the issue I am facing on Distributed always on AG. Appreciate your help on this regard BR Eben

…

On Tue, Nov 2, 2021 at 8:32 AM Eben Earnest ***@***.***> wrote: Hi Andreas, Thanking you for your kindness to review the concern, find below PS C:\Windows\system32> $replicas = Get-DbaAgReplica -SqlInstance $AvailabilityGroupName PS C:\Windows\system32> $primaryInstance = $replicas | Where Role -eq Primary | select -ExpandProperty name PS C:\Windows\system32> $secondaryInstances = $replicas | Where Role -ne Primary | select -ExpandProperty name PS C:\Windows\system32> $primaryInstanceConnection = Connect-DbaInstance $primaryInstance -ClientName $ClientName Connect-DbaInstance : Cannot bind argument to parameter 'SqlInstance' because it is null. At line:1 char:50 + ... maryInstanceConnection = Connect-DbaInstance $primaryInstance -Client ... + ~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidData: (:) [Connect-DbaInstance], ParameterBindingValidationException + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Connect-DbaInstance PS C:\Windows\system32> In define AG name i am issuing the Listener name as that connects both replicas on primary DC and the DR DC is an different cluster as its an DAG setup. Using 1.0.49 DBA tools version and got this error With latest version of DBA tools i was getting an direct error that the DAG name is not reachable and that was just an conventional name that we gave as DAG name during DAG setup and hence felt helpless. I am not an expert in SQL so ay guidance and gaps from my end then let me know Thanking you Eben # define the AG name $AvailabilityGroupName ='XXXXXXAG01' On Thu, Oct 28, 2021 at 9:36 AM Andreas Jordan ***@***.***> wrote: > Can you run the commands in the try block one by one and tell me which > command failed? > > — > You are receiving this because you authored the thread. > Reply to this email directly, view it on GitHub > <#7845 (comment)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/ANJUWA3TC7FWKAOLQF2DLQTUJGCYJANCNFSM5FPM66JA> > . > Triage notifications on the go with GitHub Mobile for iOS > <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> > or Android > <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. > >

0 replies

andreasjordan · 2021-11-11T17:54:55Z

andreasjordan
Nov 11, 2021
Collaborator

$primaryInstanceConnection = Connect-DbaInstance $primaryInstance -ClientName $ClientName is missing the parameter name -SqlInstance, so it should be $primaryInstanceConnection = Connect-DbaInstance -SqlInstance $primaryInstance -ClientName $ClientName

0 replies

EbenRaja · 2021-11-11T18:51:10Z

EbenRaja
Nov 11, 2021
Author

Hi Andreas, I am stuck with new error as in below after changing the line on code snippet and running the .ps on an whole i am now getting an different error as in below , so i tried to break the code by running the try block statement one by one and the error now is different *SHPPTHPLPAG01 *is my listener name and i am able to connect this listener from within and outside servers using SSMS tool. PS D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\AG> .\SyncLoginsToReplica.ps1 D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\AG\SyncLoginsToReplica.ps1 : Error while syncing logins for Availability Group '*SHPPTHPLPAG01: The network path was not found*' At line:1 char:1 + .\SyncLoginsToReplica.ps1 + ~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,SyncLoginsToReplica.ps1 *Error within try block when executed one by one :* PS D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\AG> $replicas = Get-DbaAgReplica -SqlInstance $AvailabilityGroupName PS D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\AG> $primaryInstance = $replicas | Where Role -eq Primary | select -Expan dProperty name PS D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\AG> $secondaryInstances = $replicas | Where Role -ne Primary | select -Ex pandProperty name PS D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\AG> $primaryInstanceConnection = Connect-DbaInstance -SqlInstance $primar yInstance -ClientName $ClientName Connect-DbaInstance : *Cannot bind argument to parameter 'SqlInstance' because it is null.At line:1 char:63* + ... onnection = Connect-DbaInstance -SqlInstance $primaryInstance -Client ... + ~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidData: (:) [Connect-DbaInstance], ParameterBindingValidationException + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Connect-DbaInstance

…

On Thu, Nov 11, 2021 at 9:55 AM Andreas Jordan ***@***.***> wrote: $primaryInstanceConnection = Connect-DbaInstance $primaryInstance -ClientName $ClientName is missing the parameter name -SqlInstance, so it should be $primaryInstanceConnection = Connect-DbaInstance -SqlInstance $primaryInstance -ClientName $ClientName — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#7845 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ANJUWAZZS3ZDOVDE2I43YOLULP7PTANCNFSM5FPM66JA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

0 replies

EbenRaja · 2021-11-11T18:53:43Z

EbenRaja
Nov 11, 2021
Author

Define and internal variable statements as in below # define the AG name $AvailabilityGroupName ='SHPPTHPLPAG01' # internal variables $ClientName = 'AG Login Sync helper' $primaryInstance = $null $secondaryInstances = @{}

…

On Thu, Nov 11, 2021 at 10:50 AM Eben Earnest ***@***.***> wrote: Hi Andreas, I am stuck with new error as in below after changing the line on code snippet and running the .ps on an whole i am now getting an different error as in below , so i tried to break the code by running the try block statement one by one and the error now is different *SHPPTHPLPAG01 *is my listener name and i am able to connect this listener from within and outside servers using SSMS tool. PS D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\AG> .\SyncLoginsToReplica.ps1 D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\AG\SyncLoginsToReplica.ps1 : Error while syncing logins for Availability Group '*SHPPTHPLPAG01: The network path was not found*' At line:1 char:1 + .\SyncLoginsToReplica.ps1 + ~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,SyncLoginsToReplica.ps1 *Error within try block when executed one by one :* PS D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\AG> $replicas = Get-DbaAgReplica -SqlInstance $AvailabilityGroupName PS D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\AG> $primaryInstance = $replicas | Where Role -eq Primary | select -Expan dProperty name PS D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\AG> $secondaryInstances = $replicas | Where Role -ne Primary | select -Ex pandProperty name PS D:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\AG> $primaryInstanceConnection = Connect-DbaInstance -SqlInstance $primar yInstance -ClientName $ClientName Connect-DbaInstance : *Cannot bind argument to parameter 'SqlInstance' because it is null.At line:1 char:63* + ... onnection = Connect-DbaInstance -SqlInstance $primaryInstance -Client ... + ~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidData: (:) [Connect-DbaInstance], ParameterBindingValidationException + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Connect-DbaInstance On Thu, Nov 11, 2021 at 9:55 AM Andreas Jordan ***@***.***> wrote: > $primaryInstanceConnection = Connect-DbaInstance $primaryInstance > -ClientName $ClientName is missing the parameter name -SqlInstance, so > it should be $primaryInstanceConnection = Connect-DbaInstance > -SqlInstance $primaryInstance -ClientName $ClientName > > — > You are receiving this because you authored the thread. > Reply to this email directly, view it on GitHub > <#7845 (comment)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/ANJUWAZZS3ZDOVDE2I43YOLULP7PTANCNFSM5FPM66JA> > . > Triage notifications on the go with GitHub Mobile for iOS > <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> > or Android > <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. > >

0 replies

andreasjordan · 2021-11-12T17:47:12Z

andreasjordan
Nov 12, 2021
Collaborator

Sorry, but I don't see clearly where the error is thrown. "The network path was not found" is a typical problem when the instance could not be found - so no problem inside of dbatools. Unfortunately, that's a little outside of what I can do on the side here.

0 replies

EbenRaja · 2021-11-15T15:17:31Z

EbenRaja
Nov 15, 2021
Author

Thank you Andreas for your time and attention to my issues, so you confirm to me that this has nothing to do with this script to not work properly on an Distributed always on AG setup and it wouldn't search for the DAG name ? as i remember somewhere it was searching for the DAG name and i am not able to reproduce that now . I am sticking with using the listener name only & since that connects for me internally and externally (remote) from server SSMS and hence the concern. I would work out to find an alternative solution for DAG, but I take this time to thank you since this script works well for my standard AG setup within the same DC. Thanking you Eben

…

On Fri, Nov 12, 2021 at 9:47 AM Andreas Jordan ***@***.***> wrote: Sorry, but I don't see clearly where the error is thrown. "The network path was not found" is a typical problem when the instance could not be found - so no problem inside of dbatools. Unfortunately, that's a little outside of what I can do on the side here. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#7845 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ANJUWA4Q4R5HLDUA6IOF6ELULVHKZANCNFSM5FPM66JA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Login synchornisation on Distributed always on AG #7845

{{title}}

Replies: 8 comments

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

Login synchornisation on Distributed always on AG #7845

EbenRaja Oct 6, 2021

define the AG name

internal variables

Replies: 8 comments

andreasjordan Oct 28, 2021 Collaborator

EbenRaja Nov 2, 2021 Author

EbenRaja Nov 10, 2021 Author

andreasjordan Nov 11, 2021 Collaborator

EbenRaja Nov 11, 2021 Author

EbenRaja Nov 11, 2021 Author

andreasjordan Nov 12, 2021 Collaborator

EbenRaja Nov 15, 2021 Author

EbenRaja
Oct 6, 2021

andreasjordan
Oct 28, 2021
Collaborator

EbenRaja
Nov 2, 2021
Author

EbenRaja
Nov 10, 2021
Author

andreasjordan
Nov 11, 2021
Collaborator

EbenRaja
Nov 11, 2021
Author

EbenRaja
Nov 11, 2021
Author

andreasjordan
Nov 12, 2021
Collaborator

EbenRaja
Nov 15, 2021
Author