SqlDatabase: Resource refactoring proposal

[johlju]

Resource proposal

A class-based SqlDatabase resource would manage the creation, configuration, and removal of SQL Server databases. It would use commands such as Get-SqlDscDatabase, New-SqlDscDatabase, Set-SqlDscDatabaseProperty, Remove-SqlDscDatabase, and Test-SqlDscDatabase to ensure databases are in the desired state.

Proposed properties

Property	Type qualifier	Data type	Description	Default value	Allowed values
Ensure	Key	String	Whether the database should be Present or Absent	Present	Present, Absent
Name	Key	String	Name of the database	None	Any valid database name
ServerName	Key	String	The SQL Server host name	None	Any valid server name
InstanceName	Key	String	The SQL Server instance name	None	Any valid instance name
Collation	Write	String	The name of the SQL collation to use for the database	Server collation	Valid collations
CatalogCollation	Write	String	Specifies the collation type for the system catalog (DATABASE_DEFAULT or SQL_Latin1_General_CP1_CI_AS). Requires SQL Server 2019+	None	DATABASE_DEFAULT, SQL_Latin1_General_CP1_CI_AS
CompatibilityLevel	Write	String	The version of the SQL compatibility level to use for the database	Server version	Version80, Version90, Version100, Version110, Version120, Version130, Version140, Version150, Version160
RecoveryModel	Write	String	The recovery model to be used for the database	Full	Simple, Full, BulkLogged
OwnerName	Write	String	Specifies the name of the login that should be the owner of the database	None	Any valid login
IsLedger	Write	Boolean	Specifies whether to create a ledger database. Requires SQL Server 2022+ or Azure SQL Database. Read-only after creation	False	True, False
AcceleratedRecoveryEnabled	Write	Boolean	Specifies whether Accelerated Database Recovery (ADR) is enabled. Requires SQL Server 2019+	None	True, False
AnsiNullDefault	Write	Boolean	Specifies whether new columns allow NULL by default unless explicitly specified	None	True, False
AnsiNullsEnabled	Write	Boolean	Specifies whether comparisons to NULL follow ANSI SQL behavior	None	True, False
AnsiPaddingEnabled	Write	Boolean	Specifies whether padding for variable-length columns follows ANSI rules	None	True, False
AnsiWarningsEnabled	Write	Boolean	Specifies whether ANSI warnings are generated for certain conditions	None	True, False
ArithmeticAbortEnabled	Write	Boolean	Specifies whether a query is terminated when an overflow or divide-by-zero error occurs	None	True, False
AutoClose	Write	Boolean	Specifies whether the database closes after the last user exits	None	True, False
AutoCreateIncrementalStatisticsEnabled	Write	Boolean	Specifies whether creation of incremental statistics on partitioned tables is allowed	None	True, False
AutoCreateStatisticsEnabled	Write	Boolean	Specifies whether single-column statistics are automatically created for query optimization	None	True, False
AutoShrink	Write	Boolean	Specifies whether the database automatically shrinks files when free space is detected	None	True, False
AutoUpdateStatisticsAsync	Write	Boolean	Specifies whether statistics are updated asynchronously	None	True, False
AutoUpdateStatisticsEnabled	Write	Boolean	Specifies whether statistics are automatically updated when out-of-date	None	True, False
BrokerEnabled	Write	Boolean	Specifies whether Service Broker is enabled for the database	None	True, False
ChangeTrackingAutoCleanUp	Write	Boolean	Specifies whether automatic cleanup of change tracking information is enabled	None	True, False
ChangeTrackingEnabled	Write	Boolean	Specifies whether change tracking is enabled for the database	None	True, False
ChangeTrackingRetentionPeriod	Write	Int32	Specifies the retention period value for change tracking information	None	Any valid integer
ChangeTrackingRetentionPeriodUnits	Write	String	Specifies the units for the retention period	None	Minutes, Hours, Days
CloseCursorsOnCommitEnabled	Write	Boolean	Specifies whether open cursors are closed when a transaction is committed	None	True, False
ConcatenateNullYieldsNull	Write	Boolean	Specifies whether concatenation with NULL results in NULL	None	True, False
ContainmentType	Write	String	Specifies the containment level of the database	None	None, Partial
DatabaseOwnershipChaining	Write	Boolean	Specifies whether ownership chaining across objects within the database is enabled	None	True, False
DataRetentionEnabled	Write	Boolean	Specifies whether SQL Server data retention policy is enabled. Requires SQL Server 2017+	None	True, False
DateCorrelationOptimization	Write	Boolean	Specifies whether date correlation optimization is enabled	None	True, False
DefaultFullTextLanguage	Write	Int32	Specifies the LCID of the default full-text language	None	Valid LCID
DefaultLanguage	Write	Int32	Specifies the ID of the default language for the database	None	Valid language ID
DelayedDurability	Write	String	Specifies the delayed durability setting for the database	None	Disabled, Allowed, Forced
EncryptionEnabled	Write	Boolean	Specifies whether Transparent Data Encryption (TDE) is enabled	None	True, False
FilestreamDirectoryName	Write	String	Specifies the directory name used for FILESTREAM data	None	Any valid directory name
FilestreamNonTransactedAccess	Write	String	Specifies the FILESTREAM access level for non-transactional access	None	Off, ReadOnly, Full
HonorBrokerPriority	Write	Boolean	Specifies whether honoring Service Broker conversation priority is enabled	None	True, False
IsFullTextEnabled	Write	Boolean	Specifies whether full-text search is enabled	None	True, False
IsParameterizationForced	Write	Boolean	Specifies whether forced parameterization is enabled	None	True, False
IsReadCommittedSnapshotOn	Write	Boolean	Specifies whether READ_COMMITTED_SNAPSHOT isolation is ON	None	True, False
IsSqlDw	Write	Boolean	Specifies whether the database is a SQL Data Warehouse database	None	True, False
IsVarDecimalStorageFormatEnabled	Write	Boolean	Specifies whether vardecimal compression is enabled	None	True, False
LegacyCardinalityEstimation	Write	String	Specifies the legacy cardinality estimator setting for the primary	None	Off, On, Primary
LegacyCardinalityEstimationForSecondary	Write	String	Specifies the legacy cardinality estimator setting for secondary replicas	None	Off, On, Primary
LocalCursorsDefault	Write	Boolean	Specifies whether cursors are local by default instead of global	None	True, False
MaxDop	Write	Int32	Specifies the MAXDOP database-scoped configuration for primary replicas	None	Any valid integer
MaxDopForSecondary	Write	Int32	Specifies the MAXDOP database-scoped configuration for secondary replicas	None	Any valid integer
MaxSizeInBytes	Write	Double	Specifies the maximum size of the database in bytes	None	Any valid number
MirroringPartner	Write	String	Specifies the mirroring partner server name	None	Any valid server name
MirroringPartnerInstance	Write	String	Specifies the mirroring partner instance name	None	Any valid instance name
MirroringRedoQueueMaxSize	Write	Int32	Specifies the redo queue maximum size for mirroring/AGs	None	Any valid integer
MirroringSafetyLevel	Write	String	Specifies the mirroring safety level	None	Off, Full, Unknown
MirroringTimeout	Write	Int32	Specifies the timeout in seconds for mirroring sessions	None	Any valid integer
MirroringWitness	Write	String	Specifies the mirroring witness server	None	Any valid server name
NestedTriggersEnabled	Write	Boolean	Specifies whether triggers are allowed to fire other triggers	None	True, False
NumericRoundAbortEnabled	Write	Boolean	Specifies whether an error is raised on loss of precision due to rounding	None	True, False
PageVerify	Write	String	Specifies the page verification setting	None	None, TornPageDetection, Checksum
ParameterSniffing	Write	String	Specifies the parameter sniffing setting for the primary	None	Off, On, Primary
ParameterSniffingForSecondary	Write	String	Specifies the parameter sniffing setting for secondary replicas	None	Off, On, Primary
PersistentVersionStoreFileGroup	Write	String	Specifies the filegroup used for the Persistent Version Store (PVS). Requires SQL Server 2019+	None	Any valid filegroup name
PrimaryFilePath	Write	String	Specifies the path of the primary data files directory	None	Any valid path
QueryOptimizerHotfixes	Write	String	Specifies the query optimizer hotfixes setting for the primary	None	Off, On, Primary
QueryOptimizerHotfixesForSecondary	Write	String	Specifies the query optimizer hotfixes setting for secondary replicas	None	Off, On, Primary
QuotedIdentifiersEnabled	Write	Boolean	Specifies whether identifiers can be delimited by double quotes	None	True, False
ReadOnly	Write	Boolean	Specifies whether the database is in read-only mode	None	True, False
RecursiveTriggersEnabled	Write	Boolean	Specifies whether a trigger is allowed to fire itself recursively	None	True, False
RemoteDataArchiveCredential	Write	String	Specifies the credential name for Stretch Database/remote data archive	None	Any valid credential name
RemoteDataArchiveEnabled	Write	Boolean	Specifies whether Stretch Database (remote data archive) is enabled	None	True, False
RemoteDataArchiveEndpoint	Write	String	Specifies the endpoint URL for remote data archive	None	Any valid URL
RemoteDataArchiveLinkedServer	Write	String	Specifies the linked server used by remote data archive	None	Any valid linked server
RemoteDataArchiveUseFederatedServiceAccount	Write	Boolean	Specifies whether to use federated service account for remote data archive	None	True, False
RemoteDatabaseName	Write	String	Specifies the remote database name for remote data archive	None	Any valid database name
TargetRecoveryTime	Write	Int32	Specifies the target recovery time (seconds) for indirect checkpointing	None	Any valid integer
TemporalHistoryRetentionEnabled	Write	Boolean	Specifies whether automatic cleanup of system-versioned temporal history is enabled. Requires SQL Server 2017+	None	True, False
TransformNoiseWords	Write	Boolean	Specifies how full-text noise word behavior is controlled during queries	None	True, False
Trustworthy	Write	Boolean	Specifies whether implicit access to external resources by modules is allowed	None	True, False
TwoDigitYearCutoff	Write	Int32	Specifies the two-digit year cutoff used for date conversion	None	Any valid integer
UserAccess	Write	String	Specifies the database user access mode	None	Multiple, Restricted, Single

Special considerations or limitations

Read-only properties after creation

The following properties cannot be modified after database creation and can only be set during creation:

• CatalogCollation: Can only be set during database creation
• DatabaseSnapshotBaseName: Can only be set during snapshot creation (use separate resource or command)
• IsLedger: Ledger status cannot be changed after database is created

Version requirements

• CatalogCollation: Requires SQL Server 2019 (version 15) or later
• IsLedger: Requires SQL Server 2022 (version 16) or later, or Azure SQL Database
• AcceleratedRecoveryEnabled: Requires SQL Server 2019 (version 15) or later
• DataRetentionEnabled: Requires SQL Server 2017 (version 14) or later
• TemporalHistoryRetentionEnabled: Requires SQL Server 2017 (version 14) or later
• PersistentVersionStoreFileGroup: Requires SQL Server 2019 (version 15) or later

Other considerations

• Must handle permissions, collation changes, and default values appropriately
• Contributors should consider version-specific options, error handling, and data loss risks when removing databases
• FileGroup configuration may require additional logic for complex file/filegroup scenarios
• Database snapshots should be managed through a separate mechanism
• Azure SQL Database service tier and service objective changes should use Azure management cmdlets
• Some database properties require method calls instead of direct property assignment and will need separate commands (e.g., Set-SqlDscDatabaseDefaultFileGroup)