Phil Factor

17 November 2020

• SQL Compare
• Database Builds and Deployments

Maintaining a Database Build Script with SQL Compare

When I’m working with smaller databases, in lone-developer mode, I like to use an old-fashioned build script because it is quicker. These build scripts are documented and commented because, like most of humanity, I forget things. Database objects are, I hope, arranged in logical order. One advantage of a build script is that it is so much easier to edit a whole lot of objects together in a single script. You may, for example, need to alter views and functions, and perhaps user-defined data types, all in concert, to re-engineer a database quickly. At times like this, a single script is handy. There is nothing quite so satisfying for doing this as a successful global replace on an entire database. Another advantage is that you can devote the final part of the script to stocking the newly created database with sufficient test data to run unit tests. In a development team, it is sometimes possible to work with schema scripts, which are similar, but here you need to keep dependencies in check.

Let’s say you decide to make changes to a database, so you start from a well-documented build script at the correct version, and build a new copy of it as the dev database, complete with data, on the dev server. You might need to rebuild and run tests on the database several times in a session, after making global changes to the build script, such as a change to a datatype, or renaming a table object (view, table or table-valued function) and all its references.

Having completed all global changes, you put aside the build script and continue developing and testing the Dev database at object level with individual build scripts or perhaps a visual table builder, creating objects, making changes, deleting the stuff that accidentally gets into any dev build, and so on.

When everything is fixed and passes the unit tests, you save all your changes into source control, using SQL Compare, with the dev database, as source and the source control directory as target. This updates all the object scripts, but what about the build script? That also needs to be updated from the previous version because you’ve since used other scripts and tools to change the database. You can’t just generate a new build script from SQL Compare because you lose all the other logic, and all your documentation and comments. It is, however, possible, with a bit of care and stitching, to use SQL Compare to maintain this build script from the source control directory, or even, if you need to, directly from your database.

Using a build script in a comparison

When SQL Compare uses DDL code, rather than a database, as a source or target of a comparison, it is only interested in those statements that create objects such as CREATE, EXECUTE and ALTER. It is, after all, merely interested in comparing database objects. It will not consider in its comparison any additional statements that you might have added to manipulate data or provides conditional logic. The only way to get SQL Compare to execute this additional code, during the subsequent synchronization is to add it to pre- or post- deployment scripts.

If you add this sort of ‘extra’ code that to an object-level source, SQL Compare will simply ignore it. Well, in fact, it will protest when it finds it, but will then carry on regardless.

This is a useful feature of the tool, though it can cause confusion to users if they aren’t expecting it. The advantage is that SQL Compare will read an old-fashioned build script as a source, just as if it were a source control directory. This is handy for all sorts of reasons, the most obvious being that it can check that the build script reflects exactly the target database.

You can also use a build script as a target, comparing it with a database source. What happens when you then ask SQL Compare to ‘deploy the changes’ to the build-script-as-target is useful but slightly complicated. Basically, it works.

Updating a build script

Using an extended version of the Pubs database, called PubsBuild (described here), we’ll illustrate what happens when we make some changes to a development copy of the database and then compare the database, as source, with the original build script, as target.

If the change is an alteration to an object, then when you ask SQL Compare to deploy to it will make the changes ‘in situ’ to the build script. If you add new objects, that are not part of an existing table, then they are deployed a separate files, placed in the same directory as the build script, within a subdirectory according to the object type. It is fine for the tool to do this because it always parses every file in the directory except for the pre-and post-deployment scripts. It gives us a bit more work to do though.

To see how this works, we’ll execute the build script to create the development version of PubsBuild that is up to date with all checked-in changes for the current version. Then we’ll add a couple of views to the development database, to do reporting. You’ll find the source scripts for all this on GitHub.

We compare the PubsBuild database, as source, with the source directory containing the build script as its sole content, as the target.

SQL Compare will parse the script, clucking away at the ‘non-schema’ DML statements that it detects, but continuing nonetheless, if you hit the ‘Continue without resolving errors’ button.

SQL Compare notices the two additions. We’ve selected them as being suitable for adding to the source.

If you were to deploy these changes, and opt to Update the scripts folder, SQL Compare will create two new files in a subdirectory called Views. The original build file isn’t changed.

First, though, let’s also alter an existing object in PubsBuild, updating the Reptq1 stored procedure to correctly references the new Edition and Price tables. Now, when we rerun the comparison, and hit Deploy, SQL Compare tells us that it is altering the build script, as well as creating the two new files for the views:

We run the deployment to update the source folder and then change the name of the build script from HandcutBuildSource_1-2-3.sql to HandcutBuildSource_1-2-4.sql to signify the change.

All we must do now is to transfer the code for the two views into our preferred place in the build script and add the documentation. Then we clean up the directory, leaving just the build script.

We check the build script and lo! The code for the stored procedure has changed in line with what is in PubsBuild to reflect the fact we now have Price and Edition tables.

Pulling together what we’ve found

SQL Compare can read a build script that also has DML code in it, and even if it has all the data-import code. This allows us to use it as a SQL Compare source. We can also use a build script as a target and ‘deploy’ to it. SQL Compare deletes or updates objects within the build script, as it detects differences. It creates additional objects as separate objects because SQL Compare can’t always guess whereabouts in the script you wish to place the code to create the object. It plays safe by writing each newly created object into an object-level subdirectory. However, reading this code into the build script, wherever you want it placed, and deleting the separate object-level script, isn’t a great chore, and it is a great deal easier than any other way of updating a build script. You’ll need to add these objects to the tear-down section of your build code if you do things that way.

Conclusions

Database build scripts are a useful resource, especially in the early days of a database project. It makes it a lot easier to adopt source control early on in a project, and makes it much easier for a single developer to work on a database, especially if a lot of different objects need changing in synch, and if you like to ornament your code with plenty of comments and documentation. Once the chore of merging different branches becomes too much, it is better to move to an object-level source folder for source-control purposes, but then you have a problem in maintaining a version of the build that preserves all the documentation and comments. At this point, SQL Compare comes to the rescue.

A final note of caution: SQL Compare does not officially support using a hand-cut build script as a source so, while I’ve grown to love this feature, it’s undocumented and behavior in future versions might change.