Phil Factor

23 September 2019

• SQL Compare
• Database Builds and Deployments

Automating Schema Comparisons with SQL Compare Projects and PowerShell

Your PowerShell script for automating database comparison and deployment is running smoothly, and your instinct is to ‘leave well alone’. However, you continually need to delve into the script to tweak comparison settings or add a new command line switch. This article proposes a way to avoid this using SQL Compare projects.

Database development requires a great deal of work to keep all the required databases up to the current version. If you are doing a daily build, then that requires a daily chore. When you have a process, such as a database deployment, that is well established using SQL Compare’s GUI, you will want to replace it with a script. Without some forethought, and perhaps some cunning, these scripts can get quite complicated, and so take quite a bit of development work. Therefore, once they are working well, the instinct is to leave them well-alone. This is a noble aim, but a lot of the changes are forced on us, because of the many ways that you can configure a comparison and synchronization. If you need to make a change, and you use the obvious approach of command line switches in your script, you face the task of rummaging about in a long script to change command line switches and lists of options. It is possible to make a mistake.

There are ways of avoiding these minor tweaks easily, by using the SQL Compare command-line together with the GUI, so that they share and use project files to store configuration information. This makes scripting a lot simpler.

Scripting schema comparisons and deployments

You have several alternatives for scripting SQL Compare. You are most likely to choose to use either the old-fashioned command line or PowerShell.

• With the command line, you can use SQL Compare command line in the conventional way, or use an existing project file, or you can get it to take its commands, switches and options from an XML file.
• With PowerShell, you can use SQL Compare with the switches as you would at the command prompt or batch file, use an existing project file, you can ‘splat’ them (pass parameters as hashtables, to the PowerShell cmdlets), or you can use an XML file.

The easiest of all these methods to use is the project file. You can define and refine it in the SQL Compare GUI and be confident that if you save it to the correct shared file location, it can also be used by the automated process. This will all work well, if you can set up the access permissions properly. This is very practical. You can use your stored defaults, the password is encrypted, and the file contains everything you need. Please check the license terms for automating processes with the command line, as they differ from those for the GUI.

Saving schema comparison settings using SQL Compare project files

When the SQL Compare GUI performs a comparison, such as between two databases, it creates a projects, stored as a data file with the .scp filetype. It is just XML. The SQL Compare command line can read this file an act on it, although you must first make sure that the objects you want to compare have been specified in the project. If you just create a project, without explicitly selecting the objects that you want to compare, and then save it and then try to use it in the command line, it won’t work properly; it will report only on the identical objects.

Instead, in the SQL Compare GUI, create a new project, select your source and target, and then explicitly select the objects you want to include in the comparison. If you want to compare all objects, just “select all” in the main grid.

Save the project, and the command line will now work with this project file.

PowerShell automation of schema comparison using project files

Using a project file in a script has value even if you are just doing a single and comparatively simple process. The process and the GUI share the project file. All the fiddly work of honing the comparisons or synchronization work is done using the GUI that you know, and which is designed specifically for the task. The logic required to run the code is left to PowerShell, perhaps as part of a workflow or run regularly on a scheduler.

Where the advantage of a project file really kicks in is when you are having to run a process such as getting a snapshot, doing comparisons, checking for drift or updating to a larger pool of databases, each of which may have slightly different requirements.

To manage this sort of requirement, we create several SQL Compare project files using SQL Compare GUI and share them all in a single directory. Each project file represents a SQL Compare operation that our automated process will need to execute serially. You may, for example, need to update five test machines with the latest build, from source. First, you need to create the build and then synchronize the five test machines. We do the build with a project file, probably using PowerShell. If it is successful and passes the ‘sanity’ integration tests, we deploy it to the five test machines. We’ll do this whole task using a PowerShell process that we can put on the Windows Scheduler.

Naturally, this is likely to be a varying population with different, changing requirements and you don’t want to mess with the script at all. Instead, in the synchronization phase, we create a pool of project files, one for each server that we want to update.

Executing them is very simple. Before you start any PowerShell operations, it pays to create an alias for SQL Compare command line.

Once you’ve done this, the actual operation can be reduced to a one-liner…

…but we’ll put in a check for the directory

This is fine to get started. You may need to perform operations in a certain order, in which case you would create filenames that will sort in the order you wish and add a sort-object clause into the pipeline. I would soon want to be able to report what is happening, and the success or otherwise of the operation performed by the project. To start with, we can easily read the XML project file in PowerShell to work out what the task is.

So now we get output specifying the type and location of the source and target. For example, here the source and target were live databases:

Comparing source Live database MyTestServer.MyTestDatabase to Live database MyDevServer\SQLEXPRESS.MyDevDatabase

This is better, but we still need to add the actual reporting…

This same method could be used for other tasks too. What if you have several servers that you need to check regularly to make sure that they haven’t drifted? You can set up a project for each server, each with an /assertidentical switch, which compares a SQL Compare snapshot of what’s in source for the deployed version of the database, with the live database.

Or, maybe you need to do a regular backup of changes made on all the developer machines just in case, before an update? You can have a set of projects that compare each live developer database to the build from which it originated, and write off the synch scripts to a directory, before deploying a new build to everyone

In short, with this type of script, you can keep things up to date with the changing population of servers merely by adding or removing project files or tweaking the existing ones. What I hope I’ve provided is something that you can develop into a script that would be useful to you. There is plenty that you can add to make sure it is robust. You’ll need to set up some sort of logging so that it doesn’t all just go to a screen that, in an unattended process, isn’t going to get read. Also, we need to check the value returned by the execution of the command line SQL Compare and report errors accordingly. I’ve shown how to do all this my article, A Start with Automating Database Configuration Management.

Conclusion

When I’ve got a PowerShell process running well, and it is doing what I want, I like to leave it alone. The problem is that every change really requires regression testing, and that can take a lot of time to set up. For a script to just carry on working, it needs to hold as little data as possible and report what happens when it runs, so that even if it runs on a scheduler, it is possible to see exactly what has happened. With a lot of deployment tasks, this minimalist way of using projects to work SQL Compare command line works well.