Two of my most recent posts have concerned pre-model scripts; those scripts that need to be run before the dacpac is compared against the production database. These are sometimes necessary. And this necessity is usually because SSDT may not produce a script that is optimal. One such example is an index creation: no index is created “ONLINE”, and this can be a problem if the database being deployed is online during this operation. And can be even worse if the table is particularly large.
In respect to the SSDT team, I can see why this is the case: some versions of SQL have the online index feature, some don’t. So one solution may be to write a pre-model script that will create an index with the online operation included. And while there’s nothing wrong with this, there is an alternative: deployment contributors.
According to the blurb, deployment contributors can perform custom actions when deploying a SQL script. And one such use of deployment contributors would be to alter index builds to be an online operation. Microsoft also have a Github DACExtensions repo, and this is very useful because, and in the interests of full disclosure, I have never written a deployment contributor myself. This is partly because the repo has some very good examples, including the online index issue (this post nicely covers how to make use of deployment contributors.) I know those that have and have explained how they work very well. But I think there are a few challenges w/r/t deployment contributors:
- No one has ever heard of them
- You have to use C#
- They’re not entirely straightforward.
- write a deployment contributor and get that delivered in a deployment pipeline,
- just write a pre-model script
- Too many pre-model scripts and you end up with entropy.
- You may forget to alter the database solution itself and cause an index change to be undone.
- A new member of a team may not be aware of the use of pre-model scripts and just add the change to the solution, causing issues when a change is deployed. Deployment contributors remove all these issues.