We have been using Octopus Deploy to update a database using Powershell and SQL Server Management Objects. Before the changes are applied we switch the database to single-user mode. After the changes have been applied we switch the database back to multi-user mode.
In some cases we have found the apply changes step fails and the deployment process ends leaving the database in single-user mode which then requires manual intervention to set the database back to multi-user mode.
Is it possible to wrap the setting of the database to single-user mode, applying the changes, and the resetting of the database back to multi-user mode within a SQL Server transaction? Or do transactions only cover DML changes?
You cannot set a database in a single user mode from within a transaction, you'll get the following error: "ALTER DATABASE statement not allowed within multi-statement transaction"