Automatically comparing and synchronizing data changes in a SQL Server database

For the second use case, when only data changes are made to the source control repository, a combination of 3^rd party applications – ApexSQL Build and ApexSQL Data Diff, can be used to perform this Sync step. ApexSQL Build can be used to build a database from the source control repository along with its static data, while ApexSQL Data Diff can be used to synchronize data from the built database to a QA database.

To synchronize data changes, using the above-mentioned tools, the following steps should be taken:

1. Build a new database from the source control repository and add static data using ApexSQL Build. Find out more about it from the article on this link, under the Adding static data part

2. Synchronize static data from the built database from the source control repository with a QA database:

   1. Run ApexSQL Data Diff

   2. Set the same data sources as in the same step for ApexSQL Diff

   3. Under the Options tab, check the following options to avoid any errors during the data synchronization process:

      

Options explanation:

• Disable foreign keys– Disables foreign keys at the table level, to avoid referential integrity errors, and re-enables after the rows have been synchronized

• Disable DML triggers– DML triggers that may cause issues on insert/updating/deleting rows, during the synchronization process, are disabled in a destination data source, and re-enabled upon synchronization completion

• Disable check constraints– Avoids errors due to the failed check constraints by disabling them

4. Click the Compare button

5. Data differences will be shown in the Results grid:

   

6. Check desired tables/rows for the synchronization and click the Save button from the Home tab, so that all options set can be saved into the project file that will be used in the automation process

   

7. Click the Synchronize button on the Home tab to initiate the synchronization wizard

8. The synchronization process is the same as in ApexSQL Diff, except in ApexSQL Data Diff there is no step for including dependent database objects as it only synchronizes data

9. Once everything is set and reviewed, click the Synchronize button in the bottom-right corner of the Synchronization wizard to synchronize tables/rows in a destination database

Automating the static data synchronization process

As automating the process for adding static data for a built database from the source control repository is explained here, the part for data synchronization with a QA database should be added to the sequence, in order to have the whole process for the sync step when there are only data changes in the source control repository.

The ApexSQL Data Diff’s part for the data synchronization that should be added to the PowerShell project is:

Learn more about ApexSQL Data Diff’s CLI switches from here.