Scripting a Distributed Availability Group throws ArgumentException: Unexpected value for enumeration type AvailabilityGroupAutomatedBackupPreference

從 src/Microsoft/SqlServer/Management/Smo/AvailabilityGroupBase.cs 開始，特別檢查 ScriptGroupOption、ScriptCreateGroupOptions 和 GetAutomatedBackupPreferenceScript；接著檢視 src/Microsoft/SqlServer/Management/SqlEnum/xml/AvailabilityGroup.xml 中的 sentinel 對應。重現分散式可用性群組的指令碼編寫，並驗證產生的 CREATE 陳述式會省略由 sentinel 支援的三個選項，且能夠成功執行。

AvailabilityGroup.Script() / Scripter.EnumScript() has an explicit code path for Distributed Availability Groups (ScriptCreate scripts AVAILABILITY GROUP ON ... LISTENER_URL ...), but scripting a DAG fetched from a live server always throws before that path is reached:

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 Microsoft.SqlServer.Management.Smo.AvailabilityGroup.ScriptCreate(StringCollection query, ScriptingPreferences sp)

Repro

# On the primary of any distributed availability group:
$server = New-Object Microsoft.SqlServer.Management.Smo.Server("<instance>")
$dag = $server.AvailabilityGroups["<dag name>"]
$dag.Script()   # or: (New-Object Microsoft.SqlServer.Management.Smo.Scripter($server)).EnumScript($dag)

Originally reported against dbatools (Export-DbaScript calls EnumScript): dataplat/dbatools#9220 has a full stack trace from a user's environment. dbatools now works around it by hand-building the DAG DDL from the AvailabilityReplicas properties (dataplat/dbatools#10216).

Mechanism

For a distributed availability group, sys.availability_groups returns NULL for automated_backup_preference, failure_condition_level and health_check_timeout — those options do not apply to DAGs. The enumerator metadata maps each NULL to a sentinel (AvailabilityGroup.xml#L53-L55):

<property name="AutomatedBackupPreference" ...>ISNULL(AG.automated_backup_preference, 4)</property>
<property name="FailureConditionLevel"    ...>ISNULL(AG.failure_condition_level, 6)</property>
<property name="HealthCheckTimeout"       ...>ISNULL(AG.health_check_timeout, -1)</property>

4 is AvailabilityGroupAutomatedBackupPreference.Unknown and 6 is AvailabilityGroupFailureConditionLevel.Unknown — both defined, [Browsable(false)], documented as "the replica may not be able to communicate with the cluster".

ScriptCreateGroupOptions iterates all creatable group options with scriptAll: true, and ScriptGroupOption only skips a property when prop.IsNull — a sentinel value is not null, so it is scripted. For AutomatedBackupPreference that reaches GetAutomatedBackupPreferenceScript, whose switch has no case for Unknown and throws in default.

The throw is only the first of three problems. If it were bypassed, the same ScriptGroupOption would emit the other two sentinels verbatim into the CREATE statement:

• FAILURE_CONDITION_LEVEL = 6 (script.Append((int)FailureConditionLevel); valid server range is 1–5)
• HEALTH_CHECK_TIMEOUT = -1 (minimum accepted value is 15000)

so the generated DDL would fail on the server even without the exception.

The same applies to any regular AG queried from a node where WSFC state is unavailable, since that is what the sentinels were originally introduced for — the DAG case just makes it deterministic.

Suggested fix

Treat the three sentinel values like prop.IsNull in ScriptGroupOption and skip the clause: AutomatedBackupPreference == Unknown, FailureConditionLevel == Unknown, HealthCheckTimeout < 0. ScriptCreateGroupOptions already tolerates empty per-option scripts, and the options are all optional in the DDL, so omitting them is correct for both the DAG case and the cluster-state-unavailable case.

Happy to submit a PR along those lines if that helps.

This was created by Claude and reviewed by Andreas Jordan.