microsoft / microsoft/sqlmanagementobjects

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

オープン
#201 コメント 0 件 リアクション 3 件 担当者 0 名 GitHub で見る

まだ誰も着手していません。

主要言語
C#
スター
143
フォーク
28
30日以内にマージされた PR はありません

説明

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

コントリビューションガイド

このリポジトリのコントリビューションガイドは索引されていません

はじめの一歩

  1. issue を最後まで読み、次にプロジェクトのコントリビューションガイドを読みます。
  2. 着手することを issue にコメントします — 二人が同じ作業をするのを防げます。
  3. リポジトリをフォークし、ブランチを切って変更します。
  4. issue 番号を参照したプルリクエストを送ります。

調査の方向性

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

索引モデルが issue の本文から書いたものです。

評価

技術スタック
csharp, powershell, sql
領域
backend-api-design, databases
issue の種類
バグ
難易度
4/5
見積もり時間
3〜5日
活発さ
停滞
明瞭さ
説明が足りない
初心者へのやさしさ
35/100

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。