MigratorDotNet Alter Database Required, but Can't be used

Question

I find myself with a bit of a conundrum, and am hoping someone on here can help.

I inherited an old project and it uses MigratorDotNet to deploy the database. While testing the deployment (we're using Octopus as a deployment system now) of the project I began getting errors saying that the broker isn't enabled, which caused the application to fail. I created a migration to enable the broker on the database, but the migration I created for it returns an error saying that "ALTER DATABASE statement not allowed within multi-statement transaction."

Here's the Migration:

 [CLSCompliant(false)]
[Migration(201608121015)]
public class EnableSqlServiceBroker : Migration
{
    public override void Down()
    {
        var assemblyRoot = System.Reflection.Assembly.GetExecutingAssembly().Location;
        var webConfigFileLocation = Path.Combine(assemblyRoot.Substring(0, assemblyRoot.IndexOf("\\bin")), "Web.config");
        var configuration = ConfigurationManager.OpenMappedExeConfiguration(new ExeConfigurationFileMap { ExeConfigFilename = webConfigFileLocation }, ConfigurationUserLevel.None);
        var appSettings = (configuration.GetSection("appSettings") as AppSettingsSection);

        var databaseName = appSettings.Settings[Constants.DatabaseName].Value;
        var stringBuilder = new StringBuilder()
            // Declare the BrokerEnabled variable and set it
            .AppendLine("DECLARE @BrokerEnabled bit")
            .AppendLine("SET @BrokerEnabled = 0")
            .AppendLine(
                string.Format("SELECT @BrokerEnabled =  is_broker_enabled FROM sys.databases WHERE name = '{0}'",
                    databaseName))
            .AppendLine("IF(@BrokerEnabled = 1)")
            .AppendLine("BEGIN")
            // disable the broker
            .AppendLine(string.Format("   ALTER DATABASE [{0}] SET DISABLE_BROKER", databaseName))

            .AppendLine("END");
        Database.ExecuteNonQuery(stringBuilder.ToString());
    }

    public override void Up()
    {
        var assemblyRoot = System.Reflection.Assembly.GetExecutingAssembly().Location;
        var webConfigFileLocation = Path.Combine(assemblyRoot.Substring(0, assemblyRoot.IndexOf("\\bin")), "Web.config");
        var configuration = ConfigurationManager.OpenMappedExeConfiguration(new ExeConfigurationFileMap { ExeConfigFilename = webConfigFileLocation }, ConfigurationUserLevel.None);
        var appSettings = (configuration.GetSection("appSettings") as AppSettingsSection);

        var databaseName = appSettings.Settings[Constants.DatabaseName].Value;
        var stringBuilder = new StringBuilder()
            // Declare the BrokerEnabled variable and set it
            .AppendLine("DECLARE @BrokerEnabled bit")
            .AppendLine("SET @BrokerEnabled = 0")
            .AppendLine(string.Format("SELECT @BrokerEnabled =  is_broker_enabled FROM sys.databases WHERE name = '{0}'", databaseName))
            .AppendLine("IF(@BrokerEnabled = 0)")
            .AppendLine("BEGIN")
            // enable the broker
            .AppendLine(string.Format("     ALTER DATABASE [{0}] SET ENABLE_BROKER", databaseName))

            .AppendLine("END");
        Database.ExecuteNonQuery(stringBuilder.ToString());
    }
}

I can't enable the broker without altering the database, but I can't alter the database because that's apparently not allowed. Does anyone know a way around this problem? Or a fix for it? Thanks in advance.

Answer 1

You have to commit the already running transaction before altering the database.