Intermittent errors with SMO expanding databases when they're in an AG

まずリンク先の dbatools レポートを読み、報告されたエントリポイントである EnumProcesses() と Databases コレクションを追跡します。呼び出し元は Copy-DbaLogin.ps1 の 324 行目に示されています。可用性グループのエラーを再現して両方の経路を比較します。断続的な失敗を理解し、影響を受ける SMO の動作に照らして検証できれば完了です。

So this is hard to explain, there is a bug report for dbatools that I am also experiencing, where running Copy-DbaLogin works for most users most of the time, but pretty much every day I get at least one (different login, different database in error message) that fails out with the following error:

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.

The normal path is that these databases are just skipped, but occasionally it seems like SMO tries to get in for some reason.

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
                        ```

these are the commands that seem to throw the errors most often, but there is more info on the dbatools issue.

$destServer = Connect-DbaInstance -SqlInstance mssql1
$activeConnections = $destServer.EnumProcesses() | Where-Object Login -eq $newUserName
#or
$ownedDbs = $destServer.Databases | Where-Object Owner -eq $newUserName