Remote database synchronization using a database installer package

This article will cover remote database synchronization using a database comparison and synchronization tool that will create a standalone database installer to execute a script, to complete the a synchronization and update between two remote database


These days, there are still some SQL remote databases that don’t have access to internet or it’s a limited one, but remote database synchronization still must be done to obtain the latest changes. So, there has to be a way to get these SQL remote databases synchronized with latest schema changes that were made on the master SQL database.

In order to achieve remote database synchronization, the first step would be to create a database backup or a snapshot of the remote database. Next, a backup or snapshot should be sent to a coworker that has access to the master database. That coworker should compare the master database with a remote database backup or snapshot in order to create the update (synchronization) script. Also, that script needs to be packaged and sent back to a coworker on a remote SQL Server that will execute it and a remote database synchronization is finally completed with the latest schema changes.


In this article, the synchronization of specific objects on SQL remote database will be described by using ApexSQL Diff’s database installer package.

ApexSQL Diff is a third-party tool for comparison and synchronization of SQL Server schemas between databases, backups, script folders, ApexSQL snapshots and source control projects. Furthermore, it also supports remote databases synchronization, as it can package synchronization scripts in two ways as: C# solution and executable installer. The most important difference between these two ways of packaging is that executable installer is a built application (end product) which cannot be edited, while a C# solution is a code for building executable installer that can be edited and built in Visual Studio. On the other hand, the purpose for both of these installer packages is the same – schema synchronization. Learn more from the article on all about ApexSQL database installer packages.

In the following example, ApexSQL Diff’s database installer package will be used for fulfilling remote database synchronization. In other words, the package will be used for comparing remote database backup against the master SQL database, create and package the update script for specific objects, and executed on the remote SQL Server.

To do so, the following steps need to be done:

  1. Run ApexSQL Diff

  2. Click the New button in the Project management window:

    Create new or open existing remote database synchronization projects

  3. Under the first tab of the New project window – the Data sources tab, for the Source side:

    1. Choose SQL Server where the master database is located
    2. Choose the way of authentication
    3. Select the master database from the Database drop-down list

      Source data type and details for remote database synchronization

    4. For the Destination side under the Data sources tab:

    5. Select Backup data source type from the Destination drop-down list
    6. Click the Add button and provide a path where the received remote database backup is located and load it
    7. Destination data type and details

    8. Switch to the Options tab and in addition to the options that can ignore specific things during the comparison, the Synchronization options can be set as well to avoid any synchronization failure:

      Comparison and remote database synchronization options

    9. Once the source and destination data types are set and loaded, along with the synchronization options, expand the Advanced options in the left navigation bar and click the Package tab:

      Database installer package options

      Here, a couple of package options can be set to facilitate usage of database installer packages. For example, when there is a need to execute large number of schema changes, the Use compression option can be quite handy in reducing the size of the created database installer package.

      Also, the Error handling option can be very useful while executing database installer package by setting it to Abort, Ignore, or Ask for confirmation if any error is encountered during the execution.

      Package logging can be also set and a path to the log file can be customized based on user’s preference.

      The last, but not the least, a couple of Optional package information options can be set and provide more details along with the database installer package, such as: Author, Comments, Legal, Company, and Package name.

  4. After setting up the Package options, switch to the Object filter tab, in which specific objects for schema updating script can be included.

    By default, all object types and their objects are checked by default. So, in order to check only specific ones, uncheck All objects in the left tree view panel and then click on the object types to choose which specific objects should be included in the comparison and creating of the update (synchronization) script for database installer package:

    Filter database object types and specific objects

    To learn more about object filtering, check out the article on how to narrow schema comparison and synchronization to affected objects only.

  5. Once all pre-comparison options are set, click the Compare button in the bottom-right corner of the New project window:

    Source and destination data source types and details

  6. Upon the finished comparison process, the comparison results will be shown in the Results grid:

    Comparison results of the compared data sources

  7. By default, all objects will be checked for synchronization, but each one can be unchecked and therefore excluded from the resulting update (synchronization) script if it’s not needed to be updated.

  8. Once all needed objects are checked in the Results grid, click the Synchronize button from the Home tab to initiate the Synchronization wizard:
  9. Application ribbon with its tabs

  10. In the first step of the Synchronization wizard, the Synchronization direction is shown with all information about used data sources:

    Remote database synchronization direction step of the Synchronization wizard

    By default and in this example, the synchronization direction is set from left to right (source to destination), but it can be reverted with a click on the arrow button.

  11. In the second step, the Dependencies step, if there are any dependent objects that were not included in the Results grid, those will be included by default in order to prevent any script execution failure, but any individual dependent objects can be excluded:
  12. Remote database synchronization dependencies step of the Synchronization wizard

  13. In the third step, the Output options step, choose the Create an executable installer action from the Actions drop-down list:

    Remote database synchronization output options step of the Synchronization wizard

    Additionally, a couple of executable installer options can be set, such as:

    • Run executable now – it’s unchecked by default, as in most cases an executable installer will be run on a remote SQL database
    • Create a CLI executable file – it creates the CLI executable that can be run using the Command Prompt to automate the process further
    • Create a script file – it creates a script file that contains SQL code for updating (synchronizing) a remote database
    • Create a manifest file – it will create a .manifest file, which contains information about OS compatibility versions, for which this application is designed to work with, and it sets the Windows User Account Control level to allow the application to run without any additional permissions
    Quick tip icon

    Quick tip:

    When creating executable installer, it can be created and run without all three mentioned files

    • Indicate a default database – it sets a default database against the generated update (synchronization) script
    • Specify the Visual Studio version – it can be set based on the installed Visual Studio version on a machine where executable will be run

  14. In the last fourth step of the Synchronization wizard, the Summary and warnings step, if there are any warnings, those will be shown in the Warnings tab:

    Warnings tab under the Summary and warnings step of the Synchronization wizard

    Once switched on the Actions tab, all execution actions will be shown:

    Actions tab of the Summary and warnings step of the Synchronization wizard

    Actions can be grouped in three different ways and exported in six different output files, if needed.

  15. Once everything is reviewed, click the Create button in the bottom-right corner of the Synchronization wizard. The Save as dialog will be shown where a path and executable installer name can be provided:

    Save executable installer package dialog

    Once the Save button is clicked the information message will be shown about successful creating of an executable installer.

  16. The next step, after creating executable installer, is to send it to coworker via e-mail or USB stick, for example

  17. Once received and transferred to a remote SQL Server, run the executable installer and the following window will be shown:

    Remote database synchronization executable installer package window

    Similar to the Data sources tab in ApexSQL Diff, when database is chosen as a source or destination, here the same options can be set for server name (if there are more SQL Servers on remote machine), an authentication way or to change a database on which the executable installer will be run (if there are more remote databases on that remote machine that should be synchronized, but differently named than default one that was previously set)

  18. To further inspect the received executable installer, click the About window and in its dialog all information that were set through options in the Package tab will be shown:

    About window of the executable installer package

  19. If there is a need to change Error handling options, that were also set in the Package tab, click the Options button and change it based on preference:

    Error handling options of the executable installer package

  20. At the end, once all is reviewed and set, click the Run button and upon finished execution, the Results window will be shown:

    Remote database synchronization execution results of the executable installer package

Hopefully, this article should prove useful for your next remote database synchronization. Thanks for reading.

March 4, 2019