Product articles SQL Compare Database Builds and Deployments
Using Custom Deployment Scripts with…

Using Custom Deployment Scripts with SQL Compare or SQL Change Automation

Phil Factor describes how custom pre- and post-deployment scripts work, when doing state-based database deployments with SQL Compare or SQL Change Automation, and how you might use them to, for example, add a version number to the target database, specify its database settings, or stuff data into some tables.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

When you are using the so-called ‘state-based’ method of database deployment, you’ll need to devise some additional custom scripts to get around some of the trickier deployment problems. For example, if you’re using SQL Compare or SQL Change Automation (SCA) to synchronize a script folder containing object-level scripts with a target database that holds data, you might need some custom scripts to control movement of data between the current and new version of a table.

Both SQL Compare and SCA place these scripts either at the beginning or the end of the synchronization script that it generates, before it is executed.

What are custom deployment scripts good for?

Most often, you’ll turn to a custom deployment script when you need to introduce code to deal with an awkward change from one version of a database to another. If, for example, you are changing tables in a version of a database that is already stocked with data, you will occasionally need to ‘snatch the reigns’ from SQL Compare to ensure that the old data all moves to the correct place in the improved design. It will often happen with a table rename or table split. Sometimes a single column that contains data that isn’t ‘atomic’, such as a comma-delimited list, XML or JSON, will need to be distributed into normalized columns.

You may also have a problem with dependencies, changing database properties, adding scheduled tasks, adding database version numbers and so on. At other times, you might also need to change database-level settings, manage role memberships, check for the existence of the correct static data, or create or alter SQL Server Agent jobs. All these matters can be dealt with by a custom deployment script

Deploying from a script directory

SQL Compare compares two databases, the source and the target, and automatically generates a single synchronization script that will modify the target schema so that it matches the source schema. When you are using a scripts directory as a source, SQL Compare allows you to attach extra SQL Scripts onto the beginning or the end of the synchronization script that it generates.

If your source is a scripts directory taken from your version control system, then SQL Compare allows pre- and post-deployment scripts, but you can have just one of each. SQL Compare, both UI and command line, allow them from version 13.4.7, or SQL Change Automation from version 3.0.4.

The scripts must be contained in a directory called Custom Scripts, within subdirectories called Post-Deployment or Pre-Deployment.

Both post-deployment scripts and pre-deployment scripts are outside the transaction within which the auto-generated portion of the script runs, so you need to add your own transaction handling, and manage any rollback, within these custom deployment scripts.

Each script must follow certain simple conventions. The script will not abort execution on error, so it must catch every error and report it, while also setting NOEXEC to ON, if it is a ‘fatal’ error. If an error occurs before the post-deployment script, NOEXEC will be ON. Therefore, it will not execute. Never use the NOEXEC OFF command in your post-deployment script, because the synchronization script uses the NOEXEC ON switch to abort processing after any error. Bad things will happen if you unintentionally re-enable execution after an error

Each section of the script should have a PRINT statement at the start describing what it does, so that if there is an error then it is obvious what has caused the rollback.

Each script must be re-runnable and idempotent. It must be able to run in all intended target environments and must support any possible differences in database collation across your database environments.

How pre- and post-deployment scripts work

The contents of these custom scripts aren’t part of the build because they aren’t executed until after the comparison is done, and so they have no effect on the synchronization script that SQL Compare auto-generates. A pre-deployment script is used to alter the target database after the synchronization script is generated but before it is executed. A post-deployment script is used to alter the target after the synchronization script has been executed.

If, for example, you place in the Pre-Deployment subdirectory a custom script that includes the creation of a table, then this table will not be included in the comparison. SQL Compare runs the comparison, generates the synchronization script, executes the pre-deployment script on the target, creating the new table, then executes the synchronization script. If, on the other hand, you placed the same script in the Post-Deployment subdirectory of your Custom Scripts directory, then SQL Compare will create the new table after running its synchronization script.

There is no other way within SQL Compare to add migration logic. If you need to alter the target before the comparison is done, you must do it in a separate script before you run SQL Compare.

Pre-deployment scripts can be useful, for example, if you need to copy data out of a table in the target database that will be altered and save it in a temporary table. The table can then be changed in the synchronization script and, finally, the data can be re-inserted into the new table in a post-deployment script. You might also need to use a post-deployment script to ensure that certain reference or static data exists in a table.

SQL Compare does not consider server-scoped objects in its comparisons, so if you need to synchronize agent jobs between a source and target then you can do so in either a pre- or post- deployment script. Other tasks, such as checking that database settings are correct, must use the pre-deployment script because they can easily change the way that the subsequent scripts are executed. If, for example, collation is case-insensitive and the database is case-sensitive, the synchronization won’t work.

Post-deployment scripts can be used to apply the changes required to create specific variants of the database version. If, for example, you have different variants of a payroll database according to legislative area, they can be switched according to the required legislative area.

You may need to keep several variants of the same version of the database in trunk, using conditional switches to produce the correct variant (any accounting package, for example, is likely to have variants for each tax region). Although it is possible to run CREATE scripts, or ALTER scripts, conditionally, this makes source control over-complex, and makes synchronization from a script directory a minefield.

The best practice for this, I suggest, is to include all code in the version and use a feature toggle or feature switch, such as a value in a table or extended property, to implement the correct logic. This can be set in the post-deployment script merely by simple logic such as checking the name of the target database and switching accordingly. A ‘soft’ database switch or toggle held in a function or extended property allows all variants to be tested using the same deployment.

Quirks

There are some limitations to consider when using Pre & Post-Deployment scripts.

The do not support use of SQLCMD syntax and variables, unless you execute the synchronization script separately, outside SQL Compare, using SQLCMD or by using SSMS in SQLCMD mode.

If you opt to modify existing objects as part of these custom scripts, you will need to make sure that the SQL Compare engine leaves them alone. You will need to have the new version of the object source code in the main script directory, not the ‘Custom scripts’ directory, so you will need to tell SQL Compare not to create or change them as well, using SQL Compare filters or /Exclude switches to prevent the inclusion of those objects in the comparison.

If SQL Compare runs a comparison between source and target and finds they are identical, then it will not run the Post- and Pre- scripts because there will be no synchronization script to which to attach them.

Unless these scripts are thoroughly tested, errors will only be discovered once the deployment script is executed and then they will break the build which can cause problems. Allow time to test them thoroughly before use.

Source control and custom scripts

Post-deployment and pre-deployment scripts should almost always be kept in source control. Whatever objects they create or change, the state must be in source control. Normally, for a change such as a table-split, you need only add a SQL Compare Filter or /exclude switch to tell SQL Compare not to include the affected object in the comparison, because it is done in the accompanying post-deployment script. This means that source control can have the SQL DDL code for the changed objects without their presence interfering with a complicated data split. As described earlier, it is perfectly possible to use a pre-deployment script to save existing data in the target database from a table about to be altered into a temporary table before allowing the Synchronization script to make the rest of the changes except for the objects you specify for exclusion; and then a matching post-deployment script can read the temporary table and place the data in the correct location.

Both types of custom scripts, pre- and post-deployment, should be specific to a version. However, because they are idempotent, they should not normally hurt anything even if they are accidentally re-run. The script folder will reflect just one version so it should be easy to provide the correct custom scripts for the version.

The target of a database deployment cannot necessarily be guaranteed. If you don’t maintain a version number of the live database, then you will need to ensure that the custom scripts will work with all possible target versions that are ‘out in the wild’.

Examples

These shouldn’t be run as-is; you need, for example, to fill in the name of the database and version number, or else tweak the information as required. The database settings example could have subtle but wide-ranging effects on the hapless database on which you run it. They also don’t amount to a general recommendation on how to perform these tasks because your circumstances could well be different For example, the “stocking a table with data” example will be fine until you have thousands of rows, at which point you would be better off with a BULK INSERT. These examples are just intended to be illustrative, to show you the possibilities

Stocking a table with data

Imagine that you need to build a version of the antiquated PUBS database, including all the data. This script must be in the Post-deployment directory. Here is an example that ensured that the dbo.publishers table had nothing but the original data in it.

Database settings

These must be performed in the pre-deployment script. For a database to perform as you expect there are certain database property settings that are required. It is usually a good idea to check these settings on deployment as they can sometimes produce subtle errors. Collation, recovery model and compatibility level are obvious ones, but several others, such as auto-update statistics and auto-create statistics need to be checked. You’ll, of course, need to determine the correct settings for your databases; these are just examples of settings that a DBA might recommend.

Add version information

This can be done either as a pre- or post-deployment script. This example script merely inserts the version number and description as a JSON string. A much more extended version of this code that tracks versions and when they were applied is contained in this article Associating Data Directly with SQL Server Database Objects.

Conclusions

Pre- or Post-deployment scripts allow us to get over most problems faced by development teams who are deploying database code in a scripts directory via SQL Compare, while maintaining a single source of truth of the source code in version control.

Tools in this post

Redgate Deploy

Automate database deployments across teams and technologies

Find out more

SQL Change Automation

Automate your database changes with CI and automated deployment

Find out more

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more