SQL Compare 6.2

For DBAs and database developers in busy development environments, keeping track of code or schema changes as they progress from development, to testing and staging, and then to production can be tedious and time-consuming. Tracking changes is especially complex in organizations with multiple sites or servers. Fortunately, Red Gate Software’s SQL Compare 6.2 addresses database differencing and synchronization with aplomb, by making it possible to see which differences exist. You can also selectively remove those differences by synchronizing schema and code between databases with the click of a few buttons.

SQL Compare is easy to install and use. You use a wizard to create a comparison project, then you specify the location or connection information for the two databases that you want to compare. SQL Compare then scripts the objects in both databases and compares them. It sorts comparison results into one of three logical groupings: objects that exist in both databases but are different, objects that exist in only one of the databases, and objects that are identical in both databases. You can expand or collapse these groups to review differences. You can even group, or sort, your objects by object type. SQL Compare includes an array of filters that you can use to select the objects whose differences you want to view. It also provides the ability to let you ignore certain comparison differences (such as comments and white space or even indexes and constraints), which makes it easy to fine-tune each comparison project.

Click any object in the SQL Compare window to display creation scripts for that object in the SQL Differences panel, which is docked, by default, at the bottom of the screen. However, you can undock it and display it on a second monitor. The SQL Differences panel shows a side-by-side comparison of object scripts and highlights where lines don’t match up. It also groups different types of scripts for each object (such as tables, constraints, sprocs, udfs, and so on), which makes it easy to inspect differences at a glance. With tables, for example, SQL Compare groups scripts into collapsible regions for Columns, Constraints and Indexes, and Full Text Information. SQL Compare does more than just compare tables: It provides support for every kind of object found in SQL Server, including users and roles and all of the objects supported by SQL Server 2005 such as partition schemes, certificates, and CLR assemblies.

Synchronization, which makes SQL Compare an incredible product, is easy to start. Select the objects in the SQL Compare window that you want to synchronize, and click Synchronization Wizard. In the wizard, specify the direction in which you want your changes to flow (e.g., should objects in database A look like objects in database B, or vice versa?) and then review any potential warnings or dependencies. You can specify whether you want to execute the changes immediately or export them to a T-SQL script to manually implement later. SQL Compare creates a transactionally safe script that won’t negatively affect your database if a problem with underlying data results in an error. If you execute the changes immediately, SQL Compare gives you the option to compare databases again after the synchronization script has been fired to make sure that targeted objects have been correctly synchronized.

In addition to providing comparison functionality for live databases, SQL Compare lets you make comparisons between snapshots (i.e., persisted copies of database objects made previously by SQL Compare) and folders full of raw scripts. SQL Compare Pro Edition offers integration with source control, making it possible to compare live databases with their “controlled” counterparts. A command-line version of SQL Compare Pro is available for advanced users.

You can evaluate SQL Compare by downloading a fully-functional 14-day trial version. If you’re a database professional who has to deal with lots of schema changes between different versions (development, test, staging, or production) of databases, then SQL Compare is one tool that you really can’t afford to be without.