Feodor Georgiev

1 December 2016

3 Comments

• SQL Compare
• Database Builds and Deployments

Deploying schema changes to multiple databases using the SQL Compare command line

Sometimes, you need to synchronize multiple target databases with the same source database, or with the source code for that database, in version control. You may need to push out the schema changes to ten copies of the database on ten different servers, as part of a deployment, for example. Or there may be cases where, for test purposes, you need to synchronize multiple copies of the same database, each with different names, on the same server. You might even want to synchronize a whole list of databases, maybe differently-named, on several different instances.

Whereas the SQL Compare GUI allows us to compare and synchronize one source and one destination at a time, the SQL Compare Pro command line makes it possible to automate the process of synchronizing a source with a destination. By calling SQL Compare from a batch script, we can then make it work for whole lists of destinations, one after another.

Synchronizing databases from the command line: general syntax

The basic command line syntax for performing database comparison and synchronization looks as follows:

I described this syntax in my previous article, How to automate database synchronization using the SQL Compare command line. Briefly, it compares the source and target databases, and the use of the /sync parameter means that SQL Compare will generate a script to apply any schema changes to the target database, necessary to synchronize it with the source.

The parameter /include:Identical also allows us to avoid raising an error in cases where the source and target databases are identical.

Deploying to multiple databases on the same target server

In this example, I have two target databases, AdventureWorks2014_1 and AdventureWorks2014_2, on the same target instance (.\DW), both of which I need to synchronize with the source database, AdventureWorks2014, on my local instance.

The simplest way to do this is to write a script that deploys the source to each target, one after the other, from the command line, as shown in Listing 1. The command timeout /t -1 simply keeps open the command window until we press a key (otherwise, it closes immediately, regardless of success of failure).

Listing 1

This will be fine if you are typing using the terminal, but there are problems if the script will be run several times unattended, such as by running it within a batch (.bat) file. As I described in my previous article, you’ll then need logging and error-handling (I’ll add that in Listing 4).

Also, this brute force ‘one line per database comparison’ approach gets more cumbersome and harder to manage and debug, the more target databases you have. So you will also need to find a way of executing SQL Compare in just one place in the script and turn the parameters you pass in into variables.

A simple approach is to loop through a list of target databases, as shown in Listing 2.

Listing 2

Save the script as a .bat file (in my case called simply compare.bat!) and run it from the command line. The output should look as shown in Figure 1.

Figure 1

This works reasonably well for multiple databases, since the list of the databases is easy to administer.

Deploying to multiple target databases on different servers

If we have a database with the same name on several different target instances, we simply modify Listing 2, above, to loop through server names instead of databases.

Listing 3

Of course, it gets a bit more complex if we have the destination databases on different instances and with different names. To deal with this, and to allow us to specify a different source database easily, we’ll use batch variables as well as loops.

While we’re about it, we’ll write in a log details of whether the synchronization went well, or we hit an error, as shown in Listing 4.

Listing 4

A few points of interest about this code, to help you navigate it:

• We would normally just read the destination instances and database names from a CSV file, but here we create a task file (called MyDatabases.txt) ‘on the fly’, in the same directory as the script file, and then read from it
  • The chevron, ‘>‘, means ‘create and write into this file’
  • The double chevron ‘>>‘ means ‘append to this file’
• %%g and %%h are the script variables, the first identifying the instance, the second the database, and so on. It is standard practice to start script variables at ‘g’ (a-to-e are reserved for drive letters).
• IF ERRORLEVEL 1 actually means ‘If error level is 1 or higher’
• If you don’t suppress ‘database identical’ error, using /include:Identical, then you will need to handle error level 63 in the error handling

Conclusion

In this article, we saw a way to compare and deploy changes from a single source to multiple databases. This can be quite useful for large environments, where keeping the schemas in sync rapidly becomes a tedious task. By using a fairly simple batch script, we can compare a single source to multiple destinations effortlessly.

If you’re not already using SQL Compare, download a fully-functional 14-day trial and discover how it lets you save time comparing and deploying SQL Server database schemas.