SQL Server database comparison tools: a case study

Learn how dbForge Schema Compare and Data Compare for SQL Server can assist you in the challenging process of schema comparison and what benefits it can provide.

Business challenge

A widely recognized retailer decided to refine its old-established customer management system to increase sales by converting more website visitors into customers. One of the solutions was to create accounts for potential customers and offer a range of customized services.

The retailer's production database contained a Sales schema with the Customers table holding a list of regular customers. The retailer used a staging database to add an Accounts table and a foreign key constraint to the Customers table, which referenced the AccountID field of the Accounts table. After applying the changes, the retailer tried to synchronize the staging and production databases.

The synchronization failed as the foreign key of the Customers table could not be created, because the Accounts table had been moved to the Sales database without data. The retailer looked for a reliable tool to quickly complete that task without any errors and efforts.

This case study is slightly simplified to illustrate how effectively the Devart tools can address a similar problem. See the script sample for details

Solution

Devart offers an efficient bundle of solutions for such cases. dbForge Schema Compare for SQL Server and dbForge Data Compare for SQL Server are specifically designed for non-trivial schema and data comparison and synchronization tasks. These tools successfully accomplished the required synchronization and performed far better than the tool used by the retailer previously.

The retailer used dbForge Schema Compare for SQL Server to compare the staging database Sales_2 and the production database Sales_1. The feature-rich schema comparison wizard helped compare the selected schemas. The retailer used the option "Ignore foreign keys" during comparison and synchronization.

The comparison results showed that the Customers tables are identical in both schemas, whereas the Accounts table exists only in the Sales_2 table.

After that, the retailer used dbForge Data Compare for SQL Server to synchronize the data in the compared tables. The convenient data comparison wizard allowed to select and compare the required databases in no time. Moreover, the retailer was delighted to find multiple essential comparison options. After synchronization, the Accounts table was created in the Sales_1 database.

The data comparison results were automatically grouped by type, which significantly simplified their analysis. The data synchronization wizard ensured error-free and quick data synchronization while offering backup of the target database, as well as other useful features to deliver the result required by the retailer.

Eventually, the retailer returned to the comparison results in dbForge Schema Compare and compared the schemas once again, this time having turned off the "Ignore foreign keys" option selected for the first comparison. The foreign key differences were then shown for the compared Customers tables.

Benefits

dbForge Schema Compare and Data Compare help synchronize schemas and their data, keeping referential integrity. They successfully completed the task and delivered a number of benefits:

• Less time spent on comparison and synchronization
• Deeper insights into schema and data comparison thanks to intuitive user interfaces
• Safe synchronization with an ability to backup a target database
• A wide range of additional options to get customized results

dbForge Compare Bundle for SQL Server

Devart made it more convenient for you and combined Data Compare and Schema Compare for SQL Server in a single bundle. It will save you hours of work comparing and synchronizing SQL Server databases. What is more, dbForge Compare Bundle offers a significant discount if compared with purchasing each tool separately! Buy dbForge Compare Bundle for SQL Server now and double your success!