microsoft / microsoft/sqlmanagementobjects

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

Abierto
#201 0 comentarios 3 reacciones 0 asignados Ver en GitHub

Nadie ha tomado este issue todavía.

Lenguaje dominante
C#
Estrellas
143
Forks
28
Sin PR fusionados en 30 d

Descripción

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

Guía de contribución

No hay ninguna guía de contribución indexada para este repositorio

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Línea de trabajo

Empieza leyendo el informe enlazado de dbatools y siguiendo los puntos de entrada indicados: EnumProcesses() y la colección Databases, con el llamador mostrado en Copy-DbaLogin.ps1 en la línea 324. Reproduce el error del grupo de disponibilidad y compara ambas rutas; se considera terminado cuando el fallo intermitente se haya entendido y verificado frente al comportamiento de SMO afectado.

Escrito por el modelo de indexación a partir del texto del issue.

Evaluación

Stack tecnológico
csharp, powershell, sql
Área
backend-api-design, databases
Tipo de issue
Error
Dificultad
4/5
Tiempo estimado
3-5 días
Estado de actividad
Estancado
Claridad
Necesita aclaración
Aptitud para principiantes
35/100

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.