DacServices Rollback if new constraint validation fails

Question

We are updating our databases using the DacServices. However sometimes new constraints get added that cause Constraint Violations, which require us to write a migration script to make sure the Data in the column is valid.

However with the DacServices.Deploy if the ScriptNewConstraintValidation option is true and the new constraint errors, I cannot figure out how to rollback the changes made by the DacServices.

We are using the AlwaysOn Availability so performing a backup/restore would be extremely difficult. Below is one way I have tried with no success.

var dacServices = new DacServices(dbConnection.ConnectionString);
var deploymentOptions = new DacDeployOptions
{
    CreateNewDatabase = createNewDatabase,
    ScriptDatabaseCompatibility = false,
    ScriptDatabaseCollation = false,
    IgnoreUserSettingsObjects = true,
    DropPermissionsNotInSource = false,
    DropRoleMembersNotInSource = false,
    IgnorePermissions = true,
    IgnoreRoleMembership = true,
    GenerateSmartDefaults = true,
    IncludeTransactionalScripts = true,
    ScriptNewConstraintValidation = true
};

Microsoft.SqlServer.Dac.DacPackage dacPackage = DacPackage.Load(dacPath);

Console.WriteLine("Deploying DacPac");
CancellationTokenSource tokenSource = new CancellationTokenSource();
try
{
    dacServices.Deploy(dacPackage, databaseName, true, deploymentOptions, tokenSource.Token);
}
catch(Exception)
{
    tokenSource.Cancel();
}

Shouldn't `IncludeTransactionalScripts` take care of this for you? Doesn't that force the deployment to be an all-or-nothing operation? — Dan Forbes, Feb 02 '16 at 20:28
That was what I had thought, But when I looked at the database after the Deploy I saw the new constraint was created as CHECK which meant that the next Deploy said it was successful. — Ranger1230, Feb 03 '16 at 00:21
So you're saying that the deployment goes through even though the constraint validation fails? — Dan Forbes, Feb 03 '16 at 15:22
Correct. It will throw an exception stopping the rest of the TeamCity BuildSteps. But if you just run it a second time it will complete fully because that constraint already exists so it doesn't validate it again. — Ranger1230, Feb 03 '16 at 16:27
Do you think that you are cancelling the rollback step with your `CancellationToken`? — Dan Forbes, Feb 03 '16 at 16:44
No, I had the same result before trying to add the CancellationToken. It almost seems like the ScriptNewConstraintValidation is run after the transaction is completed. — Ranger1230, Feb 03 '16 at 17:38
That is frustrating. I developed an app that uses schema compare too. We generate a script, modify it and then execute it by way of SQLCMD. One of the ways that we modify the script is by wrapping the entire thing in a transaction. We insert `BEGIN TRANSACTION` right after `USE [$(DatabaseName)]; GO` and then `COMMIT TRANSACTION` at the very end of the file. A bit hacky, I know, but perhaps that is a workaround? — Dan Forbes, Feb 03 '16 at 17:45
I was worried that would be the only option. I just implemented this and it is working perfectly. If you want to post this as an answer I'll approve it. Thanks. — Ranger1230, Feb 04 '16 at 18:06

score 3 · Accepted Answer · answered Feb 04 '16 at 21:34

The problem here, as the OP points out, is that the step to validate the new constraints happens outside of the transactional statements that are included when IncludeTransactionalScripts is set to true. This means that the changes to the database will be committed, even though the deployment specifies constraints that are inconsistent with the data in the database. This seems like a bug that Microsoft should fix.

Nonetheless, my suggestion was to use the DacServices framework to generate a deployment script, as opposed to just deploying directly to the database. Once the script has been generated, you can add transactional statements to ensure that the entirety of the script executes as an atomic transaction. You would insert a BEGIN TRANSACTION statement right after the USE [$(DatabaseName)]; GO statement and then a COMMIT TRANSACTION statement at the very end of the script.

Two things to note, though: 1) the generated script must be executed from SQLCMD (you can't just execute it using a C# SqlConnection or even an SMO ServerConnection for instance), 2) I would suggest that if you manually wrap the script in a transaction, you skip the step of setting IncludeTransactionalScripts to true.

DacServices Rollback if new constraint validation fails

1 Answers1