`New-SqlDscDatabase`: Add missing settable properties

[johlju]

Problem description

Enhancement request: Add the same settable properties as parameters to the New-SqlDscDatabase command as suggested in issue #2177, except those that already exist.

Parameters already present in New-SqlDscDatabase:

• Collation
• CompatibilityLevel
• RecoveryModel

Proposed new parameters (with reference to Microsoft.SqlServer.Management.Smo.Database properties):

• BrokerEnabled – Enables/disables Service Broker in the database.
• CatalogCollation – Catalog collation; only valid during creation (read-only after).
• ConcatenateNullYieldsNull – Whether NULL + 'x' yields NULL.
• ContainmentType – Database containment mode (none/partial).
• DelayedDurability – Controls delayed durability behavior.
• DefaultFullTextLanguage – Default full-text language.
• DefaultLanguage – Default language for users created in this DB.
• EncryptionEnabled – Enables/disables TDE at the database level.
• FilestreamNonTransactedAccess – Non-transactional FILESTREAM access level.
• HonorBrokerPriority – Honors conversation priorities for Service Broker.
• IsReadCommittedSnapshotOn – Enables RCSI (row-versioning for READ COMMITTED).
• LegacyCardinalityEstimation – Database-scoped config for legacy CE.
• LegacyCardinalityEstimationForSecondary – Same as above for secondaries.
• LocalCursorsDefault – Default cursor scope (local vs global).
• MaxDop – DB-scoped MAXDOP.
• MaxDopForSecondary – DB-scoped MAXDOP override for secondaries.
• NestedTriggersEnabled – Allows nested trigger firing.
• NumericRoundAbortEnabled – Whether rounding errors raise an error.
• PageVerify – Page corruption detection mode (NONE/TORN_PAGE_DETECTION/CHECKSUM).
• ParameterSniffing – DB-scoped config for parameter sniffing.
• ParameterSniffingForSecondary – Same as above for secondaries.
• QueryOptimizerHotfixes – DB-scoped config enabling optimizer hotfixes.
• QueryOptimizerHotfixesForSecondary – Same as above for secondaries.
• QuotedIdentifiersEnabled – Requires quoted identifiers for delimited names.
• ReadOnly – Sets database to read-only/read-write.
• RecursiveTriggersEnabled – Allows triggers to fire recursively.
• TargetRecoveryTime – Indirect checkpoint target recovery time (seconds).
• TransformNoiseWords – Controls full-text noise words behavior.
• Trustworthy – Marks DB as trustworthy for certain code paths.
• TwoDigitYearCutoff – Cutoff year for 2-digit year interpretation.
• UserAccess – MULTI_USER/SINGLE_USER/RESTRICTED_USER.
• WarnOnRename – Whether SSMS/SMO should warn when renaming DB.

Special considerations:

• Properties only settable at creation (e.g., CatalogCollation) should be handled accordingly.
• Properties that require methods (e.g., SetDefaultFileGroup) or are not settable directly should be excluded from this parameter addition request.

Reference: #2177

This would allow for more flexible configuration when creating new databases.

Verbose logs

n/a

How to reproduce

1. Review the parameters on New-SqlDscDatabase.
2. Compare with the settable properties listed above.
3. Attempt to set a property not exposed as a parameter (e.g., BrokerEnabled) and note it is not available.

Expected behavior

All settable properties on Microsoft.SqlServer.Management.Smo.Database should be available as parameters on New-SqlDscDatabase, except those not settable via property or only settable at creation.

Current behavior

Currently, only Collation, CompatibilityLevel, and RecoveryModel are available as parameters. Other settable properties must be configured after database creation or are not exposed.

Suggested solution

Add the listed properties as parameters to New-SqlDscDatabase and ensure they are settable during database creation (where applicable). Handle creation-time-only properties and those requiring a method appropriately.

Operating system the target node is running

n/a

PowerShell version and build the target node is running

n/a

Module version used

n/a