Enable‑SqlDscDatabaseSnapshotIsolation - dsccommunity/SqlServerDsc GitHub Wiki

Enables snapshot isolation for a database in a SQL Server Database Engine instance.

Enable-SqlDscDatabaseSnapshotIsolation -ServerObject <Server> -Name <String> [-Refresh] [-Force] [-PassThru]
 [-WhatIf] [-Confirm] [<CommonParameters>]

Enable-SqlDscDatabaseSnapshotIsolation -DatabaseObject <Database> [-Force] [-PassThru]
 [-WhatIf] [-Confirm] [<CommonParameters>]

This command enables snapshot isolation for a database in a SQL Server Database Engine instance. Enabling snapshot isolation may require additional tempdb space and can affect transaction behavior.

The command uses the SetSnapshotIsolation() method on the SMO Database object to enable row-versioning and snapshot isolation settings to optimize concurrency and consistency.

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance'
Enable-SqlDscDatabaseSnapshotIsolation -ServerObject $serverObject -Name 'MyDatabase'

Enables snapshot isolation for the database named MyDatabase.

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance'
$databaseObject = $serverObject | Get-SqlDscDatabase -Name 'MyDatabase'
Enable-SqlDscDatabaseSnapshotIsolation -DatabaseObject $databaseObject -Force

Enables snapshot isolation for the database using a database object without prompting for confirmation.

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance'
Enable-SqlDscDatabaseSnapshotIsolation -ServerObject $serverObject -Name 'MyDatabase' -PassThru

Enables snapshot isolation and returns the updated database object.

Specifies the database object to modify (from Get-SqlDscDatabase).

Type: Database
Parameter Sets: DatabaseObjectSet
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: True (ByValue)
Accept wildcard characters: False

Specifies that snapshot isolation should be enabled without any confirmation.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

Specifies the name of the database to modify.

Type: String
Parameter Sets: ServerObjectSet
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

Specifies that the database object should be returned after modification.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

Specifies that the ServerObject's databases should be refreshed before trying to get the database object. This is helpful when databases could have been modified outside of the ServerObject, for example through T-SQL. But on instances with a large amount of databases it might be better to make sure the ServerObject is recent enough.

This parameter is only used when setting snapshot isolation using ServerObject and Name parameters.

Type: SwitchParameter
Parameter Sets: ServerObjectSet
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

Specifies current server connection object.

Type: Server
Parameter Sets: ServerObjectSet
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

Prompts you for confirmation before running the cmdlet.

Type: SwitchParameter
Parameter Sets: (All)
Aliases: cf

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

Shows what would happen if the cmdlet runs. The cmdlet is not run.

Type: SwitchParameter
Parameter Sets: (All)
Aliases: wi

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.