search

dataplat / dbatools

🚀 SQL Server automation and instance migrations have never been safer, faster or freer
https://dbatools.io
MIT License
2.39k stars 788 forks source link

Error Generating Creation Script for Distributed AG with dbatools #9220

Open 0x7FFFFFFFFFFFFFFF opened 5 months ago

0x7FFFFFFFFFFFFFFF commented 5 months ago

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

When I attempt to generate the creation script for a normal Availability Group (AG) using dbatools, the script executes successfully.

PS C:\WINDOWS\system32> Get-DbaAvailabilityGroup -SqlInstance $s -AvailabilityGroup ag1 | Export-DbaScript

    Directory: C:\Users\user1\Documents\DbatoolsExport

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----        1/21/2024  10:40 PM           2154 node1-20240121224006-script.sql

PS C:\WINDOWS\system32>

However, running the same script against a distributed AG results in an error.

PS C:\WINDOWS\system32> Get-DbaAvailabilityGroup -SqlInstance $s -AvailabilityGroup DAG_TEST | Export-DbaScript
WARNING: [22:40:27][Export-DbaScript] Failure on node1 | System.Management.Automation.MethodInvocationException: Exception calling "EnumScript" with "1" argument(s): "Script failed for
Availability Group 'DAG_TEST'. " ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Script failed for Availability Group 'DAG_TEST'.  ---> System.ArgumentException:
 Unexpected value for enumeration type AvailabilityGroupAutomatedBackupPreference.
   at Microsoft.SqlServer.Management.Smo.AvailabilityGroup.GetAutomatedBackupPreferenceScript(AvailabilityGroupAutomatedBackupPreference preference)
   at Microsoft.SqlServer.Management.Smo.AvailabilityGroup.ScriptGroupOption(Boolean scriptAll, String propertyName, SqlServerVersionInternal targetServerVersion)
   at Microsoft.SqlServer.Management.Smo.AvailabilityGroup.<>c__DisplayClass83_0.<ScriptCreateGroupOptions>b__1(String propertyName)
   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at System.String.Join(String separator, IEnumerable`1 values)
   at Microsoft.SqlServer.Management.Smo.AvailabilityGroup.ScriptCreate(StringCollection query, ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObject(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable`1 urns)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptUrns(List`1 orderedUrns)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)
   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(Urn[] urns, SqlSmoObject[] objects)
   at Microsoft.SqlServer.Management.Smo.Scripter.Script(Urn[] urns, SqlSmoObject[] objects)
   at CallSite.Target(Closure , CallSite , Object , Object )
   --- End of inner exception stack trace ---
   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)
PS C:\WINDOWS\system32> $error[0] | select *

writeErrorStream      : True
PSMessageDetails      :
Exception             : System.Exception: Failure on node1 | System.Management.Automation.MethodInvocationException: Exception calling "EnumScript" with "1" argument(s): "Script failed
                        for Availability Group 'DAG_TEST'. " ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Script failed for Availability Group 'DAG_TEST'.
                        ---> System.ArgumentException: Unexpected value for enumeration type AvailabilityGroupAutomatedBackupPreference.
                           at Microsoft.SqlServer.Management.Smo.AvailabilityGroup.GetAutomatedBackupPreferenceScript(AvailabilityGroupAutomatedBackupPreference preference)
                           at Microsoft.SqlServer.Management.Smo.AvailabilityGroup.ScriptGroupOption(Boolean scriptAll, String propertyName, SqlServerVersionInternal targetServerVersion)
                           at Microsoft.SqlServer.Management.Smo.AvailabilityGroup.<>c__DisplayClass83_0.<ScriptCreateGroupOptions>b__1(String propertyName)
                           at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
                           at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
                           at System.String.Join(String separator, IEnumerable`1 values)
                           at Microsoft.SqlServer.Management.Smo.AvailabilityGroup.ScriptCreate(StringCollection query, ScriptingPreferences sp)
                           at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript)
                           at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObject(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
                           at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
                           at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable`1 urns)
                           at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptUrns(List`1 orderedUrns)
                           at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)
                           at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)
                           at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
                           at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
                           --- End of inner exception stack trace ---
                           at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
                           at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(Urn[] urns, SqlSmoObject[] objects)
                           at Microsoft.SqlServer.Management.Smo.Scripter.Script(Urn[] urns, SqlSmoObject[] objects)
                           at CallSite.Target(Closure , CallSite , Object , Object )
                           --- End of inner exception stack trace ---
                           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)
TargetObject          : node1
CategoryInfo          : NotSpecified: (node1:PSObject) [Write-Error], Exception
FullyQualifiedErrorId : dbatools_Export-DbaScript,Stop-Function
ErrorDetails          : Failure on node1 | System.Management.Automation.MethodInvocationException: Exception calling "EnumScript" with "1" argument(s): "Script failed for Availability
                        Group 'DAG_TEST'. " ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Script failed for Availability Group 'DAG_TEST'.  --->
                        System.ArgumentException: Unexpected value for enumeration type AvailabilityGroupAutomatedBackupPreference.
                           at Microsoft.SqlServer.Management.Smo.AvailabilityGroup.GetAutomatedBackupPreferenceScript(AvailabilityGroupAutomatedBackupPreference preference)
                           at Microsoft.SqlServer.Management.Smo.AvailabilityGroup.ScriptGroupOption(Boolean scriptAll, String propertyName, SqlServerVersionInternal targetServerVersion)
                           at Microsoft.SqlServer.Management.Smo.AvailabilityGroup.<>c__DisplayClass83_0.<ScriptCreateGroupOptions>b__1(String propertyName)
                           at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
                           at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
                           at System.String.Join(String separator, IEnumerable`1 values)
                           at Microsoft.SqlServer.Management.Smo.AvailabilityGroup.ScriptCreate(StringCollection query, ScriptingPreferences sp)
                           at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript)
                           at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObject(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
                           at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
                           at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable`1 urns)
                           at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptUrns(List`1 orderedUrns)
                           at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)
                           at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)
                           at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
                           at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
                           --- End of inner exception stack trace ---
                           at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
                           at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(Urn[] urns, SqlSmoObject[] objects)
                           at Microsoft.SqlServer.Management.Smo.Scripter.Script(Urn[] urns, SqlSmoObject[] objects)
                           at CallSite.Target(Closure , CallSite , Object , Object )
                           --- End of inner exception stack trace ---
                           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)
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at Stop-Function, <No file>: line 97916
                        at Export-DbaScript<Process>, <No file>: line 15647
                        at Select-DefaultView<Process>, <No file>: line 95439
                        at Get-DbaAvailabilityGroup<Process>, <No file>: line 21564
                        at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {0, 1}

PS C:\WINDOWS\system32>

Steps to Reproduce

$s = Connect-DbaInstance -SqlInstance node1
Get-DbaAvailabilityGroup -SqlInstance $s -AvailabilityGroup DAG_TEST | Export-DbaScript

Here DAG_TEST is the name of a Distributed Availability Group.

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


Major  Minor  Build  Revision
-----  -----  -----  --------
2      1      6      -1

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.14393.6343                                                                                                                                                
PSEdition                      Desktop                                                                                                                                                       
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                                                                                       
BuildVersion                   10.0.14393.6343                                                                                                                                               
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 2016 (SP3-GDR) (KB5021129) - 13.0.6430.49 (X64)   Jan 22 2023 17:38:22   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )

.NET Framework Version

.NET Framework 4.8.4645.0