Synchronizing Data in SQL Server

We looked at why exactly change synchronization is important and how to synchronize SQL Server databases by using Schema Compare Tool in my previous article Synchronizing MS SQL Server Databases. Feel free to check it out before reading further if you want to know more on the topic.

In this article, I’ll give an example of how to synchronize SQL Server data changes between servers with the help ofdbForgeData Compare for SQL Server.

Synchronizing SQL Server Data

To synchronize SQL Server data, we’ll use the Data Comparison Tool embedded in Devart’s dbForge Studio for SQL Server. To make things simple, we’ll be working with a source server and a target server.

We’ll also be utilizing the SRV database created for MS SQL Server management purposes. You can easily download its source code as it is distributed completely free for any purposes.

Establishing New Connection

After opening dbForge Studio, go to the «Database Sync» tab and create a new connection by clicking «New Connection»:

Fig.1. The «Database Sync» tab

In the Database Connection Properties window, enter all information necessary to connect to the MSSQLServerinstance (the source server). When all of the fields are filled in, click “Test Connection”:

Fig.2. Creating a new connection

You will see the following dialog box once the connection is established:

Fig.3. Connection test result

Click “OK” in this dialog box and then once again in the connection settings window.

Now, you will be able to see the new connection:

Fig.4. Selecting the new connection

Now, we’ll need to connect all necessary MS SQL Server instances in a similar way (in this particular example, we’ll need to create a connection for the target server).

Setting up the Data Comparison Process

When this is done, click «NewDataComparison» to configure the database comparison process on the source server and the target server:

Fig.5.Selecting New Data Comparison

The data comparison settings window will appear.

As opposed to schema synchronization, the data synchronization process requires you to follow the configuration steps in the right order. However, you can go to any of the configuration steps at any time by clicking the corresponding element on the left side of the window.

At any stage of the process, you can save the current settings as a .bat file by clicking «Save Command Line» in the bottom left corner of the window.

On the «Source and Target» tab, navigate to the Source pane and set the following parameters:

1. Type
2. Connection
3. Source database

On the Target pane, you will need to set the following parameters:

1. Type
2. Connection
3. target database

Keep in mind that database is not the only type you can choose — scripts folder and backup copy are also available. However, in our particular case, we’ll choose the database type.

When all necessary settings are selected, click «Next»:

Fig.6. Data comparison configuration — «Source and Target» pane

In the «Options» tab, you can set various parameters to your liking or leave them at default values:

Fig.7. Data comparison configuration — «Options» tab

In the «Mapping» tab, you will see a list of tables scheduled for data synchronization. Tables lacking a primary key will be marked with an exclamation point. These tables will need to be mapped manually. To do this, click ‘•••’ next to the table:

Fig.8. Data comparison configuration — «Mapping» tab

The mapping window will be opened:

Fig.9. Column Mapping

You can return to any of the previous steps if this is needed.

To start the data comparison process, click «Compare»:

Fig.10. Starting the data comparison process

The data comparison configuration window will be closed, and a window showing the comparison progress will appear:

Fig.11. Data comparison in the process

When the process is finished, look at the data comparison window. You can change the comparison settings by clicking «Edit Comparison» in the window’s top left corner. Next to«Edit Comparison», there is a circular arrow icon — it’s a refresh button that starts the data comparison process again when pressed. Slightly below, you can see all previously-registered servers:

Fig.12. Data comparison window(1)

In the “File” sub-menu, you can save the comparison settings as a .dcomp file.

Data Synchronization Process

Now, let’s look at the window’s central section. Here, you will need to ensure that all necessary objects are included into the synchronization process — you can do it by using the check-boxes next to the object names. The source objects are located at the left side, and the target objects at the right side:

Fig.13. Data comparison window(2)

In this section, the following information is displayed:

1) Data of the rows that will be inserted:

Fig.14. Data comparison window (3)

2) For the rows that will be updated — the corresponding row comparison:

Fig.15. Data comparison window(4)

3) Data of the rows that will be deleted:

Fig.16. Data comparison window(5)

In the lower half of the window, you can select which specific rows should be updated, if this is needed. By default, all rows are selected:

Fig.17. Data comparison window(6)

Also, with the filters available just above the data table, you can switch between the rows that will be inserted, updated or deleted:

Fig.18. Data comparison window(7)

You can use the Visible Columns menu to control what columns and fields will be shown in the data table:

Fig.19. Visible Columns

By default, all columns are selected.

Generating a Synchronization Script

To start the data synchronization process, click one of the buttons highlighted on the screenshot:

Fig.20. Starting the process of data synchronization configuration

In the «Output» tab, we’ll need to specify how the synchronization process will be carried out. In most cases, you'll need to either open the script directly in the internal editor or to save it to a file. In this particular case, we’ll go with the first option. It is strongly recommended to follow the configuration steps sequentially in the correct order:

Fig.21. Data synchronization parameters — «Output» tab

In the «Options» tab, you can set various synchronization parameters.

In most cases, all options from the «Database backup» group should be unchecked.

By default, the «Use a single transaction» and «Set transaction isolation level to SERIALIZABLE» options from the «Transactions» group are enabled. This helps avoid cases in which changes are applied only partially. In other words, when these options are enabled, the changes will either be applied fully or not applied at all:

Fig.22. Data synchronization parameters — «Options» tab

In the «Summary» tab, the synchronization settings are summarized. You can go back to any of the previous steps if this is needed.

You can also save the synchronization settings as a .bat file — to do this, click «SaveCommandLine» in the bottom left corner of the screen.

To start generating the synchronization script, click «Synchronize»:

Fig.23. Data synchronization parameters — «Summary» tab

When generated, the script will be opened in a new window:

Fig.24. The first part of the generated script

Fig.25. The second part of the generated script

This script will transfer data changes from the source to the target. You can either use it directly on the target server or save it as a file for later use.

When the synchronization process is finished, the objects you selected earlier will no longer be visible in the data comparison window.

Comparing Data and Schema in MS SSMS

In addition to dbForge Studio for SQL Server itself, we can use dbForge Compare Bundle for SQL Server to compare data and database schemas. This tool can be used as an add-in for SQL Server Management Studio (SSMS). Let’s take a look at how you can do it.

Fig.26. SSMS drop-down menu

Right-click the needed database and choose whether to compare data or schemas. Next, set the selected database either as the source or as the target. In the same way, you will need to select a second database as the target or the source, respectively.

When the source and target are specified, click the green arrow icon situated at the center of the screen to configure the synchronization process:

Fig.27. Setting up the source and the target

Depending on what you chose in earlier steps, either Data Synchronization Wizard or Schema Synchronization Wizard will be opened. From that point on, you will be free to configure and start the synchronization process as described previously in this article.

Conclusion

The example we looked at shows that with dbForge Data Compare for SQL Server, you can easily generate a database synchronization script. When compared to SSDT and SSIS, this tool is quicker and simpler to use as the configuration process requires fewer clicks.

Also, dbForge Compare Bundle for SQL Server can be used as a data and schema synchronization add-in for SQL Server Management Studio.

References:

• https://www.devart.com/dbforge/sql/datacompare/
• https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-data-tools/hh272690(v=vs.103)
• https://github.com/jobgemws/Projects-MS-SQL-Server-DBA/tree/master/SRV