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

Challenge

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.

Solution

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:

Run ApexSQL Diff

Click the New button in the Project management window:
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
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. 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:
8. 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:
  
  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.
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:

To learn more about object filtering, check out the article on how to narrow schema comparison and synchronization to affected objects only.
Once all pre-comparison options are set, click the Compare button in the bottom-right corner of the New project window:
Upon the finished comparison process, the comparison results will be shown in the Results grid:

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.

Once all needed objects are checked in the Results grid, click the Synchronize button from the Home tab to initiate the Synchronization wizard:

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

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.
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:

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

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:

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

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:

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

Actions can be grouped in three different ways and exported in six different output files, if needed.
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:

Once the Save button is clicked the information message will be shown about successful creating of an executable installer.
The next step, after creating executable installer, is to send it to coworker via e-mail or USB stick, for example

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

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)
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:
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:
At the end, once all is reviewed and set, click the Run button and upon finished execution, the Results window will be shown: