Database synchronization is an everyday necessity. You need it regularly when you have several similar databases that serve the same “user.”
The typical situation is, they must be identic, but you can’t grant it. For instance, the databases are administered by different teams that can update them independently. Or, there are identic databases that perform differently. So, you have to define why they differ.
The problem is, whenever you have to compare two tables for differences, the process is too complicated. Even if your database specialists develop a working method for one synchronization, any new requirement will cause a significant change. You’ll have to start the work from scratch. As a result, the SQL server table synchronization turns into an overcomplicated and time-consuming job. If this task is one-time, it becomes even worse, as it does not justify the effort.
Still, the SQL server sync task remains needed, and the need brings the supply. Instead of preparing long and complicated instructions, use the automated tool. First, it can be some built-in tool like SSIS.
Fig.1 Table data one-way synchronization package
The second option is to use third-party tools, like dbForge Data Compare for SQL Server.
Fig.2 Comparing table data in SSMS
In this article, we review the SQL server table synchronization process where you apply automated tools.
The Standard Way of the Database Comparison in SQL Server
The comparison starts with the source and target servers and databases. You configure them:
Fig.3 Setting up the source and the target
Set the column mapping for the table:
Fig.4 Column mapping
The “Columns details” section displays the referring information from two tables: the source database is on the left, and the target database is on the right.
Column mapping is especially useful id the column names are different in the source and the target. This way, we see data differences:
Fig.5 Data differences
Also, you configure the column visibility and sorting:
Fig.6 Column visibility and sorting settings
When you find all the differences, you can generate a script or perform the data synchronization.
Important! You need to make sure that the table definitions match before you compare or synchronize the data between two tables. You can do it with the standard methods, like Visual Studio or SSDT, or via 3rd-party tools.
To do this, create a new SQL Server Database project as follows:
Fig.7 Creating a new SQL Server Database project
Next, import the database:
Fig.8 Opening the database import window
A new window will open. In that window, you need to configure the connection to the necessary database.
After that, press Start to initiate the import process:
Fig.9 Configuring and starting the database import process.
In the result of the actions described above, you will see folders, subfolders, and the definitions of database objects in the project:
Fig.10 Project after import
To create a project and import the data for the second database, you should act in the same way.
To compare the database schemas, you only need two clicks:
- Right-click any of the two projects;
- Click “Schema Compare…” in the context menu.
Fig.11 Navigating to the database schema comparison window
It opens the database schema comparison window. There, you select the source and the target projects, and press Compare to start the comparison process:
Fig.12 Database schema comparison window
Using dbforge Schema Compare to Explore the SQL Difference between Two Tables
dbForge Schema Compare integrates with SSMS. This SQL compare tool is a dedicated solution that helps you to make the SQL database comparison and analyze the SQL data differences. Also, it allows you to synchronize SQL Server data easily with the help of its embedded wizard. By the way, to learn how to involve dbForge Schema Compare in the CI process, feel free to watch this video.
Fig.13 Comparing database schemas in SSMS
The process of the data comparison is the same as described above.
How to find difference between two tables in SQL Server?
- First of all, you configure the source and the target for schema comparison:
- Set the table mapping in the corresponding tab, if needed:
- Configure the field mapping in the table and select the columns for comparison
Like we have already noticed, the “Columns details” section shows the definition of information from two tables’ columns. The source database is on the left, and the target database is on the right. It matches the case when the columns’ names are different in the source and the target.
- Viewing the definition differences in the databases
After schemas are compared, you can select the necessary table and view the table definition differences in two databases:
Note the code at the bottom:
- On the bottom left, you get the definition code of the source database table;
- On the bottom right, you see the definition code of the target database.
Then you can generate the synchronization script for any schema table.
dbForge Schema Compare is a powerful and functional tool. You can use it to compare the databases and sync them. You can also automate the SQL server update tasks with the help of this solution. It is an easy and convenient way for any user to do the usually draining and over-complicated job.
