Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

DZone 's Guide to

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

Let's look at some data and schema comparison tools for MS SQL server databases.

· Database Zone ·
Free Resource

With the growth of MS SQL Server instances, the need to synchronize database data and schemas is also increasing.

Previously, we reviewed the dbForge Schema Compare for SQL Server tool, which allows you to synchronize database schemas and the dbForge Data Compare for SQL Server tool, which allows you to synchronize database data.

In this article, we aim to provide an example of using similar database compare tools developed by Quest Software. This companis one of the world leaders in producing various tools for database development and administration and DBMS (database management systems). As in the previous articles, we will take an SRV database as an example. It can be downloaded here.

Synchronizing Database Schema Using the ApexSQL Diff Tool

ApexSQL Diff tool from Quest Software integrates into SQL Server Management Studio. Therefore, to launch it after installing, you can just click the ApexSQL tab in the main menu and then select Launch in ApexSQL Diff submenu.

Fig.1 Launching ApexSQL Diff

In the window that appears, click the New button.

Fig. 2 Creating a new database schema comparison project

After that, the window with settings for database schema comparison will open. Now, fill in the necessary fields on the Data sources tab.

Fig. 3 Database schema comparison settings

Note the arrows in the lower part of the window. With their help, you can change the position of the settings left to right and right to left as well as copy the settings left to right and right to left.

Next, click the Options tab and select the necessary settings for database schema comparison (you can leave the settings unchanged).

Fig. 4 Database schema comparison options

Othe Advanced options tab, you can find the advanced options which you can changeif necessary.

On the Schema mapping tab, you can customize schema mapping.

Fig.5 Schema mapping

There are also a number of other advanced settings:

Fig.6 “Object filter” advanced settings

Fig.7 “Package” advanced settings

Fig.8 “Additional scripts” advanced settings

In the end, it is necessary to click the Compare button to launch the process of database schema compare. After you’ve done that, a window containing source database schema objects on the left and target database schema objects on the right will open.

Fig.9 Selecting objects for database schema synchronization

At the bottom of the window, you can find a code that defines the selected object. The differencebetween code fragments that define the same object in different databases are highlighted. Here, you need to select the necessary objects to synchronize database schemas. After that, you need to click the Synchronize button.

This will open the settings window for database synchronization.

On the Synchronization direction tab, you can set the synchronization direction. Click the Next button after that.

Fig.10. Synchronization direction tab

The dependencies tab shows dependencies.

Fig. 11 Dependencies tab

On the Output options tab, you can select Create synchronization script.

Fig. 12 Output options tab

On the Summary and warnings tab, you can find summary information on the selected synchronization settings. To start creating a script you need to click Create script button.

Fig. 13 Summary information for database schema synchronization

In the end, the schema synchronization script will be displayed:

Fig.14 Generated database schema synchronization script

Now let's take a closer look at the menu bar of the database schema synchronization project:

Fig.15 View tab

The View tab contains various settings for displaying window components.

Fig.16 Resources tab

The Resources tab contains information about the product and links to the documentation and user support center.

Now, let’s consider the Home tab.
The scheme comparison can be performed in SSIS package:

Fig.17 Selecting schema comparison in SSIS-package

You can save the project as an automated script.

Fig.18. Saving a project as an automated script

It is also possible to generate comparison reports in different formats:

Fig.19 Export options

The ApexSQL Diff tool can also work independently without integration into SQL Server Management Studio.

Synchronizing Data Using the Apexsql Data Diff Tool

ApexSQL Data Diff tool from the Quest Software company integrates into SQL Server Management Studio. Therefore, to launch it after installing, you can just click the ApexSQL tab in the main menu and then select Launch in ApexSQL Data Diff submenu.

Fig. 20 Launching ApexSQL Data Diff

In the window that opens, click the New button.

Fig. 21 Creating a new project for database data comparison

After that, the window with settings for database data comparison will open. Now, fill in the necessary fields on the Data sources tab.

Fig. 22. Database data comparison settings

Note the arrows in the lower part of the window. With their help, you can change the position of the settings left to right and right to left as well as copy the settings left to right and right to left.

Next, click the Options tab and select the necessary settings for database data comparison (you can leave the settings unchanged).

Fig. 23 Database data comparison options

On the Advanced options tab, you can find the advanced options that can be changed if necessary. They are similar to those given above for database schema comparison. Note that Object mapping tab is added:

Fig. 24 Object mapping


Fig. 24 Schema mapping

On this tab, tables, columns, and their contents are mapped for comparison.

Oconfiguring the settings, you need to click the Compare button to start the process of data comparison:

Fig. 25 Launching the database data comparison process

At the end of the process, the data difference window will appear.

	Fig. 26 “Package” advanced settings

A list of tables containing records that differ is located in the center of the window, the differences for a selected table are displayed below. The data from the source are displayed on the left and the data from the target are displayed on the right.

We’ll consider the main window later in this article, and now you need to check off the tables and rows to be synchronized on the left. Then, click the Synchronize button. This will open the settings window for database synchronization.

On the Synchronization directiotab, you can set the synchronization direction. Click the Next button after that.

Fig. 27 Synchronization direction tab

On the Output options tab, you can select Create synchronization script.

Fig. 28 Output options tab

On the Summary and warnings tab, you can find summary information on the selected synchronization settings. To start creating a script, you need to click Create script button.

Image title

Fig. 29 Summary information for database data synchronization

In the end, the data synchronization script will be displayed:

Fig. 30 Generated database data synchronization script

Now let's take a closer look at the menubar of the database data synchronization project, namely the main menu:

Fig. 31 View tab

The View tab contains various settings for displaying window components.

Fig. 32 Resources tab

The Resources tab contains information about the product and links to the documentation and user support center.

Now, let’s consider the Home tab.

The project can be saved as an automated script.

Fig. 33 Saving a project as an automated script

It is also possible to generate comparison reports in different formats:

Fig. 34 Export options

The ApexSQL Data Diff tool can also work independently, without integration into SQL Server Management Studio.

Price Comparison With the Similar Products

Product

Company

1  license 

10 licenses

Discounted price for buying two products

ApexSQL Diff

Quest Software

$599

$359

Discount is grantedindividually 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

The official ApexSQL Diff site provides a detailed comparison of its solution with the SQL Compare tool. Similarly, for the ApexSQL Data Diff tool, a comparison with SQL Data Compare is provided.

Conclusion

The popular software solutionsApexSQL Diff and ApexSQL Data Diff for synchronizing database schemas and data were considered.

A brief comparison of the price and functionality of these products with similar Redgate and Devart products was also made.

In the results of the review, we come to the conclusion that ApexSQL Diff and ApexSQL Data Diff products from Quest Software comprise a great choice in price and functionality.

However, the basic necessary functionality is also sufficiently present in Devart products: dbForge Schema Compare for SQL Server and dbForge Data Compare for SQL Server. These products can be purchased substantially cheaper than their ApexSQL Diff and ApexSQL Data Diff counterparts from Quest Software.

References

    1. https://www.apexsql.com/sql-tools-diff.aspx

    2. https://www.apexsql.com/sql-tools-datadiff.aspx

    3. https://www.devart.com/dbforge/sql/datacompare/

    4. https://www.devart.com/dbforge/sql/schemacompare/

    5. https://github.com/jobgemws/Projects-MS-SQL-Server-DBA/tree/master/SRV

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

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}