I am using DbUp to maintain and upgrade my SQL Server 2016 databases. Typical SQL scripts that I have executed so far included tasks like creating tables, creating or altering stored procedures, etc.
I am going to start using Memory Optimised Tables, and need to run ALTER DATABASE commands to create the necessary filegroups in the existing databases. I tried running a dynamic SQL script that builds and runs ALTER DATABASE commands containing the database name that the script is currently running for, but since DbUp uses transactional execution, it seems that ALTER statements are not allowed and I am getting this error:
Script block number: 2; Block line 1; Message: System.Data.SqlClient.SqlException (0x80131904): ALTER DATABASE statement not allowed within multi-statement transaction. ALTER DATABASE statement not allowed within multi-statement transaction. ALTER DATABASE statement not allowed within multi-statement transaction at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
Any ideas how I can run ALTER DATABASE commands via DbUp, or is there an alternative way how to handle this case?
Thanks in advance.