If you write code to SQL Server then you might be interested in this: I have written a tSQLt tdd training course which has helped over 500 people learn both tSQLt and how to apply TDD practices to their SQL Server T-SQL development, you can join the course at https://courses.agilesql.club.

Blocking SSDT Deployments When You Dont Like Something

This is probably the last contributor that I am going to add to the DacFx deployment contributors project (https://github.com/DacFxDeploymentContributors/Contributors/) for a little while, if anyone else has one they want to contribute please feel free. I have been pretty pleased with how they went but want to make the testing side better.

This is an example contributor that is different to all the others in that the previous contributors enumerated the list of steps and either modified one or swapped it with another but this one uses the context object that is passed in to find all changes to stored procedures and then based on the dacpac and deployment database made a decision about whether to fail the deployment or not:

https://github.com/DacFxDeploymentContributors/Contributors/tree/master/…

Things to point out here are that this works on context object that is of type DeploymentPlanContributorContext that is passed in to OnExecute. To get a list of changes instead of enumerating the steps it finds which ones have been changed rather than the actions that are to be carried out:

foreach (var changeDefinition in context.ComparisonResult.ElementsChanged.Keys) { var change = context.ComparisonResult.ElementsChanged[changeDefinition];

if (change.TargetObject.ObjectType == ModelSchema.Procedure)
{
VerifyStoredProcedureChange(context, change);
}
}

This is quite a subtle difference than enumerating the steps but is actually really useful, aside from changed objects you can find objects to be created and / or dropped. This ability to see the intentions rather than the steps means that we do not have to aware of things like a table alter to add a constraint is actually a new object rather than a change of the table object.

This contributor is also different in that it doesn’t use the ScriptDom (I know for national ScriptDom Heart week I should have done better!) instead it uses the TSqlObject querying to find parameters on changed procedures and verify that the change should be allowed. If the change is not allowed the it fails the deployment by publishing an ExtensibilityError with a severity of Error. This means that the deployment is effectively blocked.

Why this contributor?

This was just to show how we can do other things in contributors than just modify the deployment plan, we can validate rules than can only be validated when we have access to the source and target models and that happens at deployment time only. The rules here are to check whether a parameter is added to a procedure without a default, so if there were existing applications using the procedure they could break.

I am not sure if this is something that I would actually want to do, my preference would be to have a complete set of integration tests for the application and ensure that nothing is broken when the change has been made (in a test environment) rather than trying to protect production but still a fun contributor to do.

Enjoy and as always if you have any questions ping me, or if it is about a deployment contributor try our gitter chat room (open to all):

https://gitter.im/DacFxDeploymentContributors/Contributors

Ed

Subscribe

* indicates required

Please select all the ways you would like to hear from Agile Sql Club:

You can unsubscribe at any time by clicking the link in the footer of our emails. For information about our privacy practices, please visit our website.

We use Mailchimp as our marketing platform. By clicking below to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.