• SQL Compare
• Database Builds and Deployments

Using the SQL Compare command line with Argfiles

There are many ways of automating the many regular development chores you need to perform with SQL Compare, using PowerShell. The method that you choose depends on your requirements. This article aims to point out some of the advantages of using XML to pass the detailed instructions to SQL Compare, when you run it from the command line interface (CLI).

Passing parameters to SQL Compare CLI

SQL Compare CLI allows you to pass parameters from an XML argument file (argfile), or using a project file, as well as the more usual approach of using switches in the tail of the command line. This is useful if you require a lot of parameters but can’t generate the project file from the GUI.

There are several reasons for using XML argfiles to pass in parameters. It is not a good idea to generate a project file automatically, whereas the XML argfile is supported. This means that you can create an XML argfile for each task, from scratch, and execute them all on a schedule, maybe. You could also make a generic task with all the many details that SQL Compare sometimes requires and just fill in the details that change, such as the server, database or credentials, leaving all the other options, switches, and so on, intact. I give a run-through of all these in my article Comparing and Deploying SQL Server Databases using the SQL Compare Command Line on Linux or Windows.

We can summarize some of the advantages and disadvantages of these approaches.

Method	Advantages	Disadvantages
Project file	Good integration with the GUI Passwords encrypted Simply edited within the GUI Supports options not documented in the other techniques	Requires a file for each database for each operation. Can’t change parameters Proprietary undocumented format
XML File	Easily created and modified in PowerShell Saves a lot of typing at the CLI Allows use of all characters in regex expressions. Easily transformed into other formats using XSLT	No integration with the GUI Passwords stored in plain text
Switches in the command tail	Plenty of examples around, versatile	Can get long and complicated, difficult to type in Passwords in plain text. Some characters used in Regex expressions such as pipe (|) cannot be used.
‘Splatted’ (in PowerShell)	Easy to see your parameters	SQL Compare only accepts splatting if the switch has a parameter

Getting started with the XML approach, using PowerShell

Using an XML argfile to specify the arguments for the command line interface is especially useful when there is a lot of repetition, or if you are using a lot of command line options. Here is a simple XML argument file (argfile) for SQL Compare:

And here is a more complex one:

Having saved all these settings in an XML argfile, you can then execute it from Bash, PowerShell or the command prompt:

sqlcompare /Argfile:MyXMLfilename.xml

If you do this, the only other command-line switches you can add are /verbose or /quiet. The rest must be in the XML argfile. This gives us problems with sensitive information such as passwords which we can’t store in the XML argfile and which we can’t then pass to the CLI separately. I’ll show you how to get around this in PowerShell, shortly, but it remains a problem if you use the command prompt or Bash.

Specifying switches

Switches can be included with their switch name, in long or short form, as the name, and their value as the XML value. If you have several values, such as allowed in the /options switch, you would separate them with commas:

<options>n,oc,t</options>

The easiest way of creating the more complex argfile keys that represent switches is to check out the equivalent SQL Compare project file. I have no idea why the XML structure and keys are rather different between the two, as they do an almost parallel job. That said, most of the interesting and complicated keys are the same and you can cut and paste between them. This allows you to do the heavy lifting in the GUI, save a project file and pick out the bits you need to represent the switches that you require.

Creating Argfiles from a parameter list in a hashtable

For me, the easiest way of creating these XML argfiles in PowerShell is by putting the parameters into a hash table and passing it to a helper function to translate it to the correct XML format. It cuts out a lot of the irritating mistakes of crafting XML by hand.

Simple Argfiles for common comparison tasks

Here are a few example argfiles, for various tasks. There will be some raised eyebrows among readers at the unencrypted passwords, but we’ll deal with that a little later.

Script out a new version of the script

Generate a build script

Report on any differences between two databases

Script out, and report on, just the differences between two databases

Create a snapshot from a database

Executing SQL Compare CLI with XML argfiles

The following 1-liner will execute all the XML argfiles in an ArgFile directory:

Generating argfiles on-the-fly

What if you haven’t got any argfiles? We can generate them on the fly! Once an argfile is there, you can use the one-liner above every time you want to re-execute it.

Here is our first, simple version, which generates, on-the-fly, the argfile for making a snapshot of each supplied database. Although it is fine, it has the unencrypted password problem, if you are using SQL Server authentication rather than Windows authentication, which is likely if you are using Linux or MacOS.

Suddenly, we now have lots of snapshots, and a way of comparing live databases with snapshots to allow us to determine what’s changed and to maybe then allow use to save these changes.

The problem of storing passwords

As mentioned earlier, many readers will be raising eyebrows about putting unencrypted passwords in files; this is always a bad idea. OK, you save these in your user area so that you have the measure of protection afforded by NTFS access control. However, passwords must also be encrypted.

In fact, if you use SQL Server authentication at all, then your SQL Compare project files should also be stored your user area (in PowerShell, “${env:temp}” refers to the temporary directory within your user area). This is because, although the passwords are encrypted in project files, it is done in a way that the encrypted password can be used by anyone, whatever their Windows/Linux identity, and they can paste into their project files to access the database with SQL Compare.

To store credentials, in PowerShell, Microsoft recommends that you use Import-CliXml and Export-CliXml. The Export-Clixml cmdlet encrypts credential objects by using the Windows Data Protection API. The encryption ensures that the contents of the credential object can be decrypted only by your user account and on only that computer. The exported CLIXML argfile can’t be used on a different computer or by a different user on that computer. See Securely Store Credentials on Disk and Secure Password with PowerShell: Encrypting Credentials.

Here is an example of how to store passwords on disk. I’m just wanting to list all the details about my databases on a server and I want a routine that will do this safely, regardless of whether I am using Windows Authentication or SQL Server authentication:

The problem with SQL Compare argfiles is that the command line interface reads them directly from disk by the CLI, and we aren’t allowed to pass sensitive information such as passwords separately, as parameters. This means that the problem of storing passwords in plain text in the argfile would have to be fixed within the product.

The solution for storing passwords in Argfiles

For a quick fix to work around this, and to provide a solution for existing users of SQL Compare, we have the awkwardness of adding the password ‘on the fly’.

Let’s start again with those argfiles. We now aren’t going to put passwords into them. If they have a username, then we need to add an appropriate password on the fly, to create a temporary version of the XML argfile that we can then pass on to SQL Compare. It could be for either username1 or username2, in which it is either password1 or password2.

Now, we must create a helper function to get the password. The first time you run this function with any user and server, it will elicit from you the password and you’ll have to type it in. From then on, it will get it from the secure store.

Now we are back in business. Sadly, it is a bit more complicated in the code but that doesn’t slow things down at all. We pick up each argfile in turn, peer into it to see if it requires passwords and if so, insert them. We copy each to a new temporary file and pass it to SQL Compare. Once it has used it, we hurriedly delete it!

Conclusion

What I like most about using argfiles to pass parameters to SQL Compare is that you can do so much by gathering into a directory all the argfiles you need for a particular task, and then pass each argfile to SQL Compare, in turn. It cuts out a lot of scripting and means that you can modify the overall task by adding, modifying or deleting the argfiles. Unlike project files, these XML argfiles are easily modified by a script or in a text editor.

If you choose to use argfiles, and use PowerShell, you will need to get around the problem of unencrypted passwords, as I’ve demonstrated.

Tools in this post

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more