The Alternative to Pre-Model Scripts

Hello!

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.

And I feel points 2 and 3 are relevant here because in the real world. This is because if you were to ask a SQL Developer, who is under the time constraints if delivering something on time the options of

  • write a deployment contributor and get that delivered in a deployment pipeline,
  • just write a pre-model script

well, I think it would be pretty obvious which one would be the preferred option. But that is not necessarily a reason not to use them. Returning to the first point that no one has ever heard of them, I champion them for developers to use and to get into a pipeline, because there are as many reason to use them as there are challenges:

  • 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.

So the initial time lost will more than make up for itself with the lack off issues further down the development process! And I certainly think the benefits outweigh the challenges.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s