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();
}
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.