{{announcement.body}}
{{announcement.title}}

Data and Schema Comparison Tools for MS SQL Server Databases (Part 2)

DZone 's Guide to

Data and Schema Comparison Tools for MS SQL Server Databases (Part 2)

In this article, we aim to provide an example of using similar database compare tools developed by Redgate.

· Database Zone ·
Free Resource

You can find Part 1 here.

With the rise of MS SQL Server usage, the need for reliable tools for database data and schema synchronization is also strengthening.

In our previous articles, we reviewed the Schema Compare for SQL Server tool, which allows comparing and synchronizing SQL Server database schemas quickly and seamlessly and the dbForge Data Compare for SQL Server tool, which helps compare SQL database data.

In this article, we aim to provide an example of using similar database compare tools developed by Redgate. Redgate is one of the leading providers of software for database development and administration and DBMS (database management systems). As in the previous reviews, we will take an SRV database as an example. It can be downloaded here.

Synchronizing Database Schemas Using SQL Compare Tool

The SQL Compare tool from Redgate is supplied as a separate utility. Therefore, after installing it, you just need to open a new application and on the Data sources tab and select database synchronization on both sides:

Fig.1 Setting schema comparison on the Data sources tab

In this tool, as in those that were reviewed earlier, the source is on the left and the target is on the right.

Next, you need to fill in the required fields as shown in the picture below.

Fig.2 Filling in the required fields on the Data sources tab

On the Table mapping tab, the mapping tables are displayed.

Fig. 3 Setting schema comparison on the Table mapping tab

Clicking the Full option for the required table allows checking and changing column mapping of a selected table if necessary.

Fig. 4 Setting the column comparison for a selected table

On the Owner mapping tab, you can configure schema mapping.

Fig. 5 Setting schema mapping on the Owner mapping tab

On the Options tab, you can check and configure the advanced settings for database schema comparison.

Fig. 6 Setting schema comparison on the Options tab

Now you need to click the Compare now button at the bottom of the window (see the picture above). At the end of the process, you need to click OK button (see the picture below).

Fig. 7 Finishing schema comparison process

Next, a window displaying a list of differences will appear. At the bottom of the window, you can find a code defining a selected object. The differences will be highlighted.

Fig. 8 Viewing and selecting differences for schema synchronization

Now you need to check off the objects required for synchronization and click the Deploy button as shown in the picture below.

Fig. 9 Selecting changes for schema synchronization

After that, the settings window for database schema synchronization will appear. On the Deployment method tab, you can select the method of schema synchronization. Then click the Next button.

Fig. 10 Selecting the method of schema synchronization

In this way, we will get a ready-made script for schema synchronization between the selected databases, which can be applied to the required databases.

Fig. 11 Generated database schema synchronization script

Synchronizing Database Data Using the SQL Data Compare Tool

The SQL Data Compare tool is supplied as a separate utility. Therefore, after installing it, you just need to open a new application and on the Data sources tab, select database synchronization on both sides:

Fig. 12 Setting data comparison on the Data sources tab

In this tool, as in those that were reviewed in our previous articles, the source is on the left and the target is on the right.

Next, you need to fill in the required fields as shown in the picture below.

Fig. 13 Filling in the required fields on the Data sources tab

On the Table & views tab, the mapping tables and views are displayed.

Fig. 14 Setting data comparison on the Table & views tab

In this window, you can also click the ‘x columns’ option for the required table or view to check and change the column mapping of a selected table or view if needed.

Fig. 15 Setting data comparison for the columns of a selected table or view

On the Owner mapping tab, you can configure schema mapping for database objects.

Fig. 16 Setting data comparison on the Owner mapping tab

On the Options tab, you can view and configure the advanced settings for database data comparison.

Fig. 17 Setting data comparison on the Options tab

Now you need to click the Compare now button to start the process of comparing database data. At the end of the process, you need to click the blue OK button as shown in the picture below.

Fig. 18 Finishing the process of data comparison

In the end, a window displaying a list of differences will appear. At the bottom of the window, you can find the differences for a selected table or view. The differences will be highlighted.

Fig. 19 Viewing and selecting differences for data synchronization

As for the previous tool, you need to check off the required objects for synchronization and click the Deploy button as shown in the picture below.

Fig. 20 Selecting changes for data synchronization

The window with settings for database data synchronization will appear after that.

On the Deployment method tab, you can select a method of data synchronization. Then, click the Next button.

Fig. 21 Selecting a method of data synchronization

In this way, we will get a ready-made script for data synchronization between the selected databases, which can be applied to the required databases.

Fig. 22 Generated script for database data synchronization

Price Comparison With Similar Products

Product

Company

1

license

10 licenses

Discounted price for buying two products

ApexSQL Diff

Quest Software

$599

$359

Discount is granted individually and can be $539 for two products per license if you purchase 10 licenses.

ApexSQL Data Diff

$599

$359

SQL Compare

Redgate

$785

$785

Discount can be granted individually but on condition of purchasing more than 110 licenses

SQL Data Compare

$785

$785

dbForge Schema Compare for SQL Server

Devart

$300

$300

From $300 for 1 license if you purchase dbForge Compare Bundle

dbForge Data Compare for SQL Server

$300

$300

Conclusion

The popular software products SQL Compare and SQL Data Compare for synchronizing database schemas and data were considered. 

In the results of the review, we come to the conclusion that SQL Compare and SQL Data Compare products from Redgate comprise a great choice in functionality, but their price is also the highest out of the products analyzed.

Topics:
database ,database tools ,sql server ,sql ,comparison ,db comparison

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}