Product articles
SQL Compare
Schema Comparison Options
Fine-tuning Scripted Database…

Fine-tuning Scripted Database Comparisons with SQL Compare

When comparing databases, save your standard options and filters to a single project file, and then use PowerShell to script them out to the command line. This allows you to reuse the same project file across multiple comparison operations.

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.

From the GUI to command line automation

When using SQL Compare to compare two databases, most of the complexity is in specifying the options that describe how it should do the schema comparison, and the filters for objects that should be excluded or included.

If you want to script your database comparisons, the easiest approach is to start in the GUI. Do a few comparisons, making changes to the filters and options until you get them right. With any complex tool, this is far easier to do visually than in the underworld of the CLI interface. Having refined the way that comparisons are done, you simply save the results in a project file. When you need to do repeated operations, you just run the command-line version of SQL Compare, using either a batch job or a PowerShell script, supplying the project file as a parameter.

So far, this is easy, but if you need to compare a larger number of databases or perform a lot of different operations, it can all get a bit cumbersome. The problem is that the project file specifies the source and target data sources, as well as all the filters and options. If you need to compare different versions of the same database or do several different operations, such as saving script directories, reading snapshots and so on, then each operation would need a project file, each with its unique settings.

Imagine that you suddenly find that you need to change just one option. You’ll have lots of project files to alter, and each one would require the SQL Compare GUI. Project files can’t be altered directly: it isn’t supported. What you can do though is to read the settings from a project file.

The approach I suggest in this article is to use the SQL Compare GUI to set up a project file to store the standard filter and options settings for all the schema comparison operations you do on a database, rather than having a project file for every operation. The idea is that rather than have a project file for every operation, you get as close as possible to one project file per database.

When you are automating database comparisons, you then read the configuration from the project file for every process that uses the same configuration settings. I’ll supply PowerShell functions that will read all the option and filter settings from the project file, requiring you to specify only the required data sources for the comparison, and any other required parameter ‘switches’ like the report type. All this information is saved into an XML Argument file that is then passed directly to the SQL Compare command line for execution.

Saving and reusing SQL Compare configuration information

SQL Compare uses three types of XML file to record how a comparison should be done: a Project files (.scp). a filter file (.scpf) and an XML Argument file (.xml). SQL Compare uses and maintains only the first two, but the command-line can read all three.

Project files

A project file is the source for all the configuration information for a schema comparison. Amongst other factors, it specifies the following:

  • Data sources – identifies the source and target databases to be compared
  • Filters – to include or exclude certain classes of objects (like users, roles, DDL triggers), or even specific objects, from comparisons and deployments. These appear in the project file as a set of “include” switches and can be saved and shared separately. If you stick to excluding classes of objects only, rather than specifying objects by name, you can reuse of the same filter settings across any comparison projects involving the same database.
  • Options – specify details of how the comparison and deployment should be done. These appear in the project file as a comma-delimited <options> list and can’t be saved separately. Broadly there are two types of options:
    • Ignore options – ignore certain types of differences between objects during comparison and deployment, such as differences in collations when comparing character data, or differences in fill factor when comparing indexes and keys
    • Behavior options – control certain aspects of Compare behavior when running comparisons and deployments, such as whether to add object existence checks to deployment scripts.

Once you have a comparison configured exactly as you want it, in the GUI, you save the results in a project file (.scp). By default, they are stored in the SharedProjects directory of your SQL Compare directory (%USERPROFILE%\Documents\SQL Compare\SharedProjects).

When you run the command-line version of SQL Compare, you can supply the project file as a parameter. This will run the operation exactly as you specified it. It is a great way of doing the same operation repetitively.

Filters

In the SQL Compare GUI, you can use the left-hand Filter designer to configure the filter rules. All the filter settings will be saved automatically in the project file, but you can also use the filter pane to save them in their own ‘Filter’ file (.scpf), which you can edit subsequently. See Using Filters to Fine-tune Redgate Database Deployments for details.

Saving a filter

By default, the filter files are stored in the Filters directory of your SQL Compare directory (%USERPROFILE%\Documents\SQL Compare\Filters). While it’s probably best to generate them only via the SQL Compare GUI you can also, like any XML file, edit them with a text editor.

Once you’ve saved the file, you can supply it to SQL Compare command line session using the /filter switch. These files can also be shared with other Compare users, used in other Redgate tools, such as SQL Source Control, DLM Dashboard, and SQL Change Automation. The options can’t be saved or retrieved in the same way. If this were possible, then this would be a very short article.

Saving filters is important as they can be used in other Redgate tools such as SQL Change Automation or SQL Source Control. You can use them in automation processes by specifying the filter files within SQL Change Automation PowerShell cmdlets, such as invoke-DatabaseBuild, using the -FilterPath parameter. When you are using filters with SQL Change Automation migration projects, you can export the rules to a file called Filter.scpf within the root of the SQL Change Automation project folder, alongside the .sqlproj file. When SQL Change Automation is used to import new changes, the rules defined within the filter file will be applied: any objects that do not satisfy the rules will be excluded from the generated migrations, programmable objects and/or offline schema model.

Options

In the SQL Compare GUI, you use Tools | Project options dialog to specify how SQL Compare does a comparison. See Exploring the SQL Compare Options.

The options are saved in the individual project file but, unlike filters, collections of options do not have their own file format. You can share filters but not options. However, it is simple to extract them from project files as a list.

The following PowerShell snippet will list out the options as a comma-delimited list, from a file called Adventureworks.scp:

In the second example, I list out all the options for all the files I have in my shared project folder:

The simplest way, I think, of setting the options and filters for working with SQL Compare is to save them in a ‘general preferences’ project file. This project file can then be opened in the GUI, set to comparing a source to a target, be they databases, script folders, backup files, snapshots or whatever. Then save it for that precise task. Alternatively, you can start a new project in SQL Compare, and set up the options and filters as you wish, using your default options, and loading existing stored filter files as necessary to override the default settings.

If you are using SCA, you can specify options to the Cmdlets via the -SQLCompareOptions parameter. These options aren’t in a file but in a comma-delimited list. The first PowerShell fragment I show above will get them for you from a project file easily.

Using XML Argument files

Instead of using the project file on the command line there are, alternatively, advantages to passing in the configuration information as an XML Argfile, which is merely the command-line parameters or arguments expressed as an XML file. You just specify the one argument. It saves time and you can store them in directories so that they are all applied to SQL Compare in a batch process. Each XML file can express a single comparison. They can be generated by scripts. I go into the details about their use in Using the SQL Compare command line with Argfiles, and list the advantages and disadvantages.

Scripting using a project file

It is very quick for a script to go to the source of the configuration information, which is the project file. If it is more convenient to get filters from a filter file directly, that is possible, but options don’t have their own file. These are kept in SQL Compare GUI as default and custom options and placed in the project files that are saved by the GUI.

Script out configuration options for a schema comparison

This is most easily demonstrated in this script which includes a couple of helper functions to do the task. The filters and options are inserted afresh into an XML argument file every time it is run. This means that you can get the latest version of your option and filter preferences every time the SQL Compare script is executed.

There is a Write-FilterFileFromProjectFile function that takes a project file and saves the filter settings as a separate .scpf file. There is also a couple of helper functions. The (CreateArgsFileFromHashTable) function references the filter file and saves that and all the other configuration information from the project file into an XML Argument file. The SavedPassword helper function allows you to avoid with the problem of saving unencrypted passwords in the project file, if you use SQL authentication (see Using the SQL Compare command line with Argfiles).

Now, with these functions, we can use the project file of a SQL Compare project, for the AdventureWorks database, to provide the correct options and filters.

Some simple database comparisons using the same project file

To demonstrate the technique, let’s do two different operations that use the one project file. This project file has a filter to allow you to compare just one schema. Imagine, if you will, that you are a developer with responsibility for a particular schema in the database, and you wish to see differences in the schema between two different versions of the database.

The first example, ‘Comparing the same schema in two live databases’, will be a script that provides an on-screen comparison of two versions of the same database within a browser.

The second script, ‘Generating a single-schema build script’, uses the same project file. In this example, we are only doing single database. In reality, you’ll usually be doing these tasks for several databases, and I’ve shown a way of using several Argfiles. We can just generate an Argfile for each comparison task and execute the entire directory. Having stored the Argfiles in a directory we can apply each one to SQL Compare.

Comparing the same schema in two live databases

We’ll just do a simple comparison of two databases first.

Generating a single schema build script

If you are doing one of the many different types of comparison, your arguments have to be appropriate. I give a few examples in ‘Using the SQL Compare command line with Argfiles

As another example, we’ll generate a build script for just a single schema. We also want to do a build script that can be used in Flyway, without transactions. As this is going to be a regular operation, we need to automate it. We therefore do a trial run with SQL Compare GUI until we get it absolutely right and check the results with Flyway. Having done that we can carry on using the project as a script.

Generating a script would be something like …

Next steps

What we’ve done so far can be achieved far simpler with a project file, assuming you have domain authentication. It is only when we have a larger provisioning or scripting task than this that the solution we have here comes into its own. With this routine in place, you can do a whole batch of database comparisons and scripting by iterating through a large set of hash tables, probably stored on disc as JSON files or XML (I just write than and execute them as PowerShell files). You can also use several filters and options on the same source and target (as when scripting schemas independently, for example). By separating ‘what you do’ (the options and filters) from ‘what you do it with’ (servers, databases and credentials), we make it much easier to scale up the many database activities for which SQL Compare is so useful.

Conclusions

It might seem that I’m an automation freak, but it is rather more that I’m impatient to do real development work rather than spend a lot of time on repetitive chores. They’re important, of course, but only bearable if they’re scripted. By separating the configuration task, specifying what and how database comparisons and scripting is done, from the job of specifying the databases and servers that are the sources or targets of the comparison or scripting, it make a lot more tasks easy to automate and maintain. I don’t mind maintaining a scripting system if it can be done easily from the GUI. That just saves more time for the more creative work.

Tools in this post

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more