SqlDatabase

Parameters

Parameter	Attribute	DataType	Description	Allowed Values
InstanceName	Key	System.String	The name of the SQL Server instance to be configured. Default value is 'MSSQLSERVER'.
Name	Key	System.String	The name of the database.
AcceleratedRecoveryEnabled	Write	System.Boolean	Specifies whether Accelerated Database Recovery (ADR) is enabled. Requires SQL Server 2019 or later.
AnsiNullDefault	Write	System.Boolean	Specifies whether new columns allow NULL by default unless explicitly specified.
AnsiNullsEnabled	Write	System.Boolean	Specifies whether comparisons to NULL follow ANSI SQL behavior.
AnsiPaddingEnabled	Write	System.Boolean	Specifies whether padding for variable-length columns follows ANSI rules.
AnsiWarningsEnabled	Write	System.Boolean	Specifies whether ANSI warnings are generated for certain conditions.
ArithmeticAbortEnabled	Write	System.Boolean	Specifies whether a query is terminated when an overflow or divide-by-zero error occurs.
AutoClose	Write	System.Boolean	Specifies whether the database closes after the last user exits.
AutoCreateIncrementalStatisticsEnabled	Write	System.Boolean	Specifies whether creation of incremental statistics on partitioned tables is allowed.
AutoCreateStatisticsEnabled	Write	System.Boolean	Specifies whether single-column statistics are automatically created for query optimization.
AutoShrink	Write	System.Boolean	Specifies whether the database automatically shrinks files when free space is detected.
AutoUpdateStatisticsAsync	Write	System.Boolean	Specifies whether statistics are updated asynchronously.
AutoUpdateStatisticsEnabled	Write	System.Boolean	Specifies whether statistics are automatically updated when out-of-date.
BrokerEnabled	Write	System.Boolean	Specifies whether Service Broker is enabled for the database.
CatalogCollation	Write	System.String	Specifies the collation type for the system catalog. Can only be set during database creation. Requires SQL Server 2019 or later.	DatabaseDefault, SqlLatin1GeneralCp1CiAs
ChangeTrackingAutoCleanUp	Write	System.Boolean	Specifies whether automatic cleanup of change tracking information is enabled.
ChangeTrackingEnabled	Write	System.Boolean	Specifies whether change tracking is enabled for the database.
ChangeTrackingRetentionPeriod	Write	System.Int32	Specifies the retention period value for change tracking information.
ChangeTrackingRetentionPeriodUnits	Write	System.String	Specifies the units for the retention period.	None, Days, Hours, Minutes
CloseCursorsOnCommitEnabled	Write	System.Boolean	Specifies whether open cursors are closed when a transaction is committed.
Collation	Write	System.String	Specifies the default collation for the database.
CompatibilityLevel	Write	System.String	Specifies the database compatibility level.	Version80, Version90, Version100, Version110, Version120, Version130, Version140, Version150, Version160
ConcatenateNullYieldsNull	Write	System.Boolean	Specifies whether concatenation with NULL results in NULL.
ContainmentType	Write	System.String	Specifies the containment level of the database.	None, Partial
Credential	Write	System.Management.Automation.PSCredential	Specifies the credential to use to connect to the SQL Server instance. If parameter *Credential' is not provided then the resource instance is run using the credential that runs the configuration.
DatabaseOwnershipChaining	Write	System.Boolean	Specifies whether ownership chaining across objects within the database is enabled.
DataRetentionEnabled	Write	System.Boolean	Specifies whether SQL Server data retention policy is enabled. Requires SQL Server 2017 or later.
DateCorrelationOptimization	Write	System.Boolean	Specifies whether date correlation optimization is enabled.
DefaultFullTextLanguage	Write	System.Int32	Specifies the LCID of the default full-text language.
DefaultLanguage	Write	System.Int32	Specifies the ID of the default language for the database.
DelayedDurability	Write	System.String	Specifies the delayed durability setting for the database.	Disabled, Allowed, Forced
EncryptionEnabled	Write	System.Boolean	Specifies whether Transparent Data Encryption (TDE) is enabled.
Ensure	Write	Ensure	Specifies if the database should be present or absent. If set to Present the database will be added if it does not exist, or updated if the database exist. If Absent then the database will be removed from the server. Defaults to Present.	Present, Absent
FilestreamDirectoryName	Write	System.String	Specifies the directory name used for FILESTREAM data.
FilestreamNonTransactedAccess	Write	System.String	Specifies the FILESTREAM access level for non-transactional access.	Off, ReadOnly, Full
HonorBrokerPriority	Write	System.Boolean	Specifies whether honoring Service Broker conversation priority is enabled.
IsFullTextEnabled	Write	System.Boolean	Specifies whether full-text search is enabled.
IsLedger	Write	System.Boolean	Specifies whether to create a ledger database. Can only be set during database creation. Requires SQL Server 2022 or later.
IsParameterizationForced	Write	System.Boolean	Specifies whether forced parameterization is enabled.
IsReadCommittedSnapshotOn	Write	System.Boolean	Specifies whether READ_COMMITTED_SNAPSHOT isolation is ON.
IsSqlDw	Write	System.Boolean	Specifies whether the database is a SQL Data Warehouse database.
IsVarDecimalStorageFormatEnabled	Write	System.Boolean	Specifies whether vardecimal compression is enabled.
LegacyCardinalityEstimation	Write	System.String	Specifies the legacy cardinality estimator setting for the primary.	Off, On, Primary
LegacyCardinalityEstimationForSecondary	Write	System.String	Specifies the legacy cardinality estimator setting for secondary replicas.	Off, On, Primary
LocalCursorsDefault	Write	System.Boolean	Specifies whether cursors are local by default instead of global.
MaxDop	Write	System.Int32	Specifies the MAXDOP database-scoped configuration for primary replicas.
MaxDopForSecondary	Write	System.Int32	Specifies the MAXDOP database-scoped configuration for secondary replicas.
MaxSizeInBytes	Write	System.Double	Specifies the maximum size of the database in bytes.
MirroringPartner	Write	System.String	Specifies the mirroring partner server name.
MirroringPartnerInstance	Write	System.String	Specifies the mirroring partner instance name.
MirroringRedoQueueMaxSize	Write	System.Int32	Specifies the redo queue maximum size for mirroring/AGs.
MirroringSafetyLevel	Write	System.String	Specifies the mirroring safety level.	None, Unknown, Off, Full
MirroringTimeout	Write	System.Int32	Specifies the timeout in seconds for mirroring sessions.
MirroringWitness	Write	System.String	Specifies the mirroring witness server.
NestedTriggersEnabled	Write	System.Boolean	Specifies whether triggers are allowed to fire other triggers.
NumericRoundAbortEnabled	Write	System.Boolean	Specifies whether an error is raised on loss of precision due to rounding.
OwnerName	Write	System.String	Specifies the name of the login that should be the owner of the database.
PageVerify	Write	System.String	Specifies the page verification setting.	None, TornPageDetection, Checksum
ParameterSniffing	Write	System.String	Specifies the parameter sniffing setting for the primary.	Off, On, Primary
ParameterSniffingForSecondary	Write	System.String	Specifies the parameter sniffing setting for secondary replicas.	Off, On, Primary
PersistentVersionStoreFileGroup	Write	System.String	Specifies the filegroup used for the Persistent Version Store (PVS). Requires SQL Server 2019 or later.
Port	Write	System.UInt16	Specifies the TCP port number to use when connecting to the SQL Server instance. This parameter is only applicable when connecting via TCP/IP. If not specified for a named instance, the SQL Server Browser service will be used to determine the port. For default instances, port 1433 is used by default.
PrimaryFilePath	Write	System.String	Specifies the path of the primary data files directory.
Protocol	Write	System.String	Specifies the network protocol to use when connecting to the SQL Server instance. Valid values are 'tcp' for TCP/IP, 'np' for Named Pipes, and 'lpc' for Shared Memory. If not specified, the connection will use the default protocol order configured on the client.	tcp, np, lpc
QueryOptimizerHotfixes	Write	System.String	Specifies the query optimizer hotfixes setting for the primary.	Off, On, Primary
QueryOptimizerHotfixesForSecondary	Write	System.String	Specifies the query optimizer hotfixes setting for secondary replicas.	Off, On, Primary
QuotedIdentifiersEnabled	Write	System.Boolean	Specifies whether identifiers can be delimited by double quotes.
ReadOnly	Write	System.Boolean	Specifies whether the database is in read-only mode.
RecoveryModel	Write	System.String	Specifies the database recovery model.	Simple, Full, BulkLogged
RecursiveTriggersEnabled	Write	System.Boolean	Specifies whether a trigger is allowed to fire itself recursively.
RemoteDataArchiveCredential	Write	System.String	Specifies the credential name for Stretch Database/remote data archive.
RemoteDataArchiveEnabled	Write	System.Boolean	Specifies whether Stretch Database (remote data archive) is enabled.
RemoteDataArchiveEndpoint	Write	System.String	Specifies the endpoint URL for remote data archive.
RemoteDataArchiveLinkedServer	Write	System.String	Specifies the linked server used by remote data archive.
RemoteDataArchiveUseFederatedServiceAccount	Write	System.Boolean	Specifies whether to use federated service account for remote data archive.
RemoteDatabaseName	Write	System.String	Specifies the remote database name for remote data archive.
ServerName	Write	System.String	The host name of the SQL Server to be configured. Default value is the current computer name.
SnapshotIsolation	Write	System.Boolean	Specifies whether snapshot isolation should be enabled for the database.
TargetRecoveryTime	Write	System.Int32	Specifies the target recovery time (seconds) for indirect checkpointing.
TemporalHistoryRetentionEnabled	Write	System.Boolean	Specifies whether automatic cleanup of system-versioned temporal history is enabled. Requires SQL Server 2017 or later.
TransformNoiseWords	Write	System.Boolean	Specifies how full-text noise word behavior is controlled during queries.
Trustworthy	Write	System.Boolean	Specifies whether implicit access to external resources by modules is allowed.
TwoDigitYearCutoff	Write	System.Int32	Specifies the two-digit year cutoff used for date conversion.
UserAccess	Write	System.String	Specifies the database user access mode.	Multiple, Restricted, Single
Reasons	Read	SqlReason[]	Returns the reason a property is not in desired state.

Description

The SqlDatabase DSC resource is used to create, modify, or remove databases on a SQL Server instance.

The built-in parameter PSDscRunAsCredential can be used to run the resource as another user. The resource will then authenticate to the SQL Server instance as that user. It also possible to instead use impersonation by the parameter Credential.

Requirements

• Target machine must be running Windows Server 2012 or later.
• Target machine must be running SQL Server Database Engine 2012 or later.
• Target machine must have access to the SQLPS PowerShell module or the SqlServer PowerShell module.

Known issues

All issues are not listed here, see here for all open issues.

Property Reasons does not work with PSDscRunAsCredential

When using the built-in parameter PSDscRunAsCredential the read-only property Reasons will return empty values for the properties Code and Phrase. The built-in property PSDscRunAsCredential does not work together with class-based resources that using advanced type like the parameter Reasons have.

Using Credential property

SQL Authentication and Group Managed Service Accounts is not supported as impersonation credentials. Currently only Windows Integrated Security is supported to use as credentials.

For Windows Authentication the username must either be provided with the User Principal Name (UPN), e.g. [email protected] or if using non-domain (for example a local Windows Server account) account the username must be provided without the NetBIOS name, e.g. username. Using the NetBIOS name, e.g using the format DOMAIN\username will not work.

See more information in Credential Overview.

Read-only properties after creation

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

• CatalogCollation: The catalog-level collation used for metadata and temporary objects.
• IsLedger: Ledger status cannot be changed after database is created.

Examples

Example 1

This example shows how to create a database with the database name equal to 'Contoso'.

The second example shows how to create a database with a different collation.

The third example shows how to create a database with a different compatibility level.

The fourth example shows how to create a database with a different recovery model.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabase 'Create_Database'
        {
            Ensure               = 'Present'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            Name                 = 'Contoso'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlDatabase 'Create_Database_with_different_collation'
        {
            Ensure               = 'Present'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            Name                 = 'AdventureWorks'
            Collation            = 'SQL_Latin1_General_Pref_CP850_CI_AS'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlDatabase 'Create_Database_with_different_compatibility_level'
        {
            Ensure               = 'Present'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            Name                 = 'Fabrikam'
            CompatibilityLevel   = 'Version130'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlDatabase 'Create_Database_with_different_recovery_model'
        {
            Ensure               = 'Present'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            Name                 = 'FabrikamData'
            RecoveryModel        = 'Simple'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }

        SqlDatabase 'Create_Database_with_specific_owner'
        {
            Ensure               = 'Present'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            Name                 = 'FabrikamDataOwner'
            OwnerName            = 'sa'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 2

This example shows how to remove a database with the database name equal to 'AdventureWorks'.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlDatabase 'Delete_Database'
        {
            Ensure               = 'Absent'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            Name                 = 'AdventureWorks'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}