29 September 2016

Guest post

This is a guest post from Feodor Georgiev. Feodor has worked with SQL Server for over 15 years, specializing in database architecture, Microsoft SQL Server data platform, data model design, database design, integration solutions, business intelligence, reporting, as well as performance optimization and systems scalability.

In the past 3 years, he has expanded his focus to coding in R for assignments relating to data analytics and data science. Outside of his day-to-day schedule, he blogs, shares tips on forums and writes articles on his Data Messenger blog.

29 September 2016

How to automate database synchronization using the SQL Compare command line

Demonstrating a one-click way to compare a source database to a target database, or a snapshot of it, and generate a script to synchronize them, as well as documentation describing the changes.

Guest post

This is a guest post from Feodor Georgiev. Feodor has worked with SQL Server for over 15 years, specializing in database architecture, Microsoft SQL Server data platform, data model design, database design, integration solutions, business intelligence, reporting, as well as performance optimization and systems scalability.

In the past 3 years, he has expanded his focus to coding in R for assignments relating to data analytics and data science. Outside of his day-to-day schedule, he blogs, shares tips on forums and writes articles on his Data Messenger blog.

As a database developer, I use SQL Compare extensively during the Proof-of-Concept (PoC) phase of my projects, when I’m trying out new ideas and developing code on my local machine. I need to start from an up-to-date database schema from the production database, and have a simple way to keep track of my database schema modifications, so I can easily outline what changes it would introduce in the current production environment.

SQL Compare makes it very easy to create a directory of object-level scripts from an existing database, and then keep the two in synchronization, as I described in a previous article.

This article describes how to make this task even easier using the command line utility of SQL Compare Pro. It will demonstrate how to compose a .bat file that, with one click, can compare a database, or a snapshot of it, in the production environment with the database in the local environment, and generate a script to synchronize them, as well as documentation to outline the changes.

This article is not intended as a tutorial on the various command line switches and arguments, but I do cover some of those I found useful when using the command line for the first time.

First steps with SQL Compare command line

SQL Compare’s command line interface allows access to all the functionality of the UI tool, and allows us to introduce automation and scheduling. For example, by invoking the command line from a batch file or PowerShell script, and then perhaps executing the script as a scheduled Agent job.

Where to find the command line executable

You can find the executable file, SQLCompare.exe, in the installation directory of SQL Compare. Simply open a command line and navigate to that directory, in my case as follows:

Reviewing command line parameters and documentation

From the installation directory, a good starting point is to bring up the sqlcompare help screen:

SQL Compare command line

Figure 1

A couple of interesting switches are /verbose and /html. The former enables more detailed information about the sqlcompare.exe options, and the latter outputs the Help documentation to HTML (although it would be nice if this HTML file were already in the installation directory of SQL Compare):

Executing the above command produces a very long string of HTML text in the console window, but if we use the /out switch, we can write the file directly to the disk system, as follows:

We can then open the file in a browser to see very nice documentation for the command line tool of SQL Compare:

SQL Compare command line

Figure 2

How to configure the command line utility to compare two databases

To compare two databases, we can use the following general syntax:

This command will simply compare the source and target databases. However, if we add the /sync parameter to the end the command, SQL Compare will attempt to synchronize the two database schemas. For any database object that exists in both databases but with differences, the object’s definition in the target will be altered to match its definition in the source. Any objects that exist in source but not in target will be created, and any that exist in target but not in source will be dropped.

In my case, I have two instances of SQL Server on my local machine; one default instance and one named instance called DW. Let’s say the default instance is the Production environment and the DW instance is my local development environment. The following code will generate a script that will apply any necessary changes to the target database, AdventureWorks2014PoC, on the DW instance, in order to synchronize it with the source AdventureWorks2014 database on the default instance:

If you prefer to run the script from PowerShell, it would look like this:

However, in either case, you will get the following error:

SQL-Compare-command-line-hintThis is because SQL Compare generates a synchronization script, which it wraps within a transaction, so that it can roll back the changes should something go wrong. However, SQL Server will not allow the creation of a full-index within a transaction, so SQL Compare will deploy the full text index changes after the transaction completes. If the initial transaction includes changes to objects that reference that index, the synchronization script may fail. In this case, the uspSearchCandidateResumes stored procedure uses the full text index on this table to search within the candidate resumes.

To get around this problem, SQL Compare has an /exclude parameter, which we can use to exclude the object that causes the error:

As can be seen, the uspSearchCandidateResume stored procedure can be excluded simply by adding the /exclude parameter:

This will complete successfully, after which you can simply re-run the command without the /exclude parameter. SQL Compare will then create the uspSearchCandidateResumes stored procedure in the target database, to bring the source and target fully into synchronization.

Create a .bat file for a one-click database synchronization

I would like to automate this synchronization process so I can run it on demand whenever I need to start over with development of my PoC. After all, this is what PoCs are for: to try out an idea and retreat quickly if it doesn’t work. Of course, a good idea might be to set up a compare script which “backs up” the PoC before the iteration. This can be done by scripting the PoC to a new folder before the compare from production occurs.

All you need to do to accomplish this is to create a .bat file containing the synchronization command:

If you perform an initial synchronization that includes the uspSearchCandidateResumes stored procedure, as described in the previous section, you won’t need to exclude it. Note that the path to the executable file is in quotation marks, since it contains spaces. Also, make sure the path is correct in your case. Simply create the file with the .bat extension and double-click on the file any time you want a fresh copy of the database schema.

How to revert the command line to make the destination into a source

Let’s suppose the PoC works out well, and now I would like to generate a synchronization script that, when executed, would update the production database with the required changes.

Again, this is a simple task. We simply reverse the order of the source and target databases, remove the /sync parameter, and use the /scriptfile parameter to specify where the script file has to be saved for later review.

Of course, I can then create another .bat file from this script, as described previously, so I can invoke the compare and generate the deployment script at any time.

Generate a report outlining the changes

A great way to obtain a report of the production database objects affected by the synchronization task is to use the /report (or /r) and /ReportType (or /rt) parameters for specifying the report location and report type, respectively. For example, in order to generate an interactive report for the synchronization task above, simply add the following parameters to the command:

It will generate a report that looks like this:

SQL Compare command line

Figure 3

A step further: using project specification to do command line compare

Sometimes, I need to set up synchronization projects that require more complex object filtering rules and make use of the other advanced project comparison options SQL Compare offers.

In such cases, I find it much easier to set up the comparison using the SQL Compare UI, save the project, and then use the project as a parameter from the command line. Again, once I have it working, I can save it as a .bat file and run it on demand.

One reason to do this is to take advantage of the UI for more complex configurations, but also to overcome the fact that Options are not stored within the project, but they can be configured via the command line and saved in the .bat file.

Let’s suppose I would like to use a project to compare the AdventureWorks2014 database to the AdventureWorks2014PoC database, but I want to ignore the indexes (this will ignore indexes unless they are unique or primary keys). Just follow these steps:

  1. Open the SQL Compare UI and start a new project
  2. Select the source and target databases to compare and hit Compare now
  3. From the Tools | Project Options menu, check the Ignore Indexes box and hit Compare now
  4. Click the top check box to include all objects (or selectively pick or exclude objects as required)
  5. Choose File | Save as to save the project.

We then simply use the project as a command line parameter:

Summary

This article describes how easy it is to automate the creation of automated database synchronization scripts, via the SQL Compare Pro command line. The rich toolset lets us specify logging and reporting options, and lets us control many other aspects of the database schema comparison.

If you’d like to read more articles by Feodor Georgiev, you can find him on Simple Talk, and at sqlconcept.com.

Tools in this post

SQL Compare

Compare SQL Server schemas and deploy differences fast.

Find out more

Share this post.

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

You may also like