Sync-DbaAvailabilityGroup intermittently working where secondary is setup for readonly routing

Verified issue does not already exist?

No, I did not search

What error did you receive?

I'm using Sync-DbaAvailabilityGroup to sync all the objects between the Primary and Secondary. This working just fine on all our AlwaysOn Clustered SQL Servers, except where the secondary is configured to be readonly. I have tried this on two AlwaysOn Clustered SQL Servers configured for readonly routing. One set is Pre-Prod (so not as busy as Prod) and the other one is a Production Cluster which is busy most of the time.
The Sync-DbaAvailabilityGroup job works 99.9 times on the PreProd and fails about 85% on the Production servers. Everything I have reserached points to readonly routing issue as this can be hit an miss because of the different connections the sub commands are marking for the objects to sync.

It says the target database is available for readonly connections. See error below:"WARNING: Failed to sync Job syspolicy_purge_history: The following exception occurred while trying to enumerate the collection: "The target database ('xxxx') is in an availability group and is currently accessible for connections when the application intent is set to read only.

So my question is, is this a known issue that Sync-DbaAvailabilityGroup job does not correctly work on AlwaysOn configurations where readonly routing is setup? If so, is there a timeline to fix this? If not, does anyone know how to get around this?

Steps to Reproduce

$AG = Get-DbaAvailabilityGroup -sqlinstance $SqlInstance -AvailabilityGroup $AvailabilityGroup -IsPrimary

if ($AG.IsPrimary -eq $true) {

      #$AvailabilityGroup = 'RRRRRRRRRRRRRRRRRRRRR'   
    # Replicate all dependent local objects to SecondaryReplica
    write-host "Job is running on PrimaryReplica. Syncing all dependent local objects."

    #$SyncResults = Sync-DbaAvailabilityGroup -Primary $SQLInstance -AvailabilityGroup $AvailabilityGroup -DestinationSqlInstance $DestinationSqlInstance
    $SyncResults = Sync-DbaAvailabilityGroup -Primary $SQLInstance -AvailabilityGroup $AvailabilityGroup -Secondary  $Secondary 
    $SyncResults | ft -AutoSize -Wrap
    
    
    Foreach ($SyncResult in $SyncResults) {
        if ($SyncResult.Status -eq 'Failed') {

        $WarningMess = $SyncResult.Type + ' ' + $SyncResult.Name + ' ' + $SyncResult.Notes
        SendAlert
        }
    }

Please confirm that you are running the most recent version of dbatools

I am using version 2.1.30

Script 2.1.30 dbatools {Select-DbaObject, Set-DbatoolsConfig, Add-DbaAgDatabase, Add-DbaAgListener...}

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.17763.7009
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.7009
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM-CU30) (KB5049235) - 15.0.4415.2 (X64) Nov 18 2024 17:45:37 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: )

.NET Framework Version

.NET Framework 4.7.4126.0

[mizantronix]

Hi, I've encountered similar behavior.

I'm trying to use Copy-DbaLogin to copy a SQL login from a primary to secondary replicas:

Type Name Status Notes
---- ---- ------ -----
Login - SqlLogin TestLogin198 Failed The target database ('my_db_name') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

Note: The login's default database is master

Then I tied Sync-DbaAvailabilityGroup with everything excluded except Logins (e.g. -Exclude SpConfigure, CustomErrors, ...) — same error.
However, when I manually invoke New-DbaLogin with the same parameters (like in Copy-DbaLogin.ps1, line 362), it works correctly — no error, login is created, and it doesn't attempt to access any user databases.

---

Environment:

• Docker container based on microsoft/mssql-tools
• PowerShell 7.5.1
• dbatools 2.1.31

---

Let me know if I can provide any further details or help dig into it.

Thanks!

[jpomfret]

I'm also seeing this behaviour for copy-dbalogin - it is very intermittent as far as which login fails, and which of the databases it complains about being in an AG. It seems like an issue with smo - this is the get-error output.

Exception             : 
    Type                        : System.Management.Automation.MethodInvocationException
    ErrorRecord                 : 
        Exception             : 
            Type    : System.Management.Automation.ParentContainsErrorRecordException
            Message : Exception calling "EnumProcesses" with "0" argument(s): "Enumerate processes failed for Server 'SERVERNAME'. "
            HResult : -2146233087
        CategoryInfo          : NotSpecified: (:) [], ParentContainsErrorRecordException
        FullyQualifiedErrorId : FailedOperationException
        InvocationInfo        : 
            ScriptLineNumber : 324
            OffsetInLine     : 33
            HistoryId        : 81
            ScriptName       : C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1
            Line             : $activeConnections = $destServer.EnumProcesses() | Where-Object Login -eq $newUserName
                               
            Statement        : $activeConnections = $destServer.EnumProcesses() | Where-Object Login -eq $newUserName
            PositionMessage  : At C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1:324 char:33
                               +              $activeConnections = $destServer.EnumProcesses() | Where- 
                               +               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            PSScriptRoot     : C:\temp\loginrefresh\dbatools\dbatools\public
            PSCommandPath    : C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1
            CommandOrigin    : Internal
        ScriptStackTrace      : at Copy-Login, C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1: line 324
                                at Copy-DbaLogin<Process>, C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1: line 462
                                at Select-DefaultView<Process>, C:\temp\loginrefresh\dbatools\dbatools\private\functions\Select-DefaultView.ps1: line 70
                                at Get-DbaLogin<Process>, C:\temp\loginrefresh\dbatools\dbatools\public\Get-DbaLogin.ps1: line 281
                                at <ScriptBlock>, C:\temp\loginrefresh\login-loop.ps1: line 24
                                at <ScriptBlock>, C:\temp\loginrefresh\login-loop.ps1: line 22
                                at <ScriptBlock>, <No file>: line 1
    WasThrownFromThrowStatement : True
    TargetSite                  : 
        Name          : CheckActionPreference
        DeclaringType : [System.Management.Automation.ExceptionHandlingOps]
        MemberType    : Method
        Module        : System.Management.Automation.dll
    Message                     : Exception calling "EnumProcesses" with "0" argument(s): "Enumerate processes failed for Server 'SERVERNAME'. "
    Data                        : System.Collections.ListDictionaryInternal
    InnerException              : 
        Type             : Microsoft.SqlServer.Management.Smo.FailedOperationException
        SmoExceptionType : FailedOperationException
        Operation        : Enumerate processes
        FailedObject     : [SERVERNAME]
        Message          : Enumerate processes failed for Server 'SERVERNAME'. 
        HelpLink         : https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=17.100.76.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Enumerate+processes+Server&LinkId=20476
        TargetSite       : 
            Name          : EnumProcesses
            DeclaringType : [Microsoft.SqlServer.Management.Smo.Server]
            MemberType    : Method
            Module        : Microsoft.SqlServer.Smo.dll
        Data             : System.Collections.ListDictionaryInternal
        InnerException   : 
            Type           : Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException
            TargetSite     : 
                Name          : Process
                DeclaringType : [Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator]
                MemberType    : Method
                Module        : Microsoft.SqlServer.Management.Sdk.Sfc.dll
            Message        : Failed to retrieve data for this request.
            Data           : System.Collections.ListDictionaryInternal
            InnerException : 
                Type               : Microsoft.Data.SqlClient.SqlException
                Errors             : 
                    Source     : Core Microsoft SqlClient Data Provider
                    Number     : 978
                    State      : 1
                    Class      : 14
                    Server     : SERVERNAME
                    Message    : The target database ('DBNAME') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.
                    LineNumber : 1
                ClientConnectionId : 48273446-1530-408d-9685-b3f0e9eb411b
                Class              : 14
                LineNumber         : 1
                Number             : 978
                Server             : SERVERNAME
                State              : 1
                Source             : Core Microsoft SqlClient Data Provider
                ErrorCode          : -2146232060
                TargetSite         : 
                    Name          : ThrowExceptionAndWarning
                    DeclaringType : [Microsoft.Data.SqlClient.TdsParser]
                    MemberType    : Method
                    Module        : Microsoft.Data.SqlClient.dll
                Message            : The target database ('DBNAME') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.
                Data               : System.Collections.ListDictionaryInternal
                HResult            : -2146232060
                StackTrace         : 
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at Microsoft.Data.SqlClient.SqlDataReader.Read()
   at Microsoft.SqlServer.Management.Smo.DataProvider.ReadInternal()
   at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
            Source         : Microsoft.SqlServer.Management.Sdk.Sfc
            HResult        : -2146233088
            StackTrace     : 
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorData(Request req)
   at Microsoft.SqlServer.Management.Smo.Server.EnumProcesses()
        Source           : Microsoft.SqlServer.Smo
        HResult          : -2146233088
        StackTrace       : 
   at Microsoft.SqlServer.Management.Smo.Server.EnumProcesses()
   at CallSite.Target(Closure, CallSite, Object)
    Source                      : System.Management.Automation
    HResult                     : -2146233087
    StackTrace                  : 
   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : FailedOperationException
InvocationInfo        : 
    ScriptLineNumber : 324
    OffsetInLine     : 33
    HistoryId        : 81
    ScriptName       : C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1
    Line             : $activeConnections = $destServer.EnumProcesses() | Where-Object Login -eq $newUserName
                       
    Statement        : $activeConnections = $destServer.EnumProcesses() | Where-Object Login -eq $newUserName
    PositionMessage  : At C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1:324 char:33
                       +              $activeConnections = $destServer.EnumProcesses() | Where- 
                       +               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    PSScriptRoot     : C:\temp\loginrefresh\dbatools\dbatools\public
    PSCommandPath    : C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1
    CommandOrigin    : Internal
ScriptStackTrace      : at Copy-Login, C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1: line 324
                        at Copy-DbaLogin<Process>, C:\temp\loginrefresh\dbatools\dbatools\public\Copy-DbaLogin.ps1: line 462
                        at Select-DefaultView<Process>, C:\temp\loginrefresh\dbatools\dbatools\private\functions\Select-DefaultView.ps1: line 70
                        at Get-DbaLogin<Process>, C:\temp\loginrefresh\dbatools\dbatools\public\Get-DbaLogin.ps1: line 281
                        at <ScriptBlock>, C:\temp\loginrefresh\login-loop.ps1: line 24
                        at <ScriptBlock>, C:\temp\loginrefresh\login-loop.ps1: line 22
                        at <ScriptBlock>, <No file>: line 1
                        ```

[potatoqualitee]

Need solution from Microsoft 🙏🏼