Guest Blogger:
Feodor Georgiev

This is a guest post from Feodor Georgiev. Feodor has been working with SQL Server since 2002, specializing in database performance tuning, documentation, and scalability management. He also works as project leader and mentor on SQL Server and Business Intelligence projects on Microsoft-based solutions.

When he's not busy with his DBA work, keeping up with the latest SQL Server tricks, or sharing tips on forums, he writes articles on SQLConcept.com.

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.

SQL Compare command line script saved as a .bat file

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.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in Blog

Where’s the database in DevOps?

At the end of 2015, we interviewed James Betteley, Head of Education at DevOpsGuys about the role of the database in DevOps. As we get ready to welcome James back for our DevOps 101: An Introduction t...

Also in Redgate products

Faster SQL Server troubleshooting using SQL Prompt snippets

A good DBA will automate repetitive tasks, but sometimes you need to troubleshoot a problem 'live', with people standing over you, and the pressure on. This is something that can't be automated. SQL P...

Also about SQL Compare

How to build multiple database versions from the same source: object filters

I've worked with several companies who maintain databases in multiple data centers, each one holding the data for a particular region, and each database serving multiple applications.

Over time, for ...