SQL SERVER – How to Survive on Free Tools for Month – Database Comparison Done Easily with ApexSQL Diff

ApexSQL Diff is a SQL Server database comparison software with a free Community version and Windows Azure SQL database support. To compare Windows Azure SQL databases in ApexSQL Diff selects “Database” as the data source type and a destination. Select the SQL Server Authentication and type the SQL Azure server name, user and password. Select databases from the list of available databases and click the Compare button.

The differences will be shown in the main grid. There are several columns containing information: Source Schema, Source Object, Destination Schema, Destination Object, and Status. Any of those columns can be used for sorting the results, just by clicking on them. You can see that you have only the objects that are different, identical, or located on one source (Not Equal, Missing, Added, and Equal).

For more advanced sorting, there is a Multi column sort option in the View menu. It adds more sorting options and allows you to select up to four columns by which the results are going to be sorted.

Apart from that option, in the View menu, there are several buttons which can be used to adjust the way the results are shown. These buttons are separated into three groups. The first group is for setting an alignment, which can be left or centered (default). The next group of buttons is used for grouping the comparison results. The results can be grouped by different type, by object type, or no grouping at all. Grouping the results is a very good option, because the user can easily separate the needed results, or for instance, create a clearer report. There are two more buttons, Collapse group and Expand group, which are only useful if you choose any grouping. They allow the user to automatically collapse of expand all selected groups in the Result grid.

The last button in the View menu is Auto size columns, which is used to auto adjust the width of the columns.

When you select an object from the Results grid, differences between their scripts can be seen in the Script difference pane. This pane consists of two parts. On the left side, the source code is shown, and on the right side, the destination code is shown. When you scroll any side, they will both scroll together so you can easily see the differences in code between them. All lines of script that differ, are highlighted, so they stand out from the rest of the code, which makes them easier to find.

There are several options for Script difference pane, which can be accessed from the right-click menu. There is an option for displaying line numbers so that users can quickly find a certain line of code that they need. Enabling View whitespace shows all formatting symbols used in the code, new line, tab, etc. Enable syntax option is selected by default. It shows all SQL reserved words in different colors, to separate them from the others in the code. Finally, there is an option to select different font sizes for displaying code in this pane. The default setting is Medium, but it can be changed to Large or Small.

To see more information on object differences, check the Differences by type pane, where you can easily see what the types of differences in the selected object are. This feature is very useful, because you don’t have to search the code to see what the type of difference is there.

In the Object Filter pane, you can select which objects are going to be compared. All object types are shown in a very detailed list with checkboxes, so you can choose the ones you want to compare only. More common objects are segregated from the less common ones.

To synchronize changes between databases select the objects for comparison in the main grid and click the Synchronize button.

You’ll be prompted with the Synchronization Wizard. By clicking the Reverse button you can reverse the direction of the synchronization.

Press the Next button and choose whether to include dependencies. You can see all the actions to be made in the process of synchronization.

In the output options you can choose to save the script to a file, or open it in an editor and review.

After reviewing you can execute the script.

ApexSQL Diff also has a reporting feature to document changes and generate comprehensive reports.

Note: This blog is based on ApexSQL Database Comparison, download it and play along with is this blog post. If you want to find out more I suggest you visit SQL change management category on their Solution Center.

Reference: Pinal Dave (https://blog.sqlauthority.com)