Product articles SQL Compare Database Builds and Deployments
Questions about Comparing Databases…

Questions about Comparing Databases with SQL Compare that you were Too Shy to Ask

Phil Factor offers straightforward answers to tricky SQL Compare questions.

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.

    1. “Why would I need to compare databases?”
    2. “I just want to check that two databases are identical, as part of a scripted process.”
    3. “I’ve often heard colleagues refer to SQL Compare as a “database synchronization” tool. Is that right?”
    4. “I’m told that SQL Compare can’t run some types of deployment. Why not?”
    5. “Is it easy to roll-back a deployment if it all goes wrong?”
    6. “What is a SQL Compare Snapshot, and why would it be useful?”
    7. “How can we check whether all the current work on the shared development database is in source control?”
    8. “Can SQL Compare update all development databases with just our latest ‘utility’ functions and procedures?”
    9. “How do I use SQL Compare to create a build script from a database?”
    10. “Can SQL Compare maintain my database source as a single-file build script?”
    11. “My database source is currently a single-file build script, but I would much prefer it as an object level directory.”
    12. “I’m required to make a report of all changes in a release. How do I do this?”
    13. “How does logging work in SQL Compare? All our automated deployment processes have to be logged.”

“Why would I need to compare databases?”

You usually compare two different versions of the same database to see the differences between the objects in each one. You can also just compare parts of a database such as a group of schemas, or perhaps a type of object such as the tables or routines in a database. SQL Compare aims to make this analogous to, and as easy as, comparing the contents of two files.

You might need to compare a production database with the released version to check that changes are properly controlled, or use it on a development database for various necessary chores such as checking for dependencies, or comparing versions to make sure that there are no changes in the objects such as tables or table-valued functions on which the view you’re working on depends.

Once you have the facility to compare databases, the many uses for development seem to leap out at you. SQL Compare goes several steps further than this, and allows you to make changes to one of the databases you are comparing to make the metadata of all or part of the database the same as the other, while preserving its existing data. The closer the databases are, structurally, the easier that this ‘deploy’ stage will be. If there are any uncertainties, you can assist the process with supplementary hand-cut scripts.

SQL Compare uses a very broad definition of a database. You can use a real database, a Redgate snapshot, a script directory, or a SQL Source Control or SQL Change Automation ‘project’ as either a source or target in a comparison and deployment. This gives you an easy way of managing source control in SQL Server database development, because you can use a source-controlled scripts directory as a ‘target’. You can also quickly see the differences between different versions or releases of a database just by comparing a database that you’re working on with a target snapshot or scripts directory, rather than having to create a live database. You can even compare two versions that are purely represented by snapshots or script directories.

“I just want to check that two databases are identical, as part of a scripted process.”

Sure. This sort of check is often run prior to a deployment to ensure that a target is really at the version that it claims to be. This also means that you can compare a Redgate snapshot, script folder or SCA project. If, for example, you have a deployment script that changes a target database at one version to a different version, then you often need to check that the target is really at that version if the script is to work reliably. To do this, you can compare the target to the source at the current version, whether it is represented by a script folder or snapshot or database.

From the SQL Compare command line, you just specify the source and target, any filters and options you require in case some types of difference are allowable, and the /Assertidentical switch. SQL Compare will then return an exit code of 0 if the objects being compared are identical, or otherwise will return exit code 79.

“I’ve often heard colleagues refer to SQL Compare as a “database synchronization” tool. Is that right?”

Not exactly. A true synchronization process would have to update both databases so that each had the latest version of every object, but nobody seems to want that! I’m not sure how a synchronization would ever deal with the deletion of a database object since a true synchronization doesn’t have a source and target, just equal partners.

SQL Compare could be regarded as a 1-way synchronization tool, in that it ‘synchronizes’ the metadata of the target with that of the source, while preserving the existing data on the target database.

Nowadays, however, the term used in SQL Compare is ‘deploy’ rather than ‘synchronize’

“I’m told that SQL Compare can’t run some types of deployment. Why not?”

To be more precise, some deployments aren’t possible without some additional manual scripting. SQL Compare’s auto-generated script will preserve data wherever possible, and issue warnings if it can’t. These arise mainly when you’re deploying changes that involve radical refactoring of the data model, and SQL Compare cannot work out how to migrate the existing data from the old table design to the new one.

Rather than risk getting it wrong or losing data, it stops any automated migration so you can assist with hand-cut code. There are two ways of dealing with this. Either, you can add supplementary pre- or post-deployment scripts to your scripts folder in source control, or you can alter the migration script generated by SQL Compare. The best approach varies according to how you do your development work and releases. I don’t know of any database deployment problem that can’t be solved by one of the two approaches.

“Is it easy to roll-back a deployment if it all goes wrong?”

That depends. It is often a good precaution to do a SQL Compare Snapshot of the target before a deployment if you are not certain of the version of the target, because you then have a good copy of its state before the deployment. You can, if the worst comes to the worst, do a reverse deployment using that snapshot as the source and the broken target as the target. If you prefer, you can, before you execute the deployment script, run the comparison ‘backwards’, reversing source and target, and have this in reserve to roll back the deployment. This assumes that the migration is a simple one without any extra hand-cut scripts that have been added to assure the data migration where the table design has radically changed. In staging, you are unlikely to use SQL Compare to generate a script. You are likely to already have a well- tested script that deploys a database from one version to another, rather than to need to generate a script at the point of deployment to production. The same would be true of a rollback script.

“What is a SQL Compare Snapshot, and why would it be useful?”

To compare databases, SQL Compare first makes an object model of the database. A ‘Snapshot’ is essentially a model of the database schema saved to file. It is useful because it is a read-only file-based package that represents the database and it can be used instead of a real database, in the comparison. It has many uses in development where one would otherwise need an extra copy of the development database.

“How can we check whether all the current work on the shared development database is in source control?”

You should have the source code for the shared database in source control, as one or more build scripts. If you used a Redgate tool to save your source as a scripts folder of object-level CREATE scripts, it will include in the root directory a RedGateDatabaseInfo.xml file. This will make comparisons easy, because SQL Compare will know about the intended server and database settings such as the collation of the database and the SQL Server version on which it runs. You simply make the shared database the ‘source’ (left-hand side) and script folder the ‘target’, in the project window, deploy any differences it detects to the object-level script directory and then commit the changed files to source control.

If, however, all you have in source control is a single build script, such as generated by SSMS, then you can still do it, because SQL Compare can be persuaded to compare build scripts with a database. However, you might need to twiddle a few knobs, such as selecting an option or two when doing the comparisons.

If your build script has ALTER statements in it or tries to create the database, these will generate warnings, but the comparison can still be made. When you try to compare the shared database (source) to the build script (target), you may find that it lists tables as being different when they are the same. Check by clicking on them and you may find that it is comparing different collations. If so, just go to the project options and click on ‘ignore collations’. You can do likewise for other differences you’re not interested in such as in CHECK constraints.

When finally, you are seeing the real, significant differences, this will tell you what hasn’t been saved to your source control system. If you need to add some or all the unsaved objects, you just select them and deploy to the script folder and they will be added to the original build script.

“Can SQL Compare update all development databases with just our latest ‘utility’ functions and procedures?”

Yes, good idea. I assume that you keep them in a Utils schema in all your development databases and don’t really want them saved in the source control code for the database.

This means specifically using a SQL Compare filter to exclude the Utils schema from comparisons, for any development scripting work; otherwise they’d overwrite any new releases you do of these utility objects. It also means including that schema but only that schema via a filter whenever you want to update the Utils schema of all the development databases on a new build or existing database.

“How do I use SQL Compare to create a build script from a database?”

You compare your database as a source with an ’empty’ target database. In the command-line SQL Compare, you specify the target with the /empty2 switch. This will make a script that creates the source database schema. You specify the script file to create in the /ScriptFile parameter. This will work as a build script even though it is really a way of synchronizing an empty database with your source. Note that your source needn’t be a database but can be a Redgate snapshot, script folder or a SQL Source Control or SQL Change Automation project.

You will need to make sure that your options and filters in SQL Compare are set to allow the entire database to be built. The settings are kept with the project in the UI and it is easy to reuse a project, forgetting that it has ‘temporary’ options and filters set in it that were accidentally saved. I’ve found you need to be especially careful of this when using XML files on the command line.

“Can SQL Compare maintain my database source as a single-file build script?”

If your scripts folder has a single file build-script in it, then SQL Compare will read it and update it as such, without breaking it into object-level files. SQL Compare used to have to support a lot of developers who liked to work that way. Some RDBMSs in the early nineties were slow to introduce the ALTER DATABASE syntax, so developers found it the quickest way to work at certain phases of a database project.

Of course, many developers nowadays aren’t working that way, but instead prefer to use schema-based or object-level build scripts. Some developers prefer to use migration scripts that alter existing objects where possible to avoid database builds. However, maintaining a single build script is still a quick and practical way of working with small databases and schemas, so SQL Compare is happy to support it.

“My database source is currently a single-file build script, but I would much prefer it as an object level directory.”

A single-file build script will have the database objects in the correct dependency order, so is likely to create the database successfully just by executing it. It will, if it has been created ‘by hand’, will be richly ornamented with comments and explanations.

If you compare this build script file (source) to an empty Scripts directory (target), SQL Compare will create a subdirectory for each type of object and place the build script for each object of that type in it. However, these individual files will no longer be commented and documented.

SQL Compare will also create in the target folder a RedGateDatabaseInfo.xml file that documents the important database properties such as collation and server version.

“I’m required to make a report of all changes in a release. How do I do this?”

This sort of report can be derived from whatever source control system that you are using, but it is geared specifically to the needs of developers and is often not particularly intuitive. If you use SQL Compare to prepare the script that does the deployment of the release, then you can instead get SQL Compare to generate a report of all the changes, as part of the process.

You can have the report in a variety of formats, including xml, Excel, HTML, or the ‘classic’ side-by-side report. In command-line SQL Compare, this is done with:

“How does logging work in SQL Compare? All our automated deployment processes have to be logged.”

Yes, logging is a common requirement for any automated process, so that it can be run overnight, and then the team check the logs for anything untoward, such as warnings or unusual messages.

In SQL Compare, there are two different levels of log. If all you need is a record of the deployment having succeeded, you can persuade SQL Compare to add a short batch to the end of the deployment script that writes to the SQL Server Log. This can be viewed by any log viewer but is provided mainly in order for SQL Monitor to detect the deployment and mark it on its activity graph. To do this you either click on ‘Enable SQL Monitor Integration’ in the GUI, or un-set the Command line option: NoDeploymentLogging (Alias: ndl).

More usefully for your purposes, you can control the logging that is done in SQL Compare command line by using the /LogLevel switch, the levels being none, error, warning and verbose. This will create a log file with the minimum log level that you specify.

Log files collect information about the application while it is running a task and are written to %LocalAppData%\Red Gate\Logs. When running the Compare command line in a Docker container, the log files are in /logs.

 

Tools in this post

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more