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

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.

从 src/Microsoft/SqlServer/Management/Smo/AvailabilityGroupBase.cs 开始，重点查看 ScriptGroupOption、ScriptCreateGroupOptions 和 GetAutomatedBackupPreferenceScript；然后检查 src/Microsoft/SqlServer/Management/SqlEnum/xml/AvailabilityGroup.xml 中的 sentinel 映射。复现对分布式可用性组进行脚本编写的过程，并验证生成的 CREATE 语句会省略由 sentinel 支持的三个选项，且能够成功执行。