Pre-Deployment Scripts

Pre-deployment script do not change the outcome of deployments!

The above statements is something that most users do not realize when using pre-deployment scripts. Since the pre-deployment script is included in to the deployment script, it therefore by definition runs after the comparison the the source model with the target database (model), and therefore cannot change the behavior of the deployment.

For example:

If you know you have an orphaned object that is blocking a certain upgrade scenario,  most users expect that when you would drop the offending object in a pre-deployment script, that this would take care of things. However the deployment engine has already performed the comparison between the source model and the target database (model) and generated the deployment script based on the results.

In order to gain a better understanding about what is going on, lets step through the flow of activities inside the deployment engine.

1. The deployment engine takes as input the result of a build. It will de-serialize the source model (.dbschema) file to hydrate the source model representation in memory. It will take in to account the settings provided through the various configuration files, like Database.sqldeployment, Database.sqlsettings and Database.sqlcmdvars.
2. The second step is to create a target model representation, by importing the target database.
   Note: when the deployment engine detects that the target database does not exists, it will create a target model based on the model database, since SQL Server inherits all settings and objects inside the model database when creating a new database.
3. If a “Transaction.refactorlog” file is present, the refactorting operation will be applied to the models, by making changes to the target model to yield the correct outcome.
4. Then the two models are compared, in order to determine the differences between the source and target. Internally this results in to a set of all the differences, which are then represented in a tokenized format to represent each change, after which the tokenized stream of difference operations is ordered and optimized by folding/collapsing operations together. All this results in the actual deployment part of the script.
5. Once the previous step has been completed the single file deployment script representation is generated, which contains the following code blocks:
   
   1. The database code block contains the statements that either creates the database or checks if the database exists, based on the determination of the deployment engine.
   2. The pre-deployment code block, contains the consolidated code, by flattening all the include statements (:r filename) inside the pre-deployment master script file.
   3. The deployment code block is the code generated based on the model diff
   4. The post-deployment code block, contains the consolidated code, by flattening all the include statements (:r filename) inside the post-deployment master script file.
   5. The refactoring code block is maintaining the __RefactorLog table to indicate that a certain refactoring operation has been deployed to this instance.

Now that we better understand the order in which operations take place, we can conclude that “pre-deployment scripts can never change the outcome of a deployment”, and that we really need a pre-pre-deployment script that runs before the model comparison is taking place in order to influence the outcome of the comparison and therefore the outcome of the deployment engine.

GertD @ www.DBProj.com (aetk39ypwb)

Comments

• Anonymous
  August 14, 2012
  "we really need a pre-pre-deployment script that runs before the model comparison is taking place in order to influence the outcome of the comparison and therefore the outcome of the deployment engine." Hi Gert, Any chance that that will ever make it into SSDT? Cheers Jamie

• Anonymous
  January 09, 2013
  I strongly second the notion of a pre-comparison script step.